CODEWITHSUNDEEP
oracle-databasejdbctimestamptimezone
Lukas
Lukas

Reputation: 14303

Oracle systimestamp in query condition in different timezone

I have the following code using column lock_until TIMESTAMP(3). The code inserts systimestamp + 5 minutes and then selects all rows with lock_until < systimestamp. I would expect no rows in the result set that since the lock_until is in the future but the row is returned. The most likely reason is that my local timezone is UTC+2 but I do not see how is this possible. The code works as expected with current_timestamp but I would prefer the code be client timezone neutral.

jdbcTemplate.update("delete from shedlock where name = 'test'");
System.out.println(jdbcTemplate.queryForList("SELECT SESSIONTIMEZONE FROM dual"));
// insert, lock_until is now + 5 minutes
jdbcTemplate.update("INSERT INTO shedlock(name, lock_until, locked_at, locked_by) VALUES('test', systimestamp + 5/(24 * 60), systimestamp, 'me')");
// select if lock_until <= systimestamp
System.out.println(jdbcTemplate.queryForList("select * from shedlock where lock_until <= systimestamp"));
// systimestamp ?
System.out.println(jdbcTemplate.queryForList("select systimestamp from dual"));

results in 

Local time: 17:32:48.872
[{SESSIONTIMEZONE=Europe/Prague}]
[{NAME=test, LOCK_UNTIL=2020-04-25 15:37:49.0, LOCKED_AT=2020-04-25 15:32:49.106, LOCKED_BY=me}]
[{SYSTIMESTAMP=2020-04-25 17:32:49.168954}]

Upvotes: 0

Views: 360

Answers (1)

MT0
MT0

Reputation: 167972

From the Oracle documentation:

SYSTIMESTAMP returns the system date, including fractional seconds and time zone, of the system on which the database resides. The return type is TIMESTAMP WITH TIME ZONE.

When you do:

INSERT INTO shedlock (
  name,
  lock_until,
  locked_at,
  locked_by
) VALUES (
  'test',
  systimestamp + 5/(24 * 60),
  systimestamp,
  'me'
)

It is implicitly doing:

INSERT INTO shedlock (
  name,
  lock_until,
  locked_at,
  locked_by
) VALUES (
  'test',
  CAST( systimestamp + 5/(24 * 60) AS TIMESTAMP(3) ),
  CAST( systimestamp AS TIMESTAMP(3) ),
  'me'
)

Which will discard the time zone information and keep the other date/time components as-is.

However, when you do the SELECT it is implicitly doing:

select *
from   shedlock
where  FROM_TZ( lock_until, SESSIONTIMEZONE ) <= systimestamp

and adding the session time zone to the stored value and then comparing it to a timestamp with the time zone of the system on which the database resides; if these two time zones are not the same then you will get rows being unexpectedly included/excluded.

What you could instead do is explicitly cast SYSTIMESTAMP from a TIMESTAMP WITH TIME ZONE data type to a TIMESTAMP data type:

select *
from   shedlock
where  lock_until <= CAST( systimestamp AS TIMESTAMP(3) )

db<>fiddle

Upvotes: 1

Related Questions