SQL Filtering against a time stamp
Every time I come to write an SQL query that uses the current time against a time stamp in a record, someone always seems to suggest a different way of solving the problem. That in itself isn’t an issue, there’s usually several ways of coding ones way round any situation with personal preference or familiarity probably being as good as any other.
In this example I used DATEADD with a negative integer to filter against any records more than 30 days old. I’ve always thought that using plus and minus together in any situation is a little odd, but it’s the Microsoft way in SQL Server and it works fine.
In MySQL:
SELECT *
FROM mytable
WHERE record_date >= SYSDATE() - INTERVAL 30 DAY
In SQL Server:
SELECT *
FROM mytable
WHERE record_date >= DATEADD(day, -30, GETDATE())
In Oracle:
SELECT *
FROM mytable
WHERE record_date >= SYSDATE - 30
In PostgreSQL:
SELECT *
FROM mytable
WHERE record_date >= NOW() - '30 day'::INTERVAL