Function handles DATE / TIME in SQL - Part 2

This article will show you in detail how to use all functions to handle Date / Time in SQL with syntax and specific examples to make it easier to visualize and capture functions.

Function handles DATE / TIME in SQL - Part 2 Picture 1

ADDDATE function ()

There are 2 types of ADDDATE functions:

1. ADDDATE (date, interval expr unit)
2. ADDDATE (expr, day)

1. ADDDATE (expr, day)

Add a certain amount of time to the passed time parameter.

1. expr: the amount of time you want to change.
2. day: an integer number you want to add to the expr expression.

mysql> SELECT ADDDATE('2019-01-02', 31); +---------------------------------------------------------+ | ADDDATE('2019-01-02', 31) | +---------------------------------------------------------+ | 2019-02-02 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

2. ADDDATE (date, interval expr unit)

When the second argument is called INTERVAL, ADDDATE () has the same function as DATE_ADD () .

Example function DATE_ADD used like this:

mysql> SELECT DATE_ADD('2019-01-02', INTERVAL 31 DAY); +---------------------------------------------------------+ | DATE_ADD('2019-01-02', INTERVAL 31 DAY) | +---------------------------------------------------------+ | 2019-02-02 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

And ADDDATE has the same function:

Function handles DATE / TIME in SQL - Part 2 Picture 2

ADDTIME function ()

ADDTIME () in SQL has the form: ADDTIME (expr1, expr2).

This function adds expr2 to expr1 and returns the result. Expr1 is a time or datetime expression , while expr2 is a time expression .

mysql> SELECT ADDTIME('2018-12-31 23:59:59.999999','1 1:1:1.000002'); +---------------------------------------------------------+ | DATE_ADD('2018-12-31 23:59:59.999999','1 1:1:1.000002') | +---------------------------------------------------------+ | 2019-01-02 01:01:01.000001 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

CONVERT_TZ ()

CONVERT_TZ () in SQL is of the form: CONVERT_TZ (dt, from_tz, to_tz) .

This function is used to convert the datetime value dt from the time zone from_tz to the time zone to_tz and return the result. CONVERT_TZ () returns NULL if the arguments are invalid.

Example 1:

mysql> SELECT CONVERT_TZ('2019-01-01 12:00:00','GMT','MET'); +---------------------------------------------------------+ | CONVERT_TZ('2019-01-01 12:00:00','GMT','MET') | +---------------------------------------------------------+ | 2019-01-01 13:00:00 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

Example 2:

mysql> SELECT CONVERT_TZ('2019-01-01 12:00:00','+00:00','+10:00'); +---------------------------------------------------------+ | CONVERT_TZ('2019-01-01 12:00:00','+00:00','+10:00') | +---------------------------------------------------------+ | 2019-01-01 22:00:00 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

CURDATE function ()

CURDATE () in SQL returns the current date in the format ' YYYY-MM-DD ' or YYYYMMDD, depending on whether the function is being used in the string or numeric context.

mysql> SELECT CURDATE(); +---------------------------------------------------------+ | CURDATE() | +---------------------------------------------------------+ | 2019-03-15 | +---------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT CURDATE() + 0; +---------------------------------------------------------+ | CURDATE() + 0 | +---------------------------------------------------------+ | 20190315 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

CURRENT_DATE ()

CURRENT_DATE or CURRENT_DATE () in SQL returns the current date in the format ' YYYY-MM-DD ' or YYYYMMDD, depending on the function being used in the string or numeric context.

This function is similar to CURDATE () .

mysql> SELECT CURRENT_DATE; +---------------------------------------------------------+ | CURRENT_DATE | +---------------------------------------------------------+ | 2019-03-15 | +---------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT CURRENT_DATE() + 1; +---------------------------------------------------------+ | CURRENT_DATE() + 1 | +---------------------------------------------------------+ | 20190316 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

CURTIME function ()

CURTIME () in SQL returns the current time as the value of ' HH: MM: SS ' or HHMMSS, depending on whether the function is being used in the string or numeric context.

mysql> SELECT CURTIME(); +---------------------------------------------------------+ | CURTIME() | +---------------------------------------------------------+ | 14:50:26 | +---------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT CURTIME() + 0; +---------------------------------------------------------+ | CURTIME() + 0 | +---------------------------------------------------------+ | 145026 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

Function CURRENT_TIME ()

The CURRENT_TIME or CURRENT_TIME () function in SQL returns the current time as the value of ' HH: MM: SS ' or HHMMSS, depending on whether the function is being used in the string or numeric context.

This function is similar to CURTIME ().

CURRENT_TIMESTAMP () function

The CURRENT_TIMESTAMP or CURRENT_TIMESTAMP () function in SQL returns the current datetime as a value in the format ' YYYY-MM-DD HH: MM: SS ' or YYYYMMDHHMMSS, depending on whether the function is being used in a good string context number.

This function is similar to NOW ().

mysql> SELECT CURRENT_TIMESTAMP; +---------------------------------------------------------+ | CURRENT_TIMESTAMP | +---------------------------------------------------------+ | 2019-03-15 07:36:12 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

Function DATE ()

DATE () in SQL is of the form: DATE (expr).

The DATE () function returns the date part of the expr expression in the date or datetime form that is passed.

mysql> SELECT DATE('2019-12-31 01:02:03'); +---------------------------------------------------------+ | DATE('2019-12-31 01:02:03') | +---------------------------------------------------------+ | 2019-12-31 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

DATEDIFF function ()

DATEDIFF () in SQL has the form: DATEDIFF (expr1, expr2).

This function returns the difference between two time values ​​based on the specified time period expr1 and expr2. These two time values ​​must be expressions in date or datetime forms . Calculate only the day portion of the values ​​used in the calculation.

mysql> SELECT DATEDIFF('2019-12-31 23:59:59','2019-12-30'); +---------------------------------------------------------+ | DATEDIFF('2019-12-31 23:59:59','2019-12-30') | +---------------------------------------------------------+ | 1 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

mysql> SELECT DATEDIFF('2019-01-12 23:59:59','2019-02-12 23:59:59'); +---------------------------------------------------------+ | DATEDIFF('2019-01-12 23:59:59','2019-02-12 23:59:59') | +---------------------------------------------------------+ | -31 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

Function DATE_ADD ()

DATE_ADD () in SQL has the form: DATE_ADD (date, INTERVAL expr unit).

The function DATE_ADD () is used to perform arithmetic operations with '+' arithmetic on date.

1. The date parameter is a DATE or DATETIME value that specifies the initial time.
2. The parameter expr is an expression that defines the value of the time period added to the original date , expr is a string; It can start with a '-' for negative time periods.
3. unit is a keyword that indicates the units in the expression that will be interpreted.

The INTERVAL keyword and unit are not case sensitive.

The following table shows the format of the expr argument for each unit value .

VALUE UNIT EXPR FORMAT MICROSECOND MICROSECOND - Micro seconds SECOND SECOND - Second MINUTE MINUTE - Minutes HOUR HOUR - Hour DAY DAY - WEEK WEEK - MONTH MONTH Week - Month QUARTER QUARTER - YEAR YEAR - Year SECOND_MICROSECOND 'SECOND.MICROSECOND' - Seconds.Micro seconds MINUTE_MICROSECOND 'MINUTE.MICROSECOND' - MinutesMicro MINUTE_SECOND 'MINUTE: SECOND' - Minutes: Second HOUR_MICROSECOND 'HOUR.MICROSECOND' - Hour.Micro HOUR_SECOND 'HOUR: MINUTES: SECOND' - Hours: Minutes: Second HOUR_MINUTE 'HOUR: MINUTE' - Hour: Minutes DAY_MICROSECOND 'DAY.MICROSECOND' - Date.Mic second DAY_SECOND 'DAY HOUR: MINUTE: SECOND' - Date & Time: Minutes: Second DAY_MINUTE 'DAY HOUR: MINUTE' - Date Time: Minutes DAY_HOUR 'DAY HOUR' - Date and Time YEAR_MONTH 'YEAR-MONTH' - Year-Month

Values ​​of QUARTER and WEEK start from MySQL version 5.0.0 .

mysql> SELECT DATE_ADD('2019-12-31 23:59:59', INTERVAL '1:1' MINUTE_SECOND); +---------------------------------------------------------+ | DATE_ADD('2019-12-31 23:59:59', INTERVAL. | +---------------------------------------------------------+ | 2020-01-01 00:01:00 | +---------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT DATE_ADD('2019-01-01', INTERVAL 1 HOUR); +---------------------------------------------------------+ | DATE_ADD('2019-01-01', INTERVAL 1 HOUR) | +---------------------------------------------------------+ | 2019-01-01 01:00:00 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

DATE_SUB () function

DATE_SUB () in SQL takes the form: DATE_SUB (date, INTERVAL expr unit).

The DATE_SUB () function is used to perform the subtraction of '-' arithmetic on date.

1. The date parameter is a DATE or DATETIME value that specifies the initial time.
2. The parameter expr is an expression that defines the value of the time period minus the original date, expr is a string; it can start with a '-'.
3. unit is a keyword that indicates the units in the expression that will be interpreted.

The INTERVAL keyword and unit are not case sensitive.

The format of the expr argument for each unit value is similar to the function DATE_ADD () .

Values ​​of QUARTER and WEEK start from MySQL version 5.0.0.

mysql> SELECT DATE_SUB('2019-06-15', INTERVAL -10 DAY); +---------------------------------------------------------+ | SELECT DATE_SUB('2019-06-15', INTERVAL -10 DAY); | +---------------------------------------------------------+ | 2019-06-25 | +---------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT DATE_SUB('2019-12-31 23:59:59', INTERVAL '1:1' MINUTE_SECOND); +---------------------------------------------------------+ | DATE_SUB('2019-12-31 23:59:59', INTERVAL. | +---------------------------------------------------------+ | 2019-12-31 23:58:58 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

Function DATE_FORMAT ()

DATE_FORMAT () in SQL takes the form: DATE_FORMAT (date, format) used to format the date value in the format string .

Below are the specifiers that can be used in the format string . The character '%' is required before these specifier characters.

Specifier Explanation % a Abbreviated name (Sun.Sat)% b Abbreviated month name (Jan.Dec)% c Month, numeric value (0.12)% D Date by count (0th, 1st, 2nd, 3rd, .)% d Day of the month, numeric value (00.31)% e Day of the month, numeric value (0.31)% f Microsecond (000000 . 999999)% H Hour, value format (00.23)% h Hour, value form (01.12)% I Hour, value form (01.12)% i Minutes, numeric value ( 00.59)% j Year of the year (001.366)% k Hour, value form (0.23)% l Hour, value form (1.12)% M Month name (January . December)% m Month, numeric value format (00.12)% p AM or PM% r Time, 12h (hh: mm: ss is followed by AM or PM)% S Seconds (00.59) % s Seconds (00.59)% T Time, 24h (hh: mm: ss)% U Week of the year (00.53), with Sunday being the first day of the week% u Week of the year (00. .53), with Monday being the first day of the week % V Week of the year (01.53), with Sunday being the first day of the week; used with% X% v Week of the year (01.53), with Monday being the first day of the week; used with% x% W Weekday name (Sunday.Saturday)% w Day of the week, numeric form (0 = Sunday.6 = Saturday)% X Year for the week, with Sunday being the first day of the week , 4-digit value; used with% V% x Year for the week, with Monday being the first day of the week, 4-digit value; used with% v% Y Year, 4-digit value% y Year, 2-digit value %% A character constant%. % x For any .x. Which is not listed above

For example:

mysql> SELECT DATE_FORMAT('2019-10-04 22:23:00', '%H %k %I %r %T %S %w'); +---------------------------------------------------------+ | DATE_FORMAT('2019-10-04 22:23:00.. | +---------------------------------------------------------+ | 22 22 10 10:23:00 PM 22:23:00 00 5 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

mysql> SELECT DATE_FORMAT('2019-10-04 22:23:00', '%W %M %Y'); +---------------------------------------------------------+ | DATE_FORMAT('2019-10-04 22:23:00', '%W %M %Y') | +---------------------------------------------------------+ | Saturday October 2019 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

DAY function ()

DAY () in SQL is in the form: DAY (date) .

The DAY () function returns the date in the month from the passed date , between 0 and 31.

mysql> SELECT DAY('2019-06-15'); +---------------------------------------------------------+ | DAY('2019-06-15') | +---------------------------------------------------------+ | 15 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

This function is similar to DAYOFMONTH ().

Function DAYNAME ()

DAYNAME () in SQL takes the form: DAYNAME (date).

The DAYNAME () function returns the name of the day of the week of the passed date .

mysql> SELECT DAYNAME('2019-03-15'); +---------------------------------------------------------+ | DAYNAME('2019-03-15') | +---------------------------------------------------------+ | Friday | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

DAYOFMONTH ()

DAYOFMONTH () in SQL is in the form: DAYOFMONTH (date).

The DAYOFMONTH () function returns the date in the month from the passed date , between 0 and 31.

mysql> SELECT DAYOFMONTH('2019-06-15'); +---------------------------------------------------------+ | DAYOFMONTH('2019-06-15') | +---------------------------------------------------------+ | 15 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

This function is similar to DAY ().

DAYOFWEEK ()

DAYOFWEEK () in SQL is in the form: DAYOFWEEK (date).

The DAYOFWEEK () function returns the day of the week (1 = Sunday, 2 = Monday . 7 = Saturday). These index values ​​correspond to the ODBC standard.

mysql> SELECT DAYOFWEEK('2019-03-15'); +---------------------------------------------------------+ |DAYOFWEEK('2019-03-15') | +---------------------------------------------------------+ | 6 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

Function DAYOFYEAR ()

DAYOFYEAR () in SQL takes the form: DAYOFYEAR (date).

The DAYOFYEAR () function returns the serial number of the day passed in the year between 1 and 366.

mysql> SELECT DAYOFYEAR('2019-03-15'); +---------------------------------------------------------+ | DAYOFYEAR('2019-03-15') | +---------------------------------------------------------+ | 74 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

Function EXTRACT ()

EXTRACT () in SQL has the form: EXTRACT (unit FROM date).

The EXTRACT () function uses the same unit specifiers as the DATE_ADD () and DATE_SUB () functions , but it extracts parts from date rather than performing arithmetic operations on date.

mysql> SELECT EXTRACT(YEAR_MONTH FROM '2019-03-15 01:02:03'); +---------------------------------------------------------+ | EXTRACT(YEAR_MONTH FROM '2019-03-15 01:02:03') | +---------------------------------------------------------+ | 201903 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

mysql> SELECT EXTRACT(YEAR FROM '2019-03-15'); +---------------------------------------------------------+ | EXTRACT(YEAR FROM '2019-03-15') | +---------------------------------------------------------+ | 2019 | +---------------------------------------------------------+ 1 row in set (0.00 sec)

Function FROM_DAYS ()

FROM_DAYS () in SQL is of the form: FROM_DAYS (N)

This function is used to convert N numbers into a specific date value. FROM_DAYS () is only used with Gregorian calendar dates (1582).

mysql> SELECT FROM_DAYS(737498); +---------------------------------------------------------+ | FROM_DAYS(737498) | +---------------------------------------------------------+ | 2019-03-15 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

FROM_UNIXTIME function ()

FROM_UNIXTIME () in SQL is of the form: FROM_UNIXTIME (unix_timestamp, [format]).

Unix Time (Unix timestamp) is a system for expressing a point on the time axis, according to the time axis it uses the number of seconds to determine the time, with the original point from 00:00:00 on January 1 / 1970 UTC hour.

For example: At 00:00:00 - 12/02/2016 the timestamp value is 1455235200; It means that from 00:00 to 1/1/1970 to 00:00:00 - 12/02/2016 is 1455235200 seconds.

The FROM_UNIXTIME () function returns the representative date of the unix_timestamp argument as a value in the format 'YYYY-MM-DD HH: MM: SS' or YYYYMMDDHHMMSS depending on whether the function is being used in the string or numeric context .

The value is shown in the current time zone. The unix_timestamp argument can be created by the UNIX_TIMESTAMP () function .

mysql> SELECT FROM_UNIXTIME(1552688580); +---------------------------------------------------------+ | FROM_UNIXTIME(1552688580) | +---------------------------------------------------------+ | 2019-03-15 22:23:00 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

If the format parameter is passed, the result will be formatted according to the format string listed similar to the DATE_FORMAT () function .

mysql> SELECT FROM_UNIXTIME(1552688580, '%W %M %Y'); +---------------------------------------------------------+ | FROM_UNIXTIME(1552688580, '%W %M %Y') | +---------------------------------------------------------+ | Friday March 2019 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

Ham HOUR ()

The HOUR () function in SQL has the form: HOUR (time) returns the time from the time passed. The return value is in the range from 0 to 23 for the time of day value.

mysql> SELECT HOUR('10:05:03'); +---------------------------------------------------------+ | HOUR('10:05:03') | +---------------------------------------------------------+ | 10 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

Function LAST_DAY ()

The LAST_DAY () function in SQL is in the form: LAST_DAY (date).

This function has the passed parameter as the date or datetime value , which returns the corresponding value for the last day of the month. The output will be NULL if the parameter is invalid.

mysql> SELECT LAST_DAY('2019-03-15'); +---------------------------------------------------------+ | LAST_DAY('2019-03-15') | +---------------------------------------------------------+ | 2019-03-31 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

LOCALTIME function ()

LOCALTIME or LOCALTIME () returns the current datetime as a value in the format ' YYYY-MM-DD HH: MM: SS ' or YYYYMMDHHMMSS, depending on whether the function is being used in the string or numeric context.

This function is similar to NOW ().

mysql> SELECT LOCALTIME(); +---------------------------------------------------------+ | LOCALTIME() | +---------------------------------------------------------+ | 2019-03-15 09:53:17 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

LOCALTIMESTAMP () function

The LOCALTIMESTAMP or LOCALTIMESTAMP () function returns the current datetime as a value in the format 'YYYY-MM-DD HH: MM: SS' or YYYYMMDHHMMSS, depending on whether the function is being used in the string or numeric context.

This function is similar to NOW ().

mysql> SELECT LOCALTIMESTAMP; +---------------------------------------------------------+ | LOCALTIMESTAMP | +---------------------------------------------------------+ | 2019-03-15 10:12:12 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

Function MAKEDATE ()

MAKEDATE () in SQL is in the form: MAKEDATE (year, dayofyear) .

This function returns a date with the year parameter and the dayofyear passed. The dayofyear value must be greater than zero or the result will be NULL.

mysql> SELECT MAKEDATE(2019,31); +---------------------------------------------------------+ | MAKEDATE(2019,31) | +---------------------------------------------------------+ | 2019-01-31 | +---------------------------------------------------------+ 1 row in set (0.00 sec)

mysql> SELECT MAKEDATE(2019,122); +---------------------------------------------------------+ | MAKEDATE(2019,122) | +---------------------------------------------------------+ | 2019-05-02 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

Function MAKETIME ()

MAKETIME () function in SQL has the form: MAKETIME (hour, minute, second) , returns a time value from the transmitted hour, minute, and second parameters.

mysql> SELECT MAKETIME(12,15,30); +---------------------------------------------------------+ | MAKETIME(12,15,30) | +---------------------------------------------------------+ | '12:15:30' | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

MICROSECOND function ()

The MICROSECOND () function in SQL is in the form of MICROSECOND (expr) and returns a microsecond from the time expression (expr) in the form of a number between 0 and 999999.

mysql> SELECT MICROSECOND('12:00:00.123456'); +---------------------------------------------------------+ | MICROSECOND('12:00:00.123456') | +---------------------------------------------------------+ | 123456 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

MINUTE function ()

The MINUTE () function in SQL has the form: MINUTE (time) , which returns the minute value from the passed time parameter, between 0 and 59.

mysql> SELECT MINUTE('19-03-15 10:05:03'); +---------------------------------------------------------+ | MINUTE('19-03-15 10:05:03') | +---------------------------------------------------------+ | 5 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

Function MONTH ()

The MONTH () function in SQL is in the form: MONTH (date) , returning the month from the passed date parameter, between 0 and 12.

mysql> SELECT MONTH('2019-02-12') +---------------------------------------------------------+ | MONTH('2019-02-12') | +---------------------------------------------------------+ | 2 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

Function MONTHNAME ()

The MONTHNAME () function in SQL is in the form MONTHNAME (date) , returning the month name of the passed date parameter.

mysql> SELECT MONTHNAME('2019-02-12'); +---------------------------------------------------------+ | MONTHNAME('2019-02-12') | +---------------------------------------------------------+ | February | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

NOW () function

The NOW () function in SQL returns the current datetime as a value in the format ' YYYY-MM-DD HH: MM: SS' or YYYYMMDHHMMSS, depending on whether the function is being used in the string or numeric context. The result of the function is the time in the current time zone.

mysql> SELECT NOW(); +---------------------------------------------------------+ | NOW() | +---------------------------------------------------------+ | 2019-03-15 16:50:26 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

PERIOD_ADD function ()

PERIOD_ADD () in SQL is of the form PERIOD_ADD (P, N) .

This function adds the number of months ( N ) specified at a time interval ( P ). P transmits in YYMM or YYYYMM format and returns the value in YYYYMM format .

mysql> SELECT PERIOD_ADD(1901,2); +---------------------------------------------------------+ | PERIOD_ADD(1901,2) | +---------------------------------------------------------+ | 201903 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

mysql> SELECT PERIOD_ADD(201901,2); +---------------------------------------------------------+ | PERIOD_ADD(201901,2) | +---------------------------------------------------------+ | 201903 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

PERIOD_DIFF function ()

PERIOD_DIFF () in SQL takes the form: PERIOD_DIFF (P1, P2).

This function returns the number of months between the P1 and P2 periods passed in YYMM or YYYYMM format .

mysql> SELECT PERIOD_DIFF(1902,201803); +---------------------------------------------------------+ | PERIOD_DIFF(1902,201803) | +---------------------------------------------------------+ | 11 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

QUARTER function ()

The QUARTER () function in SQL in the form QUARTER (date) returns the quarter in the year the date is passed, between 1 and 4.

mysql> SELECT QUARTER('19-04-01'); +---------------------------------------------------------+ | QUARTER('19-04-01') | +---------------------------------------------------------+ | 2 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

SECOND function ()

SECOND () function in SQL has the form SECOND (time) returns the second value from the time parameter passed in, between 0 and 59.

mysql> SELECT SECOND('10:05:03'); +---------------------------------------------------------+ | SECOND('10:05:03') | +---------------------------------------------------------+ | 3 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

SEC_TO_TIME function ()

SEC_TO_TIME () in SQL is of the form: SEC_TO_TIME (second) .

This function returns the value converted from the second parameter to the hour, minute, and second format ' HH: MM: SS ' or HHMMSS, depending on whether the function uses string or number.

mysql> SELECT SEC_TO_TIME(1202); +---------------------------------------------------------+ | SEC_TO_TIME(1202) | +---------------------------------------------------------+ | 00:20:02 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

Function STR_TO_DATE ()

STR_TO_DATE () in SQL takes the form: STR_TO_DATE (str, format).

This function contrasts with the function DATE_FORMAT () , returns a DATETIME value if the string str contains both date and time, returns DATE or TIME if the string contains only date or time sections .

mysql> SELECT STR_TO_DATE('03/15/2019', '%m/%d/%Y'); +---------------------------------------------------------+ | STR_TO_DATE('03/15/2019', '%m/%d/%Y') | +---------------------------------------------------------+ | 2019-03-15 | +---------------------------------------------------------+ 1 row in set (0.00 sec)

mysql> SELECT STR_TO_DATE('March 15 2019", '%M %d %Y'); +---------------------------------------------------------+ | STR_TO_DATE('March 15 2019", "%M %d %Y') | +---------------------------------------------------------+ | 2019-03-15 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

Function handles DATE / TIME in SQL - Part 2 Picture 3

SUBDATE function ()

There are two types of SUBDATE functions:

1. SUBDATE (expr, day)
2. SUBDATE (date, interval expr unit)

1. SUBDATE (expr, day)

Subtract a certain amount of time from the passed time parameter.

1. expr: the amount of time you want to change.
2. day: an integer number you want to subtract from the expr expression.

mysql> SELECT SUBDATE('2019-03-15', 31); +---------------------------------------------------------+ | SUBDATE('2019-03-15', 31) | +---------------------------------------------------------+ | 2019-02-12 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

2. SUBDATE (date, interval expr unit)

When the second argument is called INTERVAL, SUBDATE () has the same function as DATE_SUB () .

For example, the DATE_SUB function uses this:

mysql> SELECT DATE_SUB('2019-03-15', INTERVAL 31 DAY); +---------------------------------------------------------+ | DATE_SUB('2019-03-15', INTERVAL 31 DAY) | +---------------------------------------------------------+ | 2019-02-12 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

And SUBDATE has the same function:

mysql> SELECT SUBDATE('2019-03-15', INTERVAL 31 DAY); +---------------------------------------------------------+ | SUBDATE('2019-03-15', INTERVAL 31 DAY) | +---------------------------------------------------------+ | 2019-02-12 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

SUBTIME function ()

SUBTIME () in SQL takes the form: SUBTIME (expr1, expr2) .

This function returns the time value of the effect of expr1 except expr2. Expr1 is a time or datetime expression , while expr2 is a time expression .

mysql> SELECT SUBTIME('2019-03-31 23:59:59.999999', '1 1:1:1.000002'); +---------------------------------------------------------+ | SUBTIME('2019-03-31 23:59:59.999999', '1 1:1:1.000002') | +---------------------------------------------------------+ | 2019-03-30 22:58:58.999997 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

SYSDATE function ()

The SYSDATE () function in SQL returns the current date and time as a value in the format 'YYYY-MM-DD HH: MM: SS' or YYYYMMDDHHMMSS, depending on the function being used in the string or numeric context .

mysql> SELECT SYSDATE(); +---------------------------------------------------------+ | SYSDATE() | +---------------------------------------------------------+ | 2019-03-15 16:47:44 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

TIME function ()

The TIME () function in SQL has the form TIME (expr) which returns the time value from the expr (time or datetime) expression passed in.

mysql> SELECT TIME('2019-03-15 01:02:03'); +---------------------------------------------------------+ | TIME('2019-03-15 01:02:03') | +---------------------------------------------------------+ | 01:02:03 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

TIMEDIFF function ()

TIMEDIFF () in SQL takes the form: TIMEDIFF (expr1, expr2) .

This function returns the difference between two time values expr1 and expr2, the result is expressed as a time value . These two time values ​​are time or datetime expressions and both must be of the same type.

mysql> SELECT TIMEDIFF('2019-03-15 23:59:59.000001', '2019-03-14 01:01:01.000002'); +---------------------------------------------------------+ | TIMEDIFF('2019-03-15 23:59:59.000001', . | +---------------------------------------------------------+ | 46:58:57.999999 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

TIMESTAMP () function

There are two types of TIMESTAMP functions:

1. TIMESTAMP (expr)
2. TIMESTAMP (expr1, expr2)

1. TIMESTAMP (expr)

When there is only one parameter, the TIMESTAMP () function in SQL returns the datetime value with the expr parameter being either a date or datetime.

mysql> SELECT TIMESTAMP("2019-03-15"); +---------------------------------------------------------+ | TIMESTAMP("2019-03-15") | +---------------------------------------------------------+ | 2019-03-15 00:00:00 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

2. TIMESTAMP (expr1, expr2)

When the TIMESTAMP () function has two parameters, expr2 will be added to expr1 and return the datetime value .

1. expr1 can be a date or datetime.
2. expr2 can only be time.

mysql> SELECT TIMESTAMP("2019-03-15", "26:10:11"); +---------------------------------------------------------+ | TIMESTAMP("2019-03-15", "26:10:11") | +---------------------------------------------------------+ | 2019-03-16 02:10:11 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

TIMESTAMPADD function ()

In SQL, the TIMESTAMPADD () function returns the new datetime value when it is added a specified time period to the original passed time parameter.

The TIMESTAMPADD () syntax has the form: TIMESTAMPADD (unit, interval, datetime_expr).

1. unit: unit of time used to add to the initial time expression datetime_expr. It may be one of the following values:
   
   1. FRAC_SECOND (microsecond)
   2. SECOND, MINUTE
   3. HOUR, DAY
   4. WEEK
   5. MONTH
   6. QUARTER
   7. YEAR
2. interval: the amount of time you want to add.
3. datetime_expr: initial time expression.

mysql> SELECT TIMESTAMPADD(MINUTE,1,'2019-03-15'); +---------------------------------------------------------+ | TIMESTAMPADD(MINUTE,1,'2019-03-15') | +---------------------------------------------------------+ | 2019-03-15 00:01:00 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

TIMESTAMPDIFF function ()

Function syntax of the form: TIMESTAMPDIFF (unit, datetime_expr1, datetime_expr2).

The SQL TIMESTAMPDIFF () function returns an integer representing the time difference between two expressions datetime_expr1 and datetime_expr2.

1. datetime_expr1, datetime_expr2: parameters in date or datetime format .
2. unit: time unit of the value returned. It may be one of the following values:
   
   1. FRAC_SECOND (microsecond)
   2. SECOND, MINUTE
   3. HOUR, DAY
   4. WEEK
   5. MONTH
   6. QUARTER
   7. YEAR

mysql> SELECT TIMESTAMPDIFF(MONTH,'2019-02-01','2019-05-01'); +---------------------------------------------------------+ | TIMESTAMPDIFF(MONTH,'2019-02-01','2019-05-01') | +---------------------------------------------------------+ | 3 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

TIME_FORMAT function ()

Function syntax is of the form: TIME_FORMAT (time, format).

The TIME_FORMAT function in SQL is used as the DATE_FORMAT () function, but the format parameter may contain only the hour, minute, and second format.

Specifier Explain % f microsecond (000000.999999)% H Hour, value form (00.23)% h Hour, value form (01.12)% I Hour, value form (01.12) )% i Minutes, numeric value form (00.59)% p AM or PM% r Time, 12h (hh: mm: ss is followed by AM or PM)% S Seconds (00.59)% s Seconds (00.59)% T Time, 24h (hh: mm: ss)

For example :

mysql> SELECT TIME_FORMAT('19:30:10', '%h %i %s %p'); +---------------------------------------------------------+ | TIME_FORMAT('19:30:10', '%h %i %s %p') | +---------------------------------------------------------+ | 07 30 10 PM | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

mysql> SELECT TIME_FORMAT('19:30:10', '%r'); +---------------------------------------------------------+ | TIME_FORMAT('19:30:10', '%r') | +---------------------------------------------------------+ | 07:30:10 PM | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

mysql> SELECT TIME_FORMAT('97:15:40','%H %k %h %I %l'); +---------------------------------------------------------+ | TIME_FORMAT('97:15:40','%H %k %h %I %l') | +---------------------------------------------------------+ | 97 97 01 01 1 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

If the time value contains a part of time greater than 23, we use % H and % k to return a value greater than the range of values ​​normally used 0 . 23.

TIME_TO_SEC function ()

The TIME_TO_SEC () function in SQL is in the form TIME_TO_SEC (time) , returning the number of seconds converted from the time parameter .

mysql> SELECT TIME_TO_SEC('22:23:00'); +---------------------------------------------------------+ | TIME_TO_SEC('22:23:00') | +---------------------------------------------------------+ | 80580 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

Function TO_DAYS ()

TO_DAYS () function in SQL has the form TO_DAYS (date), returns the number of days between date value and year 0 (calculated from 0000-00-00).

mysql> SELECT TO_DAYS('2019-03-15'); +---------------------------------------------------------+ | TO_DAYS('2019-03-15') | +---------------------------------------------------------+ | 737498 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

mysql> SELECT TO_DAYS(190316); +---------------------------------------------------------+ | TO_DAYS(190316) | +---------------------------------------------------------+ | 737499 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

Function UNIX_TIMESTAMP ()

There are two types of UNIX_TIMESTAMP functions:

1. UNIX_TIMESTAMP ()
2. UNIX_TIMESTAMP (date)

1. UNIX_TIMESTAMP ()

In SQL, if the UNIX_TIMESTAMP () function does not have an associated parameter, it returns the number of seconds according to Unix timestamp, ie the number of seconds since '1970-01-01 00:00:00' UTC to the current date and time in numeric form. untouched.

mysql> SELECT UNIX_TIMESTAMP(); +---------------------------------------------------------+ | UNIX_TIMESTAMP() | +---------------------------------------------------------+ | 1552677612 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

2. UNIX_TIMESTAMP (date)

If the UNIX_TIMESTAMP function has the following date parameter, SQL will return the number of seconds according to the Unix timestamp from '1970-01-01 00:00:00' UTC to the date and time value passed as an unsigned integer (date has can be a string of DATE, DATETIME, TIMESTAMP, or a number in YYMMDD or YYYYMMDD format .

mysql> SELECT UNIX_TIMESTAMP('1995-02-12 22:23:00'); +---------------------------------------------------------+ | UNIX_TIMESTAMP('1995-02-12 22:23:00') | +---------------------------------------------------------+ | 792627780 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

Function UTC_DATE ()

The function UTC_DATE () or UTC_DATE in SQL returns the current UTC date as a value in the format 'YYYY-MM-DD' or YYYYMMDD, depending on whether the function is being used in the string or numeric context.

mysql> SELECT UTC_DATE(); +---------------------------------------------------------+ | UTC_DATE() | +---------------------------------------------------------+ | 2019-03-15 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

mysql> SELECT UTC_DATE() + 0; +---------------------------------------------------------+ | UTC_DATE() + 0 | +---------------------------------------------------------+ | 20190315 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

Function UTC_TIME ()

The UTC_TIME () or UTC_TIME function returns the current UTC time as a value formatted as 'HH: MM: SS' or HHMMSS, depending on whether the function is being used in the string or numeric context.

mysql> SELECT UTC_TIME(); +---------------------------------------------------------+ | UTC_TIME() | +---------------------------------------------------------+ | 18:07:53 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

mysql> SELECT UTC_TIME() + 0; +---------------------------------------------------------+ | UTC_TIME() + 0 | +---------------------------------------------------------+ | 180753 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

UTC_TIMESTAMP () function

The UTC_TIMESTAMP () or UTC_TIMESTAMP function returns the current UTC date and time as a value in the format ' YYYY-MM-DD HH: MM: SS ' or YYYYMMDDHHMMSS, depending on whether the function is being used in a good string context number.

mysql> SELECT UTC_TIMESTAMP(); +---------------------------------------------------------+ | UTC_TIMESTAMP() | +---------------------------------------------------------+ | 2019-03-15 18:08:04 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

mysql> SELECT UTC_TIMESTAMP() + 0; +---------------------------------------------------------+ | UTC_TIMESTAMP() + 0 | +---------------------------------------------------------+ | 20190315180804 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

WEEKDAY function ()

WEEKDAY () in SQL is of the form: WEEKDAY (date).

The WEEKDAY () function returns the weekday index (0 = Monday, 1 = Tuesday . 6 = Sunday).

mysql> SELECT WEEKDAY('2019-03-15 22:23:00'); +---------------------------------------------------------+ | WEEKDAY('2019-03-15 22:23:00') | +---------------------------------------------------------+ | 4 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

WEEKOFYEAR () function

WEEKOFYEAR () in SQL takes the form: WEEKOFYEAR (date).

The WEEKOFYEAR () function returns only the week number of the date of the year that is an integer between 1 and 53.

mysql> SELECT WEEKOFYEAR('2019-03-15'); +---------------------------------------------------------+ | WEEKOFYEAR('2019-03-15') | +---------------------------------------------------------+ | 11 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

YEAR function ()

The YEAR () function in SQL has the form YEAR (date), returning the corresponding year of the passed date parameter. The results are in the range 1000 to 9999.

mysql> SELECT YEAR('19-03-15'); +---------------------------------------------------------+ | YEAR('19-03-15') | +---------------------------------------------------------+ | 2019 | +---------------------------------------------------------+ 1 row in set (0.00 sec) 

In the next section, we will learn about the Temporary Table temporary table in SQL , please keep track.

Previous article: Function handling DATE / TIME in SQL - Part 1

Next article: Temporary Table temporary table in SQL