SQL SQL查询中的时区转换
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22933608/
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
Time zone conversion in SQL query
提问by satyanarayana
I am using a query to get some application Received Date from Oracle DB which is stored as GMT. Now I have to convert this to Eastern standard/daylight savings time while retrieving. I am using the below query for this:
我正在使用查询从存储为 GMT 的 Oracle DB 获取一些应用程序接收日期。现在我必须在检索时将其转换为东部标准/夏令时。我为此使用以下查询：
select to_char (new_time(application_recv_date,'gmt','est'), 'MON dd, YYYY') from application
It works fine for Standard time. But for daylight savings time we need to convert it to 'edt' based on timezone info. I am not very sure on how to do this. Please help me out
它适用于标准时间。但是对于夏令时，我们需要根据时区信息将其转换为“edt”。我不太确定如何做到这一点。请帮帮我
回答by Noel
You can use this query, without having to worry about timezone changes.
您可以使用此查询，而不必担心时区更改。
select to_char(cast(application_recv_date as timestamp) at time zone 'US/Eastern',
'MON dd, YYYY'
)
from application;
Ex:
前任：
EDT:
美东时间：
select cast(date'2014-04-08' as timestamp) d1,
cast(date'2014-04-08' as timestamp) at time zone 'US/Eastern' d2
from dual;
D1 D2
---------------------------------- -------------------------------------------
08-APR-14 12.00.00.000000 AM 07-APR-14 08.00.00.000000 PM US/EASTERN
EST:
美东时间：
select cast(date'2014-12-08' as timestamp) d1,
cast(date'2014-12-08' as timestamp) at time zone 'US/Eastern' d2
from dual;
D1 D2
---------------------------------- -------------------------------------------
08-DEC-14 12.00.00.000000 AM 07-DEC-14 07.00.00.000000 PM US/EASTERN
UPDATE:
更新：
Thanks to Alex Poole for reminding that, when timezone is not specified, local timezone is used for conversion.
感谢 Alex Poole 提醒，当未指定时区时，将使用本地时区进行转换。
To force the date to be recognized as GMT, use from_tz.
要强制将日期识别为 GMT，请使用 from_tz。
from_tz(cast(date'2014-12-08' as timestamp), 'GMT') at time zone 'US/Eastern'
回答by Jitender Kumar
In Oracle, you can achieve this with below query:
在 Oracle 中，您可以使用以下查询实现此目的：
Select current_timestamp, current_timestamp at time zone 'Australia/Sydney' from dual;
Where Australia/Sydneyis the name of your timezone in which you want to convert your time.
Australia/Sydney您要转换时间的时区名称在哪里。
