Find records with a date field in the last 24 hours

sql

date-functions

performance

timezones

byAnton Shumikhin·Aug 20, 2024

If you're in a burn-the-midnight-oil developer mood, here's the instant noodle solution for you:

MySQL:

-- Like scoring a date in the last 24 hours!
SELECT * FROM your_table WHERE date_column >= NOW() - INTERVAL 1 DAY;

SQL Server:

-- Is your date_column playing hide and seek? Find it!
SELECT * FROM your_table WHERE date_column >= DATEADD(day, -1, GETDATE());

Replace your_table and date_column with your actual table and column names–the SQL equivalent of a CTRL+H. Use NOW() for MySQL or GETDATE() for SQL Server.

How SQL date functions work for you

Ever took an arrow to the knee because of SQL date functions? Let's crack that puzzle today. These functions are top-tier operators providing highest precision for retrieving records based on timestamps.

Know your SQL dialect

Each SQL flavor has its own magic functions. Salvage the last 24 hours of records in PostgreSQL and Oracle like so:

-- PostgreSQL, never giving up on those past 24 hours
SELECT * FROM your_table WHERE date_column >= CURRENT_TIMESTAMP - INTERVAL '1 day';

-- Oracle, subtracting 1.. because why be complicated when you're a classic?
SELECT * FROM your_table WHERE date_column > SYSDATE - 1;

Built to deliver swift and convenience in terms of time-dependent data retrieval processes.

TimeZone Tango

Timezones can stab you in the back when it comes to fetching time-relatable data. Think of it like wearing a shoe on the wrong foot, just doesn't quite fit, right? Make sure your database timezone is doing the correct waltz.

Indexing - SQL's superpower

Optimizing your time-based SQL queries? Think of indices, the caped superheroes of the SQL universe. Index your date_column for some serious speed coding.

Between a rock and a hard-place - Advanced usage

Times when you wish for exact time frames. Turn to our trustworthy INTERVAL:

MySQL:

-- Need more than TODAY?
-- Say it louder for the folks at the back - THE LAST 24 HOURS!
SELECT * FROM your_table WHERE date_column >= NOW() - INTERVAL 24 HOUR;

SQL Server:

-- The docs said DATEADD, they never said what to ADD (-24 hours 😉)
SELECT * FROM your_table WHERE date_column >= DATEADD(hour, -24, GETDATE());

Type matters: TIMESTAMP‽ DATETIME‽ DATE‽ Oh my!

When dealing with different date types, you'll want the precision that hits the bullseye.

-- MySQL DATE without time? Let's subtract from today.
-- It's like asking "what did I miss yesterday?"
SELECT * FROM your_table WHERE date_column >= CURDATE() - INTERVAL 1 DAY;

-- SQL Server says, "Minutes matter!"
-- And we salute because what's a day but 1440 minutes?
SELECT * FROM your_table WHERE date_column >= DATEADD(minute, -1440, GETDATE());

Play the ply with PostgreSQL

PostgreSQL is your best bet when harvesting conditional data:

-- Playing SQL "Duck, Duck, Goose" with records
SELECT *,
  CASE
    WHEN date_column >= NOW() - INTERVAL '12 hours' THEN 'Fresh Clam'
    ELSE 'Old Shell'
  END as Shell_Age
FROM your_table;

Proven tactics to boost performance

Stick with SQL functions. Unless you like catching the slow train to SQL Server, that is.
Indexes are your BFFs! Querying without them is like going surfing without a board.
Bearer of universal truth: built-in SQL functions > external variables.
Seek precision with INTERVAL—you want a sniper, not a shotgun.