CODEWITHSUNDEEP
hivetimestampformathiveqlutc
akki
akki

Reputation: 91

Hive how to get current timestamp in format 2020-04-17T19:17:56.017719Z format

I am using Hive and have an important task at hand, which needs the current timestamp in the format 2020-04-17T19:17:56.017719Z.

Any help around exact solution for this in Hive, would be highly appreciated. Thanks

Upvotes: 1

Views: 1442

Answers (1)

leftjoin
leftjoin

Reputation: 38335

with time as (
select reflect('java.util.Date','getTime') as millis
)
select concat( from_unixtime(floor(millis / 1000   ),"yyyy-MM-dd'T'HH:mm:ss"), '.',cast((millis % 1000)as int),'Z')
from time

Result:

2020-04-26T11:12:35.590Z

Or one more method

select concat(from_unixtime(unix_timestamp(split(current_timestamp,'\\.')[0]),"yyyy-MM-dd'T'HH:mm:ss"),'.',split(current_timestamp,'\\.')[1],'Z')

Result:

2020-04-26T11:28:13.433Z

One more method using regexp_replace:

select regexp_replace(current_timestamp,'(.*) (.*)','$1T$2Z')

Result:

2020-04-26T11:50:51.804Z

If you need to get microseconds in Hive, the dirty trick is

with time as (
select reflect('java.util.Date','getTime') as millis, reflect('java.lang.System','nanoTime') as nano 

)
select concat( from_unixtime(floor(millis / 1000   ),"yyyy-MM-dd'T'HH:mm:ss"), '.',cast(millis%1000 as int),cast(nano%1000 as int),'Z')
from time

Result:

2020-04-26T21:53:31.261356Z

But this is not real microsecond-precision.

Upvotes: 1

Related Questions