MySQL Common date-time functions

Date and Time functions

Possible requirements:

What is the current time, the next month's today is the day of the week, statistics up to the current date 3 days before the sum of income ...

These requirements need to be implemented using date and time functions:

Three time zone settings in the MySQL server:

① system time Zone---saved in system variable System_time_zone

② server time Zone---saved in global system variables Global.time_zone

③ the time zone---each client connection is saved in the session variable Session.time_zone

Attention:

The setting of the client time zone affects the display of some date function return values, for example: Now (), Curtime (), Curdate (), and also the display of timestamp column values.

By default, the time zone of the client and server is the same, and the value is System, which indicates the time zone is used.

Mysql> SELECT @ @global. time_zone,@ @session. time_zone;+--------------------+---------------------+| @ @global. Time_zone | @ @session. time_zone |+--------------------+---------------------+| SYSTEM             | SYSTEM              |+--------------------+---------------------+1 row in Set (0.00 sec) mysql> Show variables like ' system_ Time_zone '; +------------------+-------+| Variable_name    | Value |+------------------+-------+| System_time_zone | CST   |+------------------+-------+1 row in Set (0.28 sec)

1. Now ([FSP]): Returns the current date and time of the server (FSP specifies fractional seconds of precision, value 0--6)

Format:

' Yyyy-mm-dd HH:MM:SS ' or ' YYYYMMDDHHMMSS '

The display format for now () is ' Yyyy-mm-dd HH:MM:SS '
Now () +0 the display format is ' YYYYMMDDHHMMSS '

Mysql> Select Now (); +---------------------+| Now ()               |+---------------------+| 2017-03-24 13:53:34 |+---------------------+mysql> Select Now () +0;+--------- -------+| Now () +0        |+----------------+| 20170324135428 |+----------------+

Mysql> Select Now (6); Specify the precision of fractional seconds
+----------------------------+
| Now (6) |
+----------------------------+
| 2017-04-19 19:55:46.658198 |
+----------------------------+

Synonyms for the now () function are: Current_timestamp, Current_timestamp (), Localtimestamp, Localtimestamp (), LocalTime, LocalTime ()

Attention:

Sysdate (): Returns the current date and time of the server

Different points from now: (use now instead of sysdate)

①sysdate () returns the time when the function was executed

②now () returns the time when the statement was executed

Mysql> Select Now (), Sleep (2), now (), +---------------------+----------+---------------------+| Now ()               | sleep (2) | Now ()               |+---------------------+----------+---------------------+| 2017-04-19 20:01:39 |        0 | 2017-04-19 20:01:39 |+---------------------+----------+---------------------+1 row in Set (2.00 sec) mysql> Select Sysdate (), Sleep (2), sysdate (), +---------------------+----------+---------------------+| Sysdate ()           | sleep (2) | sysdate ()           |+---------------------+----------+---------------------+| 2017-04-19 20:02:01 |        0 | 2017-04-19 20:02:03 |+---------------------+----------+---------------------+1 row in Set (2.05 sec)

2, Curtime ([FSP]): Returns the current time, only contains the time and seconds (FSP specified fractional seconds of precision, value 0--6)

Format:

' Yyyy-mm-dd HH:MM:SS ' or ' YYYYMMDDHHMMSS '

Mysql> Select Curtime (), Curtime (2), +-----------+-------------+| Curtime () | Curtime (2)  |+-----------+-------------+| 14:35:23  | 14:35:23.90 |+-----------+-------------+

Synonyms are: Current_time, Current_time ()

3, Curdate (): Returns the current date, only contains the month date

Format:

' Yyyy-mm-dd ' or ' YYYYMMDD '

Mysql> Select Curdate (), curdate () +2;+------------+-------------+| Curdate ()  | curdate () +2 |+------------+-------------+| 2017-03-24 |    20170326 |+------------+-------------+mysql> Select Curdate (), curdate () +0;+------------+-------------+| Curdate ()  | curdate () +0 |+------------+-------------+| 2017-03-24 |    20170324 |+------------+-------------+

Synonyms are: current_date, current_date ()

4, Timediff (EXPR1, EXPR2): Returns two date subtraction (expr1? EXPR2) time difference (two parameter types must be the same)

Mysql> Select Timediff (' 18:32:59 ', ' 60000 '); +------------------------------+| Timediff (' 18:32:59 ', ' 60000 ') |+------------------------------+| 12:32:59                     |+------------------------------+mysql> Select Timediff (' 18:32:59 ', ' 2017-1-1 60000 '); +----------- ----------------------------+| Timediff (' 18:32:59 ', ' 2017-1-1 60000 ') |+---------------------------------------+| NULL                                  |+---------------------------------------+

DATEDIFF (Expr1, EXPR2): Returns two date subtraction (expr1? EXPR2) Days of difference

Mysql> Select DateDiff (' 2017-3-24 18:32:59 ', ' 2016-9-1 '); +-------------------------------------------+| DateDiff (' 2017-3-24 18:32:59 ', ' 2016-9-1 ') |+-------------------------------------------+|                                       204 |+-------------------------------------------+

5. DateTime arithmetic function: Add or subtract (sub) A time interval value of expr for a given date, respectively

Format:

Date_add (date, INTERVAL expr unit);

Date_sub (date, INTERVAL expr unit);

Interval is the interval type keyword

Expr is an expression that corresponds to the following type

Unit is the interval of units (interval type) (20), as follows:

HOUR	Hours
MINUTE	Score of
SECOND	Seconds
Microsecond	Milliseconds
Year	Years
MONTH	Month
Day	Day
WEEK	Week
QUARTER	Quarter
Year_month	Year and month
Day_hour	Day and Hour
Day_minute	Day and minute
Day_ SECOND	Days and seconds
Hour_minute	Hours and minutes
Hour_second	Hours and seconds
Minute_second	Minutes and seconds

Mysql> Select Now (), Date_add (today (), Interval 1 day); #加一天 +---------------------+--------------------------------+| Now ()               | Date_add (Today (), Interval 1 day) |+---------------------+--------------------------------+| 2017-03-24 14:53:08 | 2017-03-25 14:53:08            |+---------------------+--------------------------------+mysql> SELECT date_sub ('　　2005-01-01 00:00:00 ', INTERVAL ' 1 1:1:1 ' Day_second); #减1天1小时1分1秒 +---------------------------------------------------------------+| Date_sub (' 2005-01-01 00:00:00 ', INTERVAL ' 1 1:1:1 ' Day_second) |+--------------------------------------------------- ------------+| 2004-12-30 22:58:59                                           |+---------------------------------------------------------------+

Do not use a function, or you can write an expression to add or subtract a date:

Date + INTERVAL expr unit

Date-interval Expr Unit

Mysql> SELECT ' 2008-12-31 23:59:59 ' + INTERVAL 1 second;+-------------------------------------------+| ' 2008-12-31 23:59:59 ' + INTERVAL 1 SECOND |+-------------------------------------------+| 2009-01-01 00:00:00                       |+-------------------------------------------+1 row in Set (0.00 sec) mysql> SELECT ' 2005-01-01 '-INTERVAL 1 second;+----------------------------------+| ' 2005-01-01 '-INTERVAL 1 SECOND |+----------------------------------+| 2004-12-31 23:59:59              |+----------------------------------+1 row in Set (0.00 sec)

6, select the various parts of the date time: Date, time, year, quarter, month, day, hour, minute, second, microsecond (common)

SELECT now (), Date (now ()); --Date

SELECT now (), Time (now ()); --Time

SELECT now (), Year (now ()); -year

SELECT now (), Quarter (now ()); --Quarterly

SELECT now (), Month (now ()); --month

SELECT now (), Week (now ()); --Zhou

SELECT now (), Day (now ()); --Day

SELECT now (), hour (now ()); --Hours

SELECT now (), Minute (now ()); --Minutes

SELECT now (), second (now ()); --seconds

SELECT now (), Microsecond (now ()); --microseconds

EXTRACT (unit from date): Extract a single part or combination from a date

SELECT now (), extract (year from now ()); -year

SELECT now (), extract (QUARTER from now ()); --Quarterly

SELECT now (), extract (MONTH from now ()); --month

SELECT now (), extract (WEEK from now ()); --Zhou

SELECT now (), extract (Day from now ()); --Day

SELECT now (), extract (HOUR from now ()); --Hours

SELECT now (), extract (MINUTE from now ()); --Minutes

SELECT now (), extract (SECOND from now ()); --seconds

SELECT now (), extract (Year_month from now ()); --Date

SELECT now (), extract (Hour_minute from now ()); --Hours

7, personalized display time and date

DayOfWeek (date)

DayOfMonth (date)

DayOfYear (date)

# #分别返回日期在一周, month, day of the year

Mysql> SELECT Now (), DayOfWeek (now ()), +---------------------+------------------+| Now ()               | DayOfWeek (Now ()) |+---------------------+------------------+| 2017-04-19 20:25:41 |                4 |+---------------------+------------------+1 row in Set (0.00 sec) mysql> SELECT now (), DayOfMonth (now ()); +-------- -------------+-------------------+| Now ()               | DayOfMonth (Now ()) |+---------------------+-------------------+| 2017-04-19 20:25:51 |                |+---------------------+-------------------+1 row in Set (0.03 sec) mysql> Select Now (), DayOfYear (now ()); +------- --------------+------------------+| Now ()               | DayOfYear (Now ()) |+---------------------+------------------+| 2017-04-19 20:26:00 |              109 |+---------------------+------------------+1 row in Set (0.00 sec)

　　

Dayname ()

MonthName ()

# #分别返回日期的星期和月份名称

The name is Chinese or English is controlled by the system variable Lc_time_names (the default value is ' en_US ')

Mysql> Show variables like ' lc_time_names '; +---------------+-------+| variable_name | Value |+---------------+-------+| Lc_time_names | en_US |+---------------+-------+1 row in Set (0.00 sec) mysql> Select Dayname (now ()), MonthName (now ()); +------------- ---+------------------+| Dayname (Now ()) | MonthName (now ()) |+----------------+------------------+| Wednesday      | April            |+----------------+------------------+1 row in Set (0.00 sec) mysql> set lc_time_names= ' ZH_CN '; Query OK, 0 rows Affected (0.00 sec) mysql> Select Dayname (now ()), MonthName (now ()), +----------------+--------------- ---+| Dayname (Now ()) | MonthName (now ()) |+----------------+------------------+| Wednesday         | April             |+----------------+------------------+1 row in Set (0.00 sec)

MySQL Common date-time functions