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:
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
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
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