Month: May 2010

Getting the utc and unix time in milliseconds from oracle

It is quite necessary these days, especially in the globalization/24*7-worlds to pass around (unix-)timestamps in a form being defined as:

The difference, measured in milliseconds, between the current time and midnight, January 1, 1970 UTC.

Indeed there is a dedicated method for this in the Java editions since long being declared as public static long currentTimeMillis() of class System. Also, Web-environments based on Javascript and the like offer native logic to transform a given UTC-timestamp to the some clients timezone and timestamp, respectively.

Delivering that msecs-utc-value from Oracle may seem straightforward at first, but may become more and more a jungle of options and proposed possibilities of conversion, extraction and so on. It is especially puzzling iff one actually has no timestamp with time zone instance at hand and needs to start off from existent date or timestamp w/o time zone column data. That is, data having had no time zone stored upon inserting or updating, such that calculating back to UTC is impossible without an edicated guess.

Hence, the only option for original date or timestamp w/o time zone data is using the Oracle from_tz function (save yourself from checking out new_time, it supports a very limited number of time zones only), passing in a timestamp w/o time zone and a time zone region known to Oracle. Do not just pass in a derived time zone value, e.g. sessiontimestamp, because Oracle takes this for granted without actually looking back in time, regarding daylight saving and things. For example, in 1978 daylight saving has not yet been introduced in Germany but in 1988 it was in use:

-- what's in sessiontimezone currently ?
select sessiontimezone from dual;
+02:00

-- that's ok, an educated guess on the original time zone
-- actually the only option you have
select from_tz(cast(to_date('01.08.1978 12', 'dd.mm.yyyy hh24') as timestamp),
    'Europe/Berlin') as xyz1,
  from_tz(cast(to_date('01.08.1988 12', 'dd.mm.yyyy hh24') as timestamp),
    'Europe/Berlin') as xyz1
from dual;
01/08/1978 12:00:00,000000 +01:00	01/08/1988 12:00:00,000000 +02:00

-- possible and likely given in the oracle docs but wrong, oracle just follows
--   your specification without actually looking back in time
-- iff you do this in winter, the result may become wrong again in another
--   way because sessiontimezone will now read +01:00
select from_tz(cast(to_date('01.08.1978 12', 'dd.mm.yyyy hh24') as timestamp),
    sessiontimezone) as xyz1,
  from_tz(cast(to_date('01.08.1988 12', 'dd.mm.yyyy hh24') as timestamp),
    sessiontimezone) as xyz1
from dual;
01/08/1978 12:00:00,000000 +02:00	01/08/1988 12:00:00,000000 +02:00

Once you have derived this time zone aware value, retieving the UTC time is just employing a supplied function and a cast like this:

select cast(sys_extract_utc(
  from_tz(cast(to_date('01.08.1978 12', 'dd.mm.yyyy hh24') as timestamp),
    'Europe/Berlin')) as date) as xyz1
from dual;

Finally, substract any time being passed before january 1st 1970 and calculate the msecs-value, preassuming that one Oracle day is, in numbers, exactly = 1.

select to_number(cast(sys_extract_utc(
  from_tz(cast(to_date('01.08.1978 12', 'dd.mm.yyyy hh24') as timestamp),
    'Europe/Berlin')) as date) - to_date('01.01.1970','dd.mm.yyyy'))
    * (24 * 60 * 60 * 1000) as xyz1
from dual;

have fun.