How to find records added to a database table in the last 24 hours?

Question

I use MySQL in particular, but I hope for a solution for different vendors. I use 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

SQL Server query:

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

As far as I can tell (unverified!) MySQL equivalent

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

Answer 3

For Sybase SQL Anywhere:

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

Answer 4

For Oracle

 SELECT * FROM messages WHERE messageTime > SYSDATE - 1 

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

Answer 5

There is no solution for a cross database, since most of them have their own syntax and semantics for date processing (and basically interval representation).

In PostgreSQL it will be

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

Answer 6

If you access this from an API-based client (I assume this is because of the “? In the query”), you can do this from within your program, not through SQL.

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

On the side of the insert 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 request 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)); 

This should work in a portable way.