How to select records from last 24 hours using SQL?

sql

database-optimization

sql-queries

date-and-time

byNikita Barsukov·Nov 3, 2024

For a 24-hour range in MySQL, combine NOW() and INTERVAL:

-- The clock's ticking in MySQL!
SELECT * FROM your_table
WHERE your_datetime_column > NOW() - INTERVAL 1 DAY;

With SQL Server, apply GETDATE() and DATEADD:

-- SQL Server says: Time for some DATEADD magic!
SELECT * FROM your_table
WHERE your_datetime_column > DATEADD(DAY, -1, GETDATE());

Ensure to replace your_table and your_datetime_column with your database details.

Exploring different databases

Each type of SQL dialect uses specific commands for date and time. Let's look at how few handle the 24-hour range task:

PostgreSQL and Redshift share syntax similarities with MySQL:

-- PostgreSQL whispers: Did I hear INTERVAL?
SELECT * FROM your_table
WHERE your_timestamp_column > NOW() - '1 day'::INTERVAL;

-- Redshift nudges: Paging for GETDATE!
SELECT * FROM your_table
WHERE your_timestamp_column > GETDATE() - '1 day'::INTERVAL;

Oracle and SQLite employ their unique styles:

-- Oracle's favourite operation: Subtraction!
SELECT * FROM your_table
WHERE your_timestamp_column > SYSDATE - 1;

-- SQLite: datetime('now', '-1 day') is the new black!
SELECT * FROM your_table
WHERE your_timestamp_column > datetime('now', '-1 day');

For MS Access, use Now minus one:

-- MS Access winks: Keepin' it simple folks!
SELECT * FROM your_table
WHERE your_timestamp_column > Now() - 1;

Here are some golden rules for optimizing time-related queries:

Swap SELECT * with SELECT specific_columns for boosted performance.
Index your_datetime_column to speed up queries.
Check your timezone settings to avoid bad timing.
Adopt GETUTCDATE() in SQL Server for uniform UTC time.

Handling varying time spans

Modify the INTERVAL command or its equivalent to cater to different time frames:

-- MySQL is flexible: What's your number?
SELECT * FROM your_table
WHERE your_datetime_column > NOW() - INTERVAL 3 DAY;

-- SQL Server: 4-hour energy?
SELECT * FROM your_table
WHERE your_datetime_column > DATEADD(HOUR, -4, GETDATE());

Digging deeper: Advanced tips

Leap seconds and daylight saving time

Is daylight saving time causing a wrinkle in time? Functions accounting for DST changes or leap seconds can smooth it out.

Precise timestamps

For near real-time precision, SQL Server 2008 or later have SYSDATETIME(), because who has time for stale data?

-- SQL Server 2008 or later: No time like the precise present!
SELECT * FROM your_table
WHERE your_timestamp_column > DATEADD(SECOND, -86400, SYSDATETIME());