Local time zone offset in PostgreSQL

Question

My web app stores all timestamps in UTC without time zones. I have a shell/psql script that returns a list of recent logins, and I want that script to display login times in the server's local time zone (which may vary depending on where the server is and with daylight savings).

To get an interval representing the difference between my database server's time zone and UTC, I'm currently using this hack:

SELECT age(now() at time zone 'UTC', now());

That works, but is there a more straightforward way?

There's a server configuration parameter called "timezone" that returns a valid timezone string, but I don't think it's possible to access those parameters in a query. (I guess that's a separate question, but an answer to it would resolve the time zone issue.)

Thanks for the question. I added a paragraph explaining why I want to get the difference. — TimH
– TimH, Commented Jan 21, 2010 at 16:52

Quassnoi · Accepted Answer · 2010-01-21 15:15:22Z

55

SELECT  current_setting('TIMEZONE')

This can be used in a query, however, this does not give a numerical difference.

Your solution is fine.

answered Jan 21, 2010 at 15:15

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

TimH Over a year ago

Thanks, that's what I needed to know. This will work: SELECT some_table.utc_timestamp at time zone current_setting('TIMEZONE'); And that's a lot clearer to someone reading the code than my hack.

Craig Ringer Over a year ago

The only downside of this is that it can produce an offset like '-05:00', a full length timezone name like 'Australia/Perth', or even a potentially ambiguous abbreviation like 'EST'.

Quassnoi Over a year ago

@CraigRinger: everything returned by CURRENT_SETTING('timezone') can be fed to AT TIME ZONE without problems.

Erwin Brandstetter Over a year ago

@TimH: As I found out the hard way, time zone abbreviations seem to take precedence. Stay away from ambiguous time zone names like CET - they would be interpreted as time zone abbreviation with subtly different effects.

shaunc · Accepted Answer · 2013-12-11 04:17:32Z

12

To get # of seconds difference as an integer, I have used simply:

select extract( timezone from now() );

answered Dec 11, 2013 at 4:17

shaunc

5,6614 gold badges48 silver badges67 bronze badges

Comments

jqgsninimo · Accepted Answer · 2022-03-03 22:10:56Z

0

Get the interval based on shaunc's answer:

select (extract(timezone from now()) || 'seconds')::interval;

answered Mar 3, 2022 at 22:10

jqgsninimo

7,1681 gold badge40 silver badges32 bronze badges

Collectives™ on Stack Overflow

Local time zone offset in PostgreSQL

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related