How to turn date format to YYYYMMDD format in Hive QL

In PostgreSQL I can just use:

to_char(current_date - 14, 'YYYYMMDD') 

However this is not an accepted syntax in Hive and I can't find what the proper function is for this date format.

Could someone help?

2 answers

  • answered 2020-09-30 23:18 GMB

    In Hive, you would typically use an intermediate conversion to a unix timestamp:

    from_unixtime(unix_timestamp() - 14 * 24 * 60 * 60, 'yyyyMMdd')
    

    unix_timestamp() returns the current date/time as an epoch timestamp; you can then substract 14 days (expressed as seconds), then use from_unixtime() to format the result as a string in the target format.

  • answered 2020-10-01 05:33 leftjoin

    For Hive version >= 1.2.0 you can use date_format function. Also date_sub function is available:

    date_format(date_sub(current_date,14),'yyyyMMdd')