SQL Query to exclude only data from last 24 hours?

Question

I have the following data:

Table Name: NODE
NID | Name | Date
001 | One  | 1252587739

Date is a unix timestamp. I need a query in which I can only select nodes that have a "Date" older than 24 hours. Something like that:

SELECT * FROM NODE WHERE Date < NOW() - SOMETHING

• Does anyone know how to do this?
• Does the NOW () - SOMETHING side take into account that the date is stored as a unix timestamp?

Answer 1

Unix timestamp is in seconds. This works with MySQL:

SELECT * FROM NODE WHERE Date < (UNIX_TIMESTAMP(NOW()) - 24*60*60)

Answer 2

where dateiff (hh, date, now ()) <24

Answer 3

"Unix Timestamp = 1 1970 " MS SQL Server ( 7.0 ):

SELECT *
 from NODE
 where datediff(ss, dateadd(ss, Date, 'Jan 1, 1970'), getdate()) < 86400

The inner bracket adds the number of seconds until January 1, 1970 to get the date and time of the string in SQL server format, the outer bracket gets the number of seconds between this date and "now", and 86400 is the number of seconds in 24 hours. (But double check this is - I cannot debug it just now, and I may have a mixed order of param parameters.)