开发者

Local time zone offset in PostgreSQL

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 开发者_JAVA百科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.)


SELECT  current_setting('TIMEZONE')

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

Your solution is fine.


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

select extract( timezone from now() );


Get the interval based on shaunc's answer:

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

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜