SQL Reference: Date Time Functions
Jump to navigation
Jump to search
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.
Home > Administration > Reporting > SQL Reference
IntroductionWhile 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 |
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