SQL Reference: Date Time Functions

Home > Administration > Reporting > SQL Reference

Introduction While Hornbill measures intrinsically operate on the basis of arranging records within a specific time period (day/week/month/year), when creating certain types of widgets, there may be occasions when you wish to only return data from a specific time frame. SQL date functions help us do this. The date time functions are only relevant to columns that hold a date/time stamp and would be used in your "WHERE" clause criteria.

Related Articles Reporting

Date Functions

Requirement	Operator	SQL Function
Since First Day of this month	>=	LAST_DAY(NOW() - INTERVAL 1 MONTH) + INTERVAL 1 DAY
Since Last Day of this month	<	LAST_DAY(NOW())
Since First Day of last month	>=	LAST_DAY(NOW() - INTERVAL 2 MONTH) + INTERVAL 1 DAY
Since Last Day of last month	<	LAST_DAY(NOW() - INTERVAL 1 MONTH)

Requirement	Operator	SQL Function
Since First Day of Last Week (Sunday)	>=	CURDATE() - INTERVAL DAYOFWEEK(CURDATE())+6 DAY
Since Last Day of Last Week (Saturday)	<	CURDATE() - INTERVAL DAYOFWEEK(CURDATE())-1 DAY

Requirement	Operator	SQL Function
Show Last 10 Days Records	>=	DATE_SUB(CURDATE(), INTERVAL 10 DAY)
Show Last 1 Months Records	>=	DATE_SUB(CURDATE(), INTERVAL 1 MONTH)

Requirement	Operator	SQL Function
Show Todays Records	>=	curdate()
Show Yesterdays Records	>=	DATE_SUB(CURDATE(), INTERVAL 1 DAY) and h_datelogged < curdate()

Requirement	Operator	SQL Function
Calls logged so far this week (from Sunday)	>=	curdate() - INTERVAL DAYOFWEEK(curdate()) -1 DAY

Requirement	Operator	SQL Function
Add 24 hours from now	<	DATE_ADD(NOW(), INTERVAL 1 DAY)

Source: https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html