postgresql 如何将字符串转换为所需时区的时间戳
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42684304/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to convert a string to timestamp in a desired timezone
提问by spoonboy
I need to convert a string that represents a timestamp without time zone (i.e. "2017-03-08T20:53:05") in some time zone (e.g.'Etc/UTC') to a timestamp with timezone.
我需要将表示某个时区（例如'Etc/UTC'）中没有时区的时间戳（即“2017-03-08T20:53:05”）的字符串转换为带时区的时间戳。
The issue is that the default time zone is not 'Etc/UTC'. So, when I'm trying to to
问题是默认时区不是“Etc/UTC”。所以，当我试图
SELECT to_timestamp(field1, 'YYYY-MM-DD hh24:mi:ss')::timestamp without time zone at time zone 'Etc/UTC'
it converts the string to a value with the default timezone and then applies conversion from a local time zone to the 'Etc/UTC'. This is not what's requred.
它将字符串转换为具有默认时区的值，然后应用从本地时区到“Etc/UTC”的转换。这不是所需要的。
Basically, I'm looking for a way to tell postgres that the original string value is representing time in a particular timezone, not the default local one.
基本上，我正在寻找一种方法来告诉 postgres 原始字符串值代表特定时区中的时间，而不是默认的本地时区。
Thank you.
谢谢你。
Update: I've checked that the above statement is actually working.
更新：我已经检查过上述语句是否确实有效。
SELECT to_timestamp(field1, 'YYYY-MM-DD hh24:mi:ss')::timestamp without time zone at time zone 'Etc/UTC'
I've been mislead by by the client's timezone setting.
我被客户的时区设置误导了。
回答by SegFault
You could run set time zone UTC;before your query:
您可以set time zone UTC;在查询之前运行：
set time zone UTC;
SELECT to_timestamp(field1, 'YYYY-MM-DD hh24:mi:ss')::timestamp without time zone at time zone 'Etc/UTC';
Does this solve your issue ?
这能解决您的问题吗？
回答by Magdalena
::timestamp without time zoneis not needed, just do:
::timestamp without time zone不需要，只需执行以下操作：
SELECT to_timestamp(field1, 'YYYY-MM-DD hh24:mi:ss') at time zone 'Etc/UTC'
回答by shajji
First find your default timezone. by using following query.
首先找到您的默认时区。通过使用以下查询。
Select current_setting('timezone');
Select current_setting('timezone');
In my case Asia/Karachi
就我而言 Asia/Karachi
k, now just try following query.
k，现在只需尝试以下查询。
Select Cast('1990-01-25' as Date) at time zone '<Your Default Timezone>' at time zone 'utc';
In my case.
就我而言。
Select Cast('1990-01-25' as Date) at time zone 'Asia/Karachi' at time zone 'utc';
回答by Paolo Cappello
In my case this did the trick (thanks to shajji answer)
就我而言，这成功了（感谢 shajji 的回答）
to_timestamp(:date, 'YYYY-MM-DD"T"HH24:MI:SS:MS"Z"') at time zone (select current_setting('timezone')) at time zone 'Etc/UTC'
回答by The Red Pea
My need is different than OP. My string already knows its timezone. I just want to convert to the timestamptzdatatype.
我的需要与 OP 不同。我的字符串已经知道它的时区。我只想转换为timestamptz数据类型。
In other words: "how to convert a string in a desired timezone, to a timestamp". I could use the approach described hereand here; casting with ::timestamptz
换句话说：“如何将所需时区中的字符串转换为时间戳”。我可以使用这里和这里描述的方法；铸造与::timestamptz
SELECT '2016-01-01 00:00+10'::timestamptz;
SELECT '2016-01-01 00:00+10'::timestamptz;
