I have a timestamp stored on a table in an Oracle Database, such as:
I want to convert it to seconds since Unix's Epoch to return in a query. What would be the easiestway to do it?
EDIT: oh, I need timestamp precision and can't rely on Date type here, as it has less precision.
WITH I AS (SELECT (TO_TIMESTAMP_TZ('01/03/12 16:13:33,000000000' || SESSIONTIMEZONE, 'DD/MM/RR HH24:MI:SS,FF9TZH:TZM') - TO_TIMESTAMP_TZ('01/01/1970 00:00:00 GMT', 'DD/MM/YYYY HH24:MI:SS TZR')) AS UNIX_INTERVAL FROM DUAL) SELECT (EXTRACT(DAY FROM UNIX_INTERVAL) * 86400) + (EXTRACT(HOUR FROM UNIX_INTERVAL) * 3600) + (EXTRACT(MINUTE FROM UNIX_INTERVAL) * 60) + (EXTRACT(SECOND FROM UNIX_INTERVAL)) FROM I
will get you started. This assumes a 01-Jan-1970 00:00:00Z epoch date.
Share and enjoy.