I use MySql on a semi-regular basis to analyse data to determine trends and basic statistical information (e.g. min / max / avg) however, one of the issues is often I only care about the information when it occurs within business hours (which in my case is defined as Monday – Friday between 9am and 5pm.

Whilst I was aware of WHERE x = y fields, e.g. where sensor = a which lets you only show values in the table where the field sensor = a, I must admit I was not aware of the weekday, hour and between operators.

As a result i thought it would be worthwhile sharing them on here;

• WEEKDAY returns the weekday index for a week value (Mon = 0, Sun = 6).
• HOUR returns the hour of a time within the range of 0 to 23.
• BETWEEN allows you to only show values where the result is between defined values (e.g. below)

WHERE WEEKDAY(raw_data.entry_date) BETWEEN 0 AND 4 AND HOUR(raw_data.entry_date) BETWEEN 9 AND 17

In my example above, i would be filtering values where raw_data.entry_date is between; Monday – Friday, and the hour is between 09 (9am) and 17 (5pm). This is attached to the end of the query (e.g. SELECT * FROM table WHERE …

Hope that helps, I wish I had of known it earlier!