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!

a screenshot of a cell phone
Prev MIOPS Mobile Remote
Next Remove Public Holidays from MySQL Results

Comments are closed.

Discover more from Travis Hale (Photography and Science)

Subscribe now to keep reading and get access to the full archive.

Continue reading