3

timestamp with time zone vs timestamp without 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.

Say, I decide to use timestamp without time zone only, even in cases where timestamp with time zone would be recommended. And users of my website are ok with keeping in mind that I and they would be operating with UTC at all times, even if we happen to be in different time zones physically, would timestamp without time zone work out well and with no flaws?

1 Answer 1

6

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:

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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