2

I'm having a string like "08/03/2018 02:00:00" which i'm trying to convert into a timestamp value.

I'm using the below code:

unix_timestamp("08/03/2018 02:00:00", "yyyy-MM-dd'T'HH:mm:ss.SSSXXX")

when i use the above code it's throwing a NULL value.

How can i convert this string to Timestamp in Hive/Hue Editor?

leftjoin
  • 36,950
  • 8
  • 57
  • 116
Sara
  • 312
  • 6
  • 15

1 Answers1

2

The format you specified does not match to the actual timestamp. If 08/03 in your example is dd/MM then:

select unix_timestamp("08/03/2018 02:00:00", "dd/MM/yyyy HH:mm:ss")
OK
1520503200
Time taken: 0.299 seconds, Fetched: 1 row(s)



select from_unixtime(unix_timestamp("08/03/2018 02:00:00", "dd/MM/yyyy HH:mm:ss"))
OK
2018-03-08 02:00:00
Time taken: 0.068 seconds, Fetched: 1 row(s)

See this answer if you want convert from ISO timestamp https://stackoverflow.com/a/23520257/2700344

You can specify date pattern for unix_timestamp for non-standard format. See docs here: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-DateFunctions

leftjoin
  • 36,950
  • 8
  • 57
  • 116