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;
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.