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
Add a comment
|
2 Answers
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 commandSET TIME ZONE, or using a number of other options. - Use the
AT TIME ZONEconstruct.
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
2 Comments
Ilya Semenov
SET TIME ZONE and AT TIME ZONE are different things. The latter drops time zone completely by changing the type of value. The output will be 2017-09-07 09:24:33+00 and 2017-09-07 09:24:33, respectively.
Zack
@IlyaSemenov: thanks for the clarification!
timezone definition (https://www.postgresql.org/docs/9.1/functions-datetime.html) : The function timezone(zone, timestamp) is equivalent to the SQL-conforming construct timestamp AT TIME ZONE zone
SELECT timezone('UTC','2017-09-07T17:24:33+08:00');
if selecting from column,
with t as (
SELECT '2017-09-07T17:24:33+08:00' as tm
)
SELECT timezone('UTC',tm::timestamptz) as ts
from t;
3 Comments
crash
Thanks, this works as you wrote it but it does not work when I use a table column instead of the hardcoded timestamp (eg.
SELECT timezone('UTC', time_column)). I'm getting the error SQL Error [42883]: ERROR: function timezone(unknown, text) does not exist. Hint: No function matches the given name and argument types. You might need to add explicit type casts.. Should I cast the column to timestamp? If you have any suggestion can you update your answer? Thanks!
jimmu
@crash function expects timestamp datatype, so casting should work, see updated answer
Belayer
If it's at all possible change the column definition from "text" to "timestamp with time zone". If you leave it as text you can count on having issues at some point. Always store date containing data as a date defining data_type.