Difference between revisions of "SQL Reference: Date Time Functions"
Jump to navigation
Jump to search
| (2 intermediate revisions by one other user not shown) | |||
| Line 18: | Line 18: | ||
{| class="wikitable" width="75%" style="text-align: left" | {| class="wikitable" width="75%" style="text-align: left" | ||
| − | ! width = " | + | ! width = "15%", align = "left" | Requirement |
| − | ! width = " | + | ! width = "5%", align = "left" | Operator |
| + | ! width = "40%", align = "left" | SQL Function | ||
|- | |- | ||
|- | |- | ||
| align = "left" |<!-- Requirement --> Since First Day of this month | | align = "left" |<!-- Requirement --> Since First Day of this month | ||
| + | | <!-- Operator --> >= | ||
| <!-- SQL Function--> LAST_DAY(NOW() - INTERVAL 1 MONTH) + INTERVAL 1 DAY | | <!-- SQL Function--> LAST_DAY(NOW() - INTERVAL 1 MONTH) + INTERVAL 1 DAY | ||
|- | |- | ||
| align = "left" |<!-- Requirement --> Since Last Day of this month | | align = "left" |<!-- Requirement --> Since Last Day of this month | ||
| + | | <!-- Operator --> < | ||
| <!-- SQL Function--> LAST_DAY(NOW()) | | <!-- SQL Function--> LAST_DAY(NOW()) | ||
|- | |- | ||
| align = "left" |<!-- Requirement --> Since First Day of last month | | align = "left" |<!-- Requirement --> Since First Day of last month | ||
| + | | <!-- Operator --> >= | ||
| <!-- SQL Function--> LAST_DAY(NOW() - INTERVAL 2 MONTH) + INTERVAL 1 DAY | | <!-- SQL Function--> LAST_DAY(NOW() - INTERVAL 2 MONTH) + INTERVAL 1 DAY | ||
|- | |- | ||
| align = "left" |<!-- Requirement --> Since Last Day of last month | | align = "left" |<!-- Requirement --> Since Last Day of last month | ||
| + | | <!-- Operator --> < | ||
| <!-- SQL Function--> LAST_DAY(NOW() - INTERVAL 1 MONTH) | | <!-- SQL Function--> LAST_DAY(NOW() - INTERVAL 1 MONTH) | ||
|- | |- | ||
| Line 38: | Line 43: | ||
{| class="wikitable" width="75%" style="text-align: left" | {| class="wikitable" width="75%" style="text-align: left" | ||
| − | ! width = " | + | ! width = "15%", align = "left" | Requirement |
| − | ! width = " | + | ! width = "5%", align = "left" | Operator |
| + | ! width = "40%", align = "left" | SQL Function | ||
|- | |- | ||
|- | |- | ||
| align = "left" |<!-- Requirement --> Since First Day of Last Week (Sunday) | | align = "left" |<!-- Requirement --> Since First Day of Last Week (Sunday) | ||
| + | | <!-- Operator --> >= | ||
| <!-- SQL Function--> CURDATE() - INTERVAL DAYOFWEEK(CURDATE())+6 DAY | | <!-- SQL Function--> CURDATE() - INTERVAL DAYOFWEEK(CURDATE())+6 DAY | ||
|- | |- | ||
| align = "left" |<!-- Requirement --> Since Last Day of Last Week (Saturday) | | align = "left" |<!-- Requirement --> Since Last Day of Last Week (Saturday) | ||
| + | | <!-- Operator --> < | ||
| <!-- SQL Function--> CURDATE() - INTERVAL DAYOFWEEK(CURDATE())-1 DAY | | <!-- SQL Function--> CURDATE() - INTERVAL DAYOFWEEK(CURDATE())-1 DAY | ||
|- | |- | ||
| Line 54: | Line 62: | ||
! width = "25%", align = "left" | Requirement | ! width = "25%", align = "left" | Requirement | ||
| − | ! width = " | + | ! width = "5%", align = "left" | Operator |
| + | ! width = "40%", align = "left" | SQL Function | ||
|- | |- | ||
|- | |- | ||
| align = "left" |<!-- Requirement --> Show Last 10 Days Records | | align = "left" |<!-- Requirement --> Show Last 10 Days Records | ||
| + | | <!-- Operator --> >= | ||
| <!-- SQL Function--> DATE_SUB(CURDATE(), INTERVAL 10 DAY) | | <!-- SQL Function--> DATE_SUB(CURDATE(), INTERVAL 10 DAY) | ||
|- | |- | ||
| align = "left" |<!-- Requirement --> Show Last 1 Months Records | | align = "left" |<!-- Requirement --> Show Last 1 Months Records | ||
| + | | <!-- Operator --> >= | ||
| <!-- SQL Function--> DATE_SUB(CURDATE(), INTERVAL 1 MONTH) | | <!-- SQL Function--> DATE_SUB(CURDATE(), INTERVAL 1 MONTH) | ||
|- | |- | ||
| Line 67: | Line 78: | ||
{| class="wikitable" width="75%" style="text-align: left" | {| class="wikitable" width="75%" style="text-align: left" | ||
| − | ! width = " | + | ! width = "15%", align = "left" | Requirement |
| − | ! width = " | + | ! width = "5%", align = "left" | Operator |
| + | ! width = "40%", align = "left" | SQL Function | ||
|- | |- | ||
|- | |- | ||
| align = "left" |<!-- Requirement --> Show Todays Records | | align = "left" |<!-- Requirement --> Show Todays Records | ||
| + | | <!-- Operator --> >= | ||
| <!-- SQL Function--> curdate() | | <!-- SQL Function--> curdate() | ||
|- | |- | ||
| align = "left" |<!-- Requirement --> Show Yesterdays Records | | align = "left" |<!-- Requirement --> Show Yesterdays Records | ||
| + | | <!-- Operator --> >= | ||
| <!-- SQL Function--> DATE_SUB(CURDATE(), INTERVAL 1 DAY) and h_datelogged < curdate() | | <!-- SQL Function--> DATE_SUB(CURDATE(), INTERVAL 1 DAY) and h_datelogged < curdate() | ||
|- | |- | ||
| Line 81: | Line 95: | ||
{| class="wikitable" width="75%" style="text-align: left" | {| class="wikitable" width="75%" style="text-align: left" | ||
| − | ! width = " | + | ! width = "15%", align = "left" | Requirement |
| − | ! width = " | + | ! width = "5%", align = "left" | Operator |
| + | ! width = "40%", align = "left" | SQL Function | ||
|- | |- | ||
|- | |- | ||
| − | | align = "left" |<!-- Requirement | + | | align = "left" |<!-- Requirement--> Calls logged so far this week (from Sunday) |
| − | | <!-- SQL Function--> curdate() - INTERVAL DAYOFWEEK(curdate()) -1 DAY | + | | <!-- Operator--> >= |
| + | | <!-- SQL Function--> curdate() - INTERVAL DAYOFWEEK(curdate()) -1 DAY | ||
|- | |- | ||
|} | |} | ||
| Line 92: | Line 108: | ||
{| class="wikitable" width="75%" style="text-align: left" | {| class="wikitable" width="75%" style="text-align: left" | ||
| − | ! width = " | + | ! width = "15%", align = "left" | Requirement |
| − | ! width = " | + | ! width = "5%", align = "left" | Operator |
| + | ! width = "40%", align = "left" | SQL Function | ||
|- | |- | ||
|- | |- | ||
| − | | align = "left" |<!-- Requirement | + | | align = "left" |<!-- Requirement--> Add 24 hours from now |
| + | | <!-- Operator--> < | ||
| <!-- SQL Function--> DATE_ADD(NOW(), INTERVAL 1 DAY) | | <!-- SQL Function--> DATE_ADD(NOW(), INTERVAL 1 DAY) | ||
|- | |- | ||
| Line 104: | Line 122: | ||
| − | [[Category: | + | [[Category:Configuration]] |
Latest revision as of 22:48, 18 March 2024
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