2.91 FAQ-936 How do I Convert a Unix Timestamp to an Origin Date?

convert-unix-timestamp-Origin-date

Last Update: 6/11/2020

Beginning with Origin 2020b, you can use Origin's UnixTime( ) function to convert between Unix timestamp and Julian Day. For earlier versions of Origin, see the following.

A Unix timestamp is defined as "... the number of seconds that have elapsed since the Unix epoch, that is the time 00:00:00 UTC on 1 January 1970, minus leap seconds. Leap seconds are ignored." ([1])

A Julian date is defined as "a continuous count of days and fractions since noon Universal Time on January 1, 4713 BC (on the Julian calendar)." ([2])

Origin uses Julian dates to internally store date data. So in order to perform date-based actions on Unix timestamps in Origin (e.g displaying as a date-time), it is necessary to convert them to Julian dates.

The formula is simple:

JulianDate = (UnixTimestamp / 86400.0) + 2440587.0

Where 2440587.0 is the Julian date for 00:00:00 UTC on 1 January 1970 (see Note below).

To convert Unix timestamps stored in a worksheet column using Set Column Values, add a new column to the worksheet, and use the following formula (assuming the timestamps are in column A).

Pre-Origin 2017 or Spreadsheet Cell Notation off:

(col(A) / 86400.0) + 2440587.0

Origin 2017 and later with Spreadsheet Cell Notation on:

(A / 86400.0) + 2440587.0

Note: The observant reader may notice that the formula provided generates a value that is off by 0.5 from those generated by third-party converters. This is intentional because, for reasons historical to the product, Origin's Julian date values have a 12 hour offset from the proper definition of a Julian date. Please see this page for more information.