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
Advertisement
Answer
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.