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:
|
1 2 3 4 5 6 |
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:
|
1 2 |
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:
|
1 2 3 4 |
--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:
|
1 2 3 4 |
--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:
|
1 2 3 |
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 |