How to select records from last 24 hours using SQL?

Question

I am looking for a where clause that can be used to retrieve records for the last 24 hours?

Do you have a timestamp field on these records? – Shawn Steward Dec 11 '09 at 14:52 — Shawn Steward, Dec 11 '09 at 14:52

score 711 · Answer 1 · edited Feb 08 '18 at 20:47

711

In MySQL:

SELECT  *
FROM    mytable
WHERE   record_date >= NOW() - INTERVAL 1 DAY

In SQL Server:

SELECT  *
FROM    mytable
WHERE   record_date >= DATEADD(day, -1, GETDATE())

In Oracle:

SELECT  *
FROM    mytable
WHERE   record_date >= SYSDATE - 1

In PostgreSQL:

SELECT  *
FROM    mytable
WHERE   record_date >= NOW() - '1 day'::INTERVAL

In Redshift:

SELECT  *
FROM    mytable
WHERE   record_date >= GETDATE() - '1 day'::INTERVAL

In SQLite:

SELECT  *
FROM    mytable
WHERE   record_date >= datetime('now','-1 day')

In MS Access:

SELECT  *
FROM    mytable
WHERE   record_date >= (Now - 1)

edited Feb 08 '18 at 20:47

4castle

32,613
11
69
106

answered Dec 11 '09 at 14:52

Quassnoi

413,100
91
616
614

3

I try the query for mysql but if its 2AM, for example, i get the records from 00 - 02 AM. Two hours only instead of 24. Any ideas? – Manos Jul 15 '14 at 22:36
I very much like the solution provided here, just what I noted in MySQL is the performance of _Guillaume Flandre_ solution was faster. – oneworld Jul 31 '14 at 00:36
1

In Amazon Redshift, I get `function getdate() does not exist`. Replaced it with `current_date` and it worked fine. – FloatingRock Aug 17 '16 at 06:23
1

@Manos In MySQL, it looks like `CURDATE()` doesn't return a date with a time portion, so it goes to `00`. I fixed it by using `NOW()` instead of `CURDATE()`. – 4castle Feb 08 '18 at 20:39

score 108 · Accepted Answer · answered Dec 11 '09 at 14:54

108

SELECT * 
FROM table_name
WHERE table_name.the_date > DATE_SUB(CURDATE(), INTERVAL 1 DAY)

answered Dec 11 '09 at 14:54

Guillaume Flandre

8,936
8
46
54

11

If the records are stored as `DATETIME`, this will select all records from the previous date, disregarding the time part. Been run at `23:59:59`, the query will return all records for the last `48` hours, not `24`. – Quassnoi Dec 11 '09 at 15:02
23

If you want to select the last 24 hours from a datetime field, substitute 'curate()' with 'now()'. This also includes the time. – Haentz Apr 30 '11 at 06:11

score 23 · Answer 3 · answered Feb 05 '13 at 23:06

23

MySQL :

SELECT * 
FROM table_name
WHERE table_name.the_date > DATE_SUB(NOW(), INTERVAL 24 HOUR)

The INTERVAL can be in YEAR, MONTH, DAY, HOUR, MINUTE, SECOND

For example, In the last 10 minutes

SELECT * 
FROM table_name
WHERE table_name.the_date > DATE_SUB(NOW(), INTERVAL 10 MINUTE)

answered Feb 05 '13 at 23:06

Karthik Jayapal

555
1
4
14

3

This is the answer that best answers the specific question: How to return the PRECEDING 24 HOURS (from present moment), as opposed to the previous 24 (which suggests all results for previous day). This answered my requirements, this gets my upvote. EDIT: Its worth mentioning the table_name.the_date column should be a timestamp. – Anthony Cregan Aug 22 '17 at 12:17

score 8 · Answer 4 · answered Dec 11 '09 at 14:54

Which SQL was not specified, SQL 2005 / 2008

SELECT yourfields from yourTable WHERE yourfieldWithDate > dateadd(dd,-1,getdate())

If you are on the 2008 increased accuracy date types, then use the new sysdatetime() function instead, equally if using UTC times internally swap to the UTC calls.

score 7 · Answer 5 · answered Jun 20 '12 at 22:31

7

in postgres, assuming your field type is a timestamp:

select * from table where date_field > (now() - interval '24 hour');

answered Jun 20 '12 at 22:31

Pikachu

774
13
15

score 4 · Answer 6 · answered May 06 '16 at 21:22

4

If the timestamp considered is a UNIX timestamp You need to first convert UNIX timestamp (e.g 1462567865) to mysql timestamp or data

SELECT * FROM `orders` WHERE FROM_UNIXTIME(order_ts) > DATE_SUB(CURDATE(), INTERVAL 1 DAY)

answered May 06 '16 at 21:22

lightup

634
8
18

That's assuming the engine being used is MySQL as FROM_UNIXTIME() is a MySQL function. For SQL Server use: ```SELECT DATEDIFF(s, '1970-01-01 00:00:00', DateField)``` If you need millisecond accuracy in SQL Server 2016 and later use: ```SELECT DATEDIFF_BIG(millisecond, '1970-01-01 00:00:00', DateField)``` – luisdev Feb 13 '20 at 09:58
This runs a function on each row, and will not benefit from index. Ok for smaller amounts of data or one time operations, not so ok for repeated use on large datasets. – Tedd Hansen Apr 25 '21 at 08:21

score 3 · Answer 7 · answered Dec 11 '09 at 14:51

3

select ...
from ...
where YourDateColumn >= getdate()-1

answered Dec 11 '09 at 14:51

Mladen Prajdic

15,457
2
43
51

score 2 · Answer 8 · answered Dec 11 '09 at 14:53

2

SELECT * 
FROM tableName 
WHERE datecolumn >= dateadd(hour,-24,getdate())

answered Dec 11 '09 at 14:53

Galwegian

41,475
16
112
158

score 2 · Answer 9 · answered Oct 10 '17 at 07:52

2

Hello i now it past a lot of time from the original post but i got a similar problem and i want to share.

I got a datetime field with this format YYYY-MM-DD hh:mm:ss, and i want to access a whole day, so here is my solution.

The function DATE(), in MySQL: Extract the date part of a date or datetime expression.

SELECT * FROM `your_table` WHERE DATE(`your_datatime_field`)='2017-10-09'

with this i get all the row register in this day.

I hope its help anyone.

answered Oct 10 '17 at 07:52

Rafa cosquiere

73
1
9

This is the only one that worked for me on mySQL. For some reason when I tried SELECT * FROM myTable WHERE myDate > DATE_SUB('2018-06-13 00:00:00', INTERVAL 24 HOUR); I was still getting records from 2018-06-14. – wheeleruniverse Jun 21 '18 at 11:22
This runs a function on each row, and will not benefit from index. Ok for smaller amounts of data or one time operations, not so ok for repeated use on large datasets. – Tedd Hansen Apr 25 '21 at 08:21

score 1 · Answer 10 · edited Apr 03 '12 at 15:49

1

In SQL Server (For last 24 hours):

SELECT  *
FROM    mytable
WHERE   order_date > DateAdd(DAY, -1, GETDATE()) and order_date<=GETDATE()

edited Apr 03 '12 at 15:49

antyrat

27,479
9
75
76

answered Mar 06 '12 at 06:44

arunkumar.halder

57
1
6

2

why would you need to add "and order_date <=GETDATE()" when you are already looking for last 24 hours form the moment of exection of that query? It really makes no sense to have that part in the where clause when you have "GETDATE" already mentioned in the first one. You better make it "order_date >= DateAdd(DAY, -1, GETDATE())" thats it! – KMX May 16 '14 at 18:29

score 0 · Answer 11 · answered Jul 27 '20 at 19:01

In Oracle (For last 24 hours):

SELECT  *
FROM    my_table
WHERE   date_column >= SYSDATE - 24/24

In case, for any reason, you have rows with future dates, you can use between, like this:

SELECT  *
FROM    my_table
WHERE   date_column BETWEEN (SYSDATE - 24/24) AND SYSDATE

score 0 · Answer 12 · edited Mar 11 '23 at 17:07

0

Someone might need this as it is my case. If you want to select a record after 24 hours, you can change the >= to <=

For example: In MySQL will be:

SELECT  *
FROM    mytable
WHERE   record_date <= NOW() - INTERVAL 1 DAY

edited Mar 11 '23 at 17:07

F. Müller

3,969
8
38
49

answered Mar 07 '23 at 05:22

Alid

1
1

How to select records from last 24 hours using SQL?

12 Answers12

Linked