Search code examples
sqlhivetimestamputchue

HIVE SQL Convert (UTC) 2018-06-19T14:01:17.000000 to timestamp ('MM/dd/yyyy HH:mm:ss' )

I know the question seen simple and it should be a simple answer but I spent sometime in this and can´t resolve it. Some help would be really appreciated. I'm using SQL on HUE Hive.

TO_DATE(col) works alright but only returns date, but not the time. For what I understand the original format is UTC string

I believe the code should be something like and I'm probably misplacing the fields. 

from_unixtime(unix_timestamp(column_name, 'yyyy-MM-dd HH:mm:ss.S'), 'yyyy-MM-dd')

As a result I get NULL instead desired: 'yyyy-MM-dd HH:mm:ss'

Solution

  • You need to match T in your unix_timestamp conversion

    Try with this syntax:

    hive> select from_unixtime(unix_timestamp("2018-06-19T14:01:17.000000", "yyyy-MM-dd'T'HH:mm:ss.SSSSSS"), 'yyyy-MM-dd HH:mm:ss');

    (or)

    hive> select timestamp(from_unixtime(unix_timestamp("2018-06-19T14:01:17.000000", "yyyy-MM-dd'T'HH:mm:ss"), 'yyyy-MM-dd HH:mm:ss')); --casting to timestamp

    Result:

    2018-06-19 14:01:17