TIME_FORMAT(HOUR(`dPostTime`), '%l %p') Returning Same Values

Question

The following is my query:

SELECT COUNT(*), TIME_FORMAT(HOUR(`dPostTime`), '%l %p') AS `hr`, HOUR(`dPostTime`) AS 'hour' FROM `tblQA` WHERE dPostDateTime >= '$_POST[startDate]' AND dPostDateTime <= '$_POST[endDate]' AND dPostTime <> '0000-00-00 00:00:00' GROUP BY `hour`"

I have the data posting to a table through a php while loop. It's returning all 12 AM, instead of 1 AM, 2AM, 3AM, etc...

If I use just HOUR('dPostTime') it displays properly, BUT i'm looking to format the date instead of just returning a value.

Thanks!

Answer 1

You should use the dPostTime directly in TIME_FORMAT, e.g.:

TIME_FORMAT(`dPostTime`, '%l %p') AS `hr`