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:

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:

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:

In example below is used sessiontimezone for calculation UNIX TIMEZONE

EXAMPLE:

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: