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 are very simple to use as a data type. For example:
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.
You can tell Postgres which time zone to use when making a request for a timestamp column. Here are two examples:
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.
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:
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:
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.
Instead of specifying the timezone in the client connections, it can also be set once within the database itself. For example:
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:
To set the database so it uses the same timezone as the underlying Linux server is configured to use:
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:
Multiple string formats can be used when setting the timezone. The two easiest of which are the full name, or the abbreviation, such as:
The currently-selected timezone can also be queried:
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:
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: