Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to get the numeric difference between DBTIMEZONE and SESSIONTIMEZONE?

Is there in Oracle a smooth way to get the numeric difference between SESSIONTIMEZONE and DBTIMEZONE at the current moment (when I perform the call)?

For instance:

SELECT SESSIONTIMEZONE, DBTIMEZONE FROM DUAL;

Returns:

+04:00  +07:00

So, i need some kind of function, by calling which with given parameters, I get the difference between these two values.

For the instance above:

SELECT get_numeric_offset(SESSIONTIMEZONE, DBTIMEZONE) FROM DUAL;

Would return -3 (the sign is crucial).

Of course, it's possible for me to write this function myself by working with strings and parsing them and then proceeding some arithmetical operations or do something like this (which I still don't consider as a pretty smooth solution:

SELECT (
        CAST(SYSTIMESTAMP AT TIME ZONE SESSIONTIMEZONE AS DATE) - 
        CAST(SYSTIMESTAMP AT TIME ZONE DBTIMEZONE AS DATE)
       )*24 
 FROM DUAL;

Maybe I missed something and Oracle actually provides a way to calculate difference between two given timezones?

like image 877
ZZa Avatar asked Aug 06 '13 05:08

ZZa


People also ask

What is Dbtimezone and Sessiontimezone in Oracle?

Returns the current date and time in the session time zone as a TIMESTAMP WITH TIME ZONE value. DBTIMEZONE. Returns the value of the database time zone. The value is a time zone offset or a time zone region name.

Does Oracle timestamp have timezone?

In Oracle, date format and timezones feature multiple data types, including DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE.

Which data type should you choose when you need to store dates and times that include time zone information?

Datetime and Interval Data Types. The datetime data types are DATE , TIMESTAMP , TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE . Values of datetime data types are sometimes called datetimes. The interval data types are INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND .

How do I know the timezone of my Oracle database?

You can check the session time zone by issuing the SQL in Listing 7. SQL> alter session set TIME_ZONE='-03:00'; Session altered.


2 Answers

A little warning on using SESSIONTIMEZONE

Your first example is not bullet-proof, as the session time zone can be set to either of the following :

  • Operating system local time zone ('OS_TZ')
  • Database time zone ('DB_TZ')
  • Absolute offset from UTC (for example, '-05:00')
  • Time zone region name (for example, 'Europe/London')

Just look at what happens here :

SELECT SESSIONTIMEZONE, DBTIMEZONE FROM DUAL;

SESSIONTIMEZONE  |  DBTIMEZONE
         +04:00  |      +07:00

ALTER SESSION SET TIME_ZONE='Europe/Paris';

SELECT SESSIONTIMEZONE, DBTIMEZONE FROM DUAL;

SESSIONTIMEZONE  |  DBTIMEZONE
   Europe/Paris  |      +07:00

Parsing that 'Europe/Paris' string in your function is going to be a lot harder... You should use the TZ_OFFSET function, to ensure that you always get the same ±HH:MM format.

ALTER SESSION SET TIME_ZONE='Europe/Paris';

SELECT DBTIMEZONE, SESSIONTIMEZONE, TZ_OFFSET(SESSIONTIMEZONE) FROM DUAL;

 DBTIMEZONE | SESSIONTIMEZONE | TZ_OFFSET(SESSIONTIMEZONE)
     +07:00 |    Europe/Paris |                     +02:00

About your second solution

I think I like your second solution better. You can shorten it by using CURRENT_DATE instead of CAST(SYSTIMESTAMP AT TIME ZONE SESSIONTIMEZONE AS DATE), they are equivalent :

SELECT (
        CURRENT_DATE - 
        CAST(SYSTIMESTAMP AT TIME ZONE DBTIMEZONE AS DATE)
       )*24 
 FROM DUAL;

You could technically even do this !

SELECT (CURRENT_DATE - SYSDATE) * 24 
 FROM DUAL;

BUT actually, SYSDATE is not guaranteed to be on the same timezone as DBTIMEZONE. SYSDATE is always bound to the underlying OS' timezone, while DBTIMEZONE can be altered once the server has been started.

And while I'm splitting hairs, I should also remind you that certain countries / regions use offsets that are not whole numbers, for example Iran Standard Time is UTC+03:30, Myanmar Time is UTC+06:30... I don't know if you'll ever encounter this in your production environment, but I hope you're okay with the possibility that your query returns something like 1.5...

like image 87
Miklos Aubert Avatar answered Sep 30 '22 19:09

Miklos Aubert


What you want is not possible, because timezones are not fixed, but change over time. Hence you cannot calculate THE difference between two timezones, but only the difference that is valid at a certain point in time.

It is quite common, that timezones change over time. In the TZ Database there are each year many changes for the time zones, e.g. places changing their time zone or changes on the start, end or difference of the day light saving time, or leap seconds getting inserted at "random" times.

So to calculate the offset, you must also give a particular point in time, for which you want the offset calculated (i.e. which version of the time zone definitions should be applied).

With this in mind, it becomes clear, how to calculate the difference between two time zones at a certain point in time:

select 
  (TIMESTAMP '2012-06-30 22:00:00 US/Pacific') - (TIMESTAMP '2012-06-30 22:00:00 Europe/Berlin') as diff
from
    dual
;

DIFF
------------------------------
+000000000 09:00:00.000000000

When you have this information, you have to decide, up to which precision you want the difference. I have chosen the date above on purpose, because on that night a leap second was inserted. So there might be some point in time, where you will get a difference of not exactly nine hours but of nine hours and one second (or is it nine hours minus one second).

It is also important, that you insert the exact locations and not some other timestamp information. Locations can change to different time zones. Hence you have to specify tow locations and a point in time to get the correct time zone difference.

If you want to round the difference, you can access its components with EXTRACT(HOUR FROM ...) and EXTRACT(MINUTE FROM ...). If want to round at the second level, you can use the following trick:

select 
    (to_number(to_char(sys_extract_utc(TIMESTAMP '2012-07-01 01:00:00 US/Pacific'), 'SSSSSFF3')) 
    - to_number(to_char(sys_extract_utc(TIMESTAMP '2012-07-01 01:00:00 Europe/Berlin'), 'SSSSSFF3')))/1000 as diff_s
from 
    dual
;

    DIFF_S
----------
    -54000
like image 35
stefan.schwetschke Avatar answered Sep 30 '22 18:09

stefan.schwetschke