Sample Header Ad - 728x90

How would you TRIM the leading ZEROs from Time in Postgres

1 vote
1 answer
217 views
I have the following query that I am toying with:
SELECT
    to_char(date_trunc('hour', "TimeStamp"), 'HH12 AM') || ' - ' || to_char(date_trunc('hour', "TimeStamp")  + interval '1 hour', 'HH12 AM') as time,
    COUNT(*) as totals
FROM 
    eventtotals
WHERE "TimeStamp" >= NOW() - Interval '24 HOURS'
GROUP BY date_trunc('hour', "TimeStamp")
It displays the following output:
TIME			    TOTAL
04 PM - 05 PM		300
05 PM - 06 PM		452
06 PM - 07 PM		393
07 PM - 08 PM		356
08 PM - 09 PM		356
09 PM - 10 PM		361
10 PM - 11 PM		359
11 PM - 12 AM		367
12 AM - 01 AM		357
01 AM - 02 AM		360
02 AM - 03 AM		286
I am aware of:
TRIM(LEADING '0'...
However, I would like to remove the leading zeros from the hours, I am just unsure of where to actually put the line for trimming in the above listed query, unless there is another way.
Asked by muttBunch (65 rep)
Nov 11, 2024, 07:56 AM
Last activity: Nov 12, 2024, 07:39 PM