Postgres timestamp and time zone for raw UTC values

Question

I'm hesitant to revisit this overly discussed topic, but I created a set of tables to store data and many of the tables included a field named "createdate" designated as "timestamp without time zone". The date/time values fed to these by the code base are always in UTC. It is intended that the UI will be able to control how the data is presented to the user, so some setting would dictate to convert to time zone in the UI.

Some of these timestamps will be for reports, so to display info for the end user. Other times, the values will be used to determine nightly jobs run against the data.

This is a typical is a multi-tenant cloud hosted system with clients across different time zones. The server should never be moved, although I suppose changing hosting zones is a very remote possibility. It was written on the .net platform. Not using noda time, just the built in DateTime stuff (for now).

The docs are pretty clear how the timestamp with time zone stores the info: https://www.postgresql.org/docs/current/datatype-datetime.html

This answer also has good background on the difference in the two main timestamp data types: https://stackoverflow.com/a/14616640/1905693

This answer also has some good info, but is Java-oriented: What is the most recommended way to store time in PostgreSQL using Java?

And Josh Berkus has a dated article that was helpful: https://it.toolbox.com/blogs/josh-berkus/zone-of-misunderstanding-092811

It seems most of these recommend the timestamp with time zone, but in my case, is timestamp without time zone appropriate?

If I did want to rely on pg to do the conversion, would the AT TIME ZONE clause be ok?

From an overall system architecture, is relying on the UI to change presentation a common and reasonable approach? (yeah, this one may be too opinion-flavored for SO's format)

Answer 1

Not a moment

As others said, TIMESTAMP WITHOUT TIME ZONE is the wrong data type.

That type holds only a date with time-of-day, for example noon on January 21st of 2021. But we cannot know if that means noon in Tokyo Japan, noon in Toulouse France, or noon in Toledo Ohio US — three very different moments, several hours apart from one another. So this type cannot represent a moment, is not a specific moment on the timeline.

The TIMESTAMP WITHOUT TIME ZONE type is good for three kinds of use-cases:

• Representing multiple moments all known the same in their locality. For example, Acme Corp orders the manager at each factory in Delhi, Düsseldorf, and Detroit, to make an announcement in two days at their local time of noon.
• Representing a date and time-of-day where the intended time zone is unknown. I consider this faulty data that should be rejected. But if you insist on writing it to the database, this type would be appropriate.
• Booking future appointments where we want to keep the time-of-day even if those pesky politicians change the offset of the time zone(s) in their jurisdiction. These political changes happen surprisingly often. So book an appointment using two columns: TIMESTAMP WITHOUT TIME ZONE in one, and the name of the intended time zone in another. Time zones are named with Continent/Region format such as Africa/Tunis. At runtime, when you need a moment for calendaring, apply the time zone to the date and time to dynamically determine a moment according to the now-current time zone rules. In Noda Time, you would retrieve a LocalDateTime and time zone, to produce a ZonedDateTime for calendaring.

Moments

When you care about moments, specific points on the timeline, use TIMESTAMP WITH TIME ZONE.

In Postgres and many other databases, this type has a bit of a misnomer. The time zone is not actually saved with the date and time. Instead, upon submission to the database, any indicator of time zone or offset-from-UTC is used to adjust to UTC (an offset of zero hours-minutes-seconds). That UTC value is what Postgres writes to the database. And UTC is what Postgres always retrieves from the database.

Beware: Some middleware and tooling has the well-intentioned but very confusing anti-feature of dynamically applying a default time zone to the retrieved value, adjusting from UTC to that time zone. This creates the illusion of that time zone having been saved with the data. But, no, not so. A TIMESTAMP WITH TIME ZONE column stores only UTC values.

Use case examples:

• Tracking the moment when a database record was created, modified, or deleted.
• Logging for debugging or sysadmin work.
• Tracking when a critical contract is signed or comes into effect.

Answer 2

Like you have seen repeatedly in the sources you quote, timestamp with time zone (timestamptz) is the more appropriate choice in all cases where different time zones may be involved.

Internally, storage is the same. But with timestamptz, Postgres already knows where timestamp value applies, while it is just oblivious in this regard with timestamp without time zone (timestamp).

Note that the given time zone is not stored. That's a common misunderstanding. It's only used to compute UTC time. See:

• Time zone storage in data type "timestamp with time zone"

AT TIME ZONE is the SQL construct to use to get the corresponding time for a given time zone.

SELECT my_timestamptz AT TIME ZONE 'US/Hawaii';

With timestamp it gets awkward:

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

Also, when using timestamp any appended time zone information in literals is ignored, which is typically not what you want.

See:

• Ignoring time zones altogether in Rails and PostgreSQL

Answer 3

I usually set the timezone of the role I am using?

ALTER ROLE my_db_user IN DATABASE my_database
    SET "TimeZone" TO 'UTC';

Seems to help getting the DateTime correctly.