Convert Julian Dates to Gregorian dates in impala

Question

I have a table where the dates are in Julian dates, and I would like to convert these dates into Calendar dates.

Here is a sample of a Julian date I have: 2457395.

In calendar date it should be: 07012016 (DDMMYYYY).

I tried to convert the date into the number of days since (01/01/1900) but even if I don't know how to have a date with a number of days as an input.

Many thanks in advance!

Answer 1

Well if it is a JDE Julian date for example 118163 for 12-06-2018 then below code can be used date_add(to_timestamp(concat(substr(cast(cast(118162 + 1900000 as int) as string),1,4),'01','01'),'yyyyMMdd'), cast(substr(cast(cast(118162 + 1900000 as int) as string),5,3) as int)-1)

Answer 2

Impala does not support dates, only timestamps; and it does not have many date/time formatting features; so you need to be creative, e.g.

select JULIAN_DATE,
  adddate('1900-01-01 00:00:00Z', JULIAN_DATE -2415021) as AS_TIMESTAMP,
  to_date(adddate('1900-01-01 00:00:00Z', JULIAN_DATE -2415021)) as AS_ISO_DATE_STRING,
  from_unixtime(unix_timestamp(adddate('1900-01-01 00:00:00Z', JULIAN_DATE -2415021)), "ddMMyyyy") as AS_DMY_DATE_STRING
from WHATEVER

Sample output:

julian_date as_timestamp        as_iso_date_string as_dmy_date_string 
----------- ------------------- ------------------ ------------------ 
2457395     2016-01-07 00:00:00 2016-01-07         07012016