What value should I choose as length for timestamp without timezone in PostgreSQL?

Question

I'm currently setting up a database to populate it with big data. All the data I get comes from Valve's Steam Web API. Therefore, as all timestamps are returned in Unix timestamps, I cannot directly derive the correct timezone - which is also not of any interest as it's to fine-grained for what it is intended for. However, PostgreSQL enables the "length"-value field when setting the column up for timestamp without timezone but I don't know which value is meaningful to enter here and I also couldn't find any information about this value - neither in the official documentation nor on StackExchange so far.

As I'm someone who doesn't set up databases all the time I'm a bit confused and would love to get some assistance. Thanks in advance for your suggestions and input.

• nbk: see this answer in SO stackoverflow.com/a/34903619/5193536

Answer 1

It's not the "length", it's the precision of the timestamp, so it controls the fractional seconds (milliseconds, microseconds) you can store.

e.g. '2020-12-14 23:14:10.123456'::timestamp(0) returns 2020年12月14日 23:14:10 and '2020-12-14 23:14:10.123456'::timestamp(3) returns 2020年12月14日 23:14:10.123

As you get a unix epoch which has no fractional seconds, timestamp(0) should be OK.

But in the end it doesn't matter, as changing the precision doesn't change the storage requirements. It's always 8 byte regardless of the precision you choose.

Answer 2

By all means, use timestamptz instead!

• timestamp (w/o timezone) stores a date and time for a calendar and a clock.
• timestamptz (w/ timezone) stores a point in time, the EPOCH. It does not store the timezone.
• Unix timestamps are usually UTC and stored as EPOCH.
• You might want to set the precision to milliseconds(=3), microseconds(=6), or (in the future) to nanoseconds(=9). Be aware, that any input value will be rounded to that precision.

I would prefer timestamptz(9) because that is the maximum used precision of all APIs and computers that I know of. (see "System.nanoTime()", or "timespec" in <time.h>)

However, Postgres (currently PG17) does not support more than timestamptz(6).