How to store dates and times in PostgreSQL

The ultimate guide to understanding timestamp data types.

TL;DR, Use PostgreSQL’s “timestamp with time zone” (“timestamptz”).

When developing software applications, we usually have users across multiple geographic areas in different timezones, which means that to accurately represent a date and time in a single data type(timestamp) it needs to be timezone aware.

In this article, we are going to explore how to store timestamps in PostgreSQL and how to make our life more comfortable, as software developers, by deferring the “hard work” to the PostgreSQL runtime.

Constraints

• PostgreSQL has a timezone configuration parameter that can be modified. Usually, it’s only set once, ‘UTC’ in most cases.

-- Check timezone in psql
SHOW time zone;
+---------------+
| TimeZone      |
+---------------+
| UTC           |
+---------------+
-- Change timezone value
SET TIME ZONE 'Europe/Rome';
-- Check new timezone value
SHOW time zone;
+---------------+
| TimeZone      |
+---------------+
| 'Europe/Rome' |
+---------------+

• By timezone aware timestamps we mean timestamps with an offset, e.g., 2000–01–01 00:00:00 +0700
• In most cases, the client attempts to store timezone aware timestamps.
• In the rare case in which a client attempts to store a timezone unaware timestamp, we’ll assume the PostgreSQL timezone configuration value as its timezone.
• Every time that we read a timestamp value, it should be timezone aware.

PostgreSQL date and times data types

PostgreSQL has six different data types to represent date and times from which only two store a date and a time in a single data type:

• “timestamp without time zone” (“timestamp”).
• “timestamp with time zone” (“timestamptz”),

From the documentation, it looks like both data types use 8 bytes of storage size, which doesn’t seem right because “timestamptz” should be storing more information that “timestamp”. Let’s make sure that the docs are right:

SELECT 
pg_column_size('2000-01-01 00:00:00 +00:00'::timestamp) as "timestamp byte size",
pg_column_size('2000-01-01 00:00:00 +00:00'::timestamptz) as "timestamptz byte size";
+--------------------+-----------------------+
|timestamp byte size | timestamptz byte size |
+--------------------+-----------------------+
|                  8 |                     8 |
+--------------------+-----------------------+

What?

Both data types have 8 bytes. PostgreSQL is storing the same quantity of data for both data types, which means that “timestamptz” is not saving any additional timezone information.

Weird, how does it work?

timestamp vs. timestamptz

These 2 PostgreSQL data types store date and time in a single field, the difference is that “timestamptz” converts the value to UTC and “timestamp” doesn’t.

Let’s see an example:

SET TIME ZONE 'UTC';
SELECT
'2000-01-01 00:00:00 +05:00'::timestamp as "Timestamp without time zone",
'2000-01-01 00:00:00 +05:00'::timestamptz as "Timestamp with time zone";
+---------------------------+------------------------+
|Timestamp without time zone|Timestamp with time zone|
+---------------------------+------------------------+
| 2000-01-01 00:00:00       |1999-12-31 19:00:00+00  |
+---------------------------+------------------------+

From the previous example:

• The “timestamp” data type ignores the offset (‘+05:00’) from the original value.
• The “timestamptz” data type takes into account the offset (‘+05:00’) from the original value.

Better PostgreSQL timestamp data type naming

As we’ve seen, both datatypes store the same information, none related to timezone. “timestamptz” takes into account the offset, while “timestamp” ignores it. Here, better naming:

• “timestamp without time zone” (“timestamp”) means “timestamp offset unaware”.
• “timestamp with time zone” (“timestamptz”) means “timestamp offset aware”.

Business use case

Imagine we are developing an app that provides a chat interface to its users. Users can send messages, and we want to be able to show the messages’ timestamp to each user taking into account their geographical location (timezone).

For example, a user in UTC visualizes a message with timestamp “2000–01–01 00:00:00”, and a different user in Rome (UTC -1) visualizes the same message with timestamp “2000–01–01 01:00:00”.

Let’s explore the work to be done when choosing either “timestamp” or “timestamptz”:

1. Using timestamp without time zone (timestamp)

If we use “timestamp” as the data type to store the message’s date and time:

• The application layer needs to convert all timestamps to a predetermined timezone, which we’ll call “global timezone” (usually UTC) before writing it to the database.
• We need to set the PostgreSQL timezone configuration value to be the same as the “global timezone” in the application; hence both the database and the application need to be in sync.
• When reading from the database, if we want to convert the stored timestamp to the client’s timezone at the database layer we need to cast the value from “timestamp” to “timestamptz” and then convert it to the client’s timezone, e.g.:

SELECT ('2000-01-01 00:00:00 +00:00'::timestamp)::timestamptz at time zone 'Europe/Rome' as "Rome's timestamp";
+----------------------+
|  Rome's timestamp    |
+----------------------+
| 2000-01-01 01:00:00  |
+----------------------+

• When reading from the database, if we want to convert the stored timestamp to the client’s timezone at the application layer, we’ll gather the value from the database (timezone unaware), treat it as timezone aware using the “base timezone” and convert it to the client’s timezone.

2. Using timestamp with time zone (timestamptz)

If we use “timestamptz” as the data type to store the message’s date and time:

• When writing to the database, we’ll let PostgreSQL convert the timestamps to UTC (done automatically by setting the data type to be “timestamptz”).
• We can set the PostgreSQL timezone configuration value to whatever value we want (I always set it to UTC). This timezone value will only be relevant in the minority of cases where the client writes a timezone unaware timestamp, in which case the PostgreSQL runtime assigns its timezone configuration value to the timestamp, making it timezone aware.
• To convert the stored timestamp to the client’s timezone at the database layer we only need to convert it to the client’s timezone, e.g.:

SELECT '2000-01-01 00:00:00 +00:00'::timestamptz at time zone 'Europe/Rome' as "Rome's timestamp";
+----------------------+
|  Rome's timestamp    |
+----------------------+
| 2000-01-01 01:00:00  |
+----------------------+

• To convert the stored timestamp to the client’s timezone at the application layer, we’ll gather the value from the database (timezone aware) and convert it to the client’s timezone.

Building the system

In this case using “timestamptz” is a better approach than using “timestamp”, and honestly, I yet have to discover a use case in which using “timestamp” is superior to using “timestamptz”.

By using “timestamptz” instead of “timestamp”, we simplify the system:

• The application layer writing to the database is simpler because we don’t need to convert every timestamp to a previously determined timezone.
• We don’t need to synchronize the timezone used by PostgreSQL and the timezone used by the application layer (what we’ve previously called “global timezone”).
• When reading from the database and converting to the user’s timezone at the database layer, the data transformation has fewer steps.
• When reading from the database and converting to the user’s timezone at the application layer, we don’t need to treat a timezone unaware timestamp as a timezone aware one.

The database, like any other “piece” of the system, doesn’t live in isolation. It always needs an application layer reading from and writing to it.

This application layer enables enough flexibility to implement whatever logic we need. However, this flexibility comes with the inconvenient of having to maintain every line of code that we write.

In this case, building the system means using the right PostgreSQL data type and letting its runtime do the “hard work” of storing every timestamp at UTC.