Table of Contents

About

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

It's the number of milliseconds / second since a start date (generally Thursday, 1 January 1970) but if you start your own epoch, you may change it to get more space (on 32bit second level, you get 68 years)

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

Precision

Instant - Precision

Second

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.

Instants:

  • 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>

Day

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

Command

Bash / Linux

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

Java

System.currentTimeMillis()

Oracle

  • 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