PostgreSQL field type for unix timestamp?

Question

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))

Answer 1

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?

1. smallint is clearly a bad choice.
2. 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.
3. 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.

Answer 2

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());