CODEWITHSUNDEEP
oracle-databasecookiestimezoneoracle-apex
nick zoum
nick zoum

Reputation: 7325

Oracle Apex cookie expiration offset by 8 hours

The cookies I create with owa_cookie.send are getting an additional 8 hours to the expiration date.

The server is using UTC time (The result of select to_char(sysdate, 'hh24:mi') from dual is the same as the current UTC time). So I don't think the difference is caused by the time zone.

I'm now using the following call to create a cookie (note the -8/24 on the expires). It works for now, but this doesn't seem very safe.

-- creates a cookie that lasts for 1 hour that can also be seen by JS
-- the value of the cookie is the expiration date in ISO format
owa_cookie.send(
  name => 'TEST_DTM',
  value => to_char(cast((sysdate + 1/24) as timestamp) at time zone 'UTC', 'yyyy-mm-dd"T"hh24:mi:ss.ff3"Z"'),
  expires => sysdate + (1 - 8) / 24,
  path => '/ords',
  secure => 'Y',
  httponly => null);

Does anybody know where the difference in time is coming from?

Upvotes: 0

Views: 311

Answers (1)

MT0
MT0

Reputation: 168671

You have an implicit cast from a TIMESTAMP to a TIMESTAMP WITH TIME ZONE:

to_char(
  cast((sysdate + 1/24) as timestamp) -- TIMESTAMP (without time zone)
  at time zone 'UTC',                 -- TIMESTAMP WITH TIME ZONE
  'yyyy-mm-dd"T"hh24:mi:ss.ff3"Z"'
)

This is effectively adding a time zone:

to_char(
  cast(sysdate + 1/24 as timestamp with local time zone)
  at time zone 'UTC',
  'yyyy-mm-dd"T"hh24:mi:ss.ff3"Z"'
)

Which converts SYSDATE in the database's time zone to the local time zone (whichever time zone the client application is using, and not the database time zone) and then converts it to UTC.

You would be better to use SYSTIMESTAMP rather than SYSDATE and save yourself a lot of confusion about data types and time zones:

TO_CHAR(
  (SYSTIMESTAMP + INTERVAL '1' HOUR) AT TIME ZONE 'UTC',
  'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"'
)

However, if you really did want to use SYSDATE then use the database time zone rather than the local (client) time zone:

to_char(
  FROM_TZ(CAST(sysdate + 1/24 AS TIMESTAMP), DBTIMEZONE)
  at time zone 'UTC',
  'yyyy-mm-dd"T"hh24:mi:ss.ff3"Z"'
)

For example, if the database time is UTC and you do:

ALTER SESSION SET TIME_ZONE = 'Asia/Shanghai';

SELECT 'SYSDATE with implicit local cast' AS method,
       to_char(
         cast(sysdate + 1/24 as timestamp)
         at time zone 'UTC',
         'yyyy-mm-dd"T"hh24:mi:ss.ff3"Z"'
       ) AS utc_timestamp
FROM   DUAL
UNION ALL
SELECT 'SYSDATE with explicit local cast',
       to_char(
         cast(sysdate + 1/24 as timestamp with local time zone)
         at time zone 'UTC',
         'yyyy-mm-dd"T"hh24:mi:ss.ff3"Z"'
       )
FROM   DUAL
UNION ALL
SELECT 'SYSDATE with explicit DB timezone cast',
       to_char(
         FROM_TZ(CAST(sysdate + 1/24 AS TIMESTAMP), DBTIMEZONE)
         at time zone 'UTC',
         'yyyy-mm-dd"T"hh24:mi:ss.ff3"Z"'
       )
FROM   DUAL
UNION ALL
SELECT 'SYSTIMESTAMP',
       TO_CHAR(
         (SYSTIMESTAMP + INTERVAL '1' HOUR) AT TIME ZONE 'UTC',
         'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"'
       )
FROM   DUAL;

Then the output is:

METHOD UTC_TIMESTAMP
SYSDATE with implicit local cast 2021-11-01T04:07:04.000Z
SYSDATE with explicit local cast 2021-11-01T04:07:04.000Z
SYSDATE with explicit DB timezone cast 2021-11-01T12:07:04.000Z
SYSTIMESTAMP 2021-11-01T12:07:04.200Z

db<>fiddle here

Upvotes: 1

Related Questions