Wednesday, June 27, 2012

Converting GMT dates to local time zone

In TW 6.x the dates in LSW_TASK table are stored in GMT time zone. When querying the table directly (should be avoided in general) the dates can be converted to a given time zone similar to the following:

SELECT
    task_id,
    to_char(rcvd_datetime, 'yyyy/MM/dd HH24:mi:ss') AS rcvd_datetime,
    to_char(from_tz(CAST(rcvd_datetime AS TIMESTAMP),'GMT') AT TIME ZONE '+03:00', 'yyyy/MM/dd HH24:mi:ss') AS local_rcvd_datetime
    FROM
        lsw_task

In order to automate conversion and take into account Daylight Saving changes the query can use dbtimezone Oracle function (assuming the Oracle instance is configured correctly):

SELECT
    task_id,
    to_char(rcvd_datetime, 'yyyy/MM/dd HH24:mi:ss') AS rcvd_datetime,
    to_char(from_tz(CAST(rcvd_datetime AS TIMESTAMP),'GMT') AT TIME ZONE dbtimezone, 'yyyy/MM/dd HH24:mi:ss') AS local_rcvd_datetime
    FROM
        lsw_task


If the date needs to be displayed in TW's end user time zone, than the offset can be retrieved via tw.local.user_timeZoneOffset variable:

SELECT
    task_id,
    to_char(rcvd_datetime, 'yyyy/MM/dd HH24:mi:ss') AS rcvd_datetime,
    to_char(from_tz(CAST(rcvd_datetime AS TIMESTAMP),'GMT') AT TIME ZONE '<#= tw.local.user_timeZoneOffset #>', 'yyyy/MM/dd HH24:mi:ss') AS local_rcvd_datetime
    FROM
        lsw_task