Oracle Conversions

1. Oracle Convert time in format hh24:mi:ss to seconds In example below is shown how to convert time ’01:20:15′ – presented in ‘hh24:mi:ss’ format – to seconds.

EXAMPLE:

SELECT
SUM(TO_NUMBER(TO_CHAR(TO_DATE('01:20:15', 'hh24:mi:ss'),'hh24' ))* 3600
  + TO_NUMBER(TO_CHAR(TO_DATE('01:20:15', 'hh24:mi:ss'),'mi' ))*60
  + TO_NUMBER(TO_CHAR(TO_DATE('01:20:15', 'hh24:mi:ss'),'ss' )))
  duration
FROM dual

2. Convert date to UNIX TIMESTAMP In example below is shown how to convert date time ‘01.07.2011 01:20:15’ – presented in ‘dd.mm.yyyy hh24:mi:ss’ format – to UNIX TIMESTAMP.

EXAMPLE:

SELECT (TO_DATE('22.07.2011 09:20:15', 'dd.mm.yyyy hh24:mi:ss') - TO_DATE('19700101','yyyymmdd')) * 86400
  FROM dual

In case that you need to convert sysdate to UNIX TIMESTAMP you should note that UNIX TIMESTAMP always refers to UTC time. Because of that you should include your timezone in formula.

EXAMPLE:

--Note: session timezone = +2

SELECT (SYSDATE - TO_DATE('19700101','yyyymmdd')) * 86400 - 2*3600
  FROM dual

In example below is used sessiontimezone for calculation UNIX TIMEZONE

EXAMPLE:

--Note: session timezone = +2

SELECT (SYSDATE - TO_DATE('19700101','yyyymmdd')) * 86400 - TO_NUMBER(SUBSTR(TZ_OFFSET(sessiontimezone),1,3))*3600
  FROM dual

3. Oracle calculate time difference between dates in seconds In example below is shown how to calculate time difference between two dates in seconds.

EXAMPLE:

SELECT ((TO_DATE('22.07.2011 09:00:00', 'dd.mm.yyyy hh24:mi:ss') - to_date('19700101','yyyymmdd')) * 86400) -
       ((TO_DATE('22.07.2011 08:00:00', 'dd.mm.yyyy hh24:mi:ss') - to_date('19700101','yyyymmdd')) * 86400) difference
 FROM dual