Alessandro Lacava

on Designing and Developing Software. In love with Functional Programming.

How to Compute a Timestamp In Oracle (PL SQL)

Many sources use the term timestamp to refer specifically to Unix time, the number of seconds since 00:00:00 UTC on January 1, 1970. In Oracle you can compute this number very easily. For example, the following query computes the number of seconds between 00:00:00 UTC on January 1, 1970 and October 9, 2007.

SELECT (to_date('09-10-2007', 'DD-MM-YYYY') -
to_date('01-01-1970', 'DD-MM-YYYY')) * 60 * 60 * 24
FROM dual

The result of the preceding query should be 1191888000.

How it works:

The subtraction between two dates returns the number of days between them. Multiplying this number by 60 * 60 * 24–the number of seconds in one day–you obtain the number of seconds between those dates.