0

I am trying to convert following string to timestamp. however not able to figure out how to handle last milisecond part

String:

2020-06-30T23:57:48.000-0400

following is working. howsoever it's loosing information about milliseconds.

hive> select from_unixtime(unix_timestamp("2020-06-30T23:57:48.000-0400", "yyyy-MM-dd'T'HH:mm:ss.SSS'-'SSS"), "yyyy-MM-dd'T'HH:mm:ss.SSS'-'SSSS");
OK
2020-06-30T23:57:48.000-0000

Expected Value

2020-06-30T23:57:48.000-0400

update

even this is not working

Query

hive> select date_format("2020-01-23 23:59:54.0000400", "yyyy-MM-dd HH:mm:ss.SSSSSSS");

output

2020-01-23 23:59:54.0000000
Gaurang Shah
  • 11,764
  • 9
  • 74
  • 137
  • unix_timestamp does not preserve milliseconds. But there are already many answers about different timestamp formats with milliseconds convertion. The idea is to extract milliseconds and concatenate with converted timestamp. Read this: https://stackoverflow.com/a/59645846/2700344 and https://stackoverflow.com/a/58713989/2700344 and https://stackoverflow.com/a/59842130/2700344 and https://stackoverflow.com/a/59528697/2700344 – leftjoin Jul 16 '20 at 07:57

1 Answers1

0

Try this -

Select concat(from_unixtime(unix_timesstamp('2020-01-23 23:59:54.0000400', 'yyyy-MM-dd HH:mm:ss')), '.', split('2020-01-23 23:59:54.0000400', '[.]')[1]);

I hope this is helps.

Ajay Ahuja
  • 1,196
  • 11
  • 26