CODEWITHSUNDEEP
postgresqltime
three-cups
three-cups

Reputation: 4385

Retrieving UTC timestamptzs from postgres in the correct time zone

I store timestamps with time zone in my postgres database. The server time zone is UTC. All the timestamptzs are stored as UTC datetimes.

Now, I'd like to retrieve those timestamps, but for a specific time zone (e.g. US/Pacific). Because of daylight savings time, the correct time zone is sometimes PDT and sometimes PST. So I can't just run a query like select t at time zone 'pdt' because this will be wrong for the pst dates.

Is there a way to pull the dates from the database in the correct time zone?

Upvotes: 1

Views: 170

Answers (1)

Dzmitry Savinkou
Dzmitry Savinkou

Reputation: 5190

According to the documentation, together with TIME ZONE code you can also specify locales. For your case you can use something like that:

ds=# SELECT current_setting('TIMEZONE');
 current_setting 
-----------------
 UTC
(1 row)

ds=# SELECT now();
              now              
-------------------------------
 2015-11-05 00:35:03.126317+00
(1 row)

pm7=# SELECT now() AT TIME ZONE 'America/Los_Angeles';
          timezone          
----------------------------
 2015-11-04 16:35:06.344367
(1 row)

Upvotes: 2

Related Questions