SQL Query to only exclude data of 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, whereby I can select only the nodes who's "Date" is older than 24 hours. Something like this:

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

1. Anybody know how to do this?
2. Does the NOW() - SOMETHING part take into account that the date is stored as a unix timestamp?

Answer 1

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

Answer 2

Unix timestamp is in seconds. This works with MySQL:

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

Answer 3

Going by the definition of "Unix Timestamp = number of seconds from Jan 1, 1970", and based on MS SQL Server (7.0 and up compatible):

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

The innermost parenthesis adds the number of seconds to Jan 1 1970 to get the row's datetime in SQL server format, the outer parenthesis gets the number of seconds between that date and "now", and 86400 is the number of seconds in 24 hours. (But double-check this--I can't debug this just now, and I might have the function paramater order mixed.)