Oracle Timezone Conversions – GMT to localtime (and back)
| Posted by watashii | Filed under Oracle, Programming
GMT to Local Time
Convert a datetime value (which has no timezone info, but assumed as GMT/UTC) to a local datetime (based on timezone parameter as set by SESSIONTIMEZONE, eg ‘+11:00′):
SELECT CAST((FROM_TZ(CAST(TO_DATE('1999-12-01 11:00:00',
'YYYY-MM-DD HH24:MI:SS') AS TIMESTAMP), 'GMT')
AT LOCAL) AS DATE) "Local Time"
FROM DUAL;
Local Time
--------------------------------------------------
1/12/1999 10:00:00 PM
Explaining the code:
1) TO_DATE(‘…’, ‘YYYY-MM-DD HH24:MI:SS’) converts the string value (which we assume it is GMT) to a datetime datatype. Contains no timezone info.
2) CAST(… AS TIMESTAMP) converts the datetime value to a timestamp datatype. Contains no timezone info.
3) FROM_TZ(…, ‘GMT’) adds the timezone value to the timestamp datatype. In this case, the GMT timezone was added. Alternatively use: FROM_TZ(…, ‘+00:00′)
4) … AT LOCAL performs the time shift of the timestamp value into Oracle DB’s local timestamp. It is also updated with the new timezone info (based on the SESSIONTIMEZONE value, such as +11:00). Alternatively use: … AT TIME ZONE SESSIONTIMEZONE or … AT TIME ZONE ‘+11:00′
5) CAST(… AS DATE) converts the timestamp value back to a datetime datatype. Timezone info is discarded.
Alternatives that does the same are:
SELECT CAST((FROM_TZ(CAST(TO_DATE('1999-12-01 11:00:00',
'YYYY-MM-DD HH24:MI:SS') AS TIMESTAMP), 'GMT')
AT TIME ZONE SESSIONTIMEZONE) AS DATE)
FROM DUAL;
SELECT CAST((FROM_TZ(CAST(TO_DATE('1999-12-01 11:00:00',
'YYYY-MM-DD HH24:MI:SS') AS TIMESTAMP), '+00:00')
AT TIME ZONE '+11:00') AS DATE)
FROM DUAL;
SELECT CAST((FROM_TZ(CAST(TO_DATE('1999-12-01 11:00:00',
'YYYY-MM-DD HH24:MI:SS') AS TIMESTAMP), 'GMT')
AT TIME ZONE 'Australia/Victoria') AS DATE)
FROM DUAL;Local Time to GMT
To perform a reverse from a local time back to GMT time, we can simply reverse the timezone indicators in the same query:
SELECT CAST((FROM_TZ(CAST(TO_DATE('1999-12-01 22:00:00',
'YYYY-MM-DD HH24:MI:SS') AS TIMESTAMP), SESSIONTIMEZONE)
AT TIME ZONE 'GMT') AS DATE)
FROM DUAL;Share:
Related Posts:
Tags: gmt, localtime, oracle, timezone, utc
One Response to “Oracle Timezone Conversions – GMT to localtime (and back)”
-
Richard Says:
April 12th, 2010 at 10:28 pmGreat post – thanks for the information. This came in handy today :-)
