Time - (Unix|POSIX|Epoch) time
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
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>;