MySQL Date and time function

Date and Time functions
This chapter discusses some functions that can be used to manipulate time values. See section 11.3, "Date and Time type" for valid formats for each time and date type with a range and a specified value.

The following example uses the time function. The following query selects all records with Date_col values for the last 30 days:

Mysql> SELECT something from Tbl_name

-> WHERE date_sub (Curdate (), INTERVAL Day) <= Date_col;

Note that this query can also select a future date record.

Functions used for date values typically accept time-date values and ignore the time portion. Functions that are used for time values usually accept the time date value and ignore the date part.

Functions that return their current date or time are evaluated at the beginning of each query execution. This means that in a single query, multiple accesses to a function such as now () will always get the same result (not our purpose, and a single query also includes calls to the stored program or trigger and all subroutines called by the program/trigger). This principle also applies to curdate (), Curtime (), Utc_date (), Utc_time (), Utc_timestamp (), and all functions that have the same meaning as them.

The Current_timestamp (), Current_time (), current_date (), and From_unixtime () functions return the values that are attached to the current time zone, which can be used as a value for Time_zone system variables. In addition, Unix_timestamp () assumes that the parameter is a time date value for the current time zone. See section 5.10.8, "MySQL Server time zone support."

The scope of the return value in the discussion of the following function will request a full date. A function that extracts part of a date value may return 0 if a date is a value of "0" or an incomplete date such as ' 2001-11-00 '. For example, DayOfMonth (' 2001-11-00 ') will return 0.

Adddate (Date,interval expr type) adddate (expr,days)
When activated by the interval format of the second parameter, adddate () is synonymous with date_add (). The correlation function subdate () is a synonym for Date_sub (). For information on the interval parameter, see the discussion about Date_add ().

mysql> SELECT date_add (' 1998-01-02 ', INTERVAL Day);

-> ' 1998-02-02 '

mysql> SELECT adddate (' 1998-01-02 ', INTERVAL Day);

-> ' 1998-02-02 '

If the days argument is only an integer value, MySQL 5.1 adds it as a day value to expr.

mysql> SELECT adddate (' 1998-01-02 ', 31);

-> ' 1998-02-02 '

Addtime (EXPR,EXPR2)
Addtime () adds expr2 to expr and then returns the result. Expr is a time or time-date expression, and expr2 is a time expression.

mysql> SELECT addtime (' 1997-12-31 23:59:59.999999 ',

-> ' 1 1:1:1.000002 ');

-> ' 1998-01-02 01:01:01.000001 '

mysql> SELECT addtime (' 01:00:00.999999 ', ' 02:00:00.999998 ');

-> ' 03:00:01.999997 '

Convert_tz (Dt,from_tz,to_tz)
Convert_tz () Moves the time date value DT from From_tz to the timezone given by To_tz and returns the resulting value. For a detailed description of the time zone that may be specified, see section 5.10.8, "MySQL Server time zone support." If the argument is invalid, the function returns NULL.

In the conversion from From_tz to UTC, the value is beyond the supported range of the TIMESTAMP type, then the conversion does not occur. For a discussion of the scope of TIMESTAMP, see section 11.1.2, "Overview of date and time types."

mysql> SELECT Convert_tz (' 2004-01-01 12:00:00 ', ' GMT ', ' MET ');

-> ' 2004-01-01 13:00:00 '

mysql> SELECT Convert_tz (' 2004-01-01 12:00:00 ', ' +00:00 ', ' +10:00 ');

-> ' 2004-01-01 22:00:00 '

Note: To use a specified time zone such as ' MET ' or ' Europe/moscow ', first set the correct time zone table. For more information, see section 5.10.8, "MySQL Server time zone support".

Curdate ()
Returns the current date according to the value in ' Yyyy-mm-dd ' or YYYYMMDD format, depending on the function used in a string or a numeric context.

Mysql> SELECT curdate ();

-> ' 1997-12-15 '

Mysql> SELECT curdate () + 0;

-> 19971215

Current_date current_date ()
Current_date and Current_date () are synonyms.

Curtime ()
Returns the current time in ' HH:MM:SS ' or HHMMSS format, depending on the function used in a string or in a digital context.

Mysql> SELECT curtime ();

-> ' 23:50:26 '

Mysql> SELECT curtime () + 0;

-> 235026

Current_time, Current_time ()
Current_time and Current_time () are synonyms for curtime ().

Current_timestamp, Current_timestamp ()
Current_timestamp and Current_timestamp () are synonyms for now ().

DATE (expr)
Extracts the date part of a date or time date expression in expr.

mysql> SELECT DATE (' 2003-12-31 01:02:03 ');

-> ' 2003-12-31 '

DATEDIFF (EXPR,EXPR2)
DATEDIFF () returns the number of days between the start time expr and the end time expr2. Expr and expr2 are dates or date-and-time expressions. The date part of the calculation that is used only for these values.

mysql> SELECT DATEDIFF (' 1997-12-31 23:59:59 ', ' 1997-12-30 ');

-> 1

mysql> SELECT DATEDIFF (' 1997-11-30 23:59:59 ', ' 1997-12-31 ');

->-31

Date_add (Date,interval expr type) date_sub (Date,interval expr type)
These functions perform date operations. Date is a DATETIME or date value that specifies the starting time.  Expr is an expression that specifies the value of a time interval that is added or subtracted from the start date. expr is a string; For a negative time interval, it can begin with a '-'. Type is the keyword, which indicates how the expression is interpreted.

Keyword Interva and type classifiers are case-insensitive.

The following table shows the relationship between the type and the expr parameters:

Type value


Expected format of expr





Microsecond


microseconds





SECOND


SECONDS





MINUTE


MINUTES





HOUR


HOURS





Day


Days





WEEK


WEEKS





MONTH


MONTHS





Quarter


Quarters





Year


YEARS





Second_microsecond


' SECONDS. Microseconds '





Minute_microsecond


' MINUTES. Microseconds '





Minute_second


' Minutes:seconds '





Hour_microsecond


' HOURS. Microseconds '





Hour_second


' HOURS:MINUTES:SECONDS '





Hour_minute


' Hours:minutes '





Day_microsecond


' Days. Microseconds '





Day_second


' Days HOURS:MINUTES:SECONDS '





Day_minute


' Days Hours:minutes '





Day_hour


' Days HOURS '





Year_month


' Years-months '

MySQL allows punctuation separators in any expr format. The suggested separator is shown in the table. If the date parameter is a date value and your calculation only includes the year, month, and day portions (that is, there is no time part), the result is a date value. Otherwise, the result will be a datetime value.

If the expression at the other end is a date or datetime value, the interval expr type is only allowed at both ends of the + operator. For the-operator, the INTERVAL expr type is only allowed at the right end because it is meaningless to extract a date or datetime value from one time interval. (see the example below).

Mysql> SELECT ' 1997-12-31 23:59:59 ' + INTERVAL 1 SECOND;

-> ' 1998-01-01 00:00:00 '

mysql> SELECT INTERVAL 1 day + ' 1997-12-31 ';

-> ' 1998-01-01 '

mysql> SELECT ' 1998-01-01 '-INTERVAL 1 SECOND;

-> ' 1997-12-31 23:59:59 '

mysql> SELECT date_add (' 1997-12-31 23:59:59 ',

-> INTERVAL 1 SECOND);

-> ' 1998-01-01 00:00:00 '

mysql> SELECT date_add (' 1997-12-31 23:59:59 ',

-> INTERVAL 1 day);

-> ' 1998-01-01 23:59:59 '

mysql> SELECT date_add (' 1997-12-31 23:59:59 ',

-> INTERVAL ' 1:1 ' minute_second);

-> ' 1998-01-01 00:01:00 '

mysql> SELECT date_sub (' 1998-01-01 00:00:00 ',

-> INTERVAL ' 1 1:1:1 ' Day_second);

-> ' 1997-12-30 22:58:59 '

mysql> SELECT date_add (' 1998-01-01 00:00:00 ',

-> INTERVAL '-1 ' day_hour);

-> ' 1997-12-30 14:00:00 '

mysql> SELECT date_sub (' 1998-01-02 ', INTERVAL Day);

-> ' 1997-12-02 '

mysql> SELECT date_add (' 1992-12-31 23:59:59.000002 ',

-> INTERVAL ' 1.999999 ' Second_microsecond);

-> ' 1993-01-01 00:00:01.000001 '

If you specify a time interval value that is too short (excluding all the interval parts expected by the type keyword), MySQL assumes that you have omitted the leftmost portion of the time interval value. For example, you specify a type of Day_second, and the value of expr is expected to have the day, hour, minute, and second portions. If you specify a value similar to ' 1:10 ', MySQL assumes that the day and hour portions do not exist, then this value represents minutes and seconds. In other words, ' 1:10 ' day_second is interpreted as equivalent to ' 1:10 ' Minute_second. This is equivalent to the way MySQL interprets a time value as a time-consuming rather than a day.

If you add or subtract some content that contains a time portion to a date value, the result is automatically converted to a date-time value:

mysql> SELECT date_add (' 1999-01-01 ', INTERVAL 1 day);

-> ' 1999-01-02 '

mysql> SELECT date_add (' 1999-01-01 ', INTERVAL 1 HOUR);

-> ' 1999-01-01 01:00:00 '

If you use a date with a badly formatted error, the result is NULL. If you add MONTH, Year_month, or year, and the date of the resulting date is greater than the date of the added month, this date is automatically adjusted to the maximum date of the month added:

mysql> SELECT date_add (' 1998-01-30 ', INTERVAL 1 MONTH);

-> ' 1998-02-28 '

Date_format (Date,format)
Arranges the format of date values based on the format string.

The following specifiers are available in the format string:

Specifiers


Description





%a


Abbreviated name for weekday (Sun ... Sat)





%b


The abbreviated name of the month. DEC)





%c


month, digital form (0..12)





%d


The month date with the English suffix (0th, 1st, 2nd, 3rd, ...)





%d


Date of the month, digital form (00..31)





%e


Date of the month, digital form (0..31)





%f


Microsecond (000000..999999)





%H


Hours (00..23)





%h


Hours (01..12)





%I


Hours (01..12)





%i


Minutes, digital form (00..59)





%j


Number of days in a year (001..366)





%k


Hours (0..23)





%l


Hours (1..12)





%m


Month name (January. December)





%m


month, digital form (00..12)





%p


A.M. (AM) or PM (PM)





%r


Time, 12-hour system (hours hh: minutes mm: Seconds of SS plus am or PM)





%s


Seconds (00..59)





%s


Seconds (00..59)





%T


Time, 24-hour system (hours hh: minutes mm: Number of seconds ss)





%u


Week (00..53), where Sunday is the first day of the week





%u


Week (00..53), where Monday is the first day of the week





%V


Week (01..53), of which Sunday was the first day of the week; Use at the same time as%x





%v


Week (01..53), of which Monday was the first day of the week; Use at the same time as%x





%w


Weekday name (Sunday. Saturday





%w


Daily of the week (0= Sunday. 6= Saturday)





%x


The year of the week, of which Sunday is the first day of the week, the number form, 4 digits; and%v use simultaneously.





%x


The year of the week, of which Monday is the first day of the week, the number form, 4 digits; and%v use simultaneously.





%Y


Year, digital form, 4 digits





%y


Year, number form (2 digits)





%%


'% ' literal characters

All other characters are copied to the result without explanation.

Note that the '% ' character is required before the format specifier.

The range of month and date specifiers starts at zero because MySQL allows you to store incomplete dates such as ' 2004-00-00 '.

mysql&gt; SELECT date_format (' 1997-10-04 22:23:00 ', '%w%m%Y ');&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;-&gt; ' Saturday October 1997 ' Mysql&gt; SELECT date_format (' 1997-10-04 22:23:00 ', '%h:%i:%s ');&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;-&gt; ' 22:23:00 ' mysql&gt; SELECT date_format (' 1997-10-04 22:23:00 ',&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp; '%d%y%a%d%m%b%j ');&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;-&gt; ' 4th the Sat Oct 277 ' mysql&gt; SELECT date_format (' 1997-10-04 22:23:00 ',&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; '%H%k%I%r%T%s%w ');&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;-&gt; ' 10:23:00 PM 22:23:00 6 ' mysql&gt; SELECT date_format (' 1999-01-01 ', '%x%V ');&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;-&gt; ' 1998 ' Day (date)


Day () and DayOfMonth () have the same meaning.

Dayname (date)
Returns the name of the weekday that corresponds to date.

mysql> SELECT dayname (' 1998-02-05 ');

-> ' Thursday '

DayOfMonth (date)
Returns the date of the month, from 1 to 31, corresponding to date.

mysql> SELECT dayofmonth (' 1998-02-03 ');

-> 3

DayOfWeek (date)
Returns date (1 = Sunday, 2 = Monday, ..., 7 = Saturday) the corresponding weekday index. These index values are compliant with ODBC standards.

mysql> SELECT DayOfWeek (' 1998-02-03 ');

-> 3

DayOfYear (date)
Returns the number of days in the year corresponding to date, ranging from 1 to 366.

mysql> SELECT dayofyear (' 1998-02-03 ');

-> 34

EXTRACT (type from date)
The EXTRACT () function uses the same interval type description Fu Tong date_add () or date_sub (), but it extracts its part from the date instead of performing a date operation.

Mysql> SELECT EXTRACT (year from ' 1999-07-02 ');

-> 1999

Mysql> SELECT EXTRACT (year_month from ' 1999-07-02 01:02:03 ');

-> 199907

Mysql> SELECT EXTRACT (day_minute from ' 1999-07-02 01:02:03 ');

-> 20102

Mysql> SELECT EXTRACT (microsecond

-> from ' 2003-01-02 10:30:00.00123 ');

-> 123

From_days (N)
Given a number of days N, returns a date value.

Mysql> SELECT from_days (729669);

-> ' 1997-10-07 '

Be careful when using from_days () to process old dates. He is not used to deal with the date before the Gregorian calendar (1582). See section 12.6, "What calendar does MySQL use?" ”。

From_unixtime (Unix_timestamp), From_unixtime (Unix_timestamp,format)
Returns the Unix_timestamp parameter of the ' Yyyy-mm-dd HH:MM:SS ' or YYYYMMDDHHMMSS format value, depending on whether the function is used in a string or in a digital context.

If format is already given, the format of the result depends on the format string. The format can contain the same descriptor as the Date_format () function entry list.

Mysql> SELECT from_unixtime (875996580);

-> ' 1997-10-04 22:23:00 '

Mysql> SELECT From_unixtime (875996580) + 0;

-> 19971004222300

Mysql> SELECT From_unixtime (Unix_timestamp (),

-> '%Y%d%m%h:%i:%s%x ');

-> ' 2003 6th August 06:22:58 2003 '

Get_format (date| time| DATETIME, ' EUR ' | ' USA ' | ' JIS ' | ' ISO ' | ' INTERNAL ')
Returns a format string. This function is useful when combined with date_format () and Str_to_date () functions.

The 3 possible values for the first parameter and 5 possible values for the second parameter produce 15 possible format strings (see the Date_format () function Description table for the descriptor used).

Function calls
  results
 
Get_format (date, ' USA ')
  '%m.%d.%y '
 
Get_format (date, ' JIS '
  '%y-%m-%d '
 
Get_format (date, ' ISO ')
  '%y-%m-%d '
 
Get_format (date, ' EUR ')
  '%d.%m.%y '
 
Get_format (DATE, ' INTERNAL ')
  '%y%m%d '
 
Get_format ( datetime, ' USA ')
  '%y-%m-%d-%h.%i.%s '
 
Get_format (datetime, ' JIS ')
  '%y-%m-%d%h:%i:% S '
 
Get_format (datetime, ' ISO ')
  '%y-%m-%d%h:%i:%s '
 
Get_format (datetime, ' EUR ')
  '%y-%m-%d-%h.%i.%s '
 
Get_format (DATETIME, ' INTERNAL ')
  '%y%m%d%h%i%s '
 
Get_format (Time, ' USA ')
  '%h:%i:%s%p '
 
Get_format (Time, ' JIS ')
  '%h:%i:%s '
&NBSP
Get_format (Time, ' ISO ')
  '%h:%i:%s '
 
Get_format (Time, ' EUR ')
  '%h.%i.%s '
 
Get_format (Time, ' INTERNAL ')
  '%h%i%s '
 

The ISO format is ISO 9075, not ISO 8601.

You can also use timestamp, when the return value of Get_format () is the same as DateTime.

mysql> SELECT date_format (' 2003-10-03 ', Get_format (DATE, ' EUR '));

-> ' 03.10.2003 '

mysql> SELECT str_to_date (' 10.31.2003 ', Get_format (DATE, ' USA '));

-> ' 2003-10-31 '

or see section 13.5.3, "set syntax."

HOUR (Time)
Returns the number of hours corresponding to the time. The range of return values for day values is from 0 to 23.

mysql> SELECT HOUR (' 10:05:03 ');

-> 10

However, the range of time values is actually very large, so hour can return a value greater than 23.

mysql> SELECT HOUR (' 272:59:59 ');

-> 272

Last_day (date)
Gets a date or datetime value that returns the corresponding value for the last day of the month. Returns null if the argument is invalid.

mysql> SELECT last_day (' 2003-02-05 ');

-> ' 2003-02-28 '

mysql> SELECT last_day (' 2004-02-05 ');

-> ' 2004-02-29 '

mysql> SELECT last_day (' 2004-01-01 01:01:01 ');

-> ' 2004-01-31 '

mysql> SELECT last_day (' 2003-03-32 ');

-> NULL

LocalTime, LocalTime ()
LocalTime and LocalTime () and now () have the same meaning.

Localtimestamp, Localtimestamp ()
Localtimestamp and Localtimestamp () and now () have the same meaning.

Makedate (Year,dayofyear)
Gives the year value and the number of days in the year, returning a date. The dayofyear must be greater than 0 or the result will be NULL.

Mysql> SELECT makedate (2001,31), makedate (2001,32);

-> ' 2001-01-31 ', ' 2001-02-01 '

Mysql> SELECT makedate (2001,365), makedate (2004,365);

-> ' 2001-12-31 ', ' 2004-12-30 '

Mysql> SELECT makedate (2001,0);

-> NULL

Maketime (Hour,minute,second)
Returns the time value computed by the hour, minute, and second parameters.

Mysql> SELECT maketime (12,15,30);

-> ' 12:15:30 '

Microsecond (expr)
Returns the Microsecond value from the time or datetime expression expr, with a range of digits ranging from 0 to 999999.

mysql> SELECT microsecond (' 12:00:00.123456 ');

-> 123456

mysql> SELECT microsecond (' 1997-12-31 23:59:59.000010 ');

-> 10

MINUTE (Time)
Returns the number of minutes corresponding to time, ranging from 0 to 59.

mysql> SELECT MINUTE (' 98-02-03 10:05:03 ');

-> 5

MONTH (date)
Returns the month corresponding to date, ranging from 1 to 12.

mysql> SELECT MONTH (' 1998-02-03 ');

-> 2

MonthName (date)
Returns the full name of the date corresponding to the month.

mysql> SELECT monthname (' 1998-02-05 ');

-> ' February '

Now ()
Returns the current date and time value, in the form of ' yyyy-mm-dd HH:MM:SS ' or YYYYMMDDHHMMSS, depending on whether the function is used in a string or in a numeric context.

Mysql> SELECT now ();

-> ' 1997-12-15 23:50:26 '

Mysql> SELECT Now () + 0;

-> 19971215235026

Within a stored program or trigger, now () returns a constant time that indicates when the program or trigger statement starts executing. This differs from the operation of Sysdate ().

Period_add (P,n)
Add N months to cycle p (format yymm or yyyymm), and the return value is in the form of yyyymm. Note that the cycle parameter P is not a date value.

Mysql> SELECT Period_add (9801,2);

-> 199803

Period_diff (P1,P2)
Returns the number of months between the cycle P1 and P2. The formats for P1 and P2 should be yymm or yyyymm. Note that the periodic parameters P1 and P2 are not date values.

Mysql> SELECT Period_diff (9802,199703);

-> 11

Quarter (date)
Returns the quarterly value in the year corresponding to date, ranging from 1 to 4.

Mysql> SELECT Quarter (' 98-04-01 ');

-> 2

SECOND (Time)
Returns the number of seconds for time, ranging from 0 to 59.

mysql> SELECT SECOND (' 10:05:03 ');

-> 3

Sec_to_time (seconds)
Returns the value of the seconds parameter converted to hours, minutes, and seconds, in the form of ' HH:MM:SS ' or HHMMSS, depending on whether the function is used in a string or a numeric context.

Mysql> SELECT sec_to_time (2378);

-> ' 00:39:38 '

Mysql> SELECT Sec_to_time (2378) + 0;

-> 3938

Str_to_date (Str,format)
This is the reversal of the Date_format () function. It gets a string of str and a format string formatted. If the format string contains a date and time part, then Str_to_date () returns a DATETIME value that returns a date or time value if the string contains only a part of the day or a period.

The date, time, or date-time value that STR contains should be given in the format indicated by the form. For specifiers that are available in FORMAT, see the Date_format () function Description table. All other characters are acquired verbatim, so they are not interpreted. If Str contains an illegal date, time, or date-time value, str_to_date () returns NULL. At the same time, an illegal value can cause a warning.

The scope of the date-value section is checked in sections 11.3.1, and "DATETIME, date, and timestamp types" are described in detail. The implication is that, for example, a specific date part of a date is greater than the number of days in one months, as long as the range of the specific date part ranges from 1 to 31. Also, allow "0" dates or dates with 0-valued portions.

mysql> SELECT str_to_date (' 00/00/0000 ', '%m/%d/%y ');

-> ' 0000-00-00 '

mysql> SELECT str_to_date (' 04/31/2004 ', '%m/%d/%y ');

-> ' 2004-04-31 '

Subdate (Date,interval expr type) subdate (expr,days)
When called by the interval type of the second argument, the meaning of Subdate () and Date_sub () is the same. For information about the interval parameters, see the discussion of Date_add ().

mysql> SELECT date_sub (' 1998-01-02 ', INTERVAL Day);

-> ' 1997-12-02 '

mysql> SELECT subdate (' 1998-01-02 ', INTERVAL Day);

-> ' 1997-12-02 '

The second form allows integer values to be used for days. In these cases, it is counted as the number of days extracted by the date or date-time expression expr.

mysql> SELECT subdate (' 1998-01-02 12:00:00 ', 31);

-> ' 1997-12-02 12:00:00 '

Note You cannot use the format "%x%v" to convert a Year-week string to a date because the combination of one year and week does not mark a unique year and month when one weeks span a month boundary. To convert Year-week to a date, you should also specify a specific weekday:

Mysql> Select Str_to_date (' 200442 Monday ', '%x%v%w ');

-> 2004-10-18

Subtime (EXPR,EXPR2)
Subtime () extracts the expr2 from expr and returns the result. Expr is a time or date-time expression, and xpr2 is a time expression.

mysql> SELECT subtime (' 1997-12-31 23:59:59.999999 ', ' 1 1:1:1.000002 ');

-> ' 1997-12-30 22:58:58.999997 '

mysql> SELECT subtime (' 01:00:00.999999 ', ' 02:00:00.999998 ');

-> ' -00:59:59.999999 '

Sysdate ()
Returns the current date and time value, in the form ' Yyyy-mm-dd HH:MM:SS ' or YYYYMMDDHHMMSS, depending on whether the function is used in a string or a digital context.

In a stored program or trigger, Sysdate () returns the time it was executed, rather than the time when the Chengdu or triggering statement started executing. This now () works differently.

Time (expr)
Extracts the time part of a time or date-time expression and returns it as a string.

Mysql> SELECT time (' 2003-12-31 01:02:03 ');

-> ' 01:02:03 '

Mysql> SELECT time (' 2003-12-31 01:02:03.000123 ');

-> ' 01:02:03.000123 '

Timediff (EXPR,EXPR2)
Timediff () returns the time between the start time expr and the end time expr2. Expr and expr2 are time or date-and-time expressions, and two types must be the same.

mysql> SELECT Timediff (' 2000:01:01 00:00:00 '),

-> ' 2000:01:01 00:00:00.000001 ');

-> ' -00:00:00.000001 '

mysql> SELECT Timediff (' 1997-12-31 23:59:59.000001 ',

-> ' 1997-12-30 01:01:01.000002 ');

-> ' 46:58:57.999999 '

TIMESTAMP (expr), TIMESTAMP (EXPR,EXPR2)
For a single argument, the function returns the date or datetime expression expr as a DateTime value. For two parameters, it adds the time expression expr2 to the date or datetime expression expr, and returns the Theresult as a datetime value.

mysql> SELECT TIMESTAMP (' 2003-12-31 ');

-> ' 2003-12-31 00:00:00 '

mysql> SELECT TIMESTAMP (' 2003-12-31 12:00:00 ', ' 12:00:00 ');

-> ' 2004-01-01 00:00:00 '

Timestampadd (interval,int_expr,datetime_expr)
Adds an integral expression int_expr to a date or datetime expression datetime_expr. The int_expr unit is given by the interval parameter, which must be one of the following values: Frac_second, SECOND, MINUTE, HOUR, Day, WEEK, MONTH, Quarter, or year.

You can use the keyword displayed to specify the interval value, or use the sql_tsi_ prefix. For example, day or Sql_tsi_day are correct.

Mysql> SELECT Timestampadd (minute,1, ' 2003-01-02 ');

-> ' 2003-01-02 00:01:00 '

Mysql> SELECT Timestampadd (week,1, ' 2003-01-02 ');

-> ' 2003-01-09 '

Timestampdiff (INTERVAL,DATETIME_EXPR1,DATETIME_EXPR2)
Returns the integer difference between a date or datetime expression Datetime_expr1 and datetime_expr2the. The unit of the result is given by the interval parameter. The legal value of the interval is the same as those listed in the Timestampadd () function description.

Mysql> SELECT Timestampdiff (MONTH, ' 2003-02-01 ', ' 2003-05-01 ');

-> 3

Mysql> SELECT Timestampdiff (year, ' 2002-05-01 ', ' 2001-01-01 ');

->-1

Time_format (Time,format)
It uses the same as the Date_format () function, whereas the format string may contain only format specifiers that handle hours, minutes, and seconds. Other descriptors produce a null value or 0.

If time value contains an hour portion greater than 23, the%H and%k hour format specifiers produce a greater than 0. The usual range value of 23. Another hour format specifier produces a small value of 12 modulus.

mysql> SELECT time_format (' 100:00:00 ', '%H%k%H '%I%l ');

-> ' 100 100 04 04 4 '

Time_to_sec (Time)
Returns a time parameter that has been converted to seconds.

mysql> SELECT time_to_sec (' 22:23:00 ');

-> 80580

mysql> SELECT time_to_sec (' 00:39:38 ');

-> 2378

To_days (date)
Returns a number of days, starting from year 0, given a date.

Mysql> SELECT to_days (950501);

-> 728779

mysql> SELECT to_days (' 1997-10-07 ');

-> 729669

To_days () is not used for the value before the Gregorian calendar (1582), because the lost date is not taken into account when the calendars change. See section 12.6, "What calendar does MySQL use?" ”。

Keep in mind that MySQL uses section 11.3, and the rule in date and time type converts the two-digit year value in the date to four bits. For example, ' 1997-10-07 ' and ' 97-10-07 ' are considered the same date:

mysql> SELECT to_days (' 1997-10-07 '), To_days (' 97-10-07 ');

-> 729669, 729669

The results of this function are not reliable for dates prior to 1582 (perhaps in other regions for the next year). See section 12.6, "What calendar does MySQL use?" ” 。

Unix_timestamp (), Unix_timestamp (date)
If no parameters are invoked, a UNIX timestamp (the number of seconds after the ' 1970-01-01 00:00:00 ' GMT) is returned as an unsigned integer. If you call Unix_timestamp () with date, it returns the value of the parameter as the number of seconds after the ' 1970-01-01 00:00:00 ' GMT. Date can be a date string, a DateTime string, a timestamp, or a number in the YYMMDD or YYYMMDD format of a local time.

Mysql> SELECT Unix_timestamp ();

-> 882226357

mysql> SELECT unix_timestamp (' 1997-10-04 22:23:00 ');

-> 875996580

When Unix_timestamp is used in the timestamp column, the function returns the internal timestamp value directly without any implied "string-to-unix-timestamp" conversion. If you pass an overflow date to Unix_timestamp (), it will return 0, but be aware that only the basic scope checks will be performed (the year from 1970 to 2037, the month from 01 to 12, and the date from 01 to 31).

If you want to subtract the Unix_timestamp () column, you may want to delete the result of the signed integer. See section 12.8, "Cast functions and operators".

Utc_date, Utc_date ()
Returns the current UTC date value in the format ' yyyy-mm-dd ' or YYYYMMDD, depending on whether the function is used in a string or a numeric context.

Mysql> SELECT utc_date (), utc_date () + 0;

-> ' 2003-08-14 ', 20030814

Utc_time, Utc_time ()
Returns the current UTC value, in the format ' HH:MM:SS ' or HHMMSS, depending on whether the function is used in a string or a numeric context.

Mysql> SELECT utc_time (), utc_time () + 0;

-> ' 18:07:53 ', 180753

Utc_timestamp, Utc_timestamp ()
Returns the current UTC date and time value, in the form ' Yyyy-mm-dd HH:MM:SS ' or YYYYMMDDHHMMSS, depending on whether the function is used in a string or a numeric context.

Mysql> SELECT Utc_timestamp (), Utc_timestamp () + 0;

-> ' 2003-08-14 18:08:04 ', 20030814180804

WEEK (Date[,mode])
This function returns the number of weeks that date corresponds to. The two-Parameter form of WEEK () allows you to specify whether the week starts in Sunday or Monday, and whether the return value ranges from 0 to 53 or from 1 to 53. If the mode parameter is omitted, the value of the Default_week_format system argument is used. See section 5.3.3, "Server System variables."

The following table illustrates the working process of the mode parameter: D

First day



Mode
Days
Range
Week 1 is the first week ...

0
Sunday
0-53
There is a Sunday in the year

1
Monday
0-53
More than 3 days in the year

2
Sunday
1-53
There is a Sunday in the year

3
Monday
1-53
More than 3 days in the year

4
Sunday
0-53
More than 3 days in the year

5
Monday
0-53
There is a Monday in the year

6
Sunday
1-53
More than 3 days in the year

7
Monday
1-53
There is a Monday in the year

mysql> SELECT WEEK (' 1998-02-20 ');

-> 7

mysql> SELECT WEEK (' 1998-02-20 ', 0);

-> 7

mysql> SELECT WEEK (' 1998-02-20 ', 1);

-> 8

mysql> SELECT WEEK (' 1998-12-31 ', 1);

-> 53

Note that if you have a date in the last week of the previous year, if you do not use 2, 3, 6, or 7 as the mode parameter, MySQL returns 0:

mysql> SELECT year (' 2000-01-01 '), WEEK (' 2000-01-01 ', 0);

-> 2000, 0

One might argue that MySQL should return 52 for the week () function because the given date actually occurs in the 52nd week of 1999. The reason we decided to return 0 instead was because we wanted the function to return the number of weeks in a given year. This makes the week () function more reliable when combined with other functions that extract date parts from dates.

If you prefer the calculated results for the year include the first day of the week for the given date, you should use 0, 2, 5, or 7 as the mode parameter selection.

mysql> SELECT WEEK (' 2000-01-01 ', 2);

-> 52

As a selection, you can use the Yearweek () function:

mysql> SELECT yearweek (' 2000-01-01 ');

-> 199952

Mysql> SELECT MID (Yearweek (' 2000-01-01 '), 5,2);

-> ' 52 '

Weekday (date)
Returns date (0 = Monday, 1 = Tuesday, ...). 6 = Sunday) corresponding weekday index weekday index for

Mysql> SELECT Weekday (' 1998-02-03 22:23:00 ');

-> 1

Mysql> SELECT Weekday (' 1997-11-05 ');

-> 2

WeekOfYear (date)
The Gregorian week of the date is returned as a number, ranging from 1 to 53. It is a compatibility function, equivalent to week (date,3).

mysql> SELECT weekofyear (' 1998-02-20 ');

-> 8

Year (date)
Returns the year corresponding to date, ranging from 1000 to 9999.

mysql> SELECT year (' 98-02-03 ');

-> 1998

Yearweek (date), Yearweek (Date,start)
Returns the year or week that corresponds to a date. The start parameter works the same as the start parameter for WEEK (). The year in the result can differ from the date parameters for the first and last weeks of the year.

mysql> SELECT yearweek (' 1987-01-01 ');

-> 198653

Note that the number of weeks and WEEK () function team Optional Parameters 0 or 1 may be returned (0) W is different, since WEEK () returns the week in the context of the given year.

-> ' 1997-10-07 '