Difference between revisions of "SQL Reference: Date Time Functions"

From Hornbill
Jump to navigation Jump to search
Line 18: Line 18:
 
{| class="wikitable" width="75%" style="text-align: left"
 
{| class="wikitable" width="75%" style="text-align: left"
  
−
! width = "25%", align = "left" | Requirement
+
! width = "15%", align = "left" | Requirement
 
! width = "5%", align = "left" | Operator
 
! width = "5%", align = "left" | Operator
−
! width = "75%", align = "left" | SQL Function
+
! width = "40%", align = "left" | SQL Function
 
|-
 
|-
 
|-
 
|-
Line 43: Line 43:
 
{| class="wikitable" width="75%" style="text-align: left"
 
{| class="wikitable" width="75%" style="text-align: left"
  
−
! width = "25%", align = "left" | Requirement
+
! width = "15%", align = "left" | Requirement
 
! width = "5%", align = "left" | Operator
 
! width = "5%", align = "left" | Operator
−
! width = "75%", align = "left" | SQL Function
+
! width = "40%", align = "left" | SQL Function
 
|-
 
|-
 
|-
 
|-
Line 63: Line 63:
 
! width = "25%", align = "left" | Requirement
 
! width = "25%", align = "left" | Requirement
 
! width = "5%", align = "left" | Operator
 
! width = "5%", align = "left" | Operator
−
! width = "75%", align = "left" | SQL Function
+
! width = "40%", align = "left" | SQL Function
 
|-
 
|-
 
|-
 
|-
Line 78: Line 78:
 
{| class="wikitable" width="75%" style="text-align: left"
 
{| class="wikitable" width="75%" style="text-align: left"
  
−
! width = "25%", align = "left" | Requirement
+
! width = "15%", align = "left" | Requirement
 
! width = "5%", align = "left" | Operator
 
! width = "5%", align = "left" | Operator
−
! width = "75%", align = "left" | SQL Function
+
! width = "40%", align = "left" | SQL Function
 
|-
 
|-
 
|-
 
|-
Line 95: Line 95:
 
{| class="wikitable" width="75%" style="text-align: left"
 
{| class="wikitable" width="75%" style="text-align: left"
  
−
! width = "25%", align = "left" | Requirement
+
! width = "15%", align = "left" | Requirement
 
! width = "5%", align = "left" | Operator
 
! width = "5%", align = "left" | Operator
−
! width = "75%", align = "left" | SQL Function
+
! width = "40%", align = "left" | SQL Function
 
|-
 
|-
 
|-
 
|-
−
| align = "left"            |<!-- Requirement   --> Calls logged so far this week (from Sunday)
+
| align = "left"            |<!-- Requirement--> Calls logged so far this week (from Sunday)
−
|                            <!-- Operator --> >=
+
|                            <!-- Operator--> >=
−
|                            <!-- SQL Function--> curdate() - INTERVAL DAYOFWEEK(curdate()) -1 DAY  
+
|                            <!-- SQL Function--> curdate() - INTERVAL DAYOFWEEK(curdate()) -1 DAY
 
|-
 
|-
 
|}
 
|}
Line 108: Line 108:
 
{| class="wikitable" width="75%" style="text-align: left"
 
{| class="wikitable" width="75%" style="text-align: left"
  
−
! width = "25%", align = "left" | Requirement
+
! width = "15%", align = "left" | Requirement
 
! width = "5%", align = "left" | Operator
 
! width = "5%", align = "left" | Operator
−
! width = "75%", align = "left" | SQL Function
+
! width = "40%", align = "left" | SQL Function
 
|-
 
|-
 
|-
 
|-
−
| align = "left"            |<!-- Requirement   --> Add 24 hours from now
+
| align = "left"            |<!-- Requirement--> Add 24 hours from now
−
|                            <!-- Operator --> <
+
|                            <!-- Operator--> <
 
|                            <!-- SQL Function--> DATE_ADD(NOW(), INTERVAL 1 DAY)  
 
|                            <!-- SQL Function--> DATE_ADD(NOW(), INTERVAL 1 DAY)  
 
|-
 
|-

Revision as of 10:30, 13 May 2019

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

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

Retrieved from "https://wiki.hornbill.com/index.php?title=SQL_Reference:_Date_Time_Functions&oldid=20714"