POSIX_TIME

Purpose

Posix time (also known as Epoch time or Unix time) is a system for describing points in time, defined as the number of seconds elapsed since midnight of January 1, 1970 (UTC). By using this function you can convert a datetime value to a numerical value.

Syntax

Usage Notes

• POSIX_TIME(<datetime>) is equivalent to the function call SECONDS_BETWEEN(<datetime>, '1970-01-01 00:00:00') if the session time zone is set to UTC.
• If you omit the parameter, the Posix time refers at the current moment. that is, CURRENT_TIMESTAMP.
• For datetime values before January 1, 1970 (UTC), this function will return negative numbers.
• Using the function FROM_POSIX_TIME you can convert a numerical value into a datetime value.

Example

ALTER SESSION SET TIME_ZONE='UTC';
SELECT POSIX_TIME('1970-01-01 00:00:01') PT1,
       POSIX_TIME('2009-02-13 23:31:30') PT2;

Result