MySQL Function - Date Function

(1) Functions to get the current date and the current time
(2) Functions to get the current date and time
(3)UNIX timestamp function
(4) Functions that return UTC date and UTC time
(5) Get the month's functions month(date) and monthname(date)
(6) Get the week's functions dayname(d), dayofweek(d) and weekday(d)
(7) Functions week(d) and day of year (d) to obtain the number of weeks
(8) Functions dayofyear(d) and dayofmonth(d) to obtain days
(9) Get the functions of year, quarter, hour, minute and second.
(10) Function extract(type from date) to get the specified value of the date
(11) Function of time-to-second conversion
(12) Function for calculating date and time
(13) Functions that format dates and times

(1) Functions to get the current date and the current time

1.curdate() and current_date()
[Example] The date function is used to get the current date of the system. The SQL statement is as follows:

mysql> select curdate(),current_date(),curdate()+0;
+------------+----------------+-------------+
| curdate()  | current_date() | curdate()+0 |
+------------+----------------+-------------+
| 2019-08-18 | 2019-08-18     |    20190818 |
+------------+----------------+-------------+
1 row in set (0.00 sec)
  • curdate()+0 means that the current date value is converted to a numerical type.

2.curtime() and current_time()
[Example] Use the time function to get the current time of the system. The SQL statement is as follows:

mysql> select curtime(),current_time(),curtime()+0;
+-----------+----------------+-------------+
| curtime() | current_time() | curtime()+0 |
+-----------+----------------+-------------+
| 17:08:07  | 17:08:07       |      170807 |
+-----------+----------------+-------------+
1 row in set (0.00 sec)
  • curtime()+0 means that the current time value is converted to a numerical value.
(2) Functions to get the current date and time
  • Curr_timestamp(), localtime(), now() and sysdate() all return the current date and time values.

[Example] Use the date-time function to get the current system time and date. The SQL statement is as follows:

mysql> select current_timestamp(),
    -> localtime(),
    -> now(),
    -> sysdate();
+---------------------+---------------------+---------------------+---------------------+
| current_timestamp() | localtime()         | now()               | sysdate()           |
+---------------------+---------------------+---------------------+---------------------+
| 2019-08-18 19:10:05 | 2019-08-18 19:10:05 | 2019-08-18 19:10:05 | 2019-08-18 19:10:05 |
+---------------------+---------------------+---------------------+---------------------+
1 row in set (0.05 sec)
(3)UNIX timestamp function
  • If unix_timestamp(date) is called without parameters, a UNIX timestamp (seconds after GMT, GMT 1970.1.1) is returned as an unsigned integer.
  • Date can be a date string, a datetime string, a timestamp, or a number in the YY[YY]MMDD format of the local time.

1.unix_timestamp(date)
[Example] The unix_timestamp function is used to return the unix-formatted timestamp. The SQL statement is as follows:

mysql> select unix_timestamp(),unix_timestamp(now()),now();
+------------------+-----------------------+---------------------+
| unix_timestamp() | unix_timestamp(now()) | now()               |
+------------------+-----------------------+---------------------+
|       1566127316 |            1566127316 | 2019-08-18 19:21:56 |
+------------------+-----------------------+---------------------+
1 row in set (0.05 sec)
  • The from_unixtime() function converts the UNIX timestamp into a normal format, which is inverse to the unix_timestamp(date) function.

2.from_unixtime(date)
[Example] Use the from_unixtime function to convert the unix timestamp to the normal format time. The SQL statement is as follows:

mysql> select from_unixtime('1566127316');
+-----------------------------+
| from_unixtime('1566127316') |
+-----------------------------+
| 2019-08-18 19:21:56.000000  |
+-----------------------------+
1 row in set (0.00 sec)

(4) Functions that return UTC date and UTC time.

1.UTC_DATE()
[Example] The current UTC date value is returned using the utc_date() function. The SQL statement is as follows:

mysql> select utc_date(),utc_date()+0;
+------------+--------------+
| utc_date() | utc_date()+0 |
+------------+--------------+
| 2019-08-18 |     20190818 |
+------------+--------------+
1 row in set (0.05 sec)

2.UTC_TIME()
[Example] Using UTC_TIME() function to return the current UTC time value, the SQL statement is as follows:

mysql> select utc_time(),utc_time()+0;
+------------+--------------+
| utc_time() | utc_time()+0 |
+------------+--------------+
| 11:32:27   |       113227 |
+------------+--------------+
1 row in set (0.00 sec)
(5) Get the month's functions month(date) and monthname(date)

1.month(date)
[Example] The month() function is used to return the month in the specified date. The SQL statement is as follows:

mysql> select month('2019-08-18');
+---------------------+
| month('2019-08-18') |
+---------------------+
|                   8 |
+---------------------+
1 row in set (0.00 sec)

2.monthname(date)
[Example] Use the monthname() function to return the month name in the specified date. The SQL statement is as follows:

mysql> select monthname('2019-08-18');
+-------------------------+
| monthname('2019-08-18') |
+-------------------------+
| August                  |
+-------------------------+
1 row in set (0.00 sec)
(6) Get the week's functions dayname(d), dayofweek(d) and weekday(d)

1.dayname(d)
[Example] Use the dayname() function to return the workday name of the specified date. The SQL statement is as follows:

mysql> select dayname('2019-08-18');
+-----------------------+
| dayname('2019-08-18') |
+-----------------------+
| Sunday                |
+-----------------------+
1 row in set (0.00 sec)

2.dayofweek(d)
[Example] Using the dayofweek() function to return the weekly index corresponding to the date, the SQL statement is as follows:

mysql> select dayofweek('2019-08-18');
+-------------------------+
| dayofweek('2019-08-18') |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.00 sec)

3.weekday(d)

  • Wekday (d) returns the index of working days corresponding to d: 0 for Monday, 1 generation for Tuesday,... 6 represents Sunday.

[Example] The weekday() function is used to return the index of the workday corresponding to the date. The SQL statement is as follows:

mysql> select weekday('2019-08-18 19:40:00'),
    -> weekday('2019-08-18');
+--------------------------------+-----------------------+
| weekday('2019-08-18 19:40:00') | weekday('2019-08-18') |
+--------------------------------+-----------------------+
|                              6 |                     6 |
+--------------------------------+-----------------------+
1 row in set (0.00 sec)
(7) Functions week(d) and day of year (d) to obtain the number of weeks
  • week(d) calculation date D is the week in a year. Double-parameter form allows you to specify whether the week begins on Sunday or Monday. If the Mode l parameter is ignored, use the default_week_format system independent variable value.

1.week(d)
[Example] The week() function is used to query the specified date for the week of the year. The SQL statement is as follows:

mysql> select week('2019-08-18'),week('2019-08-18',0),week('2019-08-18',1);
+--------------------+----------------------+----------------------+
| week('2019-08-18') | week('2019-08-18',0) | week('2019-08-18',1) |
+--------------------+----------------------+----------------------+
|                 33 |                   33 |                   33 |
+--------------------+----------------------+----------------------+
1 row in set (0.05 sec

2.weekofyear(d)
[Example] Using weekofyear() to query the specified date is the week of the year. The SQL statement is as follows:

mysql> select week('2019-08-18',3),weekofyear('2019-08-18');
+----------------------+--------------------------+
| week('2019-08-18',3) | weekofyear('2019-08-18') |
+----------------------+--------------------------+
|                   33 |                       33 |
+----------------------+--------------------------+
1 row in set (0.05 sec)
(8) Functions dayofyear(d) and dayofmonth(d) to obtain days

1.dayofyear()
[Example] Use the dayofyear() function to return the location of the specified date in a year. The SQL statement is as follows:

mysql> select dayofyear('2019-08-18');
+-------------------------+
| dayofyear('2019-08-18') |
+-------------------------+
|                     230 |
+-------------------------+
1 row in set (0.00 sec)

2.dayofmonth()
[Example] Use the dayofmonth() function to return the location of the specified date in a month. The SQL statement is as follows.

mysql> select dayofmonth('2019-08-18');
+--------------------------+
| dayofmonth('2019-08-18') |
+--------------------------+
|                       18 |
+--------------------------+
1 row in set (0.00 sec)
(9) Get the functions of year, quarter, hour, minute and second.

1.YEAR(date)
[Example] The year() function is used to return the year corresponding to the specified date. The SQL statement is as follows:

mysql> select year('19-08-18'),year('98-02-19');
+------------------+------------------+
| year('19-08-18') | year('98-02-19') |
+------------------+------------------+
|             2019 |             1998 |
+------------------+------------------+
1 row in set (0.05 sec)

2.QUARTER(date)
[Example] The quarter() function is used to return the quarter corresponding to the specified date. The SQL statement is as follows:

mysql> select quarter('19-08-18');
+---------------------+
| quarter('19-08-18') |
+---------------------+
|                   3 |
+---------------------+
1 row in set (0.00 sec)

3.MINUTE(time)
[Example] The minute() function is used to return the minute value of the specified time. The SQL statement is as follows:

mysql> select minute('19-08-18 20:07:00');
+-----------------------------+
| minute('19-08-18 20:07:00') |
+-----------------------------+
|                           7 |
+-----------------------------+
1 row in set (0.00 sec)

4.SECOND(time)
[Example] The second() function is used to return the second value of the specified time. The SQL statement is as follows:

mysql> select second('20:07:00');
+--------------------+
| second('20:07:00') |
+--------------------+
|                  0 |
+--------------------+
1 row in set (0.00 sec)
(10) Function extract(type from date) to get the specified value of the date

[Example] Use the extract(type from date) function to extract date or time values.

mysql> select extract(year from '2019-08-18') as col1,
    -> extract(year_month from '2019-08-18 20:46:01') as col2,
    -> extract(day_minute from '2019-08-18 20:46:01') as col3;
+------+--------+--------+
| col1 | col2   | col3   |
+------+--------+--------+
| 2019 | 201908 | 182046 |
+------+--------+--------+
1 row in set (0.00 sec)
(11) Function of time-to-second conversion

1.time_to_sec(time)

  • time_to_sec(time) returns a time parameter that has been converted into seconds. The conversion formula is: hours x 3600 + minutes * 60 + seconds.

[Example] The time_to_sec function is used to convert the time value to the second value.

mysql> select time_to_sec('20:34:00');
+-------------------------+
| time_to_sec('20:34:00') |
+-------------------------+
|                   74040 |
+-------------------------+
1 row in set (0.00 sec)

2.sec_to_time(seconds)

  • The return value of the sec_to_time function becomes a small value after adding a value of 0.
  • time_to_sec and sec_to_time are inverse functions.

[Example] The sec_to_time() function is used to convert the second value into time format, and the SQL statement is as follows.

mysql> select sec_to_time(2345),sec_to_time(2345)+0,
    -> time_to_sec('20:36:00'),sec_to_time('74040');
+-------------------+---------------------+-------------------------+----------------------+
| sec_to_time(2345) | sec_to_time(2345)+0 | time_to_sec('20:36:00') | sec_to_time('74040') |
+-------------------+---------------------+-------------------------+----------------------+
| 00:39:05          |                3905 |                   74160 | 20:34:00.000000      |
+-------------------+---------------------+-------------------------+----------------------+
1 row in set (0.05 sec)
(12) The function of calculating date and time.

Format for calculating date and time in MySQL:

1. The functions of date_add (date, interval expr type) and adddate(date,interval expr type) are the same. The addition of date is performed:

[Example] The date_add() and adddate() functions are used to perform the date-add operation. The SQL statements are as follows:

mysql> select date_add('2019-08-18 23:59:59',interval 1 second) as col1,
    -> adddate('2019-08-18 23:59:59',interval 1 second) as col2,
    -> date_add('2019-08-18 23:59:59',interval '1:1' minute_second) as col3;
+---------------------+---------------------+---------------------+
| col1                | col2                | col3                |
+---------------------+---------------------+---------------------+
| 2019-08-19 00:00:00 | 2019-08-19 00:00:00 | 2019-08-19 00:01:00 |
+---------------------+---------------------+---------------------+
1 row in set (0.05 sec)

2. The date_sub (date, interval expr type) and the subdate(date,interval expr type) functions function the same, and perform the subtraction of the date:

[Example] Date subtraction is performed using date_sub and subdate functions. The SQL statements are as follows:

mysql> select date_sub('2019-08-18',interval 31 day) as col1,
    -> subdate('2019-08-18',interval 31 day) as col2,
    -> date_sub('2019-08-18 21:15:10',interval '0 0:1:1' day_second) as col3;
+------------+------------+---------------------+
| col1       | col2       | col3                |
+------------+------------+---------------------+
| 2019-07-18 | 2019-07-18 | 2019-08-18 21:14:09 |
+------------+------------+---------------------+
1 row in set (0.00 sec)

3. The addtime (date, expr) function adds the expr value to date and returns the modified value. date is a date or date-time expression, while expr is a time expression.
[Example] Use addtime to add time. The SQL statements are as follows.

mysql> select addtime('2019-08-18 21:59:59','1:1:1'),addtime('02:02:02','02:00:00');
+----------------------------------------+--------------------------------+
| addtime('2019-08-18 21:59:59','1:1:1') | addtime('02:02:02','02:00:00') |
+----------------------------------------+--------------------------------+
| 2019-08-18 23:01:00                    | 04:02:02                       |
+----------------------------------------+--------------------------------+
1 row in set (0.05 sec)

4. The subtime (date, expr) function subtracts the date from the expr value and returns the modified value. The date is a date or date-time expression, and the expr is a time expression.
[Example] The subtraction operation is performed using subtime() function. The SQL statement is as follows:

mysql> select subtime('2019-08-18 21:59:59','1:1:1'),subtime('02:02:02','02:00:00');
+----------------------------------------+--------------------------------+
| subtime('2019-08-18 21:59:59','1:1:1') | subtime('02:02:02','02:00:00') |
+----------------------------------------+--------------------------------+
| 2019-08-18 20:58:58                    | 00:02:02                       |
+----------------------------------------+--------------------------------+
1 row in set (0.00 sec)

5.datediff(date1,date2) returns the number of days between the start time date1 and the end time date2. Date1 and date2 are date or date-and-time expressions. Only the date part of these values is used in the calculation.
[Example] datediff() function is used to calculate the number of days between two dates. The SQL statement is as follows.

mysql> select datediff('2019-08-18 21:59:59','2018-07-18') as col1,
    -> datediff('2019-08-18 22:00:00','2019-08-20') as col2;
+------+------+
| col1 | col2 |
+------+------+
|  396 |   -2 |
+------+------+
1 row in set (0.00 sec)
(13) Functions that format dates and times.

DATE_FORMAT Time and Date Format:

1.date_format()
[Example] The date_format() function is used to format the output date and time values. The SQL statement is as follows:

mysql> select date_format('2019-08-18 23:33:00','%w %m %y') as col1,
    ->  date_format('2019-08-18 23:33:00','%D %y %a %d %m %b %j') as col2;
+---------+---------------------------+
| col1    | col2                      |
+---------+---------------------------+
| 0 08 19 | 18th 19 Sun 18 08 Aug 230 |
+---------+---------------------------+
1 row in set (0.05 sec)

2.time_format()
[Example] The input time value is formatted using the time_format(time,format) function. The SQL statement is as follows:

mysql> select time_format('23:39:30','%H %k %h %I %l');
+------------------------------------------+
| time_format('23:39:30','%H %k %h %I %l') |
+------------------------------------------+
| 23 23 11 11 11                           |
+------------------------------------------+
1 row in set (0.00 sec)

3.get_format()

The format string returned by get_format:

[Example] The get_format() function is used to display format strings of different formatting types. The SQL statements are as follows:

mysql> select get_format(date,'eur'),get_format(date,'usa');
+------------------------+------------------------+
| get_format(date,'eur') | get_format(date,'usa') |
+------------------------+------------------------+
| %d.%m.%Y               | %m.%d.%Y               |
+------------------------+------------------------+
1 row in set (0.05 sec)

[Example] In the date_format() function, the display format string returned by the get_format function is used to display the specified date value. The SQL statement is as follows:

mysql> select date_format('2019-08-19 23:41:30',get_format(date,'usa'));
+-----------------------------------------------------------+
| date_format('2019-08-19 23:41:30',get_format(date,'usa')) |
+-----------------------------------------------------------+
| 08.19.2019                                                |
+-----------------------------------------------------------+
1 row in set (0.00 sec)

Tags: MySQL SQL Unix

Posted on Thu, 29 Aug 2019 00:27:52 -0700 by qadeer_ahmad