Solved

How to Count records between two datetimes?

Posted on 2008-10-10
8
274 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
  • 4
  • 4
8 Comments
 
LVL 142

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 142

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 142

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 142

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MySQL - Limit or Top Records 15 49
Multilanguage Database Design in MySQL 5 107
mysql disables rename 4 67
FrontEnd tools to create web database application 7 54
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

786 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