将Postgresql中的UTC时区转换为EST（当地时间） [英] Convert a UTC timezone in postgresql to EST (local time)

问题描述

我是PostgreSQL的新手，我想知道是否有直接方法可以使用函数将表中的时间戳值转换为其他时区。在我的情况下，这是UTC到EST的时间。

I am new to PostgreSQL and I was wondering if there is a direct way to just convert the timestamp values in a table to a different timezone using a function. In my case it is UTC to EST.

这些是例如我需要转换为EST的值（不仅是一个值，而是表中的所有值）

These are the values for example that I need to convert to EST (not just one value but all the values in the table)

date
-------------------
2015-10-24 16:38:46
2016-01-19 18:27:00
2016-01-24 16:14:34
2016-02-09 23:05:49
2016-02-11 20:46:26

推荐答案

在伦敦，我们目前比世界标准时间早1小时。所以-如果我将您的时区不加时间戳记，并说它是UTC，我会为我的本地时区打印出来。

Here in London, we are currently 1 hour ahead of UTC. So - if I take your timezone without timestamp and say it is in UTC I will get it printed for my local timezone.

richardh=> SELECT ((timestamp '2015-10-24 16:38:46') AT TIME ZONE 'UTC');
        timezone        
------------------------
 2015-10-24 17:38:46+01
(1 row)

但是您想要 EST似乎在美洲的某个地方，根据返回的值判断。

But you want "EST" which seems to be somewhere in the Americas, judging by the value returned. You can wrap the expression in a little SQL function if you wanted to.

richardh=> SELECT ((timestamp '2015-10-24 16:38:46') AT TIME ZONE 'UTC') AT TIME ZONE 'EST';
      timezone       
---------------------
 2015-10-24 11:38:46
(1 row)

---

编辑：如何在查询

---

how to do it in a query

SELECT ((stored_timestamp AT TIME ZONE 'UTC') AT TIME ZONE 'EST') AS local_timestamp
FROM my_table;

如果这种事情引起您麻烦，您可能想获得一本有关SQL的入门书籍。

You will probably want to get an introductory book on SQL if this sort of thing is causing you problems.

这篇关于将Postgresql中的UTC时区转换为EST（当地时间）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！