Date/Time and Time Zones
Learn how the date, time, and time zones work in PostgreSQL.
We'll cover the following...
Handling dates, time, and time zones is a very complex matter, and on this topic, we can read Erik Naggum’s piece, “The Long, Painful History of Time.”
The timestamp data type
The first question we need to answer here is about using timestamps with or without time zones from our applications. The answer is simple: always use timestamps with time zones.
A common myth is that storing time zones will certainly add to our storage and memory footprint. It’s actually not the case:
select pg_column_size(timestamp without time zone 'now'),pg_column_size(timestamp with time zone 'now');
PostgreSQL defaults to using bigint internally to store timestamps, and the on-disk and in-memory formats are the same with or without time zone support. Here’s their whole type definition in the PostgreSQL source code (in src/include/datatype/timestamp.h):
typedef int64 Timestamp;
typedef int64 TimestampTz;
From the PostgreSQL documentation for timestamps, here’s how it works:
Note: For timestamps with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an ...