Hey, folks! Hope you all are doing well. Today, in this article, we will be highlighting some of the important SQL DATE TIME functions.
What are the SQL Date Time functions?
SQL Date Time functions are a set of functions specifically defined for manipulating and dealing with datetime values. These functions help us to segregate and work with the datetime values efficiently.
So, let us get started!
1. ADDDATE() function
The ADDDATE() function enables us to add a particular expression to the specified datetime value.
Syntax:
ADDDATE(date, INTERVAL expression unit)
Thus, by using ADDDATE() function, we can add various expressions to it such as —
- second
- minute
- hour
- day
- month
- week
- year
- quarter, etc.
The ADDDATE() function returns a datetime expression after addition of the particular expression to it.
Let us try to implement the same through an example:
SELECT ADDDATE('2000-02-01', INTERVAL 5 Month) as add_month;
SELECT ADDDATE('2000-02-01', INTERVAL 25 Minute) as add_minute;
Here, we have added the month and minute expression values to the date value of the function.
Output:
add_month
2000-07-01
add_minute
2000-02-01 00:25:00
2. ADDTIME() function
The ADDTIME() function adds a datetime expression with another expression and returns the updated datetime value.
Syntax:
ADDTIME(expression1, expression2)
Consider the below example,
SELECT ADDTIME('2000-07-12 11:45:15','5 2:14:14') as updated_timestamp;
In this example, we have added day and time values to the give date time expression.
Output:
updated_timestamp
2000-07-17 13:59:29
3. CURRENT_DATE() function
The CURRENT_DATE() function extracts the current date expression from the system and returns it in the following format–
YYYY-MM-DD
Syntax:
CURRENT_DATE;
This function does not take any argument as input and returns the date expression.
SELECT CURRENT_DATE;
Output:
CURRENT_DATE
2020-07-15
4. CURRENT_TIME() function
The CURRENT_TIME() function extracts and returns the current time expression from the underlying system.
Syntax:
CURRENT_TIME;
This function returns the time expression in the form of HH–MM–SS.
Example:
SELECT CURRENT_TIME;
Output:
CURRENT_TIME
11:27:12
5. CURRENT_TIMESTAMP() function
The CURRENT_TIMESTAMP() function returns the current date-time expression from the system in the following format —
YYYY-MM-DD HH:MM:SS
Syntax:
CURRENT_TIMESTAMP;
Let us have a look at the below example to understand the working of the above function.
SELECT CURRENT_TIMESTAMP;
Output:
CURRENT_TIMESTAMP
2020-07-15T11:34:21Z
6. DATE() function
The DATE() function extracts the date expression from the passed timestamp value and returns the date.
Syntax:
DATE(timestamp);
In the below example, we have extracted the date expressions from the timestamp values passed to the function.
SELECT DATE('2020-04-04 23:34:34') as DATE;
SELECT DATE(CURRENT_TIMESTAMP) as DATE;
Output:
DATE
2020-04-04
DATE
2020-07-15
7. DATE_SUB() function
The DATE_SUB() function subtracts a particular expression from the specified date expression.
Syntax:
DATE_SUB(date, INTERVAL expression-value unit)
Let us try to implement the same through an example.
SELECT DATE_SUB('2020-12-12', INTERVAL 5 MONTH)
Output:
DATE_SUB('2020-12-12', INTERVAL 5 MONTH)
2020-07-12
8. EXTRACT() function
The EXTRACT() function extracts a particular expression from the date value and returns the expression.
Syntax:
EXTRACT(expression from date);
In the below example, we have extracted the month value from the date expression passed to the function.
SELECT EXTRACT(MONTH from '2020-5-12')
Output:
EXTRACT(MONTH from '2020-5-12')
5
9. NOW() function
The NOW() function returns the current timestamp from the system.
Syntax:
NOW();
Example:
SELECT NOW() as Present_Time;
Output:
Present_Time
2020-07-15T12:17:08Z
10. QUARTER() function
The QUARTER() function returns the quarter value of the year from the date expression passed to it.
Syntax:
SELECT QUARTER(NOW()) as Quarter_Year;
Here the NOW() function returns 2020-07-15T12:17:08Z as the timestamp value from which the quarter value based upon the month as described below–
- 1-3: 1st Quarter
- 4-6: 2nd Quarter
- 7-9: 3rd Quarter
- 10-12: 4th Quarter
Output:
Quarter_Year
3
11. TIME() function
The Time() function extracts the time expression from the datetime expression and returns the time value.
Syntax:
TIME(datetime)
Example:
SELECT TIME(NOW());
Output:
TIME(NOW())
07:34:38
12. TIMESTAMP() function
The TIMESTAMP() function takes date expression as an argument and adds the time expression to it. Thus, the function returns a datetime expression.
Syntax:
TIMESTAMP(date)
Example:
SELECT TIMESTAMP('2020-11-11');
Output:
TIMESTAMP('2020-11-11')
2020-11-11T00:00:00Z
13. TIMEDIFF() function
The TIMEDIFF() function returns the difference between two-time expressions.
Syntax:
TIMEDIFF(time1, time2)
Example:
SELECT TIMEDIFF('30:12:20','20:10:10');
Output:
TIMEDIFF('30:12:20','20:10:10')
10:02:10
14. TO_DAYS() function
The TO_DAYS() function returns the count of days between 0 and the date expression passed to the function.
Syntax:
TO_DAYS(date)
Example:
SELECT TO_DAYS('2020-10-10')
Output:
TO_DAYS('2020-10-10')
738073
15. Extracting portion of datetime expressions
We can extract the year, month, hour, seconds value, etc from the datetime expressions as shown below–
SELECT YEAR('2020-10-10 12:30:30') as Year;
SELECT MONTH('2020-10-10 12:30:30') as Month;
SELECT MINUTE('2020-10-10 12:30:30') as Minute;
SELECT WEEK('2020-10-10 12:30:30') as WEEK;
Output:
Year
2020
Month
10
Minute
30
WEEK
40
Conclusion
By this, we have come to the end of this topic. Feel free to comment below in case you come across any question.
For more such posts related to SQL, do visit SQL JournalDev.