Solved

How to Count records between two datetimes?

Posted on 2008-10-10
8
279 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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 500 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

Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

Question has a verified solution.

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

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
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…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

691 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