PostgreSQL field type for unix timestamp :
Have been going through Date/Time Types postgreSQL V 9.1.
int(10))The unix epoch timestamp right now (2014-04-09) is 1397071518. So we need an data type capable of storing a number at least this large.
What data types are available?
If you refer to the PostgreSQL documentation on numeric types you'll find the following options:
Name Size Minimum Maximum
smallint 2 bytes -32768 +32767
integer 4 bytes -2147483648 +2147483647
bigint 8 bytes -9223372036854775808 +9223372036854775807
What does that mean in terms of time representation?
Now, we can take those numbers and convert them into dates using an epoch converter:
Name Size Minimum Date Maximum Date
smallint 2 bytes 1969-12-31 1970-01-01
integer 4 bytes 1901-12-13 2038-01-18
bigint 8 bytes -292275055-05-16 292278994-08-17
Note that in the last instance, using seconds puts you so far into the past and the future that it probably doesn't matter. The result I've given is for if you represent the unix epoch in milliseconds.
So, what have we learned?
smallint is clearly a bad choice.integer is a decent choice for the moment, but your software will blow up in the year 2038. The Y2K apocalypse has nothing on the Year 2038 Problem.bigint is the best choice. This is future-proofed against most conceivable human needs, though the Doctor may still criticise it.You may or may not consider whether it might not be best to store your timestamp in another format such as the ISO 8601 standard.
I'd just go with using TIMESTAMP WITH(OUT) TIME ZONE and use EXTRACT to get a UNIX timestamp representation when you need one.
Compare
SELECT NOW();
with
SELECT EXTRACT(EPOCH FROM NOW());
unix timestamp?TIMESTAMP WITH(OUT) TIME ZONE does not store the literal timestamps.
TIMESTAMP WITH TIME ZONE is stored as a 64-bit integer of the microseconds from midnight 2000-01-01 UTC (ignoring leap seconds, so really more like UT1). That converts pretty quickly and easily to Unix time, while allowing easy presentation and date math. If you really want the coarser resolution, integer presentation, and difficulty in date calculations, feel free to use bigint or numeric. There aren't too many situations where that's a net win, but maybe you have one of those few.integer would be good, but not enough good, because postgresql doesn't support unsigned types
