CODEWITHSUNDEEP
datetimehadoophive
optimus_prime
optimus_prime

Reputation: 827

Converting a datetime string (3/24/2017 10:00:00 PM) to (3-24-2017 22:00:00) hive i.e convert from 12 hour to 24 hour format

I have a datetime field in a hive table which is of data type string.

It looks as below:

datetime 3/24/2017 10:00:00 PM

Tried to convert it to the right format desired by hive and also tried removing the AM/PM to a 24 hour format but to no avail.

select from_unixtime(unix_timestamp(datetime,'mm-dd-yyyy HH:MM:SS')) from test_table

Upvotes: 2

Views: 4416

Answers (3)

Gino Farisano
Gino Farisano

Reputation: 1

With 'MM/dd/yyyy hh:mm:ss aa' does not work. You can archive this with:

FROM_UNIXTIME
(
    (
    CASE WHEN 
             datetime LIKE '%AM%' 
         THEN 
             UNIX_TIMESTAMP(datetime,'MM/dd/yyyy HH:mm:ss aa') 
         ELSE 
            (UNIX_TIMESTAMP(datetime,'MM/dd/yyyy HH:mm:ssaa') + (12 * 3600)) 
    END
    )
    ,'MM-dd-yyyy HH:mm:ss'
)

Upvotes: 0

Dipak Shaw
Dipak Shaw

Reputation: 401

You can achieve this using below command:

select from_unixtime(unix_timestamp(datetime,'MM/dd/yyyy hh:mm:ss aa'),'MM-dd-yyyy HH:mm:ss') from test_table;

Upvotes: 4

nobody
nobody

Reputation: 11080

The format is MM-dd-yyyy HH:mm:ss aa

select from_unixtime(unix_timestamp(datetime,'MM-dd-yyyy HH:mm:ss aa')) from test_table;

Upvotes: 0

Related Questions