?
Solved

How to Count records between two datetimes?

Posted on 2008-10-10
8
Medium Priority
?
281 Views
Last Modified: 2012-05-05
Experts,

I have a table with two columns, id (varchar) and dtcol (datetime).

I'd like to count the number of rows between two dates.  I've tried this as follows:

SELECT COUNT(id) FROM Table WHERE `dtcol` BETWEEN DATETIME '2008-10-10 11:45:00' AND '2008-10-10 10:45:00';

This gives me a syntax error.  Please help me with the correct syntax!
0
Comment
Question by:mhouldridge
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22685685
what about this:
SELECT COUNT(id) FROM Table WHERE `dtcol` BETWEEN '2008-10-10 11:45:00' AND '2008-10-10 10:45:00';

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22685690
note: as the second time is before the first, you might get no records at all, actually...
0
 

Author Comment

by:mhouldridge
ID: 22685708
IS this correct:


SELECT COUNT(id) FROM Table  WHERE `dtcol` >= '2008-10-10 10:45:00' AND `dtcol` <= '2008-10-10 11:45:00'

Returns a result now.
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22685723
syntax is correct.
if it's semantically correct depends on what exactly you need to return.
I must guess that probably, you want to exclude the last value, so that running the query for the next "hour" will not return the same record again, if that was on exactly that time.

SELECT COUNT(id) FROM Table  WHERE `dtcol` >= '2008-10-10 10:45:00' AND `dtcol` < '2008-10-10 11:45:00'

Open in new window

0
 

Author Comment

by:mhouldridge
ID: 22685725
angelIII, no result was returned for your query - I've also added the first time before that last and this doesnt work.

I believe my current attempt is the correct method, although Im concerned that this isnt getting the rows between times..

0
 

Author Comment

by:mhouldridge
ID: 22685734
I'd like to get rows between two dates... seems pretty straight forward to me, although I can't get the BETWEEN TO WORK.

I'm concerned that my query...

SELECT COUNT(id) FROM Table  WHERE `dtcol` >= '2008-10-10 10:45:00' AND `dtcol` <= '2008-10-10 11:45:00'

.. is returning all values greater than '2008-10-10 10:45:00', and all values less than '2008-10-10 11:45:00', which is not what I want.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 22685748
>.. is returning all values greater than '2008-10-10 10:45:00', and all values less than '2008-10-10 11:45:00', which is not what I want.

which is exactly what the BETWEEN is doing.
the AND will ensure that BOTH conditions must be true.

what you "fear" is what would result from this query:


SELECT COUNT(id) 
FROM Table  
WHERE ( `dtcol` >= '2008-10-10 10:45:00' OR `dtcol` <= '2008-10-10 11:45:00' )

Open in new window

0
 

Author Comment

by:mhouldridge
ID: 22685765
Yep, that's answered my question.

It's been a while since I've done any MySQL - Apologies for being obtuse!, and thanks!
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question