Find records with a date field in the last 24 hours

Question

In my SQL query how do i make it find the records in the last 24 hours? Eg

   SELECT * FROM news WHERE date < 24 hours

I usually do it by setting a variable to date() - 1 day and comparing it to that but I wondered whether the sql query way was faster?

score 161 · Answer 1 · answered Nov 10 '11 at 12:48

161

You simply select dates that are higher than the current time minus 1 day.

SELECT * FROM news WHERE date >= now() - INTERVAL 1 DAY;

answered Nov 10 '11 at 12:48

a'r

35,921
7
66
67

Andrew · Answer 2 · 2011-11-10T13:22:05.760

82

SELECT * FROM news WHERE date > DATE_SUB(NOW(), INTERVAL 24 HOUR)

edited Nov 10 '11 at 13:22

answered Nov 10 '11 at 12:46

Andrew

26,629
5
63
86

Is it possible to do this : WHERE SomeDate > Now() -1 ? – Hugh Jones Nov 10 '11 at 12:56
2

@Andrew: I think it's `24 HOUR`, not `24 HOURS`. – ypercubeᵀᴹ Nov 10 '11 at 13:21
1

Working fine for me. – Muhammad Azeem May 14 '20 at 12:41

score 22 · Answer 3 · edited Aug 16 '19 at 10:51

22

To get records from the last 24 hours:

SELECT * from [table_name] WHERE date > (NOW() - INTERVAL 24 HOUR)

edited Aug 16 '19 at 10:51

GalahadXVI

759
1
8
19

answered Sep 09 '14 at 07:21

NarayanaReddy

301
2
4

score 14 · Answer 4 · answered Dec 18 '14 at 10:42

14

SELECT * from new WHERE date < DATE_ADD(now(),interval -1 day);

answered Dec 18 '14 at 10:42

gdm

7,647
3
41
71

Simon · Answer 5 · 2011-11-10T16:03:42.903

5

SELECT * FROM news WHERE date > DATEADD(d,-1,GETDATE())

edited Nov 10 '11 at 16:03

answered Nov 10 '11 at 12:45

Simon

6,062
13
60
97

2

Never mind, I see you've added that it's MySQL you're using rather than SQL Server. Still, the answer might help someone else :) – Simon Nov 10 '11 at 12:47
To substract 1 day from current date has not the same result as to select a 24 hour intervall, which was the relevant question. The timeframe after substract 1 yay from GETDATE() has in maximum a timeframe of 47.99 hours – termigrator Jan 13 '20 at 07:16

score 3 · Answer 6 · edited Jun 28 '12 at 16:04

3

There are so many ways to do this. The listed ones work great, but here's another way if you have a datetime field:

SELECT [fields] 
FROM [table] 
WHERE timediff(now(), my_datetime_field) < '24:00:00'

timediff() returns a time object, so don't make the mistake of comparing it to 86400 (number of seconds in a day), or your output will be all kinds of wrong.

edited Jun 28 '12 at 16:04

Taryn

242,637
56
362
405

answered Jun 28 '12 at 13:40

Craig Labenz

2,489
3
22
17

1

You may wish to use < maketime(24,00,00). I noticed the behavior of the original query is inconsistent. – Jan 22 '15 at 19:09

score -2 · Answer 7 · answered Nov 10 '11 at 12:43

-2

SELECT * FROM news WHERE date < DATEADD(Day, -1, date)

answered Nov 10 '11 at 12:43

Lasse Edsvik

9,070
16
73
109

Find records with a date field in the last 24 hours

7 Answers7

Linked