I'm trying to GROUP_BY hour using this query:
SELECT count(message) AS total_pesan,
h.h as hour
FROM generate_series(0, 23) AS h
LEFT JOIN tasklist AS r
ON extract(hour from r.create_date) = h.h
AND r.create_date >= '2022-06-07 00:00:00'
AND r.create_date < '2022-06-08 00:00:00'
inner join telco_prefix on
substring(r.msisdn, 1, 4) = telco_prefix.prefix and telco_prefix.telco_id = 1 where r.user_id is not null
GROUP BY h.h
ORDER BY h.h;
it worked just fine but only one problem that is, it doen't have hour format like AM and PM or like 01 - 23
it only show like this: