CODEWITHSUNDEEP
hivehiveqliso8601
Sanket Jain
Sanket Jain

Reputation: 75

How to change 01/23/2020 11:37:23 PM to ISO dateformat

How do I change MM/DD/YYYY HH:MM:SS AM/PM to ISO Date format YYYY-MM-DDThh:mm:ssZ.

I have tried a couple of functions like unix_timestamp etc.

Upvotes: 2

Views: 43

Answers (1)

mazaneicha
mazaneicha

Reputation: 9427

Here is one way:

beeline> select date_format(cast(unix_timestamp('04/19/2020 09:30:59 AM','MM/dd/yyyy HH:mm:ss a')*1000 as timestamp),'yyyy-MM-dd\'T\'HH:mm:ss\'Z\'');

+-----------------------+
|          _c0          |
+-----------------------+
| 2020-04-19T09:30:59Z  |
+-----------------------+
1 row selected (0.255 seconds)

But, if your own timezone is not UTC then you should probably replace simple cast(... as timestamp) with a typecast+timezone conversion via to_utc_timestamp():

beeline> select date_format(to_utc_timestamp(unix_timestamp('04/19/2020 09:30:59 AM','MM/dd/yyyy HH:mm:ss a')*1000,'EST'),'yyyy-MM-dd\'T\'HH:mm:ss\'Z\'');

+-----------------------+
|          _c0          |
+-----------------------+
| 2020-04-19T14:30:59Z  |
+-----------------------+
1 row selected (0.236 seconds)

See Hive Language Manual for details.

Upvotes: 2

Related Questions