Kirix Strata Blog

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.

This entry was posted on Thursday, March 26th, 2009 at 12:29 pm and is filed under dates, tips & tricks. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.