How to compare a timestamp with a date value?

Toad for Oracle - Beta
1

All,

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

Kind regards,
Abe Kornelis

2

If you don’t mind losing precision beyond seconds and possibly time zone information, you could cast your timestamps to date like this:

SELECT TO_DATE (TO_CHAR (SYSTIMESTAMP, ‘YYYY-MON-DD HH24:MI:SS’), ‘YYYY-MON-DD HH24:MI:SS’) AS date_value

FROM DUAL

3

I posted this earlier today, but as it hasn't turned up yet .....

Morning All,

On 16/10/15 11:04, kornelis.abe wrote:

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).

I don't have Oracle 9 anywhere near at the moment - I'm "between jobs" as they say, however, you can cast the DATE column to a timestamp with time zone at a particular time zone - UTC looks to be what you might need - at least them whatever the code is doing with those two variables will at least be based on the same time zone.

select to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') from dual;

16/10/2015 11:48:32

select systimestamp at time zone 'PST' from dual;

16/10/2015 03:48:32.509000 -07:00

select cast(sysdate as timestamp with time zone)

at time zone 'PST' from dual;

16/10/2015 03:48:32.000000 -07:00

In your function, cast the date parameter to the same time zone as the timestamp with time zone parameter.

You might also wish to convert both parameters to the database time zone:

select dbtimezone from dual

+00:00

select cast(sysdate as timestamp with time zone)

at time zone DBTIMEZONE from dual;

16/10/2015 10:53:49.000000 +00:00

Or, even, the session's time zone:

select sessiontimezone from dual

+01:00

select cast(sysdate as timestamp with time zone)

at time zone SESSIONTIMEZONE from dual

16/10/2015 11:56:19.000000 +01:00

HTH

--

Cheers,

Norm. [TeamT]

4

I see that in my bewilderment and confusion I have posted an overly complex question. What it boils down to is this: I have a timestamp with local time zone column and a date column, holding the same value. When I calculate the difference, I get two hours. I understand this has to do with the local timezone setting, but I fail to find a way of converting the date column in such a way that the calculated difference will go to zero.

I have found various solutions on the internet, but they all propose conversion methods that either fail to correct the underlying time difference, or involve hardcoding a time difference. Hardcoding my time zone name is no problem, but I’d hate having to include logic to figure out whether or not Daylight Saving Time is in effect at the involved date.

What I finally have come up with is this ugly kludge:

adjusted_date := to_timestamp(to_char(case when input_date is null then null
else to_timestamp_tz(to_char(input_date, ‘YYYY-MM-DD"T"HH24:MI:SS"Z"’) || tz_offset(‘Europe/Amsterdam’),
‘YYYY-MM-DD"T"HH24:MI:SS"Z"TZH:TZM’) at time zone ‘Etc/Greenwich’
end, ‘yyyy-mm-dd hh24:mi:ss’)
, ‘yyyy-mm-dd hh24:mi:ss’);

Maybe it can be done simpler. I’ll be the first to admit that this is ugly, unmaintainable code.

Best regards,
Abe.

5

John,

thanks for the quick reply. Unfortunately, I need to convert the other way around: from date with implied timezone to a true timestamp.

Have a nice weekend!

Abe

6

Do you applicable sys_extract_utc under Oracle 9i?

7

Norm,

It’s all about time zones, good morning is ok with me, even though it’s about time to go home…
So I wish you a good evening in advance :wink:

Anyway, you were right about UTC. That seems to be most Obvious common denominator in these tiem comparisons.

I tried dbtimezone, but it fails to accomodate for the DST.

I tried sessiontimezone, but in the batch it’s 00:00, whcih is off by two hours.
Of course I could alter my session to timezone +02:00 but that brings me back to square one: how do I know the applicable time difference with UTC?

Casting the timezone as per your suggestion - unfortunately - does not work either. It does change the representation of the timestamop,
but it’s internal value is not changed. If it’s 11:20 it remains just that. It migh display as 13:20, but the difference between the two fields does not change.

I just hope my current solution will hold up when DST goes off the calendar. That will be in two weeks time.

We’ll see.

Thanks a lot for trying to help me.

Best regards, and have an ice weekend!
Abe

8

Dr Bubo,

I tried a few variants of your suggestion, as it would give me cleaner code. Unfortunately none of my formulations worked correctly.

Thanks for trying to help, anyway.

Kind regards & have a nice weekend,
Abe

9

Is this, by any chance, a Java application? I have a vague recollection of an application I supported in a past life (I supported the database, not the app itself) in which the dates were displayed out of sync with the actual data in the database. It turned out to be because, if I remember correctly, the Java code was using some date conversion which always assumed that the dates were in UTC when some of the ones affected were entered after our version of DST had been applied. Only dates entered during what the calendar refers to as British Summer Time were affected.

Anyway, I obviously have no idea what the affected code is supposed to be doing, but can you not cast both the DATE and the TIMESTAMP to the same time zone, say UTC, and then compare them, or otherwise process them?

It would be interesting to know what happens when, instead of the DATE and the TIMESTAMP, you pass in two TIMESTAMPS or two DATE values. Of course, this might not be possible.

Looking perhaps in the general direction on TZ_OFFSET might also, perhaps, be of some use here?

Have a good weekend everyone, mine will be starting in about 45 minutes!

Cheers,

Norm. [TeamT]

10

FYI: TZ_OFFSET:

TZ_OFFSET returns the time zone offset corresponding to the value entered based on the date the statement is executed. You can enter a valid time zone name, a time zone offset from UTC (which simply returns itself), or the keyword SESSIONTIMEZONE or DBTIMEZONE. For a listing of valid values, query the V$TIMEZONE_NAMES dynamic performance view.

I like the bit about “based on the date the statement is executed” which tends to imply, slightly, that it might just take DST (aka BST) into consideration? Otherwise, why would the current date be of any significance?

Playing around with it is interesting (for certain values of interesting!) - for me, in GMT/UTC but with =01:00 BST applied, I get this:

select tz_offset(‘CET’) from dual;

+02:00

select tz_offset(‘UTC’) from dual;

+00:00

select tz_offset(DBTIMEZONE) from dual;

+00:00

select tz_offset(SESSIONTIMEZONE) from dual;

+01:00

The final one is interesting as our local time is currently BST aka GMT+01:00 aka UTC + 01:00 - so perhaps the last option will give you the DST offset you need for your timezone?

Just a thought.

I’m getting jet lagged with all these timezones!

Cheers,

Norm.

11

I don’t have Ora9iR2, and I don’t know which Oracle version introduced the sys_extract_utc function, but I mean this one referring to your sample:

SELECT :input_date AS input_date,

cast ( :input_date AS TIMESTAMP WITH TIME ZONE) AS input_date_as_tstamp,

sys_extract_utc (cast ( :input_date AS TIMESTAMP WITH TIME ZONE))

AS input_date_as_utctstamp,

cast (sys_extract_utc (cast ( :input_date AS TIMESTAMP WITH TIME ZONE)) AS DATE)

AS input_date_as_utcdate

FROM dual

12

I’m wondering ifsys_extract_utc copes with daylight savings and or British so called summer time adjustments? This appears to be the problem, the DST offset isn’t being considered, lough I might have worked it out with tz_offset (sessiontimezone). See above.

Cheers,

Norm. [ TeamT ]

13

Morning All,

On 16/10/15 11:04, kornelis.abe wrote:

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).

I don't have Oracle 9 anywhere near at the moment - I'm "between jobs"

as they say, however, you can cast the DATE column to a timestamp with

time zone at a particular time zone - UTC looks to be what you might

need - at least them whatever the code is doing with those two variables

will at least be based on the same time zone.

select to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') from dual;

16/10/2015 11:48:32

select systimestamp at time zone 'PST' from dual;

16/10/2015 03:48:32.509000 -07:00

select cast(sysdate as timestamp with time zone)

at time zone 'PST' from dual;

16/10/2015 03:48:32.000000 -07:00

In your function, cast the date parameter to the same time zone as the

timestamp with time zone parameter.

You might also wish to convert both parameters to the database time zone:

select dbtimezone from dual

+00:00

select cast(sysdate as timestamp with time zone)

at time zone DBTIMEZONE from dual;

16/10/2015 10:53:49.000000 +00:00

Or, even, the session's time zone:

select sessiontimezone from dual

+01:00

select cast(sysdate as timestamp with time zone)

at time zone SESSIONTIMEZONE from dual

16/10/2015 11:56:19.000000 +01:00

HTH

--

Cheers,

Norm. [TeamT]

14

Drbubo, Norm,

thanks fior your helpful suggestions. Due to a short vacation your contributions have been left unnaswered. I’m sorry about that.

Anyway, no this is not a Java application. It’s just plain PL/SQL code. And indeed, the additional hassle of coping with Daylight Saving Time has been giving me a headache. The solution I reported earlier is still the best that I could come up with.

Abe