英文:
Same location with different time in the year PostgreSQL
问题
我遇到了一个关于timestamptz（带有时区的时间戳）的问题。我有一个纽约时间，例如：下午2点纽约东部标准时间。我将其输入为14:00:00 - 04:00（UTC-4），因为现在是七月。然而，当时间从十一月开始时，它将变为14:00:00 - 05:00（UTC-5）。并且在接下来的一年中，它将回到UTC-4。我该如何自动进行转换？
我希望它能根据系统时间自动转换为UTC-5和UTC-4。
英文:
I got an issue with timestamptz (timestamp with timezone). I have New York time, ex: 2pm NY EST. I will input it as 14:00:00 - 04:00 (UTC-4) because it is July now. However, when it starts from November, it will be 14:00:00 - 05:00 (UTC-5). And it will be turn back to UTC -4 in the following year. How can I do to convert it automatically?
I would like that it will convert to the UTC -5 and UTC-4 automatically depends on the system time.
答案1
得分: 1
如果我理解正确，您可以在SQL查询中使用at time zone 'America/New_York'。类似这样的代码：
postgres=# select '2023-07-15 13:00:00-04:00'::timestamptz at time zone 'America/New_York';
timezone
---------------------
2023-07-15 13:00:00
(1 row)
postgres=# select '2023-01-15 13:00:00-04:00'::timestamptz at time zone 'America/New_York';
timezone
---------------------
2023-01-15 12:00:00
(1 row)
英文:
If I got the question right You can use at time zone 'America/New_York' in your SQL queries. Something like that:
postgres=# select '2023-07-15 13:00:00-04:00'::timestamptz at time zone 'America/New_York';
timezone
---------------------
2023-07-15 13:00:00
(1 row)
postgres=# select '2023-01-15 13:00:00-04:00'::timestamptz at time zone 'America/New_York';
timezone
---------------------
2023-01-15 12:00:00
(1 row)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论