Implementing bottom-up path traversal for hierarchical tables

oracle supports hierarchical queries on tables by the start with and connect by clauses. this usually preassumes that the rows of some table are chained by a value in column dad_id of some row pointing to a value in column son_id in a parent row, where son_id also denotes the primary key in the table. the root rows of the tree being set up that way, furthermore have a dedicated value in column dad_id. for positive, evolving numbers (id’s , one may just choose 0 here. so far, a typical hierarchical query for a (male) family tree may look like this:

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.