CODEWITHSUNDEEP
oracle-database
Jean
Jean

Reputation: 147

oracle CURRENT_TIMESTAMP AT TIME ZONE 'GMT'-TO_DSINTERVAL

In my oracle database, I am setting one date field like this

update abc set 
startdate = CURRENT_TIMESTAMP AT TIME ZONE 'GMT'-TO_DSINTERVAL('0 00:59:00');

but when I do

select * from abc w where 
w.startdate < CURRENT_TIMESTAMP AT TIME ZONE 'GMT'-TO_DSINTERVAL('0 00:59:00');

after a few minutes then it does not return this row.

To my surprise

select * from abc w where 
w.startdate > CURRENT_TIMESTAMP AT TIME ZONE 'GMT'-TO_DSINTERVAL('0 00:59:00');

returns this row.

How is it possible? Please help me understand.

Upvotes: 0

Views: 279

Answers (1)

MT0
MT0

Reputation: 168416

From your comment

the data type of startdate is 'Date'. There is certainly some implicit conversion happening. The session is in PST. Is there any way we can stop or handle the implicit conversion?

CAST the right-hand side of the expression to a DATE rather than allowing an implicit cast to occur (which would cast the DATE value on the left-hand side to a TIMESTAMP WITH TIME ZONE data type with your current session time zone).

select *
from   abc
where  startdate < CAST(CURRENT_TIMESTAMP AT TIME ZONE 'GMT'-TO_DSINTERVAL('0 00:59:00') AS DATE);

db<>fiddle here

Upvotes: 2

Related Questions