Converting Julian dates and Unix timestamps
Converting date formats can be a tricky thing. Two of the more cryptic formats that come up in our support questions relate to Julian dates and Unix timestamps. Here's how you convert them in Strata:
Julian Dates
A Julian date is the number of days since January 1, 4713 BC GMT. To convert a Julian date to the current date, you can simply use DATE(0,0,0) + <number>, where <number> is the Julian date. For for example, if the number appears as “2454917″, the formula in Strata would be:
date(0,0,0) + 2454917 [the result would be March, 26, 2009]
Unix Timestamps
A unix timestamp is the number of seconds since 1970. To convert a unix timestamp to the current date, use DATE(<number>), where <number> is the number of milliseconds since 1970. If the timestamp is given in seconds, multiply by 1000: DATE(<number>*1000). So, for example, if you the number appears as “1237657172″, the formula in Strata would be:
date(1238088021 * 1000) [the result would be March, 26, 2009]
As a side note, sometimes one might mistake a Julian or Unix date for a different custom format. For instance, dates could be stored in the format YYDDD or YYYYDDD where YY/YYYY is the year, and DDD is the number of days since the beginning of that year. So 2001003 would be January 3, 2001
For further information on Strata date conversion, please see this help page.
April 16th, 2009 at 9:35 am
[…] Strata Blog « Converting Julian dates and Unix timestamps […]