Solved

How to Count records between two datetimes?

Posted on 2008-10-10
8
277 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

726 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