Difference between revisions of "SQL Reference: Date Time Functions"
Jump to navigation
Jump to search
(Created page with "<div style="border:1px solid #90C0FF; background:#D0E0FF; width:99%; padding:4px; margin-bottom:10px;"> __NOTOC__Home > Administration > Reporting > SQL ...") |
|||
Line 6: | Line 6: | ||
|style="width:73%"| | |style="width:73%"| | ||
==Introduction== | ==Introduction== | ||
− | While Hornbill measures intrinsically operate on the basis of arranging | + | 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. |
|style="width:5%"| | |style="width:5%"| | ||
| | | |
Revision as of 17:45, 20 November 2018
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 | 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 | 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 | 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 | SQL Function |
---|---|
Show Todays Records | curdate() |
Show Yesterdays Records | DATE_SUB(CURDATE(), INTERVAL 1 DAY) and h_datelogged < curdate() |
Requirement | SQL Function |
---|---|
Calls logged so far this week (from Sunday) | curdate() - INTERVAL DAYOFWEEK(curdate()) -1 DAY |
Requirement | 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