Unix Date Format with SQL

This is kind of a neat one. I need to check to see if the timestamp in the database (in SQL Date format: YYYY-MM-DD) is more than X days from the current unix time (in seconds since Jan 1, 1970… 1045268704). It could be done by hand in perl, but I figured out how to do it a bit more elegantly under PostgreSQL (where the field date_entered has a format of “Date”.

SELECT
field1,
field2,
date_entered, extract( epoch from date_entered)
FROM tablename;

This returns:

field1| field2 | date_entered | date_part
----------+---------+--------------+------------
foo   | bar    | 2003-01-14   | 1042502400
...

Very cool stuff. This now allows me to do date manipulation on the SQL datestamp using standard perl date/time functions. A node on perlmonks about converting from date time to unix time gave me the clue to use datediff, which led me to a PostgreSQL FAQ on working with dates and times which answered all my questions.