Getting the system time in milliseconds from oracle

it is quite popular these days, especially in the java and web worlds to pass around timestamps in 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 System. also, environments based on javascript and the like offer native logic to transform a given utc-timestamp to the (web-)clients timezone and timestamp, respectively.

delivering that msecs-utc-timestamp from oracle may seem straightforward at first, but may become more and more a jungle of options and possibilities of timestamp creation, conversion, extraction and so on. this is especially true iff one actually has no timestamp instance at hand and needs to start the transformation from the good old date datatype (forget about a call to new_time(); here, because it supports a very limited count of source timezones only).

ok, here’s how:

create or replace function get_date_millis (i_date in date) return number is
begin
return to_number(
to_date(to_char((cast(i_date as timestamp with time zone)) at time zone 'gmt', 'yyyy.mm.dd hh24:mi:ss'),
'yyyy.mm.dd hh24:mi:ss')
- to_date('01.01.1970','dd.mm.yyyy')
) * (24 * 60 * 60 * 1000);
end;
/

unpacking all that nested calls, firstly get a timestamp instance from the given date input parameter and than normalize that instance to utc-time.  secondly reconvert the timestamp instance to a date instance again and substract any time being passed before january 1st 1970. finally calculate the msecs-value, preassuming that one oracle day  is, in numbers, exactly = 1.

have fun.