I’ve been stumped by this: I have a table that holds a timestamp (with local time zone to be exact). It also holds a simple date column, which contains a date-plus-time value.
I need to pass the two values to a subroutine. The timestamp value is passed in as UTC, which is fine. We’re at +02:00 (i.e. 1 timezone East, and 1 hour of DST).
So this value shows up in the subroutine as two hours earlier than its value appears to be in the table. I understand the how and why of this difference, no problems here.
As expected, the ‘plain’ date value is passed in without conversion, so for rows holding equal values (at least to the eye, when browsing the table with Toad).
So this value shows up in the subroutine as equal to the value that appears to be in the table. Thr net result is a time difference of two hours inside the subroutine
for values that appear to be equal in the table. I understand Oracle cannot know or assume that values in the date column need to be adjusted
for my particular timezone. so I tried to adjust the date-time value before passing it into the subroutine.
E.g. table (11:20, 11:20) shows up inside my subroutine as (09:20, 11:20)
I found Tom Kyte’s solution: date_val => table.date_val - (substr(sessiontimezone, 1, 1) || ‘1’) * to_dsinterval('0 ’ || substr(sessiontimezone, 2, 5) || ‘:00’)
Which works fine when doing that in a simple select, but inside my batch program (it runs as a database job) it simply seems to have no effect at all.
There appears to be something fundamental about Oracle dates and times that I am missing. Feels like groping in the dark
I searched the internet. All I could find was quite basic. Yet I cannot imagine I would be the first to encounter this issue.
Complicating factor is: this has to work on Oracle 9. (yes, would love to upgrade, and no, will not have the resources to do so anytime soon)
Thanks very much in advance for any advice you may have for me.