CODEWITHSUNDEEP
postgresqltimezonepostgresql-10
hyphen
hyphen

Reputation: 3450

PG SQL 10 set default time zone to UTC

I need to change the database config to use UTC as the default for a PostgreSQL10 instance hosted on AWS RDS. I want it permanently changed at the database level and to never revert back to any other timezone no matter what.

I've tried running this, but it shows 0 updated rows:

ALTER DATABASE <my-db> SET timezone='UTC';

I've tried attaching a custom param group to the DB in RDS and modifying the entries like so (also rebooted after):

enter image description here

No matter what I do, when I run select * from pg_settings where name = 'TimeZone'; or SHOW timezone it shows 'America/Chicago'.

It seems like this should be easy to do, but it is proving to be a challenge.

Upvotes: 0

Views: 1540

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 247980

If you want to store your timestamps in UTC and always want the database to send the data to the client in UTC as well, you should use the data type timestamp without time zone, which will not perform any time zone handling for you. That would be the simplest solution.

To convert the data, you could proceed like this:

SET timezone = 'UTC';
ALTER TABLE mytable ALTER timestampcol TYPE timestamp without time zone;

Upvotes: 2

hyphen
hyphen

Reputation: 3450

Based on this dba.stackexchange.com question. Apparently PG stores the timestamp in UTC time but then converts it to the session time zone. From what I've gathered, since my timestamps don't include time zone information, I need to tell PG that the timezone being stored is UTC, and then convert that to whatever local time is needed, so something like this:

SELECT my_timestamp_in_utc AT TIME ZONE 'UTC' AT TIME ZONE 'America/Denver' as my_local_time FROM my_table;

This is a little verbose, but I'll go with it for now.

Upvotes: 0

Related Questions