Programming Comments - PostgreSQL, timestamps, and time zones

Summary

PostgreSQL defines data types for timestamps as well as timestamps with time zones. I strongly recommend that you never create a timestamp without a timezone. If you need a timestamp, then you likely will also need the timezone, and if you create your timestamps correctly from the start, there is a trivial amount of work required on your part to correctly handle time zones.

Timestamps

Timestamps are very simple to use as a data type. For example:

CREATE TABLE testing
(
	id		BIGSERIAL			NOT NULL UNIQUE PRIMARY KEY,
	event_timestamp TIMESTAMP WITH TIME ZONE	NOT NULL DEFAULT NOW(),
	event_address	INET				NOT NULL DEFAULT inet_client_addr(),
	event_text	TEXT 				NOT NULL CHECK (event_text <> '')
);

Remember to always use WITH TIME ZONE when creating TIMESTAMP types! Internally, PostgreSQL converts the timestamp to UTC when writing the column to disk. This way even if the server is moved and the time zone changes, all of the timestamp data stored in the tables remains valid.

If the server actually uses UTC, then retrieving the data will return timestamps in UTC. If the server is setup with a specific time zone, then timestamps will automatically be converted to that time zone.

But sometimes the server doesn't reside in the same location as the client. Or better yet, you may have multiple clients connecting from differen time zones.

Luckily, there are several ways to deal with this, depending on the type of solution needed.

Explicit client-side time zones

You can tell Postgres which time zone to use when making a request for a timestamp column. Here are two examples:

SELECT event_text, event_timestamp AT TIME ZONE 'PST8PDT' FROM testing WHERE id=123;
SELECT event_text, event_timestamp AT TIME ZONE 'America/New_York' FROM testing WHERE id=123;

But this quickly becomes tedious since it requires you to 1) explicitely name the columns and, 2) to name the time zone whenever a table has a timestamp column.

Implicit client-side time zones

If you instead want to have all timestamps automatically converted to a specific time zone, you need to tell PostgreSQL which time zone to use as a default. This is done once per connection:

SET SESSION TIME ZONE 'Europe/Berlin';

The "session" settings apply only to the current client connection, it isn't global, nor does it change anything on the server. So multiple clients, each with a unique session, can set a specific time zone to use. Once the session time zone is set, there is no more need to ask for a specific timezone in requests:

SELECT * FROM testing WHERE id=123;
-- event_timestamp will now be shown using the Europe/Berlin time zone
-- due to the previous SET SESSION call from several lines above

So when a client first connects to the server, make sure it issues a call to SET SESSION TIME ZONE, and the rest of your SQL queries never have to change or make a concious decision to correctly handle time zones.

Database server-side time zones

Instead of specifying the timezone in the client connections, it can also be set once within the database itself. For example:

ALTER DATABASE postgres SET timezone TO 'America/Vancouver';
ALTER DATABASE mytestdb SET timezone TO 'America/Vancouver';

You'll need to let PostgreSQL know to reload configuration. Either restart the service, or SELECT pg_reload_conf(); should do the trick.

If possible, it is better to leave the server running in UTC, and instead set the time zone from a client perspective. To set the database back to UTC:

ALTER DATABASE postgres SET timezone TO 'UTC';
ALTER DATABASE mytestdb SET timezone TO 'UTC';

To set the database so it uses the same timezone as the underlying Linux server is configured to use:

ALTER DATABASE postgres SET timezone TO LOCAL;
ALTER DATABASE mytestdb SET timezone TO LOCAL;

Time zones

The only thing that remains is determining the name to use when setting a time zone. Turns out this is easy, as PostgreSQL has a table that lists all of the possible time zones that the server understands:

# SELECT COUNT(*) FROM pg_timezone_names;
 count 
-------
  1212

# SELECT * FROM pg_timezone_names ORDER BY name;
                  name                  | abbrev | utc_offset | is_dst 
----------------------------------------+--------+------------+--------
 Africa/Abidjan                         | GMT    | 00:00:00   | f
 Africa/Accra                           | GMT    | 00:00:00   | f
 Africa/Addis_Ababa                     | EAT    | 03:00:00   | f
 Africa/Algiers                         | CET    | 01:00:00   | f
 Africa/Asmara                          | EAT    | 03:00:00   | f
...etc...

Multiple string formats can be used when setting the timezone. The two easiest of which are the full name, or the abbreviation, such as:

SET SESSION TIME ZONE 'Asia/Hong_Kong';

or:

SET SESSION TIME ZONE 'HKT';

The currently-selected timezone can also be queried:

# SHOW TIMEZONE;
     TimeZone      
-------------------
 America/Vancouver

# SELECT * FROM pg_settings WHERE UPPER(name) LIKE '%TIMEZONE%';
...etc...

Epoch

Marginally related to time zones is the concept of 'epoch'. Epoch is normally defined as 1970-01-01 00:00:00 UTC. Many C/C++ libraries that deal with "time" take a std::time_t variable, which is a counter for the number of seconds that have ellapsed since 'epoch'. If necessary, you can easily convert a PostgreSQL timestamp column to an epoch-based std::time_t using the following:

SELECT
    EXTRACT(EPOCH FROM event_timestamp)::INTEGER AS event_epoch
    FROM testing
    WHERE id=123;

As long as you've issued a SET SESSION TIME ZONE, the result will be the number of seconds since 1970-01-01 00:00:00 UTC, normalized to the relevant time zone.

Creating a timestamp from an epoch-style std::time_t is also very easy with the to_timestamp() function:

UPDATE testing SET event_timestamp=TO_TIMESTAMP(1234567890) WHERE id=123;