如何从PostgreSQL以UTC偏移量获取ISO格式的时间? [英] How to get time in ISO format with UTC offset from PostgreSQL?
本文介绍了如何从PostgreSQL以UTC偏移量获取ISO格式的时间?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
在python中,我可以这样获得ISO格式的不同时区的时间:
In python I can get time for different time zones in ISO format like this:
>>> import datetime
>>> import pytz
>>> datetime.datetime.now(tz=pytz.timezone('America/New_York')).isoformat()
'2019-03-15T04:01:23.919800-04:00'
>>> datetime.datetime.now(tz=pytz.timezone('Asia/Bangkok')).isoformat()
'2019-03-15T15:01:23.919800+07:00'
现在我需要以相同的格式从postgresql获取时间戳。经过研究后,我得出的最好结论是:
Now I need to get timestamps from postgresql in the same format. After some research the best I've come up with is this:
postgres=# set time zone 'America/New_York';
SET
postgres=# select to_json(now());
to_json
------------------------------------
"2019-03-15T04:32:13.628323-04:00"
是否可以做到这一点而无需更改数据库会话的时区?像这样的东西:
Is it possible to do that without changing the time zone of the database session? Something like:
postgres=# select to_json(now() at time zone 'America/New_York') NYC,
postgres-# to_json(now() at time zone 'Asia/Bangkok') Bangkok;
nyc | bangkok
------------------------------+------------------------------
"2019-03-15T04:41:07.789954" | "2019-03-15T15:41:07.789954"
否则是正确的,但缺少UTC偏移量。
which is otherwise correct but missing the UTC offset.
推荐答案
如何?
SELECT current_timestamp AT TIME ZONE 'America/New_York'
|| replace('+' || to_char(current_timestamp AT TIME ZONE 'America/New_York'
- current_timestamp AT TIME ZONE 'UTC',
'HH24:MMFM'),
'+-', '-');
?column?
----------------------------------
2019-03-15 05:43:38.901642-04:00
(1 row)
这篇关于如何从PostgreSQL以UTC偏移量获取ISO格式的时间?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文