1

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'

Zhubei Federer
  • 1,274
  • 2
  • 10
  • 27
user11925123
  • 25
  • 3

1 Answers1

0

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
notNull
  • 30,258
  • 4
  • 35
  • 50