NOW([ fsp ]) Returns the current date and time as a value in ' YYYY-MM-DD hh:mm:ss ' or YYYYMMDDhhmmss format, depending on whether the function is used in string or numeric context. The value is expressed in the session time zone. If the fsp argument is given to specify a fractional seconds precision from 0 to 6, the return value includes a fractional seconds part of that many digits.

mysql> SELECT NOW();
        -> '2007-12-15 23:50:26'
mysql> SELECT NOW() + 0;
        -> 20071215235026.000000

NOW() returns a constant time that indicates the time at which the statement began to execute. (Within a stored function or trigger, NOW() returns the time at which the function or triggering statement began to execute.) This differs from the behavior for SYSDATE() , which returns the exact time at which it executes.

mysql> SELECT NOW(), SLEEP(2), NOW();
+---------------------+----------+---------------------+
| NOW()               | SLEEP(2) | NOW()               |
+---------------------+----------+---------------------+
| 2006-04-12 13:47:36 |        0 | 2006-04-12 13:47:36 |
+---------------------+----------+---------------------+
mysql> SELECT SYSDATE(), SLEEP(2), SYSDATE();
+---------------------+----------+---------------------+
| SYSDATE()           | SLEEP(2) | SYSDATE()           |
+---------------------+----------+---------------------+
| 2006-04-12 13:47:44 |        0 | 2006-04-12 13:47:46 |
+---------------------+----------+---------------------+

In addition, the SET TIMESTAMP statement affects the value returned by NOW() but not by SYSDATE() . This means that timestamp settings in the binary log have no effect on invocations of SYSDATE() . Setting the timestamp to a nonzero value causes each subsequent invocation of NOW() to return that value. Setting the timestamp to zero cancels this effect so that NOW() once again returns the current date and time. See the description for SYSDATE() for additional information about the differences between the two functions. PERIOD_ADD( P , N ) Adds N months to period P (in the format YYMM or YYYYMM ). Returns a value in the format YYYYMM . Returns the seconds argument, converted to hours, minutes, and seconds, as a TIME value. The range of the result is constrained to that of the TIME data type. A warning occurs if the argument corresponds to a value outside that range. The function returns NULL if seconds is NULL .

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 inverse of the DATE_FORMAT() function. It takes a string str and a format string format . STR_TO_DATE() returns a DATETIME value if the format string contains both date and time parts, or a DATE or TIME value if the string contains only date or time parts. If str or format is NULL , the function returns NULL . If the date, time, or datetime value extracted from str cannot be parsed according to the rules followed by the server, STR_TO_DATE() returns NULL and produces a warning. The server scans str attempting to match format to it. The format string can contain literal characters and format specifiers beginning with % . Literal characters in format must match literally in str . Format specifiers in format must match a date or time part in str . For the specifiers that can be used in format , see the DATE_FORMAT() function description.

mysql> SELECT STR_TO_DATE('01,5,2013','%d,%m,%Y');
        -> '2013-05-01'
mysql> SELECT STR_TO_DATE('May 1, 2013','%M %d,%Y');
        -> '2013-05-01'

Scanning starts at the beginning of str and fails if format is found not to match. Extra characters at the end of str are ignored.

mysql> SELECT STR_TO_DATE('a09:30:17','a%h:%i:%s');
        -> '09:30:17'
mysql> SELECT STR_TO_DATE('a09:30:17','%h:%i:%s');
        -> NULL
mysql> SELECT STR_TO_DATE('09:30:17a','%h:%i:%s');
        -> '09:30:17'

Unspecified date or time parts have a value of 0, so incompletely specified values in str produce a result with some or all parts set to 0:

mysql> SELECT STR_TO_DATE('abc','abc');
        -> '0000-00-00'
mysql> SELECT STR_TO_DATE('9','%m');
        -> '0000-09-00'
mysql> SELECT STR_TO_DATE('9','%s');
        -> '00:00:09'

Range checking on the parts of date values is as described in Section 13.2.2, “The DATE, DATETIME, and TIMESTAMP Types” . This means, for example, that “ zero ” dates or dates with part values of 0 are permitted unless the SQL mode is set to disallow such values.

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'

If the NO_ZERO_DATE SQL mode is enabled, zero dates are disallowed. In that case, STR_TO_DATE() returns NULL and generates a warning:

mysql> SET sql_mode = '';
mysql> SELECT STR_TO_DATE('00/00/0000', '%m/%d/%Y');
+---------------------------------------+
| STR_TO_DATE('00/00/0000', '%m/%d/%Y') |
+---------------------------------------+
| 0000-00-00                            |
+---------------------------------------+
mysql> SET sql_mode = 'NO_ZERO_DATE';
mysql> SELECT STR_TO_DATE('00/00/0000', '%m/%d/%Y');
+---------------------------------------+
| STR_TO_DATE('00/00/0000', '%m/%d/%Y') |
+---------------------------------------+
| NULL                                  |
+---------------------------------------+
mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1411
Message: Incorrect datetime value: '00/00/0000' for function str_to_date

In some previous versions of MySQL, it was possible to pass an invalid date string such as '2021-11-31' to this function. In MySQL 8.4, STR_TO_DATE() performs complete range checking and raises an error if the date after conversion would be invalid. You cannot use format "%X%V" to convert a year-week string to a date because the combination of a year and week does not uniquely identify a year and month if the week crosses a month boundary. To convert a year-week to a date, you should also specify the weekday:

mysql> SELECT STR_TO_DATE('200442 Monday', '%X%V %W');
        -> '2004-10-18'

mysql> SELECT DATE_SUB('2008-01-02', INTERVAL 31 DAY);
        -> '2007-12-02'
mysql> SELECT SUBDATE('2008-01-02', INTERVAL 31 DAY);
        -> '2007-12-02'

The second form enables the use of an integer value for days . In such cases, it is interpreted as the number of days to be subtracted from the date or datetime expression expr .

mysql> SELECT SUBDATE('2008-01-02 12:00:00', 31);
        -> '2007-12-02 12:00:00'

This function returns NULL if any of its arguments are NULL . SUBTIME( expr1 , expr2 ) SUBTIME() returns expr1 − expr2 expressed as a value in the same format as expr1 . expr1 is a time or datetime expression, and expr2 is a time expression. Resolution of this function's return type is performed as it is for the ADDTIME() function; see the description of that function for more information.

mysql> SELECT SUBTIME('2007-12-31 23:59:59.999999','1 1:1:1.000002');
        -> '2007-12-30 22:58:58.999997'
mysql> SELECT SUBTIME('01:00:00.999999', '02:00:00.999998');
        -> '-00:59:59.999999'

This function returns NULL if expr1 or expr2 is NULL . SYSDATE([ fsp ]) Returns the current date and time as a value in ' YYYY-MM-DD hh:mm:ss ' or YYYYMMDDhhmmss format, depending on whether the function is used in string or numeric context. If the fsp argument is given to specify a fractional seconds precision from 0 to 6, the return value includes a fractional seconds part of that many digits. SYSDATE() returns the time at which it executes. This differs from the behavior for NOW() , which returns a constant time that indicates the time at which the statement began to execute. (Within a stored function or trigger, NOW() returns the time at which the function or triggering statement began to execute.)

mysql> SELECT NOW(), SLEEP(2), NOW();
+---------------------+----------+---------------------+
| NOW()               | SLEEP(2) | NOW()               |
+---------------------+----------+---------------------+
| 2006-04-12 13:47:36 |        0 | 2006-04-12 13:47:36 |
+---------------------+----------+---------------------+
mysql> SELECT SYSDATE(), SLEEP(2), SYSDATE();
+---------------------+----------+---------------------+
| SYSDATE()           | SLEEP(2) | SYSDATE()           |
+---------------------+----------+---------------------+
| 2006-04-12 13:47:44 |        0 | 2006-04-12 13:47:46 |
+---------------------+----------+---------------------+

In addition, the SET TIMESTAMP statement affects the value returned by NOW() but not by SYSDATE() . This means that timestamp settings in the binary log have no effect on invocations of SYSDATE() . Because SYSDATE() can return different values even within the same statement, and is not affected by SET TIMESTAMP , it is nondeterministic and therefore unsafe for replication if statement-based binary logging is used. If that is a problem, you can use row-based logging. Alternatively, you can use the --sysdate-is-now option to cause SYSDATE() to be an alias for NOW() . This works if the option is used on both the replication source server and the replica. The nondeterministic nature of SYSDATE() also means that indexes cannot be used for evaluating expressions that refer to it. TIME( expr ) Extracts the time part of the time or datetime expression expr and returns it as a string. Returns NULL if expr is NULL . This function is unsafe for statement-based replication. A warning is logged if you use this function when binlog_format is set to STATEMENT .

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( expr1 , expr2 ) TIMEDIFF() returns expr1 − expr2 expressed as a time value. expr1 and expr2 are strings which are converted to TIME or DATETIME expressions; these must be of the same type following conversion. Returns NULL if expr1 or expr2 is NULL . The result returned by TIMEDIFF() is limited to the range allowed for TIME values. Alternatively, you can use either of the functions TIMESTAMPDIFF() and UNIX_TIMESTAMP() , both of which return integers.

mysql> SELECT TIMEDIFF('2000-01-01 00:00:00',
    ->                 '2000-01-01 00:00:00.000001');
        -> '-00:00:00.000001'
mysql> SELECT TIMEDIFF('2008-12-31 23:59:59.000001',
    ->                 '2008-12-30 01:01:01.000002');
        -> '46:58:57.999999'

TIMESTAMP( expr ) , TIMESTAMP( expr1 , expr2 ) With a single argument, this function returns the date or datetime expression expr as a datetime value. With two arguments, it adds the time expression expr2 to the date or datetime expression expr1 and returns the result as a datetime value. Returns NULL if expr , expr1 , or expr2 is NULL .

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( unit , interval , datetime_expr ) Adds the integer expression interval to the date or datetime expression datetime_expr . The unit for interval is given by the unit argument, which should be one of the following values: MICROSECOND (microseconds), SECOND , MINUTE , HOUR , DAY , WEEK , MONTH , QUARTER , or YEAR . The unit value may be specified using one of keywords as shown, or with a prefix of SQL_TSI_ . For example, DAY and SQL_TSI_DAY both are legal. This function returns NULL if interval or datetime_expr is NULL .

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'

When adding a MONTH interval to a DATE or DATETIME value, and the resulting date includes a day that does not exist in the given month, the day is adjusted to the last day of the month, as shown here:

mysql> SELECT TIMESTAMPADD(MONTH, 1, DATE '2024-03-30') AS t1, 
     >        TIMESTAMPADD(MONTH, 1, DATE '2024-03-31') AS t2;
+------------+------------+
| t1         | t2         |
+------------+------------+
| 2024-04-30 | 2024-04-30 |
+------------+------------+
1 row in set (0.00 sec)

TIMESTAMPDIFF( unit , datetime_expr1 , datetime_expr2 ) Returns datetime_expr2 − datetime_expr1 , where datetime_expr1 and datetime_expr2 are date or datetime expressions. One expression may be a date and the other a datetime; a date value is treated as a datetime having the time part '00:00:00' where necessary. The unit for the result (an integer) is given by the unit argument. The legal values for unit are the same as those listed in the description of the TIMESTAMPADD() function. This function returns NULL if datetime_expr1 or datetime_expr2 is NULL .

mysql> SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');
mysql> SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01');
mysql> SELECT TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55');
        -> 128885

mysql> SELECT TIME_FORMAT('100:00:00', '%H %k %h %I %l');
        -> '100 100 04 04 4'

TIME_TO_SEC( time ) Returns the time argument, converted to seconds. Returns NULL if time is NULL .

mysql> SELECT TIME_TO_SEC('22:23:00');
        -> 80580
mysql> SELECT TIME_TO_SEC('00:39:38');
        -> 2378

TO_DAYS( date ) Given a date date , returns a day number (the number of days since year 0). Returns NULL if date is NULL .

mysql> SELECT TO_DAYS(950501);
        -> 728779
mysql> SELECT TO_DAYS('2007-10-07');
        -> 733321

TO_DAYS() is not intended for use with values that precede the advent of the Gregorian calendar (1582), because it does not take into account the days that were lost when the calendar was changed. For dates before 1582 (and possibly a later year in other locales), results from this function are not reliable. See Section 13.2.7, “What Calendar Is Used By MySQL?” , for details. Remember that MySQL converts two-digit year values in dates to four-digit form using the rules in Section 13.2, “Date and Time Data Types” . For example, '2008-10-07' and '08-10-07' are seen as identical dates:

mysql> SELECT TO_DAYS('2008-10-07'), TO_DAYS('08-10-07');
        -> 733687, 733687

In MySQL, the zero date is defined as '0000-00-00' , even though this date is itself considered invalid. This means that, for '0000-00-00' and '0000-01-01' , TO_DAYS() returns the values shown here:

mysql> SELECT TO_DAYS('0000-00-00');
+-----------------------+
| to_days('0000-00-00') |
+-----------------------+
|                  NULL |
+-----------------------+
1 row in set, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Incorrect datetime value: '0000-00-00' |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT TO_DAYS('0000-01-01');
+-----------------------+
| to_days('0000-01-01') |
+-----------------------+
|                     1 |
+-----------------------+
1 row in set (0.00 sec)

This is true whether or not the ALLOW_INVALID_DATES SQL server mode is enabled. TO_SECONDS( expr ) Given a date or datetime expr , returns the number of seconds since the year 0. If expr is not a valid date or datetime value (including NULL ), it returns NULL .

mysql> SELECT TO_SECONDS(950501);
        -> 62966505600
mysql> SELECT TO_SECONDS('2009-11-29');
        -> 63426672000
mysql> SELECT TO_SECONDS('2009-11-29 13:43:32');
        -> 63426721412
mysql> SELECT TO_SECONDS( NOW() );
        -> 63426721458

Like TO_DAYS() , TO_SECONDS() is not intended for use with values that precede the advent of the Gregorian calendar (1582), because it does not take into account the days that were lost when the calendar was changed. For dates before 1582 (and possibly a later year in other locales), results from this function are not reliable. See Section 13.2.7, “What Calendar Is Used By MySQL?” , for details. Like TO_DAYS() , TO_SECONDS() , converts two-digit year values in dates to four-digit form using the rules in Section 13.2, “Date and Time Data Types” . In MySQL, the zero date is defined as '0000-00-00' , even though this date is itself considered invalid. This means that, for '0000-00-00' and '0000-01-01' , TO_SECONDS() returns the values shown here:

mysql> SELECT TO_SECONDS('0000-00-00');
+--------------------------+
| TO_SECONDS('0000-00-00') |
+--------------------------+
|                     NULL |
+--------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Incorrect datetime value: '0000-00-00' |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT TO_SECONDS('0000-01-01');
+--------------------------+
| TO_SECONDS('0000-01-01') |
+--------------------------+
|                    86400 |
+--------------------------+
1 row in set (0.00 sec)

This is true whether or not the ALLOW_INVALID_DATES SQL server mode is enabled. UNIX_TIMESTAMP([ date ]) If UNIX_TIMESTAMP() is called with no date argument, it returns a Unix timestamp representing seconds since '1970-01-01 00:00:00' UTC. If UNIX_TIMESTAMP() is called with a date argument, it returns the value of the argument as seconds since '1970-01-01 00:00:00' UTC. The server interprets date as a value in the session time zone and converts it to an internal Unix timestamp value in UTC. (Clients can set the session time zone as described in Section 7.1.15, “MySQL Server Time Zone Support” .) The date argument may be a DATE , DATETIME , or TIMESTAMP string, or a number in YYMMDD , YYMMDDhhmmss , YYYYMMDD , or YYYYMMDDhhmmss format. If the argument includes a time part, it may optionally include a fractional seconds part. The return value is an integer if no argument is given or the argument does not include a fractional seconds part, or DECIMAL if an argument is given that includes a fractional seconds part. When the date argument is a TIMESTAMP column, UNIX_TIMESTAMP() returns the internal timestamp value directly, with no implicit “ string-to-Unix-timestamp ” conversion. The valid range of argument values is the same as for the TIMESTAMP data type: '1970-01-01 00:00:01.000000' UTC to '2038-01-19 03:14:07.999999' UTC for 32-bit platforms; for MySQL running on 64-bit platforms, the valid range of argument values for UNIX_TIMESTAMP() is '1970-01-01 00:00:01.000000' UTC to '3001-01-19 03:14:07.999999' UTC (corresponding to 32536771199.999999 seconds). Regardless of MySQL version or platform architecture, if you pass an out-of-range date to UNIX_TIMESTAMP() , it returns 0 . If date is NULL , it returns NULL .

mysql> SELECT UNIX_TIMESTAMP();
        -> 1447431666
mysql> SELECT UNIX_TIMESTAMP('2015-11-13 10:20:19');
        -> 1447431619
mysql> SELECT UNIX_TIMESTAMP('2015-11-13 10:20:19.012');
        -> 1447431619.012

If you use UNIX_TIMESTAMP() and FROM_UNIXTIME() to convert between values in a non-UTC time zone and Unix timestamp values, the conversion is lossy because the mapping is not one-to-one in both directions. For example, due to conventions for local time zone changes such as Daylight Saving Time (DST), it is possible for UNIX_TIMESTAMP() to map two values that are distinct in a non-UTC time zone to the same Unix timestamp value. FROM_UNIXTIME() maps that value back to only one of the original values. Here is an example, using values that are distinct in the MET time zone:

mysql> SET time_zone = 'MET';
mysql> SELECT UNIX_TIMESTAMP('2005-03-27 03:00:00');
+---------------------------------------+
| UNIX_TIMESTAMP('2005-03-27 03:00:00') |
+---------------------------------------+
|                            1111885200 |
+---------------------------------------+
mysql> SELECT UNIX_TIMESTAMP('2005-03-27 02:00:00');
+---------------------------------------+
| UNIX_TIMESTAMP('2005-03-27 02:00:00') |
+---------------------------------------+
|                            1111885200 |
+---------------------------------------+
mysql> SELECT FROM_UNIXTIME(1111885200);
+---------------------------+
| FROM_UNIXTIME(1111885200) |
+---------------------------+
| 2005-03-27 03:00:00       |
+---------------------------+

mysql> SELECT UTC_DATE(), UTC_DATE() + 0;
        -> '2003-08-14', 20030814

UTC_TIME , UTC_TIME([ fsp ]) Returns the current UTC time as a value in 'hh:mm:ss' or hhmmss format, depending on whether the function is used in string or numeric context. If the fsp argument is given to specify a fractional seconds precision from 0 to 6, the return value includes a fractional seconds part of that many digits.

mysql> SELECT UTC_TIME(), UTC_TIME() + 0;
        -> '18:07:53', 180753.000000

UTC_TIMESTAMP , UTC_TIMESTAMP([ fsp ]) Returns the current UTC date and time as a value in ' YYYY-MM-DD hh:mm:ss ' or YYYYMMDDhhmmss format, depending on whether the function is used in string or numeric context. If the fsp argument is given to specify a fractional seconds precision from 0 to 6, the return value includes a fractional seconds part of that many digits.

mysql> SELECT UTC_TIMESTAMP(), UTC_TIMESTAMP() + 0;
        -> '2003-08-14 18:08:04', 20030814180804.000000

WEEK( date [, mode ]) This function returns the week number for date . The two-argument form of WEEK() enables you to specify whether the week starts on Sunday or Monday and whether the return value should be in the range from 0 to 53 or from 1 to 53 . If the mode argument is omitted, the value of the default_week_format system variable is used. See Section 7.1.8, “Server System Variables” . For a NULL date value, the function returns NULL . The following table describes how the mode argument works. First day of week Range Week 1 is the first week … Sunday with a Sunday in this year Monday with 4 or more days this year Sunday with a Sunday in this year Monday with 4 or more days this year Sunday with 4 or more days this year Monday with a Monday in this year Sunday with 4 or more days this year Monday with a Monday in this year For mode values with a meaning of “ with 4 or more days this year, ” weeks are numbered according to ISO 8601:1988: If the week containing January 1 has 4 or more days in the new year, it is week 1. Otherwise, it is the last week of the previous year, and the next week is week 1.

mysql> SELECT WEEK('2008-02-20');
mysql> SELECT WEEK('2008-02-20',0);
mysql> SELECT WEEK('2008-02-20',1);
mysql> SELECT WEEK('2008-12-31',1);
          If a date falls in the last week of the previous year, MySQL
          returns 0 if you do not use
          2, 3,
          6, or 7 as the optional
          mode argument:
        
mysql> SELECT YEAR('2000-01-01'), WEEK('2000-01-01',0);
        -> 2000, 0

          One might argue that WEEK()
          should return 52 because the given date
          actually occurs in the 52nd week of 1999.
          WEEK() returns
          0 instead so that the return value is
          “the week number in the given year.” This makes
          use of the WEEK() function
          reliable when combined with other functions that extract a
          date part from a date.
          If you prefer a result evaluated with respect to the year that
          contains the first day of the week for the given date, use
          0, 2,
          5, or 7 as the optional
          mode argument.
        
mysql> SELECT WEEK('2000-01-01',2);
          Alternatively, use the
          YEARWEEK() function:
        
mysql> SELECT YEARWEEK('2000-01-01');
        -> 199952
mysql> SELECT MID(YEARWEEK('2000-01-01'),5,2);
        -> '52'