Experimenting with Date/Timestamp conversions with Oracle PL/SQL

select CURRENT_TIMESTAMP from dual;

this returns 17-SEP-12 12.23.25.893927000 PM EUROPE/BERLIN
(with millisecond and TimeZone)

What is the difference between CURRENT_TIMESTAMP and SYSTIMESTAMP ?
select CURRENT_TIMESTAMP - SYSTIMESTAMP from dual;

0 0:0:0.000017

basically NONE.

____________


How to convert a String such as
value = '2012-09-11 04:11:41.606+02:00'
to a Timestamp:

cast(to_timestamp_tz(value , 'YYYY-MM-DD hh24:mi:ss,FF3TZH:TZM') as TIMESTAMP)

____________


you can do maths with TIMESTAMP:

select CURRENT_TIMESTAMP - cast(to_timestamp_tz('2012-09-11 04:11:41.606+02:00' , 'YYYY-MM-DD hh24:mi:ss,FF3TZH:TZM') as TIMESTAMP) from dual;

returns

6 9:8:17.379691

which is a TIME INTERVAL, not a number.
How to covert a TIME INTERVAL to a NUMBER?


______________________

select TO_NUMBER(CURRENT_TIMESTAMP) from dual;

doesn't work, you get ORA-01722: invalid number

____________


This function works pretty well to get the EPOCH ( it returns a decimal number representing the SECONDS, if you want the MILLISECONDS as in the Java getCurrentTimeMillis() you should multiply by 1000 and truncate)

create or replace
FUNCTION MY_EPOCH
(
time1 TIMESTAMP
)

RETURN number

AS
sec NUMBER;
firstofjan70 timestamp;

BEGIN

firstofjan70 := timestamp '1970-01-01 00:00:00 +00:00';

select
extract(day from (time1 - firstofjan70 ))*86400+
extract(hour from (time1 - firstofjan70))*3600+
extract(minute from (time1 - firstofjan70))*60+
extract(second from (time1 - firstofjan70))
into sec
from dual;

RETURN sec;