PostgreSQL中的本地时区偏移 [英] Local time zone offset in PostgreSQL
问题描述
我的Web应用程序将所有时间戳存储在UTC中,没有时区。我有一个shell / psql脚本,该脚本返回最近登录的列表,并且我希望该脚本在服务器的本地时区显示登录时间(该时间可能会随服务器所在的位置和夏令时而有所不同)。
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).
要获取代表我的数据库服务器时区和UTC之间的时差的间隔,我目前正在使用此hack:
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?
有一个名为 timezone的服务器配置参数,该参数返回有效的时区字符串,但是我认为不可能在查询中访问这些参数。 (我想这是一个单独的问题,但对它的回答将解决时区问题。)
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.
您的解决方案很好。
这篇关于PostgreSQL中的本地时区偏移的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!