Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Learn about date and time functions in MariaDB Server. This section details SQL functions for manipulating, formatting, and calculating with date and time values for various applications.
This page is licensed: GPLv2, originally from fill_help_tables.sql
ADDTIME(expr1,expr2)SELECT ADDTIME('2007-12-31 23:59:59.999999', '1 1:1:1.000002');
+---------------------------------------------------------+
| ADDTIME('2007-12-31 23:59:59.999999', '1 1:1:1.000002') |
+---------------------------------------------------------+
| 2008-01-02 01:01:01.000001 |
+---------------------------------------------------------+
SELECT ADDTIME('01:00:00.999999', '02:00:00.999998');
+-----------------------------------------------+
| ADDTIME('01:00:00.999999', '02:00:00.999998') |
+-----------------------------------------------+
| 03:00:01.999997 |
+-----------------------------------------------+This page is licensed: GPLv2, originally from fill_help_tables.sql
CURRENT_TIME
CURRENT_TIME([precision])-See also DATE_ADD().
This page is licensed: GPLv2, originally from fill_help_tables.sql
DATE_SUB(date,INTERVAL expr unit)CURRENT_TIMESTAMP()The TIMESTAMP data type
This page is licensed: GPLv2, originally from fill_help_tables.sql
CURRENT_TIMESTAMP
CURRENT_TIMESTAMP([precision])This is the converse of the TO_DAYS() function.
This page is licensed: GPLv2, originally from fill_help_tables.sql
FROM_DAYS(N)SELECT FROM_DAYS(730669);
+-------------------+
| FROM_DAYS(730669) |
+-------------------+
| 2000-07-03 |
+-------------------+310'0000-00-00''2008-00-00'DAY() is a synonym.
This page is licensed: GPLv2, originally from fill_help_tables.sql
DAYOFMONTH(date)SELECT DAYOFMONTH('2007-02-03');
+--------------------------+
| DAYOFMONTH('2007-02-03') |
+--------------------------+
| 3 |
+--------------------------+This page is licensed: GPLv2, originally from fill_help_tables.sql
DATE(expr)SELECT DATE('2013-07-18 12:21:32');
+-----------------------------+
| DATE('2013-07-18 12:21:32') |
+-----------------------------+
| 2013-07-18 |
+-----------------------------+This page is licensed: GPLv2, originally from fill_help_tables.sql
MINUTE(time)SELECT MINUTE('2013-08-03 11:04:03');
+-------------------------------+
| MINUTE('2013-08-03 11:04:03') |
+-------------------------------+
| 4 |
+-------------------------------+
SELECT MINUTE ('23:12:50');
+---------------------+
| MINUTE ('23:12:50') |
+---------------------+
| 12 |
+---------------------+Add a time interval to a date. This function performs date arithmetic, adding a specified value like days or hours to a starting date.
ADDDATE(date,INTERVAL expr unit), ADDDATE(expr,days)When invoked with the INTERVAL form of the second argument, ADDDATE() is a synonym for . The related function is a synonym for . For information on the INTERVAL unit argument, see the discussion for .
When invoked with the days form of the second argument, MariaDB treats it as an integer number of days to be added to expr.
This page is licensed: GPLv2, originally from
Extract microseconds. This function returns the microsecond part of a time or datetime expression as a number from 0 to 999999.
MICROSECOND(expr)Returns the microseconds from the time or datetime expression expr as a number in the range from 0 to 999999.
If expr is a time with no microseconds, zero is returned, while if expr is a date with no time, zero with a warning is returned.
This page is licensed: GPLv2, originally from
Extract the month. This function returns the month portion of a date as a number from 1 (January) to 12 (December).
Format a time. This function formats a time value according to a format string, similar to DATE_FORMAT but for time values.
TIME_FORMAT(time,format)This is used like the DATE_FORMAT() function, but the format string may contain format specifiers only for hours, minutes, and seconds. Other specifiers produce a NULL value or 0.
This page is licensed: GPLv2, originally from
Convert seconds to time. This function returns a TIME value corresponding to the number of seconds elapsed from the start of the day.
Calculate the difference between two months.
MONTHS_BETWEEN returns the number of months between dates two dates. If the first date given is later than the second date, the result is positive; otherwise, the result is negative. If both dates are the same days of the month, or both are last days of months, the result is always an integer. Otherwise, the fractional portion of the result based on a 31-day month is calculated, and considered the difference in time components between the dates.
The following example calculates the months between two dates:
The result is 1.03225806.
SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
+-----------------------------------------+
| DATE_SUB('1998-01-02', INTERVAL 31 DAY) |
+-----------------------------------------+
| 1997-12-02 |
+-----------------------------------------+SELECT DATE_SUB('2005-01-01 00:00:00', INTERVAL '1 1:1:1' DAY_SECOND);
+----------------------------------------------------------------+
| DATE_SUB('2005-01-01 00:00:00', INTERVAL '1 1:1:1' DAY_SECOND) |
+----------------------------------------------------------------+
| 2004-12-30 22:58:59 |
+----------------------------------------------------------------+CREATE TABLE t1 (d DATETIME);
INSERT INTO t1 VALUES
("2007-01-30 21:31:07"),
("1983-10-15 06:42:51"),
("2011-04-21 12:34:56"),
("2011-10-30 06:31:41"),
("2011-01-30 14:03:25"),
("2004-10-07 11:19:34");SELECT d FROM t1 where DAYOFMONTH(d) = 30;
+---------------------+
| d |
+---------------------+
| 2007-01-30 21:31:07 |
| 2011-10-30 06:31:41 |
| 2011-01-30 14:03:25 |
+---------------------+SELECT MONTHS_BETWEEN
(TO_DATE('02-02-1995','MM-DD-YYYY'),
TO_DATE('01-01-1995','MM-DD-YYYY')
);In order to use named time zones, such as GMT, MET or Africa/Johannesburg, the time_zone tables must be loaded (see mysql_tzinfo_to_sql).
No conversion will take place if the value falls outside of the supported TIMESTAMP range ('1970-01-01 00:00:01' to '2038-01-19 05:14:07' UTC) when converted from from_tz to UTC.
This function returns NULL if the arguments are invalid (or named time zones have not been loaded).
See time zones for more information.
Using named time zones (with the time zone tables loaded):
The value is out of the TIMESTAMP range, so no conversion takes place:
This page is licensed: GPLv2, originally from fill_help_tables.sql
CONVERT_TZ(dt,from_tz,to_tz)The time will be returned in the format hh:mm:ss, or hhmmss if used in a numeric calculation.
This page is licensed: GPLv2, originally from fill_help_tables.sql
SEC_TO_TIME(seconds)HHMMSS.uuuuuuThe optional precision determines the microsecond precision. See Microseconds in MariaDB.
With precision:
This page is licensed: GPLv2, originally from fill_help_tables.sql
PPPReturns a value in the format YYYYMM.
This page is licensed: GPLv2, originally from fill_help_tables.sql
PERIOD_ADD(P,N)YYYY-MM-DDYYYYMMDDCURRENT_DATE and CURRENT_DATE() are synonyms.
In a numeric context (note this is not performing date calculations):
Date calculation:
This page is licensed: GPLv2, originally from fill_help_tables.sql
CURDATE()
CURRENT_DATE
CURRENT_DATE()SELECT CURDATE();
+------------+
| CURDATE() |
+------------+
| 2019-03-05 |
+------------+minutesecondIf minute or second are out of the range 0 to 60, NULL is returned. The hour can be in the range -838 to 838, outside of which the value is truncated with a warning.
This page is licensed: GPLv2, originally from fill_help_tables.sql
MAKETIME(hour,minute,second)expr1expr2expr1expr1This page is licensed: GPLv2, originally from fill_help_tables.sql
SUBTIME(expr1,expr2)0 = Monday, 1 = Tuesday, ... 6 = Sunday).This page is licensed: GPLv2, originally from fill_help_tables.sql
DAYOFWEEK(date)SELECT DAYOFWEEK('2007-02-03');
+-------------------------+
| DAYOFWEEK('2007-02-03') |
+-------------------------+
| 7 |
+-------------------------+ADD_MONTHS adds an integer months to a given date (DATE, DATETIME or TIMESTAMP), returning the resulting date.
months can be positive or negative. If months is not a whole number, then it will be rounded to the nearest whole number (not truncated).
The resulting day component will remain the same as that specified in date, unless the resulting month has fewer days than the day component of the given date, in which case the day will be the last day of the resulting month.
Returns NULL if given an invalid date, or a NULL argument.
This page is licensed: CC BY-SA / Gnu FDL
This page is licensed: GPLv2, originally from fill_help_tables.sql
LOCALTIMESTAMP
LOCALTIMESTAMP([precision])NULLThis page is licensed: GPLv2, originally from fill_help_tables.sql
QUARTER(date)SELECT QUARTER('2008-04-01');
+-----------------------+
| QUARTER('2008-04-01') |
+-----------------------+
| 2 |
+-----------------------+
SELECT QUARTER('2019-00-01');
+-----------------------+
| QUARTER('2019-00-01') |
+-----------------------+
| 0 |
+-----------------------+This page is licensed: GPLv2, originally from fill_help_tables.sql
LOCALTIME
LOCALTIME([precision])SELECT ADDDATE('2008-01-02', 31);
+---------------------------+
| ADDDATE('2008-01-02', 31) |
+---------------------------+
| 2008-02-02 |
+---------------------------+SELECT MONTH('2019-01-03');
+---------------------+
| MONTH('2019-01-03') |
+---------------------+
| 1 |
+---------------------+
SELECT MONTH('2019-00-03');
+---------------------+
| MONTH('2019-00-03') |
+---------------------+
| 0 |
+---------------------+SELECT TIME_FORMAT('100:00:00', '%H %k %h %I %l');
+--------------------------------------------+
| TIME_FORMAT('100:00:00', '%H %k %h %I %l') |
+--------------------------------------------+
| 100 100 04 04 4 |
+--------------------------------------------+SELECT DAYOFYEAR('2018-02-16');
+-------------------------+
| DAYOFYEAR('2018-02-16') |
+-------------------------+
| 47 |
+-------------------------+Return a format string. This function provides standard format strings for DATE_FORMAT and STR_TO_DATE based on regions like 'USA' or 'EUR'.
GET_FORMAT({DATE|DATETIME|TIME}, {'EUR'|'USA'|'JIS'|'ISO'|'INTERNAL'})Returns a format string. This function is useful in combination with the DATE_FORMAT() and the STR_TO_DATE() functions.
Possible result formats are:
Obtaining the string matching to the standard European date format:
Using the same string to format a date:
This page is licensed: GPLv2, originally from
Add a time interval to a date. This function calculates a new date by adding a specified unit, such as days or seconds, to a starting value.
Performs date arithmetic. The date argument specifies the starting date or datetime value. expr is an expression specifying the interval value to be added to the starting date. expr is a string; it may start with a "-" for negative intervals. unit is a keyword indicating the units in which the expression should be interpreted. See for a complete list of permitted units.
The result type of DATE_ADD() is determined as follows:
if the first argument is of the type DATETIME, the function returns DATETIME ;
if the first argument is DATE and the interval uses HOUR or smaller units, the function returns DATETIME ;
if the first argument is
This page is licensed: GPLv2, originally from
Return the name of the month. This function returns the full name of the month, such as 'January' or 'December', for a given date.
MONTHNAME(date)Returns the full name of the month for date. The language used for the name is controlled by the value of the lc_time_names system variable. See server locale for more on the supported locales.
Changing the locale:
This page is licensed: GPLv2, originally from
Return the last day of the month. This function calculates the date of the final day for the month containing the given date.
Extract a specific part of a date. This function retrieves components like YEAR, MONTH, DAY, or HOUR from a date or datetime expression.
EXTRACT(unit FROM date)The EXTRACT() function extracts the required unit from the date. See Date and Time Units for a complete list of permitted units.
is not a standard SQL function, so continues to adhere to the old behavior inherited from MySQL.
EXTRACT (HOUR FROM...) returns a value from 0 to 23, as per the SQL standard. HOUR is not a standard function, so continues to adhere to the old behaviour inherited from MySQL.
This page is licensed: GPLv2, originally from
Extract the hour. This function returns the hour portion of a time or datetime value as a number from 0 to 23.
HOUR(time)Returns the hour for time. The range of the return value is 0 to 23 for time-of-day values. However, the range of TIME values actually is much larger, so HOUR can return values greater than 23.
The return value is always positive, even if a negative TIME value is provided.
This page is licensed: GPLv2, originally from
Convert to datetime or add time. With one argument, it returns a datetime; with two, it adds a time expression to a date or datetime.
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.
This page is licensed: GPLv2, originally from
Reference keywords for date arithmetic. These units, such as DAY, HOUR, and MINUTE, specify the interval type used in functions like DATE_ADD and EXTRACT.
The INTERVAL keyword can be used to add or subtract a time interval of time to a DATETIME, DATE or TIME value.
The syntax is:
For example, the SECOND unit is used below by the DATE_ADD() function:
The following units are valid:
MICROSECOND
The time units containing an underscore are composite; that is, they consist of multiple base time units. For base time units, time_quantity is an integer number. For composite units, the quantity must be expressed as a string with multiple integer numbers separated by any punctuation character.
Example of composite units:
Time units can be used in the following contexts:
after a or a operator;
with the following DATE or TIME functions: , , , , , , ;
in the ON SCHEDULE clause of and ;
This page is licensed: CC BY-SA / Gnu FDL
Create a date from a year and day of year. This function constructs a DATE value given a year and the day number within that year.
Synonym for DAYOFMONTH(). Returns the day of the month (1-31) for a given date.
DAY(date)DAY() is a synonym for DAYOFMONTH().
This page is licensed: GPLv2, originally from
Extract the second. This function returns the second portion of a time or datetime value as a number from 0 to 59.
Calculate the difference between periods. This function returns the number of months between two periods formatted as YYMM or YYYYMM.
PERIOD_DIFF(P1,P2)Returns the number of months between periods P1 and P2. P1 and P2 can be in the format YYMM or YYYYMM, and are not date values.
If P1 or P2 contains a two-digit year, values from 00 to 69 are converted to from 2000 to 2069, while values from 70 are converted to 1970 upwards.
This page is licensed: GPLv2, originally from
Calculate the difference between two dates. This function returns the number of days between two date values, ignoring the time component.
DATEDIFF(expr1,expr2)DATEDIFF() returns (expr1 – expr2) expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation.
This page is licensed: GPLv2, originally from
Return the current UTC date. This function returns the current Coordinated Universal Time date in 'YYYY-MM-DD' or YYYYMMDD format.
UTC_DATE, UTC_DATE()Returns the current UTC date as a value in YYYY-MM-DD or YYYYMMDD format, depending on whether the function is used in a string or numeric context.
This page is licensed: GPLv2, originally from
Subtract two time values. This function calculates the difference between two time or datetime expressions.
Return the name of the weekday. This function returns the full name of the day, such as 'Monday' or 'Sunday', for a given date.
Add an interval to a timestamp. This function adds a specified integer number of units (like MONTH or SECOND) to a datetime expression.
SELECT CONVERT_TZ('2016-01-01 12:00:00','+00:00','+10:00');
+-----------------------------------------------------+
| CONVERT_TZ('2016-01-01 12:00:00','+00:00','+10:00') |
+-----------------------------------------------------+
| 2016-01-01 22:00:00 |
+-----------------------------------------------------+SELECT CONVERT_TZ('2016-01-01 12:00:00','GMT','Africa/Johannesburg');
+---------------------------------------------------------------+
| CONVERT_TZ('2016-01-01 12:00:00','GMT','Africa/Johannesburg') |
+---------------------------------------------------------------+
| 2016-01-01 14:00:00 |
+---------------------------------------------------------------+SELECT CONVERT_TZ('1969-12-31 22:00:00','+00:00','+10:00');
+-----------------------------------------------------+
| CONVERT_TZ('1969-12-31 22:00:00','+00:00','+10:00') |
+-----------------------------------------------------+
| 1969-12-31 22:00:00 |
+-----------------------------------------------------+SELECT SEC_TO_TIME(12414);
+--------------------+
| SEC_TO_TIME(12414) |
+--------------------+
| 03:26:54 |
+--------------------+
SELECT SEC_TO_TIME(12414)+0;
+----------------------+
| SEC_TO_TIME(12414)+0 |
+----------------------+
| 32654 |
+----------------------+
SELECT SEC_TO_TIME(9999999);
+----------------------+
| SEC_TO_TIME(9999999) |
+----------------------+
| 838:59:59 |
+----------------------+
1 row in set, 1 warning (0.00 sec)
SHOW WARNINGS;
+---------+------+-------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------+
| Warning | 1292 | Truncated incorrect time value: '9999999' |
+---------+------+-------------------------------------------+CURTIME([precision])SELECT CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 12:45:39 |
+-----------+
SELECT CURTIME() + 0;
+---------------+
| CURTIME() + 0 |
+---------------+
| 124545.000000 |
+---------------+SELECT CURTIME(2);
+-------------+
| CURTIME(2) |
+-------------+
| 09:49:08.09 |
+-------------+SELECT PERIOD_ADD(200801,2);
+----------------------+
| PERIOD_ADD(200801,2) |
+----------------------+
| 200803 |
+----------------------+
SELECT PERIOD_ADD(6910,2);
+--------------------+
| PERIOD_ADD(6910,2) |
+--------------------+
| 206912 |
+--------------------+
SELECT PERIOD_ADD(7010,2);
+--------------------+
| PERIOD_ADD(7010,2) |
+--------------------+
| 197012 |
+--------------------+SELECT CURDATE() +0;
+--------------+
| CURDATE() +0 |
+--------------+
| 20190305 |
+--------------+SELECT CURDATE() - INTERVAL 5 DAY;
+----------------------------+
| CURDATE() - INTERVAL 5 DAY |
+----------------------------+
| 2019-02-28 |
+----------------------------+SELECT MAKETIME(13,57,33);
+--------------------+
| MAKETIME(13,57,33) |
+--------------------+
| 13:57:33 |
+--------------------+
SELECT MAKETIME(-13,57,33);
+---------------------+
| MAKETIME(-13,57,33) |
+---------------------+
| -13:57:33 |
+---------------------+
SELECT MAKETIME(13,67,33);
+--------------------+
| MAKETIME(13,67,33) |
+--------------------+
| NULL |
+--------------------+
SELECT MAKETIME(-1000,57,33);
+-----------------------+
| MAKETIME(-1000,57,33) |
+-----------------------+
| -838:59:59 |
+-----------------------+
1 row in set, 1 warning (0.00 sec)
SHOW WARNINGS;
+---------+------+-----------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------+
| Warning | 1292 | Truncated incorrect time value: '-1000:57:33' |
+---------+------+-----------------------------------------------+SELECT SUBTIME('2007-12-31 23:59:59.999999','1 1:1:1.000002');
+--------------------------------------------------------+
| SUBTIME('2007-12-31 23:59:59.999999','1 1:1:1.000002') |
+--------------------------------------------------------+
| 2007-12-30 22:58:58.999997 |
+--------------------------------------------------------+
SELECT SUBTIME('01:00:00.999999', '02:00:00.999998');
+-----------------------------------------------+
| SUBTIME('01:00:00.999999', '02:00:00.999998') |
+-----------------------------------------------+
| -00:59:59.999999 |
+-----------------------------------------------+CREATE TABLE t1 (d DATETIME);
INSERT INTO t1 VALUES
("2007-01-30 21:31:07"),
("1983-10-15 06:42:51"),
("2011-04-21 12:34:56"),
("2011-10-30 06:31:41"),
("2011-01-30 14:03:25"),
("2004-10-07 11:19:34");SELECT d, DAYNAME(d), DAYOFWEEK(d), WEEKDAY(d) from t1;
+---------------------+------------+--------------+------------+
| d | DAYNAME(d) | DAYOFWEEK(d) | WEEKDAY(d) |
+---------------------+------------+--------------+------------+
| 2007-01-30 21:31:07 | Tuesday | 3 | 1 |
| 1983-10-15 06:42:51 | Saturday | 7 | 5 |
| 2011-04-21 12:34:56 | Thursday | 5 | 3 |
| 2011-10-30 06:31:41 | Sunday | 1 | 6 |
| 2011-01-30 14:03:25 | Sunday | 1 | 6 |
| 2004-10-07 11:19:34 | Thursday | 5 | 3 |
+---------------------+------------+--------------+------------+ADD_MONTHS(date, months)SELECT ADD_MONTHS('2012-01-31', 2);
+-----------------------------+
| ADD_MONTHS('2012-01-31', 2) |
+-----------------------------+
| 2012-03-31 |
+-----------------------------+
SELECT ADD_MONTHS('2012-01-31', -5);
+------------------------------+
| ADD_MONTHS('2012-01-31', -5) |
+------------------------------+
| 2011-08-31 |
+------------------------------+
SELECT ADD_MONTHS('2011-01-31', 1);
+-----------------------------+
| ADD_MONTHS('2011-01-31', 1) |
+-----------------------------+
| 2011-02-28 |
+-----------------------------+
SELECT ADD_MONTHS('2012-01-31', 1);
+-----------------------------+
| ADD_MONTHS('2012-01-31', 1) |
+-----------------------------+
| 2012-02-29 |
+-----------------------------+
SELECT ADD_MONTHS('2012-01-31', 2);
+-----------------------------+
| ADD_MONTHS('2012-01-31', 2) |
+-----------------------------+
| 2012-03-31 |
+-----------------------------+
SELECT ADD_MONTHS('2012-01-31', 3);
+-----------------------------+
| ADD_MONTHS('2012-01-31', 3) |
+-----------------------------+
| 2012-04-30 |
+-----------------------------+
SELECT ADD_MONTHS('2011-01-15', 2.5);
+-------------------------------+
| ADD_MONTHS('2011-01-15', 2.5) |
+-------------------------------+
| 2011-04-15 |
+-------------------------------+
1 row in set (0.001 sec)
SELECT ADD_MONTHS('2011-01-15', 2.6);
+-------------------------------+
| ADD_MONTHS('2011-01-15', 2.6) |
+-------------------------------+
| 2011-04-15 |
+-------------------------------+
1 row in set (0.001 sec)
SELECT ADD_MONTHS('2011-01-15', 2.1);
+-------------------------------+
| ADD_MONTHS('2011-01-15', 2.1) |
+-------------------------------+
| 2011-03-15 |
+-------------------------------+
1 row in set (0.004 sec)SELECT DATE_ADD('2008-01-02', INTERVAL 31 DAY);
+-----------------------------------------+
| DATE_ADD('2008-01-02', INTERVAL 31 DAY) |
+-----------------------------------------+
| 2008-02-02 |
+-----------------------------------------+
SELECT ADDDATE('2008-01-02', INTERVAL 31 DAY);
+----------------------------------------+
| ADDDATE('2008-01-02', INTERVAL 31 DAY) |
+----------------------------------------+
| 2008-02-02 |
+----------------------------------------+CREATE TABLE t1 (d DATETIME);
INSERT INTO t1 VALUES
("2007-01-30 21:31:07"),
("1983-10-15 06:42:51"),
("2011-04-21 12:34:56"),
("2011-10-30 06:31:41"),
("2011-01-30 14:03:25"),
("2004-10-07 11:19:34");SELECT d, ADDDATE(d, 10) from t1;
+---------------------+---------------------+
| d | ADDDATE(d, 10) |
+---------------------+---------------------+
| 2007-01-30 21:31:07 | 2007-02-09 21:31:07 |
| 1983-10-15 06:42:51 | 1983-10-25 06:42:51 |
| 2011-04-21 12:34:56 | 2011-05-01 12:34:56 |
| 2011-10-30 06:31:41 | 2011-11-09 06:31:41 |
| 2011-01-30 14:03:25 | 2011-02-09 14:03:25 |
| 2004-10-07 11:19:34 | 2004-10-17 11:19:34 |
+---------------------+---------------------+
SELECT d, ADDDATE(d, INTERVAL 10 HOUR) from t1;
+---------------------+------------------------------+
| d | ADDDATE(d, INTERVAL 10 HOUR) |
+---------------------+------------------------------+
| 2007-01-30 21:31:07 | 2007-01-31 07:31:07 |
| 1983-10-15 06:42:51 | 1983-10-15 16:42:51 |
| 2011-04-21 12:34:56 | 2011-04-21 22:34:56 |
| 2011-10-30 06:31:41 | 2011-10-30 16:31:41 |
| 2011-01-30 14:03:25 | 2011-01-31 00:03:25 |
| 2004-10-07 11:19:34 | 2004-10-07 21:19:34 |
+---------------------+------------------------------+SELECT MICROSECOND('12:00:00.123456');
+--------------------------------+
| MICROSECOND('12:00:00.123456') |
+--------------------------------+
| 123456 |
+--------------------------------+
SELECT MICROSECOND('2009-12-31 23:59:59.000010');
+-------------------------------------------+
| MICROSECOND('2009-12-31 23:59:59.000010') |
+-------------------------------------------+
| 10 |
+-------------------------------------------+
SELECT MICROSECOND('2013-08-07 12:13:14');
+------------------------------------+
| MICROSECOND('2013-08-07 12:13:14') |
+------------------------------------+
| 0 |
+------------------------------------+
SELECT MICROSECOND('2013-08-07');
+---------------------------+
| MICROSECOND('2013-08-07') |
+---------------------------+
| 0 |
+---------------------------+
1 row in set, 1 warning (0.00 sec)
SHOW WARNINGS;
+---------+------+----------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------+
| Warning | 1292 | Truncated incorrect time value: '2013-08-07' |
+---------+------+----------------------------------------------+DATE_ADD(date,INTERVAL expr unit)INTERVAL time_quantity time_unitSELECT '2008-12-31 23:59:59' + INTERVAL 1 SECOND;
+-------------------------------------------+
| '2008-12-31 23:59:59' + INTERVAL 1 SECOND |
+-------------------------------------------+
| 2009-01-01 00:00:00 |
+-------------------------------------------+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,'INTERNAL')
'%Y%m%d%H%i%s'
GET_FORMAT(TIME,'EUR')
'%H.%i.%s'
GET_FORMAT(TIME,'USA')
'%h:%i:%s %p'
GET_FORMAT(TIME,'JIS')
'%H:%i:%s'
GET_FORMAT(TIME,'ISO')
'%H:%i:%s'
GET_FORMAT(TIME,'INTERNAL')
'%H%i%s'
GET_FORMAT(DATE,'EUR')
'%d.%m.%Y'
GET_FORMAT(DATE,'USA')
'%m.%d.%Y'
GET_FORMAT(DATE,'JIS')
'%Y-%m-%d'
GET_FORMAT(DATE,'ISO')
'%Y-%m-%d'
GET_FORMAT(DATE,'INTERNAL')
'%Y%m%d'
GET_FORMAT(DATETIME,'EUR')
'%Y-%m-%d %H.%i.%s'
DATEDAYDATEsimilarly, if the first argument is TIME and the interval uses DAY or smaller units the function returns TIME, if the interval uses anything larger, the function returns DATETIME ;
if the first argument isn't a temporal type, the function returns a string.
SELECT MONTHNAME('2019-02-03');
+-------------------------+
| MONTHNAME('2019-02-03') |
+-------------------------+
| February |
+-------------------------+SELECT LAST_DAY('2003-02-05');
+------------------------+
| LAST_DAY('2003-02-05') |
+------------------------+
| 2003-02-28 |
+------------------------+
SELECT LAST_DAY('2004-02-05');
+------------------------+
| LAST_DAY('2004-02-05') |
+------------------------+
| 2004-02-29 |
+------------------------+
SELECT LAST_DAY('2004-01-01 01:01:01');
+---------------------------------+
| LAST_DAY('2004-01-01 01:01:01') |
+---------------------------------+
| 2004-01-31 |
+---------------------------------+
SELECT LAST_DAY('2003-03-32');
+------------------------+
| LAST_DAY('2003-03-32') |
+------------------------+
| NULL |
+------------------------+
1 row in set, 1 warning (0.00 sec)
Warning (Code 1292): Incorrect datetime value: '2003-03-32'SELECT TIME_TO_SEC('22:23:00');
+-------------------------+
| TIME_TO_SEC('22:23:00') |
+-------------------------+
| 80580 |
+-------------------------+SELECT TIME('2003-12-31 01:02:03');
+-----------------------------+
| TIME('2003-12-31 01:02:03') |
+-----------------------------+
| 01:02:03 |
+-----------------------------+
SELECT TIME('2003-12-31 01:02:03.000123');
+------------------------------------+
| TIME('2003-12-31 01:02:03.000123') |
+------------------------------------+
| 01:02:03.000123 |
+------------------------------------+when defining a partitioning BY SYSTEM_TIME .
Microseconds
SECOND
Seconds
MINUTE
Minutes
HOUR
Hours
DAY
Days
WEEK
Weeks
MONTH
Months
QUARTER
Quarters
YEAR
Years
SECOND_MICROSECOND
Seconds.Microseconds
MINUTE_MICROSECOND
Minutes.Seconds.Microseconds
MINUTE_SECOND
Minutes.Seconds
HOUR_MICROSECOND
Hours.Minutes.Seconds.Microseconds
HOUR_SECOND
Hours.Minutes.Seconds
HOUR_MINUTE
Hours.Minutes
DAY_MICROSECOND
Days Hours.Minutes.Seconds.Microseconds
DAY_SECOND
Days Hours.Minutes.Seconds
DAY_MINUTE
Days Hours.Minutes
DAY_HOUR
Days Hours
YEAR_MONTH
Years-Months
SELECT MAKEDATE(2011,31), MAKEDATE(2011,32);
+-------------------+-------------------+
| MAKEDATE(2011,31) | MAKEDATE(2011,32) |
+-------------------+-------------------+
| 2011-01-31 | 2011-02-01 |
+-------------------+-------------------+SELECT SECOND('10:05:03');
+--------------------+
| SECOND('10:05:03') |
+--------------------+
| 3 |
+--------------------+
SELECT SECOND('10:05:01.999999');
+---------------------------+
| SECOND('10:05:01.999999') |
+---------------------------+
| 1 |
+---------------------------+SELECT PERIOD_DIFF(200802,200703);
+----------------------------+
| PERIOD_DIFF(200802,200703) |
+----------------------------+
| 11 |
+----------------------------+
SELECT PERIOD_DIFF(6902,6803);
+------------------------+
| PERIOD_DIFF(6902,6803) |
+------------------------+
| 11 |
+------------------------+
SELECT PERIOD_DIFF(7002,6803);
+------------------------+
| PERIOD_DIFF(7002,6803) |
+------------------------+
| -1177 |
+------------------------+SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
+----------------------------------------------+
| DATEDIFF('2007-12-31 23:59:59','2007-12-30') |
+----------------------------------------------+
| 1 |
+----------------------------------------------+
SELECT DATEDIFF('2010-11-30 23:59:59','2010-12-31');
+----------------------------------------------+
| DATEDIFF('2010-11-30 23:59:59','2010-12-31') |
+----------------------------------------------+
| -31 |
+----------------------------------------------+SELECT UTC_DATE(), UTC_DATE() + 0;
+------------+----------------+
| UTC_DATE() | UTC_DATE() + 0 |
+------------+----------------+
| 2010-03-27 | 20100327 |
+------------+----------------+SELECT TIMEDIFF('2000:01:01 00:00:00', '2000:01:01 00:00:00.000001');
+---------------------------------------------------------------+
| TIMEDIFF('2000:01:01 00:00:00', '2000:01:01 00:00:00.000001') |
+---------------------------------------------------------------+
| -00:00:00.000001 |
+---------------------------------------------------------------+
SELECT TIMEDIFF('2008-12-31 23:59:59.000001', '2008-12-30 01:01:01.000002');
+----------------------------------------------------------------------+
| TIMEDIFF('2008-12-31 23:59:59.000001', '2008-12-30 01:01:01.000002') |
+----------------------------------------------------------------------+
| 46:58:57.999999 |
+----------------------------------------------------------------------+Changing the locale:
This page is licensed: GPLv2, originally from fill_help_tables.sql
DAYNAME(date)YYYYMMDDHHMMSS.uuuuuuThe optional precision determines the microsecond precision. See Microseconds in MariaDB.
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 routine or trigger, NOW() returns the time at which the routine or triggering statement began to execute.)
In addition, changing the timestamp system variable with a 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 non-deterministic and therefore unsafe for replication if statement-based binary logging is used. If that is a problem, you can use row-based logging, or start the server with the --sysdate-is-now mariadbd option to cause SYSDATE() to be an alias for NOW(). The non-deterministic nature of SYSDATE() also means that indexes cannot be used for evaluating expressions that refer to it, and that statements using the SYSDATE() function are unsafe for statement-based replication.
Difference between NOW() and SYSDATE():
With precision:
This page is licensed: GPLv2, originally from fill_help_tables.sql
SUBDATE()The second form allows 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.
This page is licensed: GPLv2, originally from fill_help_tables.sql
SUBDATE(date,INTERVAL expr unit), SUBDATE(expr,days)MICROSECONDSECONDMINUTEHOURDAYWEEKMONTHQUARTERYEARThe 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 allowed.
This page is licensed: GPLv2, originally from fill_help_tables.sql
TIMESTAMPADD(unit,interval,datetime_expr)SELECT GET_FORMAT(DATE, 'EUR');
+-------------------------+
| GET_FORMAT(DATE, 'EUR') |
+-------------------------+
| %d.%m.%Y |
+-------------------------+SELECT DATE_FORMAT('2003-10-03',GET_FORMAT(DATE,'EUR'));
+--------------------------------------------------+
| DATE_FORMAT('2003-10-03',GET_FORMAT(DATE,'EUR')) |
+--------------------------------------------------+
| 03.10.2003 |
+--------------------------------------------------+
SELECT STR_TO_DATE('10.31.2003',GET_FORMAT(DATE,'USA'));
+--------------------------------------------------+
| STR_TO_DATE('10.31.2003',GET_FORMAT(DATE,'USA')) |
+--------------------------------------------------+
| 2003-10-31 |
+--------------------------------------------------+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 |
+-------------------------------------------+SELECT INTERVAL 1 DAY + '2008-12-31';
+-------------------------------+
| INTERVAL 1 DAY + '2008-12-31' |
+-------------------------------+
| 2009-01-01 |
+-------------------------------+SELECT '2005-01-01' - INTERVAL 1 SECOND;
+----------------------------------+
| '2005-01-01' - INTERVAL 1 SECOND |
+----------------------------------+
| 2004-12-31 23:59:59 |
+----------------------------------+SELECT DATE_ADD('2000-12-31 23:59:59', INTERVAL 1 SECOND);
+----------------------------------------------------+
| DATE_ADD('2000-12-31 23:59:59', INTERVAL 1 SECOND) |
+----------------------------------------------------+
| 2001-01-01 00:00:00 |
+----------------------------------------------------+SELECT DATE_ADD('2010-12-31 23:59:59', INTERVAL 1 DAY);
+-------------------------------------------------+
| DATE_ADD('2010-12-31 23:59:59', INTERVAL 1 DAY) |
+-------------------------------------------------+
| 2011-01-01 23:59:59 |
+-------------------------------------------------+SELECT DATE_ADD('2100-12-31 23:59:59', INTERVAL '1:1' MINUTE_SECOND);
+---------------------------------------------------------------+
| DATE_ADD('2100-12-31 23:59:59', INTERVAL '1:1' MINUTE_SECOND) |
+---------------------------------------------------------------+
| 2101-01-01 00:01:00 |
+---------------------------------------------------------------+SELECT DATE_ADD('1900-01-01 00:00:00', INTERVAL '-1 10' DAY_HOUR);
+------------------------------------------------------------+
| DATE_ADD('1900-01-01 00:00:00', INTERVAL '-1 10' DAY_HOUR) |
+------------------------------------------------------------+
| 1899-12-30 14:00:00 |
+------------------------------------------------------------+SELECT DATE_ADD('1992-12-31 23:59:59.000002', INTERVAL '1.999999' SECOND_MICROSECOND);
+--------------------------------------------------------------------------------+
| DATE_ADD('1992-12-31 23:59:59.000002', INTERVAL '1.999999' SECOND_MICROSECOND) |
+--------------------------------------------------------------------------------+
| 1993-01-01 00:00:01.000001 |
+--------------------------------------------------------------------------------+SET lc_time_names = 'fr_CA';
SELECT MONTHNAME('2019-05-21');
+-------------------------+
| MONTHNAME('2019-05-21') |
+-------------------------+
| mai |
+-------------------------+SELECT EXTRACT(YEAR FROM '2009-07-02');
+---------------------------------+
| EXTRACT(YEAR FROM '2009-07-02') |
+---------------------------------+
| 2009 |
+---------------------------------+
SELECT EXTRACT(YEAR_MONTH FROM '2009-07-02 01:02:03');
+------------------------------------------------+
| EXTRACT(YEAR_MONTH FROM '2009-07-02 01:02:03') |
+------------------------------------------------+
| 200907 |
+------------------------------------------------+
SELECT EXTRACT(DAY_MINUTE FROM '2009-07-02 01:02:03');
+------------------------------------------------+
| EXTRACT(DAY_MINUTE FROM '2009-07-02 01:02:03') |
+------------------------------------------------+
| 20102 |
+------------------------------------------------+
SELECT EXTRACT(MICROSECOND FROM '2003-01-02 10:30:00.000123');
+--------------------------------------------------------+
| EXTRACT(MICROSECOND FROM '2003-01-02 10:30:00.000123') |
+--------------------------------------------------------+
| 123 |
+--------------------------------------------------------+SELECT EXTRACT(HOUR FROM '26:30:00'), HOUR('26:30:00');
+-------------------------------+------------------+
| EXTRACT(HOUR FROM '26:30:00') | HOUR('26:30:00') |
+-------------------------------+------------------+
| 2 | 26 |
+-------------------------------+------------------+SELECT HOUR('10:05:03');
+------------------+
| HOUR('10:05:03') |
+------------------+
| 10 |
+------------------+
SELECT HOUR('272:59:59');
+-------------------+
| HOUR('272:59:59') |
+-------------------+
| 272 |
+-------------------+SELECT TIME_TO_SEC('00:39:38');
+-------------------------+
| TIME_TO_SEC('00:39:38') |
+-------------------------+
| 2378 |
+-------------------------+SELECT TIME_TO_SEC('09:12:55.2355');
+------------------------------+
| TIME_TO_SEC('09:12:55.2355') |
+------------------------------+
| 33175.2355 |
+------------------------------+
1 row in set (0.000 sec)SELECT TIMESTAMP('2003-12-31');
+-------------------------+
| TIMESTAMP('2003-12-31') |
+-------------------------+
| 2003-12-31 00:00:00 |
+-------------------------+
SELECT TIMESTAMP('2003-12-31 12:00:00','6:30:00');
+--------------------------------------------+
| TIMESTAMP('2003-12-31 12:00:00','6:30:00') |
+--------------------------------------------+
| 2003-12-31 18:30:00 |
+--------------------------------------------+INTERVAL '2:2' YEAR_MONTH
INTERVAL '1:30:30' HOUR_SECOND
INTERVAL '1!30!30' HOUR_SECOND -- same as aboveSELECT MAKEDATE(2011,365), MAKEDATE(2012,365);
+--------------------+--------------------+
| MAKEDATE(2011,365) | MAKEDATE(2012,365) |
+--------------------+--------------------+
| 2011-12-31 | 2012-12-30 |
+--------------------+--------------------+
SELECT MAKEDATE(2011,366), MAKEDATE(2012,366);
+--------------------+--------------------+
| MAKEDATE(2011,366) | MAKEDATE(2012,366) |
+--------------------+--------------------+
| 2012-01-01 | 2012-12-31 |
+--------------------+--------------------+
SELECT MAKEDATE(2011,0);
+------------------+
| MAKEDATE(2011,0) |
+------------------+
| NULL |
+------------------+CREATE TABLE t1 (d DATETIME);
INSERT INTO t1 VALUES
("2007-01-30 21:31:07"),
("1983-10-15 06:42:51"),
("2011-04-21 12:34:56"),
("2011-10-30 06:31:41"),
("2011-01-30 14:03:25"),
("2004-10-07 11:19:34");SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2011-05-23 10:56:05 |
+---------------------+
SELECT d, DATEDIFF(NOW(),d) FROM t1;
+---------------------+-------------------+
| d | DATEDIFF(NOW(),d) |
+---------------------+-------------------+
| 2007-01-30 21:31:07 | 1574 |
| 1983-10-15 06:42:51 | 10082 |
| 2011-04-21 12:34:56 | 32 |
| 2011-10-30 06:31:41 | -160 |
| 2011-01-30 14:03:25 | 113 |
| 2004-10-07 11:19:34 | 2419 |
+---------------------+-------------------+SELECT DAYNAME('2007-02-03');
+-----------------------+
| DAYNAME('2007-02-03') |
+-----------------------+
| Saturday |
+-----------------------+CREATE TABLE t1 (d DATETIME);
INSERT INTO t1 VALUES
("2007-01-30 21:31:07"),
("1983-10-15 06:42:51"),
("2011-04-21 12:34:56"),
("2011-10-30 06:31:41"),
("2011-01-30 14:03:25"),
("2004-10-07 11:19:34");SELECT d, DAYNAME(d) FROM t1;
+---------------------+------------+
| d | DAYNAME(d) |
+---------------------+------------+
| 2007-01-30 21:31:07 | Tuesday |
| 1983-10-15 06:42:51 | Saturday |
| 2011-04-21 12:34:56 | Thursday |
| 2011-10-30 06:31:41 | Sunday |
| 2011-01-30 14:03:25 | Sunday |
| 2004-10-07 11:19:34 | Thursday |
+---------------------+------------+SET lc_time_names = 'fr_CA';
SELECT DAYNAME('2013-04-01');
+-----------------------+
| DAYNAME('2013-04-01') |
+-----------------------+
| lundi |
+-----------------------+SYSDATE([precision])SELECT NOW(), SLEEP(2), NOW();
+---------------------+----------+---------------------+
| NOW() | SLEEP(2) | NOW() |
+---------------------+----------+---------------------+
| 2010-03-27 13:23:40 | 0 | 2010-03-27 13:23:40 |
+---------------------+----------+---------------------+
SELECT SYSDATE(), SLEEP(2), SYSDATE();
+---------------------+----------+---------------------+
| SYSDATE() | SLEEP(2) | SYSDATE() |
+---------------------+----------+---------------------+
| 2010-03-27 13:23:52 | 0 | 2010-03-27 13:23:54 |
+---------------------+----------+---------------------+SELECT SYSDATE(4);
+--------------------------+
| SYSDATE(4) |
+--------------------------+
| 2018-07-10 10:17:13.1689 |
+--------------------------+SELECT DATE_SUB('2008-01-02', INTERVAL 31 DAY);
+-----------------------------------------+
| DATE_SUB('2008-01-02', INTERVAL 31 DAY) |
+-----------------------------------------+
| 2007-12-02 |
+-----------------------------------------+
SELECT SUBDATE('2008-01-02', INTERVAL 31 DAY);
+----------------------------------------+
| SUBDATE('2008-01-02', INTERVAL 31 DAY) |
+----------------------------------------+
| 2007-12-02 |
+----------------------------------------+SELECT SUBDATE('2008-01-02 12:00:00', 31);
+------------------------------------+
| SUBDATE('2008-01-02 12:00:00', 31) |
+------------------------------------+
| 2007-12-02 12:00:00 |
+------------------------------------+CREATE TABLE t1 (d DATETIME);
INSERT INTO t1 VALUES
("2007-01-30 21:31:07"),
("1983-10-15 06:42:51"),
("2011-04-21 12:34:56"),
("2011-10-30 06:31:41"),
("2011-01-30 14:03:25"),
("2004-10-07 11:19:34");SELECT d, SUBDATE(d, 10) from t1;
+---------------------+---------------------+
| d | SUBDATE(d, 10) |
+---------------------+---------------------+
| 2007-01-30 21:31:07 | 2007-01-20 21:31:07 |
| 1983-10-15 06:42:51 | 1983-10-05 06:42:51 |
| 2011-04-21 12:34:56 | 2011-04-11 12:34:56 |
| 2011-10-30 06:31:41 | 2011-10-20 06:31:41 |
| 2011-01-30 14:03:25 | 2011-01-20 14:03:25 |
| 2004-10-07 11:19:34 | 2004-09-27 11:19:34 |
+---------------------+---------------------+
SELECT d, SUBDATE(d, INTERVAL 10 MINUTE) from t1;
+---------------------+--------------------------------+
| d | SUBDATE(d, INTERVAL 10 MINUTE) |
+---------------------+--------------------------------+
| 2007-01-30 21:31:07 | 2007-01-30 21:21:07 |
| 1983-10-15 06:42:51 | 1983-10-15 06:32:51 |
| 2011-04-21 12:34:56 | 2011-04-21 12:24:56 |
| 2011-10-30 06:31:41 | 2011-10-30 06:21:41 |
| 2011-01-30 14:03:25 | 2011-01-30 13:53:25 |
| 2004-10-07 11:19:34 | 2004-10-07 11:09:34 |
+---------------------+--------------------------------+SELECT TIMESTAMPADD(MINUTE,1,'2003-01-02');
+-------------------------------------+
| TIMESTAMPADD(MINUTE,1,'2003-01-02') |
+-------------------------------------+
| 2003-01-02 00:01:00 |
+-------------------------------------+
SELECT TIMESTAMPADD(WEEK,1,'2003-01-02');
+-----------------------------------+
| TIMESTAMPADD(WEEK,1,'2003-01-02') |
+-----------------------------------+
| 2003-01-09 |
+-----------------------------------+Given a time in picoseconds, returns a human-readable time value and unit indicator. Resulting unit is dependent on the length of the argument, and can be:
ps - picoseconds
ns - nanoseconds
us - microseconds
ms - milliseconds
s - seconds
min - minutes
h - hours
d - days
With the exception of results under one nanosecond, which are not rounded and are represented as whole numbers, the result is rounded to 2 decimal places, with a minimum of 3 significant digits.
Returns NULL if the argument is NULL.
This function is very similar to the Sys Schema FORMAT_TIME function, but with the following differences:
Represents minutes as min rather than m.
Does not represent weeks.
This page is licensed: CC BY-SA / Gnu FDL
Returns a DATETIME truncated according to fmt.
Supported formats:
Truncate to day: DD, DDD, J
Truncate to month: MM, MON, MONTH, RM
Truncate to year: SYEAR, SYYYY, Y,YEAR, YY, YYY, YYYY
This page is licensed: CC BY-SA / Gnu FDL
TRUNC(date[,fmt])Return the current date and time. This function returns the current timestamp as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format.
Returns the current date and time as a value in YYYY-MM-DD HH:MM:SS or YYYYMMDDHHMMSS.uuuuuu format, depending on whether the function is used in a string or numeric context. The value is expressed in the current .
MariaDB starting with
These functions return SQL standard compliant types:
NOW() and CURRENT_TIMESTAMP() return a TIMESTAMP value (analogous to the standard type TIMESTAMP WITH LOCAL TIME ZONE) which corresponds to the current point in time and is unambiguous around DST changes.
LOCALTIMESTAMP returns a DATETIME value (analogous to the standard type
The optional precision determines the microsecond precision. See .
NOW() (or its synonyms) can be used as the default value for columns as well as.
When displayed in the table, a default is displayed as current_timestamp() .
Changing the with a timestamp statement affects the value returned by NOW(), but not by .
With precision:
Used as a default TIMESTAMP:
This page is licensed: GPLv2, originally from
Return the current UTC time. This function returns the current Coordinated Universal Time in 'HH:MM:SS' or HHMMSS format.
UTC_TIME
UTC_TIME([precision])Returns the current UTC time as a value in HH:MM:SS or HHMMSS.uuuuuu format, depending on whether the function is used in a string or numeric context.
The optional precision determines the microsecond precision. See .
With precision:
This page is licensed: GPLv2, originally from
Return the weekday index. This function returns the index of the day of the week (0=Monday, 6=Sunday).
Return the year and week. This function returns the year and week number for a date, useful for grouping results by week.
YEARWEEK(date), YEARWEEK(date,mode)Returns year and week for a date. The mode argument works exactly like the mode argument to WEEK(). The year in the result may be different from the year in the date argument for the first and the last week of the year.
This page is licensed: GPLv2, originally from
Return the calendar week. This function returns the week number of the date (1-53), equivalent to WEEK(date, 3).
Convert a date to a day number. This function returns the number of days between year 0 and the given date.
TO_DAYS(date)Given a date date, returns the number of days since the start of the current calendar (0000-00-00).
The function is not designed for use with dates before the advent of the Gregorian calendar in October 1582. Results will not be reliable since it doesn't account for the lost days when the calendar changed from the Julian calendar.
This is the converse of the function.
This page is licensed: GPLv2, originally from
Understand microsecond precision. This concept page explains how MariaDB stores and handles fractional seconds in time data types.
The , , and types, along with the temporal functions, and , support microseconds. The datetime precision of a column can be specified when creating the table with , for example:
Generally, the precision can be specified for any TIME, DATETIME, or TIMESTAMP column, in parentheses, after the type name. The datetime precision specifies number of digits after the decimal dot and can be any integer number from 0 to 6. If no precision is specified it is assumed to be 0, for backward compatibility reasons.
A datetime precision can be specified wherever a type name is used. For example:
FORMAT_PICO_TIME(time_val)SELECT
FORMAT_PICO_TIME(43) AS ps,
FORMAT_PICO_TIME(4321) AS ns,
FORMAT_PICO_TIME(43211234) AS us,
FORMAT_PICO_TIME(432112344321) AS ms,
FORMAT_PICO_TIME(43211234432123) AS s,
FORMAT_PICO_TIME(432112344321234) AS m,
FORMAT_PICO_TIME(4321123443212345) AS h,
FORMAT_PICO_TIME(432112344321234545) AS d;
+--------+---------+----------+-----------+---------+----------+--------+--------+
| ps | ns | us | ms | s | m | h | d |
+--------+---------+----------+-----------+---------+----------+--------+--------+
| 43 ps | 4.32 ns | 43.21 us | 432.11 ms | 43.21 s | 7.20 min | 1.20 h | 5.00 d |
+--------+---------+----------+-----------+---------+----------+--------+--------+SELECT TRUNC('2025-09-24 12:43','DD');
+--------------------------------+
| TRUNC('2025-09-24 12:43','DD') |
+--------------------------------+
| 2025-09-24 00:00:00 |
+--------------------------------+
SELECT TRUNC('2025-09-24 12:43','MM');
+--------------------------------+
| TRUNC('2025-09-24 12:43','MM') |
+--------------------------------+
| 2025-09-01 00:00:00 |
+--------------------------------+
SELECT TRUNC('2025-09-24 12:43','YY');
+--------------------------------+
| TRUNC('2025-09-24 12:43','YY') |
+--------------------------------+
| 2025-01-01 00:00:00 |
+--------------------------------+NOW([precision])
CURRENT_TIMESTAMP
CURRENT_TIMESTAMP([precision])
LOCALTIME, LOCALTIME([precision])
LOCALTIMESTAMP
LOCALTIMESTAMP([precision])SELECT WEEKDAY('2008-02-03 22:23:00');
+--------------------------------+
| WEEKDAY('2008-02-03 22:23:00') |
+--------------------------------+
| 6 |
+--------------------------------+
SELECT WEEKDAY('2007-11-06');
+-----------------------+
| WEEKDAY('2007-11-06') |
+-----------------------+
| 1 |
+-----------------------+SELECT TO_SECONDS('2013-06-13');
+--------------------------+
| TO_SECONDS('2013-06-13') |
+--------------------------+
| 63538300800 |
+--------------------------+
SELECT TO_SECONDS('2013-06-13 21:45:13');
+-----------------------------------+
| TO_SECONDS('2013-06-13 21:45:13') |
+-----------------------------------+
| 63538379113 |
+-----------------------------------+
SELECT TO_SECONDS(NOW());
+-------------------+
| TO_SECONDS(NOW()) |
+-------------------+
| 63543530875 |
+-------------------+
SELECT TO_SECONDS(20130513);
+----------------------+
| TO_SECONDS(20130513) |
+----------------------+
| 63535622400 |
+----------------------+
1 row in set (0.00 sec)
SELECT TO_SECONDS(130513);
+--------------------+
| TO_SECONDS(130513) |
+--------------------+
| 63535622400 |
+--------------------+SELECT YEARWEEK('1987-01-01');
+------------------------+
| YEARWEEK('1987-01-01') |
+------------------------+
| 198652 |
+------------------------+SELECT WEEKOFYEAR('2008-02-20');
+--------------------------+
| WEEKOFYEAR('2008-02-20') |
+--------------------------+
| 8 |
+--------------------------+SELECT TO_DAYS('2007-10-07');
+-----------------------+
| TO_DAYS('2007-10-07') |
+-----------------------+
| 733321 |
+-----------------------+
SELECT TO_DAYS('0000-01-01');
+-----------------------+
| TO_DAYS('0000-01-01') |
+-----------------------+
| 1 |
+-----------------------+
SELECT TO_DAYS(950501);
+-----------------+
| TO_DAYS(950501) |
+-----------------+
| 728779 |
+-----------------+datetime_expr1datetime_expr1datetime_expr2MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.TIMESTAMPDIFF can also be used to calculate age.
Calculating age:
Age as of 2014-08-02:
This page is licensed: GPLv2, originally from fill_help_tables.sql
TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)SELECT UTC_TIME(), UTC_TIME() + 0;
+------------+----------------+
| UTC_TIME() | UTC_TIME() + 0 |
+------------+----------------+
| 17:32:34 | 173234.000000 |
+------------+----------------+SELECT UTC_TIME(5);
+----------------+
| UTC_TIME(5) |
+----------------+
| 07:52:50.78369 |
+----------------+CREATE TABLE t1 (d DATETIME);
INSERT INTO t1 VALUES
("2007-01-30 21:31:07"),
("1983-10-15 06:42:51"),
("2011-04-21 12:34:56"),
("2011-10-30 06:31:41"),
("2011-01-30 14:03:25"),
("2004-10-07 11:19:34");SELECT d FROM t1 where WEEKDAY(d) = 6;
+---------------------+
| d |
+---------------------+
| 2011-10-30 06:31:41 |
| 2011-01-30 14:03:25 |
+---------------------+CREATE TABLE t1 (d DATETIME);
INSERT INTO t1 VALUES
("2007-01-30 21:31:07"),
("1983-10-15 06:42:51"),
("2011-04-21 12:34:56"),
("2011-10-30 06:31:41"),
("2011-01-30 14:03:25"),
("2004-10-07 11:19:34");SELECT * FROM t1;
+---------------------+
| d |
+---------------------+
| 2007-01-30 21:31:07 |
| 1983-10-15 06:42:51 |
| 2011-04-21 12:34:56 |
| 2011-10-30 06:31:41 |
| 2011-01-30 14:03:25 |
| 2004-10-07 11:19:34 |
+---------------------+
6 rows in set (0.02 sec)SELECT YEARWEEK(d) FROM t1 WHERE YEAR(d) = 2011;
+-------------+
| YEARWEEK(d) |
+-------------+
| 201116 |
| 201144 |
| 201105 |
+-------------+
3 rows in set (0.03 sec)CREATE TABLE t1 (d DATETIME);
INSERT INTO t1 VALUES
("2007-01-30 21:31:07"),
("1983-10-15 06:42:51"),
("2011-04-21 12:34:56"),
("2011-10-30 06:31:41"),
("2011-01-30 14:03:25"),
("2004-10-07 11:19:34");SELECT * FROM t1;
+---------------------+
| d |
+---------------------+
| 2007-01-30 21:31:07 |
| 1983-10-15 06:42:51 |
| 2011-04-21 12:34:56 |
| 2011-10-30 06:31:41 |
| 2011-01-30 14:03:25 |
| 2004-10-07 11:19:34 |
+---------------------+SELECT d, WEEKOFYEAR(d), WEEK(d,3) from t1;
+---------------------+---------------+-----------+
| d | WEEKOFYEAR(d) | WEEK(d,3) |
+---------------------+---------------+-----------+
| 2007-01-30 21:31:07 | 5 | 5 |
| 1983-10-15 06:42:51 | 41 | 41 |
| 2011-04-21 12:34:56 | 16 | 16 |
| 2011-10-30 06:31:41 | 43 | 43 |
| 2011-01-30 14:03:25 | 4 | 4 |
| 2004-10-07 11:19:34 | 41 | 41 |
+---------------------+---------------+-----------+SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');
+------------------------------------------------+
| TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01') |
+------------------------------------------------+
| 3 |
+------------------------------------------------+
SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01');
+-----------------------------------------------+
| TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01') |
+-----------------------------------------------+
| -1 |
+-----------------------------------------------+
SELECT TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55');
+----------------------------------------------------------+
| TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55') |
+----------------------------------------------------------+
| 128885 |
+----------------------------------------------------------+SELECT CURDATE();
+------------+
| CURDATE() |
+------------+
| 2019-05-27 |
+------------+
SELECT TIMESTAMPDIFF(YEAR, '1971-06-06', CURDATE()) AS age;
+------+
| age |
+------+
| 47 |
+------+
SELECT TIMESTAMPDIFF(YEAR, '1971-05-06', CURDATE()) AS age;
+------+
| age |
+------+
| 48 |
+------+SELECT name, date_of_birth, TIMESTAMPDIFF(YEAR,date_of_birth,'2014-08-02') AS age
FROM student_details;
+---------+---------------+------+
| name | date_of_birth | age |
+---------+---------------+------+
| Chun | 1993-12-31 | 20 |
| Esben | 1946-01-01 | 68 |
| Kaolin | 1996-07-16 | 18 |
| Tatiana | 1988-04-13 | 26 |
+---------+---------------+------+TIMESTAMP WITHOUT TIME ZONETIMESTAMPThese functions do not return SQL standard compliant types:
NOW()
CURRENT_TIMESTAMP()
LOCALTIMESTAMP
when specifying a return type of a stored function;
when declaring variables;
in a CAST function.
%f is used as the formatting option for microseconds in the STR_TO_DATE, DATE_FORMAT and FROM_UNIXTIME functions, for example:
When comparing anything to a temporal value (DATETIME, TIME, DATE, or TIMESTAMP), both values are compared as temporal values, not as strings.
The INFORMATION_SCHEMA.COLUMNS table has a new column DATETIME_PRECISION
NOW(), CURTIME(), UTC_TIMESTAMP(), UTC_TIME(), , , and accept datetime precision as an optional argument. For example:
TIME_TO_SEC() and UNIX_TIMESTAMP() preserve microseconds of the argument. These functions will return a decimal number if the result non-zero datetime precision and an integer otherwise (for backward compatibility).
Current versions of this patch fix a bug in the following optimization: In certain queries with DISTINCT MariaDB can ignore this clause if it can prove that all result rows are unique anyway, for example, when a primary key is compared with a constant. Sometimes this optimization was applied incorrectly, though — for example, when comparing a string with a date constant. This is now fixed.
DATE_ADD() and DATE_SUB() functions can now take a TIME expression as an argument (not just DATETIME as before).
The event_time field in the mysql.general_log table and the start_time, query_time, and lock_time fields in the mysql.slow_log table now store values with microsecond precision.
The old syntax TIMESTAMP(N), where N is the display width, is no longer supported.
When a DATETIME value is compared to a TIME value, the latter is treated as a full datetime with a zero date part, similar to comparing DATE to a DATETIME, or to comparing DECIMAL numbers.
Earlier versions of MariaDB used to compare only the time part of both operands in such a case.
In MariaDB, an extra column has been added to the table, as well as to the output of .
Note: When you convert a temporal value to a value with a smaller precision, it will be truncated, not rounded. This is done to guarantee that the date part is not changed. For example:
This page is licensed: CC BY-SA / Gnu FDL
Return the current UTC timestamp. This function returns the current Coordinated Universal Time date and time.
UTC_TIMESTAMP
UTC_TIMESTAMP([precision])Returns the current UTC date and time as a value in YYYY-MM-DD HH:MM:SS or YYYYMMDDHHMMSS.uuuuuu format, depending on whether the function is used in a string or numeric context.
The optional precision determines the microsecond precision. See .
With precision:
This page is licensed: GPLv2, originally from
SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2010-03-27 13:13:25 |
+---------------------+
SELECT NOW() + 0;
+-----------------------+
| NOW() + 0 |
+-----------------------+
| 20100327131329.000000 |
+-----------------------+SELECT CURRENT_TIMESTAMP(2);
+------------------------+
| CURRENT_TIMESTAMP(2) |
+------------------------+
| 2018-07-10 09:47:26.24 |
+------------------------+CREATE TABLE t (createdTS TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP);SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='test'
AND COLUMN_NAME LIKE '%ts%'\G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test
TABLE_NAME: t
COLUMN_NAME: ts
ORDINAL_POSITION: 1
COLUMN_DEFAULT: current_timestamp()
...CREATE TABLE example(
col_microsec DATETIME(6),
col_millisec TIME(3)
);CREATE FUNCTION example(x DATETIME(5)) RETURNS TIME(4)
BEGIN
DECLARE y TIMESTAMP(6);
RETURN CAST(x AS time(2));
END;SELECT STR_TO_DATE('20200809 020917076','%Y%m%d %H%i%s%f');
+-----------------------------------------------------+
| STR_TO_DATE('20200809 020917076','%Y%m%d %H%i%s%f') |
+-----------------------------------------------------+
| 2020-08-09 02:09:17.076000 |
+-----------------------------------------------------+SELECT CURTIME(4);
--> 10:11:12.3456SELECT TIME_TO_SEC('10:10:10.12345');
--> 36610.12345SELECT TIME('10:10:10') + INTERVAL 100 MICROSECOND;
--> 10:10:10.000100SELECT CAST('2009-12-31 23:59:59.998877' AS DATETIME(3));
-> 2009-12-31 23:59:59.998SQL_TSI_YEAR is a synonym for YEAR:The resulting output would look like this:
If SQL_MODE is strict (the default), the example above generates the following error and no values are inserted:
If SQL_MODE is not strict, the example above generates a warning and (possibly modified) values are inserted:
The resulting data would look like this:
This page is licensed: GPLv2, originally from fill_help_tables.sql
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. date may be a YYMMDD or YYYYMMDD. The server interprets date as a value in the current The inverse function of UNIX_TIMESTAMP() is FROM_UNIXTIME()
UNIX_TIMESTAMP() supports microseconds.
Timestamps in MariaDB have a maximum value of 4294967295, equivalent to 2106-02-07 06:28:15. This is due to the underlying 32-bit limitation. Using the function on a timestamp beyond this will result in NULL being returned. Use DATETIME as a storage type if you require dates beyond this.
Timestamps in MariaDB have a maximum value of 2147483647, equivalent to 2038-01-19 05:14:07. This is due to the underlying 32-bit limitation. Using the function on a timestamp beyond this will result in NULL being returned. Use DATETIME as a storage type if you require dates beyond this.
Returns NULL for wrong arguments to UNIX_TIMESTAMP().
This page is licensed: GPLv2, originally from fill_help_tables.sql
SELECT UTC_TIMESTAMP(), UTC_TIMESTAMP() + 0;
+---------------------+-----------------------+
| UTC_TIMESTAMP() | UTC_TIMESTAMP() + 0 |
+---------------------+-----------------------+
| 2010-03-27 17:33:16 | 20100327173316.000000 |
+---------------------+-----------------------+SELECT UTC_TIMESTAMP(4);
+--------------------------+
| UTC_TIMESTAMP(4) |
+--------------------------+
| 2018-07-10 07:51:09.1019 |
+--------------------------+YEAR(date)CREATE TABLE t1 (d DATETIME);
INSERT INTO t1 VALUES
("2007-01-30 21:31:07"),
("1983-10-15 06:42:51"),
("2011-04-21 12:34:56"),
("2011-10-30 06:31:41"),
("2011-01-30 14:03:25"),
("2004-10-07 11:19:34");SELECT * FROM t1;
+---------------------+
| d |
+---------------------+
| 2007-01-30 21:31:07 |
| 1983-10-15 06:42:51 |
| 2011-04-21 12:34:56 |
| 2011-10-30 06:31:41 |
| 2011-01-30 14:03:25 |
| 2004-10-07 11:19:34 |
+---------------------+
SELECT * FROM t1 WHERE YEAR(d) = 2011;
+---------------------+
| d |
+---------------------+
| 2011-04-21 12:34:56 |
| 2011-10-30 06:31:41 |
| 2011-01-30 14:03:25 |
+---------------------+SELECT YEAR('1987-01-01');
+--------------------+
| YEAR('1987-01-01') |
+--------------------+
| 1987 |
+--------------------+CREATE TABLE year_format_example (
description VARCHAR(30),
example YEAR
);INSERT INTO year_format_example VALUES
('4-digit numeric year', 1966),
('2-digit numeric year', 66),
('4-digit string year', '1966'),
('2-digit string year', '66');SELECT * FROM year_format_example;
+----------------------+---------+
| description | example |
+----------------------+---------+
| 4-digit numeric year | 1966 |
| 2-digit numeric year | 2066 |
| 4-digit string year | 1966 |
| 2-digit string year | 2066 |
+----------------------+---------+CREATE TABLE year_range_example (
description VARCHAR(30),
example YEAR
);INSERT INTO year_range_example VALUES
('minimum', 1901),
('maximum', 2155),
('below minimum', 1900),
('above maximum', 2156);ERROR 1264 (22003): Out of range value for column 'example' at row 3Warning (sql 1264): Out of range value for column 'example' at row 3
Warning (sql 1264): Out of range value for column 'example' at row 4
The resulting data would look like this:SELECT * FROM year_range_example;
+---------------+---------+
| description | example |
+---------------+---------+
| minimum | 1901 |
| maximum | 2155 |
| below minimum | 0000 |
| above maximum | 0000 |
+---------------+---------+CREATE TABLE year_zero_example (
description VARCHAR(30),
example YEAR
);INSERT INTO year_zero_example VALUES
('4-digit numeric zero', 0000),
('3-digit numeric zero', 000),
('2-digit numeric zero', 00),
('1-digit numeric zero', 0),
('4-digit string zero', '0000'),
('3-digit string zero', '000'),
('2-digit string zero', '00'),
('1-digit string zero', '0');SELECT * FROM year_zero_example;
+----------------------+---------+
| description | example |
+----------------------+---------+
| 4-digit numeric zero | 0000 |
| 3-digit numeric zero | 0000 |
| 2-digit numeric zero | 0000 |
| 1-digit numeric zero | 0000 |
| 4-digit string zero | 0000 |
| 3-digit string zero | 2000 |
| 2-digit string zero | 2000 |
| 1-digit string zero | 2000 |
+----------------------+---------+UNIX_TIMESTAMP()
UNIX_TIMESTAMP(date)SELECT UNIX_TIMESTAMP();
+------------------+
| UNIX_TIMESTAMP() |
+------------------+
| 1269711082 |
+------------------+
SELECT UNIX_TIMESTAMP('2007-11-30 10:30:19');
+---------------------------------------+
| UNIX_TIMESTAMP('2007-11-30 10:30:19') |
+---------------------------------------+
| 1196436619.000000 |
+---------------------------------------+
SELECT UNIX_TIMESTAMP("2007-11-30 10:30:19.123456");
+----------------------------------------------+
| unix_timestamp("2007-11-30 10:30:19.123456") |
+----------------------------------------------+
| 1196411419.123456 |
+----------------------------------------------+
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP('2007-11-30 10:30:19'));
+------------------------------------------------------+
| FROM_UNIXTIME(UNIX_TIMESTAMP('2007-11-30 10:30:19')) |
+------------------------------------------------------+
| 2007-11-30 10:30:19.000000 |
+------------------------------------------------------+
SELECT FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP('2007-11-30 10:30:19')));
+-------------------------------------------------------------+
| FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP('2007-11-30 10:30:19'))) |
+-------------------------------------------------------------+
| 2007-11-30 10:30:19 |
+-------------------------------------------------------------+WEEK()mode0
Sunday
0-53
a Sunday in this year
1
Monday
0-53
more than 3 days this year
2
Sunday
1-53
With the mode value of 3, which means 'more than 3 days this year', weeks are numbered according to ISO 8601:1988.
This page is licensed: GPLv2, originally from fill_help_tables.sql
WEEK(date[,mode])SELECT WEEK('2008-02-20');
+--------------------+
| WEEK('2008-02-20') |
+--------------------+
| 7 |
+--------------------+
SELECT WEEK('2008-02-20',0);
+----------------------+
| WEEK('2008-02-20',0) |
+----------------------+
| 7 |
+----------------------+
SELECT WEEK('2008-02-20',1);
+----------------------+
| WEEK('2008-02-20',1) |
+----------------------+
| 8 |
+----------------------+
SELECT WEEK('2008-12-31',0);
+----------------------+
| WEEK('2008-12-31',0) |
+----------------------+
| 52 |
+----------------------+
SELECT WEEK('2008-12-31',1);
+----------------------+
| WEEK('2008-12-31',1) |
+----------------------+
| 53 |
+----------------------+
SELECT WEEK('2019-12-30',3);
+----------------------+
| WEEK('2019-12-30',3) |
+----------------------+
| 1 |
+----------------------+CREATE TABLE t1 (d DATETIME);
INSERT INTO t1 VALUES
("2007-01-30 21:31:07"),
("1983-10-15 06:42:51"),
("2011-04-21 12:34:56"),
("2011-10-30 06:31:41"),
("2011-01-30 14:03:25"),
("2004-10-07 11:19:34");SELECT d, WEEK(d,0), WEEK(d,1) from t1;
+---------------------+-----------+-----------+
| d | WEEK(d,0) | WEEK(d,1) |
+---------------------+-----------+-----------+
| 2007-01-30 21:31:07 | 4 | 5 |
| 1983-10-15 06:42:51 | 41 | 41 |
| 2011-04-21 12:34:56 | 16 | 16 |
| 2011-10-30 06:31:41 | 44 | 43 |
| 2011-01-30 14:03:25 | 5 | 4 |
| 2004-10-07 11:19:34 | 40 | 41 |
+---------------------+-----------+-----------+a Sunday in this year
3
Monday
1-53
more than 3 days this year
4
Sunday
0-53
more than 3 days this year
5
Monday
0-53
a Monday in this year
6
Sunday
1-53
more than 3 days this year
7
Monday
1-53
a Monday in this year
The options that can be used by DATE_FORMAT(), as well as its inverse STR_TO_DATE() and the FROM_UNIXTIME() function, are:
%a
Short weekday name in current locale (Variable ).
%b
Short form month name in current locale. For locale en_US this is one of: Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov or Dec.
%c
Month with 1 or 2 digits.
%D
Day with English suffix 'th', 'nd', 'st' or 'rd''. (1st, 2nd, 3rd...).
%d
Day with 2 digits.
%e
Day with 1 or 2 digits.
To get a date in one of the standard formats, GET_FORMAT() can be used.
Optionally, the locale can be explicitly specified as the third DATE_FORMAT() argument. Doing so makes the function independent from the session settings, and the three argument version of DATE_FORMAT() can be used in virtual indexed and persistent generated-columns:
This page is licensed: GPLv2, originally from fill_help_tables.sql
TIMESTAMP values.If format is given, the result is exactly equivalent to
Timestamps in MariaDB have a maximum value of 4294967295, equivalent to 2106-02-07 06:28:15. This is due to the underlying 32-bit limitation. Using the function on a timestamp beyond this will result in NULL being returned. Use DATETIME as a storage type if you require dates beyond this.
The one-argument form of FROM_UNIXTIME() returns aDATETIME. This means that it can return values outside of valid TIMESTAMP range, in particular 1970-01-01 00:00:00. And it can return the same result for different values of unix_timestamp (around DST changes).
Timestamps in MariaDB have a maximum value of 4294967295, equivalent to 2106-02-07 06:28:15. This is due to the underlying 32-bit limitation. Using the function on a timestamp beyond this will result in NULL being returned. Use DATETIME as a storage type if you require dates beyond this.
The one-argument form of FROM_UNIXTIME() returns aDATETIME. This means that it can return values outside of valid TIMESTAMP range, in particular 1970-01-01 00:00:00. And it can return the same result for different values of unix_timestamp (around DST changes).
The maximum value is 2147483647, equivalent to 2038-01-19 05:14:07.
The following options can be used by FROM_UNIXTIME(), as well as DATE_FORMAT() and STR_TO_DATE():
%a
Short weekday name in current locale (Variable ).
%b
Short form month name in current locale. For locale en_US this is one of: Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov or Dec.
%c
Month with 1 or 2 digits.
%D
Day with English suffix 'th', 'nd', 'st' or 'rd''. (1st, 2nd, 3rd...).
%d
Day with 2 digits.
%e
Day with 1 or 2 digits.
If your session time zone is set to SYSTEM (the default), FROM_UNIXTIME() will call the OS function to convert the data using the system time zone. At least on Linux, the corresponding function (localtime_r) uses a global mutex inside glibc that can cause contention under high concurrent load.
Set your time zone to a named time zone to avoid this issue. See mysql time zone tables for details on how to do this.
This page is licensed: GPLv2, originally from fill_help_tables.sql
formatSTR_TO_DATE()DATETIMEDATETIMEThe date, time, or datetime values contained in str should be given in the format indicated by format. If str contains an illegal date, time, or datetime value, STR_TO_DATE() returns NULL. An illegal value also produces a warning.
Under specific SQL_MODE settings an error may also be generated if the str isn't a valid date:
The options that can be used by STR_TO_DATE(), as well as its inverse DATE_FORMAT() and the FROM_UNIXTIME() function, are:
%a
Short weekday name in current locale (Variable ).
%b
Short form month name in current locale. For locale en_US this is one of: Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov or Dec.
%c
Month with 1 or 2 digits.
%D
Day with English suffix 'th', 'nd', 'st' or 'rd''. (1st, 2nd, 3rd...).
%d
Day with 2 digits.
%e
Day with 1 or 2 digits.
This page is licensed: GPLv2, originally from fill_help_tables.sql
DATE_FORMAT(date, format[, locale])SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
+------------------------------------------------+
| DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y') |
+------------------------------------------------+
| Sunday October 2009 |
+------------------------------------------------+
SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
+------------------------------------------------+
| DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s') |
+------------------------------------------------+
| 22:23:00 |
+------------------------------------------------+
SELECT DATE_FORMAT('1900-10-04 22:23:00', '%D %y %a %d %m %b %j');
+------------------------------------------------------------+
| DATE_FORMAT('1900-10-04 22:23:00', '%D %y %a %d %m %b %j') |
+------------------------------------------------------------+
| 4th 00 Thu 04 10 Oct 277 |
+------------------------------------------------------------+
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H %k %I %r %T %S %w');
+------------------------------------------------------------+
| DATE_FORMAT('1997-10-04 22:23:00', '%H %k %I %r %T %S %w') |
+------------------------------------------------------------+
| 22 22 10 10:23:00 PM 22:23:00 00 6 |
+------------------------------------------------------------+
SELECT DATE_FORMAT('1999-01-01', '%X %V');
+------------------------------------+
| DATE_FORMAT('1999-01-01', '%X %V') |
+------------------------------------+
| 1998 52 |
+------------------------------------+
SELECT DATE_FORMAT('2006-06-00', '%d');
+---------------------------------+
| DATE_FORMAT('2006-06-00', '%d') |
+---------------------------------+
| 00 |
+---------------------------------+SELECT DATE_FORMAT('2006-01-01', '%W', 'el_GR');
+------------------------------------------+
| DATE_FORMAT('2006-01-01', '%W', 'el_GR') |
+------------------------------------------+
| Κυριακή |
+------------------------------------------+SELECT DATE_FORMAT(NOW(), '%W %d %M %Y %H:%i:%s %Z %z');
+--------------------------------------------------+
| DATE_FORMAT(NOW(), '%W %d %M %Y %H:%i:%s %Z %z') |
+--------------------------------------------------+
| Wednesday 20 September 2023 15:00:23 SAST +0200 |
+--------------------------------------------------+FROM_UNIXTIME(unix_timestamp)
FROM_UNIXTIME(unix_timestamp,format)DATE_FORMAT(FROM_UNIXTIME(unix_timestamp), format)SELECT FROM_UNIXTIME(1196440219);
+---------------------------+
| FROM_UNIXTIME(1196440219) |
+---------------------------+
| 2007-11-30 11:30:19 |
+---------------------------+
SELECT FROM_UNIXTIME(1196440219) + 0;
+-------------------------------+
| FROM_UNIXTIME(1196440219) + 0 |
+-------------------------------+
| 20071130113019.000000 |
+-------------------------------+
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y %D %M %h:%i:%s %x');
+---------------------------------------------------------+
| FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y %D %M %h:%i:%s %x') |
+---------------------------------------------------------+
| 2010 27th March 01:03:47 2010 |
+---------------------------------------------------------+STR_TO_DATE(str,format)SELECT STR_TO_DATE('Wednesday, June 2, 2014', '%W, %M %e, %Y');
+---------------------------------------------------------+
| STR_TO_DATE('Wednesday, June 2, 2014', '%W, %M %e, %Y') |
+---------------------------------------------------------+
| 2014-06-02 |
+---------------------------------------------------------+
SELECT STR_TO_DATE('Wednesday23423, June 2, 2014', '%W, %M %e, %Y');
+--------------------------------------------------------------+
| STR_TO_DATE('Wednesday23423, June 2, 2014', '%W, %M %e, %Y') |
+--------------------------------------------------------------+
| NULL |
+--------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
SHOW WARNINGS;
+---------+------+-----------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------------------------------------+
| Warning | 1411 | Incorrect datetime value: 'Wednesday23423, June 2, 2014' for function str_to_date |
+---------+------+-----------------------------------------------------------------------------------+
SELECT STR_TO_DATE('Wednesday23423, June 2, 2014', '%W%#, %M %e, %Y');
+----------------------------------------------------------------+
| STR_TO_DATE('Wednesday23423, June 2, 2014', '%W%#, %M %e, %Y') |
+----------------------------------------------------------------+
| 2014-06-02 |
+----------------------------------------------------------------+%f
Microseconds 6 digits.
%H
Hour with 2 digits between 00-23.
%h
Hour with 2 digits between 01-12.
%I
Hour with 2 digits between 01-12.
%i
Minute with 2 digits.
%j
Day of the year (001-366)
%k
Hour with 1 digits between 0-23.
%l
Hour with 1 digits between 1-12.
%M
Full month name in current locale (Variable lc_time_names).
%m
Month with 2 digits.
%p
AM/PM according to current locale (Variable lc_time_names).
%r
Time in 12 hour format, followed by AM/PM. Short for '%I:%i:%S %p'.
%S
Seconds with 2 digits.
%s
Seconds with 2 digits.
%T
Time in 24 hour format. Short for '%H:%i:%S'.
%U
Week number (00-53), when first day of the week is Sunday.
%u
Week number (00-53), when first day of the week is Monday.
%V
Week number (01-53), when first day of the week is Sunday. Used with %X.
%v
Week number (01-53), when first day of the week is Monday. Used with %x.
%W
Full weekday name in current locale (Variable lc_time_names).
%w
Day of the week. 0 = Sunday, 6 = Saturday.
%X
Year with 4 digits when first day of the week is Sunday. Used with %V.
%x
Year with 4 digits when first day of the week is Sunday. Used with %v.
%Y
Year with 4 digits.
%y
Year with 2 digits.
%#
For str_to_date(), skip all numbers.
%.
For str_to_date(), skip all punctation characters.
%@
For str_to_date(), skip all alpha characters.
%%
A literal % character.
%f
Microseconds 6 digits.
%H
Hour with 2 digits between 00-23.
%h
Hour with 2 digits between 01-12.
%I
Hour with 2 digits between 01-12.
%i
Minute with 2 digits.
%j
Day of the year (001-366)
%k
Hour with 1 digits between 0-23.
%l
Hour with 1 digits between 1-12.
%M
Full month name in current locale (Variable lc_time_names).
%m
Month with 2 digits.
%p
AM/PM according to current locale (Variable lc_time_names).
%r
Time in 12 hour format, followed by AM/PM. Short for '%I:%i:%S %p'.
%S
Seconds with 2 digits.
%s
Seconds with 2 digits.
%T
Time in 24 hour format. Short for '%H:%i:%S'.
%U
Week number (00-53), when first day of the week is Sunday.
%u
Week number (00-53), when first day of the week is Monday.
%V
Week number (01-53), when first day of the week is Sunday. Used with %X.
%v
Week number (01-53), when first day of the week is Monday. Used with %x.
%W
Full weekday name in current locale (Variable lc_time_names).
%w
Day of the week. 0 = Sunday, 6 = Saturday.
%X
Year with 4 digits when first day of the week is Sunday. Used with %V.
%x
Year with 4 digits when first day of the week is Monday. Used with %v.
%Y
Year with 4 digits.
%y
Year with 2 digits.
%#
For str_to_date(), skip all numbers.
%.
For str_to_date(), skip all punctation characters.
%@
For str_to_date(), skip all alpha characters.
%%
A literal % character.
%f
Microseconds 6 digits.
%H
Hour with 2 digits between 00-23.
%h
Hour with 2 digits between 01-12.
%I
Hour with 2 digits between 01-12.
%i
Minute with 2 digits.
%j
Day of the year (001-366)
%k
Hour with 1 digits between 0-23.
%l
Hour with 1 digits between 1-12.
%M
Full month name in current locale (Variable lc_time_names).
%m
Month with 2 digits.
%p
AM/PM according to current locale (Variable lc_time_names).
%r
Time in 12 hour format, followed by AM/PM. Short for '%I:%i:%S %p'.
%S
Seconds with 2 digits.
%s
Seconds with 2 digits.
%T
Time in 24 hour format. Short for '%H:%i:%S'.
%U
Week number (00-53), when first day of the week is Sunday.
%u
Week number (00-53), when first day of the week is Monday.
%V
Week number (01-53), when first day of the week is Sunday. Used with %X.
%v
Week number (01-53), when first day of the week is Monday. Used with %x.
%W
Full weekday name in current locale (Variable lc_time_names).
%w
Day of the week. 0 = Sunday, 6 = Saturday.
%X
Year with 4 digits when first day of the week is Sunday. Used with %V.
%x
Year with 4 digits when first day of the week is Monday. Used with %v.
%Y
Year with 4 digits.
%y
Year with 2 digits.
%Z
Timezone abbreviation. From .
%z
Numeric timezone +hhmm or -hhmm presenting the hour and minute offset from UTC. From .
%#
For str_to_date(), skip all numbers.
%.
For str_to_date(), skip all punctation characters.
%@
For str_to_date(), skip all alpha characters.
%%
A literal % character.