PostgreSQL how to convert date with timezone offset to UTC?

Question

I have a PostgreSQL table with date field in the following format

2017-09-07T17:24:33+08:00

and I want to convert it to UTC time.

I've looked around but found no way to do that with this specific time format. What am I missing?

Thanks

Answer 1

I have a PostgreSQL table with date field in the following format: 2017-09-07T17:24:33+08:00

This is incorrect. Per Postgres documentation,

All timezone-aware dates and times are stored internally in UTC. They are converted to local time in the zone specified by the TimeZone configuration parameter before being displayed to the client.

To display a TIMESTAMP WITH TIME ZONE attribute (which, again, is stored internally in UTC) in a specific time zone, you have a few options:

• By default, “They are converted to local time in the zone specified by the TimeZone configuration parameter before being displayed to the client.” This can be set in postgresql.conf, using the SQL command SET TIME ZONE, or using a number of other options.
• Use the AT TIME ZONE construct.

So, in regards to your original question: You don't have to do anything to your timestamptz for Postgres to store it in UTC. To display your attribute in UTC, you can either change the TimeZone configuration paramter, or use the construct below:

SELECT dt_with_timezone AT TIME ZONE 'UTC' FROM my_table