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, Jan 21 '10 at 16:52

score 54 · Accepted Answer · answered Jan 21 '10 at 15:15

54

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 '10 at 15:15

Quassnoi

413,100
91
616
614

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. – TimH Jan 22 '10 at 01:16
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'. – Craig Ringer Sep 09 '12 at 11:01
1

@CraigRinger: everything returned by `CURRENT_SETTING('timezone')` can be fed to `AT TIME ZONE` without problems. – Quassnoi Sep 09 '12 at 11:32
2

@TimH: As I found out the hard way, [time zone abbreviations seem to take precedence](http://stackoverflow.com/questions/12042506/time-zone-names-with-identical-properties-yield-different-result-when-applied-to). Stay away from ambiguous time zone names like `CET` - they would be interpreted as time zone abbreviation with subtly different effects. – Erwin Brandstetter Nov 29 '13 at 17:00

score 11 · Answer 2 · answered Dec 11 '13 at 04:17

11

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

select extract( timezone from now() );

answered Dec 11 '13 at 04:17

shaunc

5,317
4
43
58

score 0 · Answer 3 · answered Mar 03 '22 at 22:10

0

Get the interval based on shaunc's answer:

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

answered Mar 03 '22 at 22:10

jqgsninimo

6,562
1
36
30

Local time zone offset in PostgreSQL

3 Answers3

Linked