0

Timestamp with time zone vs timestamp with no time zone.

I'm aware that timestamptz is the same as timestamp no time-zone + (time-zone difference) that gets added upon saving a value in a database. That is, in a db, when stored, they both are indistinguishable.

If I decide to use timestamps with no time-zone only, even in the cases when normally timestamps with time-zone would be recommended to be used instead, but if I'm and users of my website are ok with keeping in mind that I and they would be operating with UTC only all the time, even if I or them happen to be physically in different time zones, would timestamps with no time-zone work out well and with no flaws?

0

If all are aware that the timestamp value is supposed to represent UTC time it should work, basically. A convenient advantage is that default input and output is less noisy.

I would still suggest timestamptz. Here are some reasons:

1.

timestamptz literally is the "preferred type" among date/time types in Postgres. See:

test=> SELECT typname, typispreferred FROM pg_type WHERE typcategory = 'D';
   typname   | typispreferred 
-------------+----------------
 date        | f
 time        | f
 timestamp   | f
 timestamptz | t  -- !
 timetz      | f
 time_stamp  | f
(6 rows)

The manual about pg_types.

This can work in favor of timestamptz in corner cases of type/function/operator resolution.

2.

When you have timestamptz literals as data input, that just works with type timestamptz:

'2020-04-22 04:46:46.790969+02'

Inserting the same into a timestamp column would ignore the time offset (lose information). One would have to cast to timestamptz explicitly, then transpose to UTC timestamp with something like:

'2020-04-22 04:46:46.790969+02'::timestamptz AT TIME ZONE 'UTC'

The opposite direction is less error prone. When inserting a timestamp literal into a timestamptz column, the current time zone is assumed in the absence of explicit information.

3.

now() and transaction_timestamp() return timestamptz. If you need timestamp, you have to cast. Internally, LOCALTIMESTAMP does just that: it casts transaction_timestamp(). To get the current timestamp for UTC you need now() AT TIME ZONE 'UTC' or similar.

4.

Displaying timestamps for a given time zone is simpler based on timestamptz:

SELECT my_timestamptz AT TIME ZONE 'US/Hawaii';

That's more work with timestamp. We know it's supposed to be UTC time. But Postgres does not until we say so, explicitly:

SELECT my_timestamp AT TIME ZONE 'UTC' AT TIME ZONE 'US/Hawaii'

Related:

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.