PostgreSQL field type for unix timestamp?
PostgresqlPostgresql 9.1Postgresql Problem Overview
PostgreSQL field type for unix timestamp :
- to store it as unix time stamp
- to retrieve it as a unix timestamp as well.
Have been going through Date/Time Types postgreSQL V 9.1.
- Is integer the best way to go!? (this is what I had done when I was using MySQL. Had used
int(10)
)
Postgresql Solutions
Solution 1 - Postgresql
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.- Using
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.
Solution 2 - Postgresql
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());
Solution 3 - Postgresql
integer would be good, but not enough good, because postgresql doesn't support unsigned types