CODEWITHSUNDEEP
mysqlsqldate
richardalberto
richardalberto

Reputation: 525

Sql to get all records from yesterday's turn

I need a SQL condition to get rows from a table that were created between 6:00 a.m yesterday and 7:00 a.m today. The table has a creationDate field.

This is the approach I was thinking of 

select *
from myTable   
where creationDate = DATE_SUB(CONCAT(CURDATE()), INTERVAL 1 DAY)

I'm using mysql server 5. Also my time is not on the creationDate field, it's on a separated field called startTime.

Upvotes: 1

Views: 4537

Answers (3)

fancyPants
fancyPants

Reputation: 51938

Tested and it works (this assumes that your creationDate column is of type timestamp):

select * from mytable
where
creationDate between concat(date(date_sub(now(), interval 1 day)), ' 06:00:00') and concat(date(now()), ' 07:00:00')

Note the whitespace in front of the time value, i.e. ' 06:00:00'

EDIT cause of further specification in comment:

SELECT * FROM mytable
WHERE
(creationDate >= date(date_sub(now(), interval 1 day)) AND creationTime >= '06:00:00')
AND
(creationDate <= date(now()) AND creationTime <= '07:00:00')

Yes, it needs to be this clumsy now, because if you would simply write something like (pseudocode)

...WHERE creationDate between today and yesterday AND creationTime between '06:00:00' and '07:00:00'

you would get the timespan of 1 hour for today and yesterday.

Upvotes: 5

Dor Cohen
Dor Cohen

Reputation: 17100

try to do this:

select *
from myTable   
where creationDate > DATE_SUB(CURDATE(), INTERVAL 25 HOUR);

Upvotes: 0

Naveen Kumar
Naveen Kumar

Reputation: 4601

Try this query 

select *
from myTable   
where creationDate >= 
DATE_SUB(concat(DATE_FORMAT(NOW(), %Y-%m-%d), ' 07:00:00'), INTERVAL 25 HOUR)
AND
creationDate <= concat(DATE_FORMAT(NOW(), %Y-%m-%d), ' 07:00:00')

Upvotes: 0

Related Questions