How do I find records added to my database table in the past 24 hours?

Question

I'm using MySQL in particular, but I'm hoping for a cross-vendor solution. I'm using the NOW() function to add a timestamp as a column for each record.

INSERT INTO messages 
(typeId, messageTime, stationId, message) 
VALUES 
(?, NOW(), ?, ?)

Answer 1

SELECT * FROM messages WHERE DATE_SUB(CURDATE(),INTERVAL 1 DAY) <= messageTime

Answer 2

The SQL Server query is:

Select *
From Messages
Where MessageTime > DateAdd(dd, -1, GetDate())

As far as I can tell the (untested!) MySQL equivalent is

Select *
From Messages
Where MessageTime > ADDDATE(NOW(), INTERVAL -1 DAY)

Answer 3

There is no cross database solution, as most of them have their own date handling (and mainly interval representation) syntax and semantics, sorry.

In PostgreSQL it would be

SELECT * FROM messages WHERE messagetime >= messagetime - interval '1 day'

Answer 4

For Sybase SQL Anywhere:

Select * From Messages Where MessageTime > dateadd( day, -1, now() )

Answer 5

For Oracle

SELECT * FROM messages WHERE messageTime > SYSDATE - 1

(The psuedo variable SYSDATE includes the time, so sysdate -1 will give you the last 24 hrs)

Answer 6

If you are accessing this from an API based client (I'm guessing that is the case because of the '?'s in the query) you can do this from your program rather than through SQL.

Note: The rest is for JDBC syntax, other APIs/languages will be different syntax, but should be conceptually the same.

On the insert side do

PreparedStatement stmt = connection.prepareStatement( 
    "INSERT INTO messages " +
    "(typeId, messageTime, stationId, message) VALUES " +
    "(?, ?, ?, ?)" );
stmt.setInt(1, typeId);
stmt.setDate(2, new java.sql.Date(System.currentTimeMillis()));
stmt.setInt(3, stationId);
stmt.setString(4, message);

On the query side do:

PrepatedStatement stmt = connection.prepareStatement(
   "SELECT typeId, messageTime, stationId, message " +
   "from messages where messageTime < ?");
long yesterday = System.currentTimeMillis() - 86400000; // 86400 sec/day
stmt.setDate(1,new java.sql.Date(yesterday));

That should work in a portable manner.