Search code examples
sqltimehiveqlclouderaimpala

SQL (HUE) : Is there any way to convert 24 hrs time into 12 hrs AM / PM format with hours buckets

I have table A which contains column time stored as timestamp datatype.

Table A: Contains time column in HH:MM:SS in 24 hrs format.

Sample data below:

time
12:32:45
16:09:04
09:02:16
18:34:33
08:59:30

Now I want to create a bucket based on hours and adding AM/PM.

eg: 
time between 00:00:00 - 00:59:00 = 12 AM,
01:00:00 - 01:59:00 = 01 AM,
14:00:00 - 14:59:00 = 02 PM and so on.

Desired Output :

time     new_time
12:32:45  12 PM
16:09:04  04 PM
09:02:16  09 AM
18:34:33  06 PM
08:59:30  08 AM
Solution

  • Please use below code. Replace now() with time for your query.

    SELECT now(), lpad(CONCAT ( 
CAST (extract(hour from now()) + CASE WHEN extract(hour from now()) >12 THEN -12 
WHEN extract(hour from now())=0 THEN 12 
ELSE 0  END AS string) , 
CASE WHEN extract(hour from now())  >=12 THEN ' PM' ELSE ' AM' END),5,'0') as new_time

    Explanation - firstly i am checking if hour is >12. If yes, deducting 12 to get the hour.
    Then setting up AM/PM based on hour.
    lpad is used to make sure you get data in 01 AM format. enter image description here