2.91 FAQ-936 How do I Convert a Unix Timestamp to an 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." ()
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)." ()
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
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.