SQL - From timestamptz A to timestamptz B

Let’s see how to convert a timestamp from a timezone to another.

To be able to convert the value to another timezone, it is important to use a datetime aware of the timezone: timestamptz.

Configuration of the timezone can be tuned in postgresql.conf Mine is ‘America/Toronto’.

It means that when the database will be storing records, it will use the America/Toronto time.

For PostgreSQL (tested on 9.4)

Let create an initial record.

CREATE TABLE tests(id serial not null, created_on timestamptz);
INSERT INTO tests (created_on) VALUES (now());
2016-02-16 19:26:13.823126-05

Here how to convert the current timestamp to another timezone.

SELECT created_on  AT TIME ZONE 'Europe/Paris' FROM tests;
2016-02-17 01:26:13.823126