sql - 将文本转换为带时区的时间

Question

以下列格式接收时间:

17:42:40 GMT+0300 (EEST)

16:42:40 GMT+0200 (CEST)

12:42:40 GMT-0200 (WGST)

如何将此文本转换为time with timezone 17:42:40+3 类型，以便将其插入到具有此类类型的表列中。

Answer 1

t=# select replace('17:42:40 GMT+0300 (EEST)','00 (EEST)','')::timetz;
   replace
-------------
 17:42:40-03
(1 row)

https://www.postgresql.org/docs/current/static/datatype-datetime.html

Another issue to keep in mind is that in POSIX time zone names, positive offsets are used for locations west of Greenwich. Everywhere else, PostgreSQL follows the ISO-8601 convention that positive timezone offsets are east of Greenwich.

您可能还想将 TZ 转换为小时:分钟形式，忽略括号中的值:

t=# select '17:42:40 GMT+03:00'::timetz;
   timetz
-------------
 17:42:40-03
(1 row)