Time - (Unix|POSIX|Epoch) time


Unix time (also known as POSIX time or Epoch time) is a system for describing instants in time.

It is used widely in Unix-like and many other operating systems and file formats.



It's the number of seconds (or millisecond) that have elapsed since 00:00:00 Coordinated Universal Time (UTC), Thursday, 1 January 1970, not counting leap seconds.

Because it does not handle leap seconds, it is neither a linear representation of time nor a true representation of UTC.


  • after the epoch have positive values,
  • before the epoch have negative values.

At a second level, because a timestamp is stored 32 bit integer, the max year for a Unix Timestamp is 2038 (ie 68 years from 1970) <MATH>2 147 483 648\text{ seconds} = 68.1\text{ years}</MATH>


An epoch day is a incrementing count of days where day 0 is 1970-01-01.


Bash / Linux

Unix time may be checked on most Unix systems by typing date +%s on the command line.




  • Example 1:
myDate = TO_DATE('19700101000000','YYYYMMDDHH24MISS') + NUMTODSINTERVAL(myUnixTimeStamp, 'SECOND')
  • Example 2:
select TO_CHAR( FROM_TZ( CAST(DATE '1970-01-01' + (1/24/60/60/1000) * <column name> AS TIMESTAMP), 'America/New_York'), 'MM/DD/YYYY HH24:MI:SS') from <tablename>;

Documentation / Reference

Powered by ComboStrap