将时间戳/日期时间从 UTC 转换为 EST Oracle SQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1751075/
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
Convert timestamp/date time from UTC to EST Oracle SQL
提问by Phill Pafford
I have a field with a date/time value like this:
我有一个带有日期/时间值的字段，如下所示：
2009-11-17 18:40:05
It's in UTC. In the query how can I convert this to EST?
它是 UTC。在查询中如何将其转换为 EST？
I'm trying something like this but it throws an error.
我正在尝试这样的事情，但它会引发错误。
// datetime is the field name
SELECT
FROM_TZ(TIMESTAMP TO_DATE(datetime, 'yyyy-mm-dd hh24miss'), 'EST') AS DT
FROM
db_name
回答by Dan
I had to tweak it slightly to get it to work on my database, but this worked:
我不得不稍微调整它才能让它在我的数据库上工作，但这有效：
select from_tz(to_timestamp('2009-11-17 18:40:05','yyyy-mm-dd hh24:mi:ss'), 'UTC')
at time zone 'America/New_York' from dual
The key is the "at time zone" syntax.
关键是“在时区”语法。
回答by Trevor.Screws
If you want to convert a date field from UTC to EST, this worked for me:
如果要将日期字段从 UTC 转换为 EST，这对我有用：
CAST(FROM_TZ(CAST(DATE_FIELD AS TIMESTAMP), 'UTC')
at time zone 'America/New_York' AS Date) as DESIRED_FIELD_NAME
First, I cast the desired date field (as DATE_FIELD) to a timestamp. The result of the cast is the first parameter of the FROM_TZ function, which requires the parameter to be of type TIMESTAMP. The second parameter is 'UTC', since that is what we are changing from.
首先，我将所需的日期字段（如 DATE_FIELD）转换为时间戳。强制转换的结果是 FROM_TZ 函数的第一个参数，它要求参数的类型为 TIMESTAMP。第二个参数是“UTC”，因为这是我们要改变的。
Then, I cast the results of that function call back to type DATE and give it an alias.
然后，我将该函数调用的结果强制转换回 DATE 类型并给它一个别名。
回答by Vilas
select to_char(systimestamp at time zone 'EST','HH') EST_TIME,
TO_CHAR(SYSDATE,'HH') EDT_TIME,
NEW_TIME(SYSDATE,
(
CASE
WHEN to_char(systimestamp at time zone 'EST','HH') = TO_CHAR(SYSDATE,'HH')
THEN 'EST'
ELSE 'EDT'
END
),'GMT')
from dual
