Epoch Seconds in Postgres
You want to work with dates & times in Postgres in terms of Unix epoch seconds. Last updated 2008-07-09.
Working with dates and times from databases can be painful, as they generally return dates as a string which you need to parse before you can use them in a program. For example, in Postgres, if you select the current time you get the following (all the examples in this page are run with Postgres 8.0.3):
flux=# SELECT now();
now
-------------------------------
2006-10-03 08:13:27.295267+01
(1 row)Using the extract function, we can get this date as a number of Unix epoch seconds, which is easy to use programatically (e.g. in Perl or Ruby):
flux=# SELECT extract(epoch FROM now());
date_part
------------------
1159859842.50417
(1 row)To see a more complete example (we’ll get to how to insert epoch seconds shortly), create the following table, which has a simple auto incrementing key and a timestamp field with time zone:
CREATE TABLE example (id SERIAL PRIMARY KEY, date TIMESTAMPTZ);We shall now populate our table with a little sample data:
INSERT INTO example (date) VALUES (now());
INSERT INTO example (date) VALUES ('2006-01-01');
INSERT INTO example (date) VALUES ('1950-01-01');And check what we got:
flux=# SELECT * from example;
id | date
----+-------------------------------
1 | 2006-10-03 08:27:31.774255+01
2 | 2006-01-01 00:00:00+00
3 | 1950-01-01 00:00:00+00
(3 rows)Selecting the number of epoch seconds (note how the column name is changed to ‘date_part’ when you use extract):
flux=# SELECT id, extract(epoch FROM date) FROM example;
id | date_part
----+------------------
1 | 1159860451.77426
2 | 1136073600
3 | -631152000
(3 rows)Inserting a time in epoch seconds is a bit more fiddly, as there’s not a reverse of the extract function. Instead we add the number of epoch seconds to the start of the epoch. For example, to insert 1136073600 (2006-01-01 00:00) into our table:
INSERT INTO example (date)
VALUES (TIMESTAMPTZ 'epoch' + 1136073600 * '1 second'::interval);Postgres won’t convert the number 1136073600 into an interval number of seconds automatically, so we have to multiply it by an interval of one second. ::<type> is one way of indicating a cast in Postgres, and probably the clearest (though not portable to other databases).
If we run the select again, we can see our new row (this time we’ve used ‘AS’ to rename the column):
flux=# SELECT id, extract(epoch FROM date) AS date FROM example;
id | date
----+------------------
1 | 1159860451.77426
2 | 1136073600
3 | -631152000
4 | 1136073600
(4 rows)By using epoch seconds in your database programs you avoid having to do any parsing of date strings, and your results should be consistent, as Postgres is doing the conversion to and from epoch seconds. However, using epoch seconds is not without its downside. The Unix epoch traditionally runs from 1901-2038 using a 32 bit integer, outside this time window you may get unexpected results.
To see what happens in Postgres, let’s insert 2100-01-01 into our table:
INSERT INTO example (date) VALUES ('2100-01-01');
flux=# SELECT id, extract(epoch FROM date) AS date FROM example;
id | date
----+------------------
1 | 1159860451.77426
2 | 1136073600
3 | -631152000
4 | 1136073600
5 | 4102444800
(5 rows)Selecting the result in Postgres didn’t cause a problem: Postgres isn’t limited to 32 bit values for epoch seconds.
This doesn’t mean you’re safe if you’re using software other than Postgres: you should test any libraries or functions you use to ensure they’re happy with epoch seconds larger than 32 bits. If you don’t, you risk having 2038 followed by 1901 and people gnashing their teeth at your code.
Tags: Date, Howto, Postgres, SQL, Time