PostgreSQL field type for unix timestamp?

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.