CODEWITHSUNDEEP
postgresql
ams
ams

Reputation: 62722

When should PostgreSQL timestamps be created without a time zone?

I have an application where the goal is to have all timestamps in the databes in GMT format should those time stamps be created without a timezone like so update_ts timestamp without time zone or is it safer /better to do so or is it a bad idea to create a timestamp without a timezone.

Upvotes: 1

Views: 174

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324841

I have an application where the goal is to have all timestamps in the databes in GMT format

In this case timestamp with time zone (timestamptz) is exactly what you want, despite the frankly stupid naming of timestamp types in SQL.

"timestamp with time zone" is actually "timestamp stored in utc and converted on input/output".

"timestamp without time zone" is "timestamp stored and retrieved without time zone conversion".

The types make a lot more sense if you read "with/without time zone" as "with/without time zone conversion".

Despite their names, neither type actually stores the time zone. There is no SQL type for what "timestamp with time zone" should actually have been, which is the local time stored with an unambiguous indication of the tz offset and time zone the time is in. (A simple UTC offset isn't good enough due to ambiguities around DST transitions).

If you want to do everything in UTC, use timestamptz and set the server TimeZone to UTC. That way apps that do want local time conversion can still get it by setting TimeZone.

Upvotes: 9

Related Questions