peak hours query

Jassim Rahma

Hi, I need a help please I am using MySQL. I have a patients database. In patients database I have a table called visit with created_date field as timestamp datatype. I want to do a query which will give the break down of total number of patients in every hour like this: hour -------- Patients 00 -------- 7 01 -------- 8 02 -------- 11 03 -------- 4 ... ... ... 20 -------- 5 21 -------- 10 22 -------- 3 23 -------- 0

Technology News @ www.JassimRahma.com

Mycroft Holmes

Create a query that extracts the hours (use datepart(h,datefield) component of the created date as an integer. A simple group by will then give you the result you need.

Never underestimate the power of human stupidity RAH

Bernhard Hiller

The amount of 0 items will make a problem with Mycroft Holmes' answer - that line won't show up. You need the numbers from 0-23 (somehow generated, or make a table "Hours" with those values, and then JOIN the query suggested by Mycroft Holmes to it, and replace NULL values with 0.

S Douglas

Not sure if this will directly work in MySQL, but here is what I would do in SQL Server

SELECT
DATEPART(hh, Created_Date) [Hours]
, COUNT(*) [Patients]
FROM Visit
GROUP BY
DATEPART(hh, Created_Date)

EDIT There is a format function in MySQL that will return the hour https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format[^]

Common sense is admitting there is cause and effect and that you can exert some control over what you understand.