All pages
Powered by GitBook
1 of 67

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...

DATE FUNCTION

Extract the date part from a datetime expression. This function returns the year, month, and day portions, discarding the time component.

Syntax

DATE(expr)

Description

Extracts the date part of the date or datetime expression expr. Returns NULL and throws a warning when passed an invalid date.

Examples

This page is licensed: GPLv2, originally from

CONVERT_TZ

Convert a datetime value between time zones. This function shifts a timestamp from a source time zone to a target time zone.

Syntax

Description

CONVERT_TZ() converts a datetime value

DAYOFWEEK

Return the weekday index. This function returns a number from 1 (Sunday) to 7 (Saturday) representing the day of the week.

Syntax

Description

Returns the day of the week index for the date (1 = Sunday, 2 = Monday, ..., 7 = Saturday). These index values correspond to the ODBC standard.

DATEDIFF

Calculate the difference between two dates. This function returns the number of days between two date values, ignoring the time component.

Syntax

Description

DATEDIFF() returns (

MICROSECOND

Extract microseconds. This function returns the microsecond part of a time or datetime expression as a number from 0 to 999999.

Syntax

Description

Returns the microseconds from the time or datetime expression expr as a number in the range from 0 to 999999.

DAY

Synonym for DAYOFMONTH(). Returns the day of the month (1-31) for a given date.

Syntax

Description

DAY() is a synonym for .

ADDTIME

Add a time value to a date or time expression. This function sums two time arguments, returning a new time or datetime result.

Syntax

Description

ADDTIME() adds

LOCALTIME

Synonym for NOW(). Returns the current date and time in the session time zone.

Syntax

Description

LOCALTIME and LOCALTIME()

MONTHS_BETWEEN

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.

Date & Time Functions

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.

SELECT MONTHS_BETWEEN
       (TO_DATE('02-02-1995','MM-DD-YYYY'),
        TO_DATE('01-01-1995','MM-DD-YYYY') 
       );
dt
from the
given by from_tz to the time zone given by to_tz and returns the resulting value.

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.

Examples

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)
time zone
This contrasts with WEEKDAY() which follows a different index numbering (0 = Monday, 1 = Tuesday, ... 6 = Sunday).

Examples

This page is licensed: GPLv2, originally from fill_help_tables.sql

DAYOFWEEK(date)
SELECT DAYOFWEEK('2007-02-03');
+-------------------------+
| DAYOFWEEK('2007-02-03') |
+-------------------------+
|                       7 |
+-------------------------+
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.

Examples

This page is licensed: GPLv2, originally from fill_help_tables.sql

DATEDIFF(expr1,expr2)
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 |
+----------------------------------------------+
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.

Examples

See Also

  • Microseconds in MariaDB

This page is licensed: GPLv2, originally from fill_help_tables.sql

MICROSECOND(expr)
This page is licensed: GPLv2, originally from fill_help_tables.sql
DAY(date)
DAYOFMONTH()
expr2
to
expr1
and returns the result.
expr1
is a time or datetime expression, and
expr2
is a time expression.

Examples

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                               |
+-----------------------------------------------+
are synonyms for
.

See Also

  • Microseconds in MariaDB

This page is licensed: GPLv2, originally from fill_help_tables.sql

LOCALTIME
LOCALTIME([precision])
NOW()
SELECT DATE('2013-07-18 12:21:32');
+-----------------------------+
| DATE('2013-07-18 12:21:32') |
+-----------------------------+
| 2013-07-18                  |
+-----------------------------+
fill_help_tables.sql

HOUR

Extract the hour. This function returns the hour portion of a time or datetime value as a number from 0 to 23.

Syntax

HOUR(time)

Description

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.

Examples

See Also

This page is licensed: GPLv2, originally from

CURRENT_TIME

Synonym for CURTIME(). Returns the current time as a value in 'HH:MM:SS' or HHMMSS format.

Syntax

CURRENT_TIME
CURRENT_TIME([precision])

Description

CURRENT_TIME and CURRENT_TIME() are synonyms for .

See Also

This page is licensed: GPLv2, originally from

MAKEDATE

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.

Syntax

MAKEDATE(year,dayofyear)

Description

Returns a date, given year and day-of-year values. dayofyear must be greater than 0 or the result is NULL.

Examples

2012 is a leap year:

This page is licensed: GPLv2, originally from

LAST_DAY

Return the last day of the month. This function calculates the date of the final day for the month containing the given date.

Syntax

LAST_DAY(date)

Description

Takes a date or datetime value and returns the corresponding value for the last day of the month. Returns NULL if the argument is invalid.

Examples

This page is licensed: GPLv2, originally from

PERIOD_DIFF

Calculate the difference between periods. This function returns the number of months between two periods formatted as YYMM or YYYYMM.

Syntax

PERIOD_DIFF(P1,P2)

Description

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.

Examples

This page is licensed: GPLv2, originally from

CURRENT_TIMESTAMP

Synonym for NOW(). Returns the current date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format.

Syntax

CURRENT_TIMESTAMP
CURRENT_TIMESTAMP([precision])

Description

CURRENT_TIMESTAMP and CURRENT_TIMESTAMP() are synonyms for .

See Also

  • The data type

This page is licensed: GPLv2, originally from

DAYNAME

Return the name of the weekday. This function returns the full name of the day, such as 'Monday' or 'Sunday', for a given date.

Syntax

DAYNAME(date)

Description

Returns the name of the weekday 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.

Examples

Changing the locale:

This page is licensed: GPLv2, originally from

DATE_SUB

Subtract a time interval from a date. This function calculates a past date by subtracting a specified unit, such as days, from a starting value.

Syntax

DATE_SUB(date,INTERVAL expr unit)

Description

Performs date arithmetic. The date argument specifies the starting date or datetime value. expr is an expression specifying the interval value to be subtracted from 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.

See also .

Examples

This page is licensed: GPLv2, originally from

CURTIME

Return the current time. This function outputs the current time of day as a value in 'HH:MM:SS' or HHMMSS format.

Syntax

CURTIME([precision])

Description

Returns the current 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 value is expressed in the current .

The optional precision determines the microsecond precision. See .

Examples

With precision:

See Also

This page is licensed: GPLv2, originally from

FROM_DAYS

Convert a day number to a date. This function returns a DATE value corresponding to the number of days since year 0.

Syntax

FROM_DAYS(N)

Description

Given a day number N, returns a DATE value. The day count is based on the number of days from the start of the standard 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.

Examples

This page is licensed: GPLv2, originally from

QUARTER

Return the quarter of the year. This function returns a number from 1 to 4 indicating the quarter for a given date.

Syntax

QUARTER(date)

Description

Returns the quarter of the year for date, in the range 1 to 4. Returns 0 if month contains a zero value, or NULL if the given value is not otherwise a valid date (zero values are accepted).

Examples

This page is licensed: GPLv2, originally from

CURRENT_DATE

Synonym for CURDATE(). Returns the current date as a value in 'YYYY-MM-DD' or YYYYMMDD format.

Syntax

CURRENT_DATE, CURRENT_DATE()

Description

CURRENT_DATE and CURRENT_DATE() are synonyms for .

This page is licensed: GPLv2, originally from

MONTH

Extract the month. This function returns the month portion of a date as a number from 1 (January) to 12 (December).

Syntax

MONTH(date)

Description

Returns the month for date in the range 1 to 12 for January to December, or 0 for dates such as 0000-00-00 or 2008-00-00 that have a zero month part.

Examples

This page is licensed: GPLv2, originally from

MONTHNAME

Return the name of the month. This function returns the full name of the month, such as 'January' or 'December', for a given date.

Syntax

Description

Returns the full name of the month for date. The language used for the name is controlled by the value of the system variable. See for more on the supported locales.

DAYOFYEAR

Return the day of the year. This function returns a number from 1 to 366 indicating the day's position within the year.

Syntax

Description

Returns the day of the year for date, in the range 1 to 366.

Date and Time Units

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 , or value.

The syntax is:

For example, the SECOND unit is used below by the function:

The following units are valid:

Unit
Description

ADDDATE

Add a time interval to a date. This function performs date arithmetic, adding a specified value like days or hours to a starting date.

Syntax

Description

When invoked with the INTERVAL

DATE_ADD

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.

Syntax

Description

Performs date arithmetic. The date argument specifies the starting date or datetime value. expr

LOCALTIMESTAMP

Synonym for NOW(). Returns the current date and time in the session time zone as a datetime value.

Syntax

Description

LOCALTIMESTAMP and LOCALTIMESTAMP()

PERIOD_ADD

Add months to a period. This function adds a specified number of months to a period formatted as YYMM or YYYYMM.

Syntax

Description

Adds N months to period

DAYOFMONTH

Return the day of the month. This function extracts the day portion of a date, returning a number from 1 to 31.

Syntax

Description

Returns the day of the month for date, in the range 1

MINUTE

Extract the minute. This function returns the minute portion of a time or datetime value as a number from 0 to 59.

Syntax

Description

Returns the minute for time, in the range 0 to 59.

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                                 |
+-----------------------------------------------------+
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 |
+---------------------+------------+--------------+------------+
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 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' |
+---------+------+----------------------------------------------+

Examples

Changing the locale:

This page is licensed: GPLv2, originally from fill_help_tables.sql

MONTHNAME(date)
lc_time_names
server locale
SELECT MONTHNAME('2019-02-03');
+-------------------------+
| MONTHNAME('2019-02-03') |
+-------------------------+
| February                |
+-------------------------+
Examples

This page is licensed: GPLv2, originally from fill_help_tables.sql

DAYOFYEAR(date)
SELECT DAYOFYEAR('2018-02-16');
+-------------------------+
| DAYOFYEAR('2018-02-16') |
+-------------------------+
|                      47 |
+-------------------------+

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

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: ADDDATE(), SUBDATE(), DATE_ADD(), DATE_SUB(), TIMESTAMPADD(), TIMESTAMPDIFF(), EXTRACT();

  • in the ON SCHEDULE clause of CREATE EVENT and ALTER EVENT;

  • when defining a BY SYSTEM_TIME .

See Also

  • Date and time literals

  • Operator Precedence

This page is licensed: CC BY-SA / Gnu FDL

MICROSECOND

DATETIME
DATE
TIME
DATE_ADD()

Microseconds

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.

Examples

This page is licensed: GPLv2, originally from fill_help_tables.sql

ADDDATE(date,INTERVAL expr unit), ADDDATE(expr,days)
DATE_ADD()
SUBDATE()
DATE_SUB()
DATE_ADD()
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 DATE and the interval uses DAY or larger units, the function returns DATE ;

  • similarly, 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.

Examples

See Also

  • DATE_SUB

  • ADD_MONTHS

This page is licensed: GPLv2, originally from fill_help_tables.sql

Date and Time Units
are synonyms for
.

See Also

  • Microseconds in MariaDB

This page is licensed: GPLv2, originally from fill_help_tables.sql

LOCALTIMESTAMP
LOCALTIMESTAMP([precision])
NOW()
P
.
P
is in the format YYMM or YYYYMM, and is not a date value. If
P
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.

Returns a value in the format YYYYMM.

Examples

This page is licensed: GPLv2, originally from fill_help_tables.sql

PERIOD_ADD(P,N)
to
31
, or
0
for dates such as
'0000-00-00'
or
'2008-00-00'
which have a zero day part.

DAY() is a synonym.

Examples

This page is licensed: GPLv2, originally from fill_help_tables.sql

DAYOFMONTH(date)
SELECT DAYOFMONTH('2007-02-03');
+--------------------------+
| DAYOFMONTH('2007-02-03') |
+--------------------------+
|                        3 |
+--------------------------+
Examples

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 |
+---------------------+
Date and Time Units
Date and Time Literals
EXTRACT()
fill_help_tables.sql
SELECT MAKEDATE(2011,31), MAKEDATE(2011,32);
+-------------------+-------------------+
| MAKEDATE(2011,31) | MAKEDATE(2011,32) |
+-------------------+-------------------+
| 2011-01-31        | 2011-02-01        |
+-------------------+-------------------+
fill_help_tables.sql
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'
fill_help_tables.sql
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 |
+------------------------+
fill_help_tables.sql
SELECT DAYNAME('2007-02-03');
+-----------------------+
| DAYNAME('2007-02-03') |
+-----------------------+
| Saturday              |
+-----------------------+
fill_help_tables.sql
SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
+-----------------------------------------+
| DATE_SUB('1998-01-02', INTERVAL 31 DAY) |
+-----------------------------------------+
| 1997-12-02                              |
+-----------------------------------------+
Date and Time Units
DATE_ADD()
fill_help_tables.sql
time zone
Microseconds in MariaDB
Microseconds in MariaDB
fill_help_tables.sql
SELECT FROM_DAYS(730669);
+-------------------+
| FROM_DAYS(730669) |
+-------------------+
| 2000-07-03        |
+-------------------+
TO_DAYS()
fill_help_tables.sql
SELECT QUARTER('2008-04-01');
+-----------------------+
| QUARTER('2008-04-01') |
+-----------------------+
|                     2 |
+-----------------------+

SELECT QUARTER('2019-00-01');
+-----------------------+
| QUARTER('2019-00-01') |
+-----------------------+
|                     0 |
+-----------------------+
fill_help_tables.sql
SELECT MONTH('2019-01-03');
+---------------------+
| MONTH('2019-01-03') |
+---------------------+
|                   1 |
+---------------------+

SELECT MONTH('2019-00-03');
+---------------------+
| MONTH('2019-00-03') |
+---------------------+
|                   0 |
+---------------------+
fill_help_tables.sql
CURTIME()
Microseconds in MariaDB
fill_help_tables.sql
NOW()
Microseconds in MariaDB
TIMESTAMP
fill_help_tables.sql
CURDATE()
fill_help_tables.sql

ADD_MONTHS

Add a specific number of months to a date. This Oracle-compatible function simplifies date calculations involving monthly intervals.

ADD_MONTHS is available from 10.6.1.

The ADD_MONTHS function was introduced to enhance Oracle compatibility. Similar functionality can be achieved with the DATE_ADD function.

Syntax

Description

ADD_MONTHS adds an integer months to a given date (, or ), 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.

Examples

See Also

This page is licensed: CC BY-SA / Gnu FDL

NOW

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.

Syntax

Description

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 .

Examples

With precision:

Used as a default TIMESTAMP:

See Also

This page is licensed: GPLv2, originally from

EXTRACT

Extract a specific part of a date. This function retrieves components like YEAR, MONTH, DAY, or HOUR from a date or datetime expression.

Syntax

EXTRACT(unit FROM date)

Description

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.

Examples

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.

See Also

This page is licensed: GPLv2, originally from

MAKETIME

Create a time from hour, minute, and second. This function constructs a TIME value from three numeric arguments.

Syntax

MAKETIME(hour,minute,second)

Description

Returns a time value calculated from the hour, minute, and second arguments.

If 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.

Examples

This page is licensed: GPLv2, originally from

CURDATE

Return the current date. This function outputs today's date as a value in 'YYYY-MM-DD' or YYYYMMDD format, depending on the context.

Syntax

CURDATE()
CURRENT_DATE
CURRENT_DATE()

Description

CURDATE returns the current date as a value in YYYY-MM-DD or YYYYMMDD format, depending on whether the function is used in a string or numeric context.

CURRENT_DATE and CURRENT_DATE() are synonyms.

Examples

In a numeric context (note this is not performing date calculations):

Date calculation:

This page is licensed: GPLv2, originally from

FORMAT_PICO_TIME

Format a time in picoseconds. This function converts a numeric picosecond value into a human-readable string with units like ps, ns, us, ms, s, m, h, or d.

FORMAT_PICO_TIME is available from .

Syntax

Description

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

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 function, but with the following differences:

  • Represents minutes as min rather than m.

  • Does not represent weeks.

Examples

This page is licensed: CC BY-SA / Gnu FDL

GET_FORMAT

Return a format string. This function provides standard format strings for DATE_FORMAT and STR_TO_DATE based on regions like 'USA' or 'EUR'.

Syntax

Description

Returns a format string. This function is useful in combination with the and the functions.

TIME_TO_SEC

Syntax

Description

Returns the time argument, converted to seconds.

The value returned by TIME_TO_SEC

TIME_FORMAT

Format a time. This function formats a time value according to a format string, similar to DATE_FORMAT but for time values.

Syntax

Description

This is used like the function, but the format string may contain format specifiers only for hours, minutes, and seconds. Other specifiers produce a NULL value or 0.

SECOND

Extract the second. This function returns the second portion of a time or datetime value as a number from 0 to 59.

Syntax

Description

Returns the second for a given time

TIMEDIFF

Subtract two time values. This function calculates the difference between two time or datetime expressions.

Syntax

Description

TIMEDIFF() returns expr1 -

SET lc_time_names = 'fr_CA';

SELECT MONTHNAME('2019-05-21');
+-------------------------+
| MONTHNAME('2019-05-21') |
+-------------------------+
| mai                     |
+-------------------------+
INTERVAL time_quantity time_unit
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                       |
+-------------------------------------------+
INTERVAL '2:2' YEAR_MONTH
INTERVAL '1:30:30' HOUR_SECOND
INTERVAL '1!30!30' HOUR_SECOND -- same as above
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                             |
+----------------------------------------+
SELECT ADDDATE('2008-01-02', 31);
+---------------------------+
| ADDDATE('2008-01-02', 31) |
+---------------------------+
| 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          |
+---------------------+------------------------------+
DATE_ADD(date,INTERVAL expr unit)
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                                                     |
+--------------------------------------------------------------------------------+
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 |
+--------------------+
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 HOUR('10:05:03');
+------------------+
| HOUR('10:05:03') |
+------------------+
|               10 |
+------------------+

SELECT HOUR('272:59:59');
+-------------------+
| HOUR('272:59:59') |
+-------------------+
|               272 |
+-------------------+
SELECT 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 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                 |
+-----------------------+
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                                            |
+----------------------------------------------------------------+
SELECT CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 12:45:39  |
+-----------+

SELECT CURTIME() + 0;
+---------------+
| CURTIME() + 0 |
+---------------+
| 124545.000000 |
+---------------+
SELECT CURTIME(2);
+-------------+
| CURTIME(2)  |
+-------------+
| 09:49:08.09 |
+-------------+
NOW([precision])
CURRENT_TIMESTAMP
CURRENT_TIMESTAMP([precision])
LOCALTIME, LOCALTIME([precision])
LOCALTIMESTAMP
LOCALTIMESTAMP([precision])
FORMAT_PICO_TIME(time_val)
partitioning
HOUR()
Date and Time Units
Date and Time Literals
HOUR()
fill_help_tables.sql
fill_help_tables.sql
SELECT CURDATE();
+------------+
| CURDATE()  |
+------------+
| 2019-03-05 |
+------------+
fill_help_tables.sql
is of type
. The returned value preserves microseconds of the argument. See also
.

Examples

This page is licensed: GPLv2, originally from fill_help_tables.sql

TIME_TO_SEC(time)
DOUBLE
Microseconds in MariaDB

Examples

This page is licensed: GPLv2, originally from fill_help_tables.sql

TIME_FORMAT(time,format)
DATE_FORMAT()
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                            |
+--------------------------------------------+
(which can include
), in the range 0 to 59, or NULL if not given a valid time value.

Examples

This page is licensed: GPLv2, originally from fill_help_tables.sql

SECOND(time)
microseconds
expr2
expressed as a time value.
expr1
and
expr2
are time or date-and-time expressions, but both must be of the same type.

Examples

This page is licensed: GPLv2, originally from fill_help_tables.sql

TIMEDIFF(expr1,expr2)
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 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 CURDATE() +0;
+--------------+
| CURDATE() +0 |
+--------------+
|     20190305 |
+--------------+
SELECT CURDATE() - INTERVAL 5 DAY;
+----------------------------+
| CURDATE() - INTERVAL 5 DAY |
+----------------------------+
| 2019-02-28                 |
+----------------------------+
SELECT TIME_TO_SEC('22:23:00');
+-------------------------+
| TIME_TO_SEC('22:23:00') |
+-------------------------+
|                   80580 |
+-------------------------+
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 SECOND('10:05:03');
+--------------------+
| SECOND('10:05:03') |
+--------------------+
|                  3 |
+--------------------+

SELECT SECOND('10:05:01.999999');
+---------------------------+
| SECOND('10:05:01.999999') |
+---------------------------+
|                         1 |
+---------------------------+
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                                                      |
+----------------------------------------------------------------------+
TIMESTAMP WITHOUT TIME ZONE
). Storing its result in a
TIMESTAMP
column can result in a data loss around DST changes.

These functions do not return SQL standard compliant types:

  • NOW()

  • CURRENT_TIMESTAMP()

  • LOCALTIMESTAMP

time zone
Microseconds in MariaDB
TIMESTAMP
INFORMATION_SCHEMA.COLUMNS
CURRENT TIMESTAMP
timestamp system variable
SET
SYSDATE()
Microseconds in MariaDB
timestamp server system variable
fill_help_tables.sql
s - seconds
  • min - minutes

  • h - hours

  • d - days

  • Sys Schema
    FORMAT_TIME
    Possible result formats are:
    Function Call
    Result Format

    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'

    Examples

    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 fill_help_tables.sql

    DATE_FORMAT()
    STR_TO_DATE()
    DATE
    DATETIME
    TIMESTAMP

    TIMESTAMP

    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.

    For the timestamp data type, see TIMESTAMP.

    Syntax

    TIMESTAMP(expr), TIMESTAMP(expr1,expr2)

    Description

    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.

    Examples

    This page is licensed: GPLv2, originally from

    TIMESTAMPADD

    Add an interval to a timestamp. This function adds a specified integer number of units (like MONTH or SECOND) to a datetime expression.

    Syntax

    TIMESTAMPADD(unit,interval,datetime_expr)

    Description

    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, 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 allowed.

    Examples

    This page is licensed: GPLv2, originally from

    SYSDATE

    Return the time of execution. Unlike NOW(), which returns the start time of the statement, SYSDATE() returns the time it executes.

    Syntax

    Description

    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 optional precision determines the microsecond precision. See .

    SYSDATE() returns the time at which it executes. This differs from the behavior for , 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 with a timestamp statement affects the value returned by NOW() but not by SYSDATE(). This means that timestamp settings in the 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 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 .

    Examples

    Difference between NOW() and SYSDATE():

    With precision:

    See Also

    This page is licensed: GPLv2, originally from

    TO_DAYS

    Convert a date to a day number. This function returns the number of days between year 0 and the given date.

    Syntax

    TO_DAYS(date)

    Description

    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.

    Examples

    This page is licensed: GPLv2, originally from

    YEARWEEK

    Return the year and week. This function returns the year and week number for a date, useful for grouping results by week.

    Syntax

    YEARWEEK(date), YEARWEEK(date,mode)

    Description

    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.

    Examples

    This page is licensed: GPLv2, originally from

    WEEKDAY

    Return the weekday index. This function returns the index of the day of the week (0=Monday, 6=Sunday).

    Syntax

    WEEKDAY(date)

    Description

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

    This contrasts with which follows the ODBC standard (1 = Sunday, 2 = Monday, ..., 7 = Saturday).

    Examples

    This page is licensed: GPLv2, originally from

    Microseconds in MariaDB

    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:

    SUBDATE

    Subtract a time interval from a date. This synonym for DATE_SUB calculates a past date by subtracting a specified unit from a starting value.

    Syntax

    Description

    When invoked with the INTERVAL

    SUBTIME

    Subtract a time value. This function subtracts one time or datetime expression from another and returns the result.

    Syntax

    Description

    SUBTIME() returns

    SEC_TO_TIME

    Convert seconds to time. This function returns a TIME value corresponding to the number of seconds elapsed from the start of the day.

    Syntax

    Description

    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 . A warning occurs if the argument corresponds to a value outside that range.

    TIME

    Extract the time portion. This function returns the time part of a time or datetime expression.

    Syntax

    Description

    Extracts the time part of the time or datetime expression expr

    WEEKOFYEAR

    Return the calendar week. This function returns the week number of the date (1-53), equivalent to WEEK(date, 3).

    Syntax

    Description

    Returns the calendar week of the date as a number in the range from 1 sqto 53. WEEKOFYEAR()

    UTC_TIME

    Return the current UTC time. This function returns the current Coordinated Universal Time in 'HH:MM:SS' or HHMMSS format.

    Syntax

    Description

    Returns the current as a value in HH:MM:SS

    TO_SECONDS

    Convert a date to seconds. This function returns the number of seconds from year 0 to the given date or datetime.

    Syntax

    Description

    Returns the number of seconds from year 0 till expr

    TRUNC

    Truncate a date. In Oracle mode, this function truncates a date value to a specified unit of measure.

    Introduced in .

    Syntax

    UTC_DATE

    Return the current UTC date. This function returns the current Coordinated Universal Time date in 'YYYY-MM-DD' or YYYYMMDD format.

    Syntax

    Description

    Returns the current as a value in YYYY-MM-DD

    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()
    ...
    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 |
    +--------+---------+----------+-----------+---------+----------+--------+--------+
    GET_FORMAT({DATE|DATETIME|TIME}, {'EUR'|'USA'|'JIS'|'ISO'|'INTERNAL'})
    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                                       |
    +--------------------------------------------------+
    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)
    SYSDATE([precision])

    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'

    fill_help_tables.sql
    fill_help_tables.sql
    Microseconds in MariaDB
    NOW()
    timestamp system variable
    SET
    binary log
    --sysdate-is-now
    unsafe for statement-based replication
    Microseconds in MariaDB
    timestamp server system variable
    fill_help_tables.sql
    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 |
    +-----------------+
    FROM_DAYS()
    fill_help_tables.sql
    SELECT YEARWEEK('1987-01-01');
    +------------------------+
    | YEARWEEK('1987-01-01') |
    +------------------------+
    |                 198652 |
    +------------------------+
    fill_help_tables.sql
    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 |
    +-----------------------+
    DAYOFWEEK()
    fill_help_tables.sql
    form of the second argument,
    SUBDATE()
    is a synonym for
    . See
    for a complete list of permitted units.

    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.

    Examples

    This page is licensed: GPLv2, originally from fill_help_tables.sql

    SUBDATE(date,INTERVAL expr unit), SUBDATE(expr,days)
    DATE_SUB()
    Date and Time Units
    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.

    Examples

    This page is licensed: GPLv2, originally from fill_help_tables.sql

    SUBTIME(expr1,expr2)

    The time will be returned in the format hh:mm:ss, or hhmmss if used in a numeric calculation.

    Examples

    This page is licensed: GPLv2, originally from fill_help_tables.sql

    SEC_TO_TIME(seconds)
    TIME data type
    and returns it as a string.

    Examples

    This page is licensed: GPLv2, originally from fill_help_tables.sql

    TIME(expr)
    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                    |
    +------------------------------------+
    is a compatibility function that is equivalent to
    .

    Examples

    This page is licensed: GPLv2, originally from fill_help_tables.sql

    WEEKOFYEAR(date)
    SELECT WEEKOFYEAR('2008-02-20');
    +--------------------------+
    | WEEKOFYEAR('2008-02-20') |
    +--------------------------+
    |                        8 |
    +--------------------------+
    WEEK(date,3)
    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 Microseconds in MariaDB.

    Examples

    With precision:

    See Also

    • Microseconds in MariaDB

    This page is licensed: GPLv2, originally from fill_help_tables.sql

    UTC time
    , or NULL if
    expr
    is not a valid date or
    .

    Examples

    This page is licensed: CC BY-SA / Gnu FDL

    TO_SECONDS(expr)
    datetime
    or
    YYYYMMDD
    format, depending on whether the function is used in a string or numeric context.

    Examples

    This page is licensed: GPLv2, originally from fill_help_tables.sql

    UTC_DATE, UTC_DATE()
    UTC date
    SELECT UTC_DATE(), UTC_DATE() + 0;
    +------------+----------------+
    | UTC_DATE() | UTC_DATE() + 0 |
    +------------+----------------+
    | 2010-03-27 |       20100327 |
    +------------+----------------+
    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                        |
    +--------------------------------------------+
    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                        |
    +-----------------------------------+
    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 |
    +--------------------------+
    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 d FROM t1 where WEEKDAY(d) = 6;
    +---------------------+
    | d                   |
    +---------------------+
    | 2011-10-30 06:31:41 |
    | 2011-01-30 14:03:25 |
    +---------------------+
    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 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                              |
    +-----------------------------------------------+
    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' |
    +---------+------+-------------------------------------------+
    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 |
    +---------------------+---------------+-----------+
    UTC_TIME
    UTC_TIME([precision])
    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 |
    +----------------+
    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 |
    +--------------------+
    when declaring arguments of stored routines;
  • 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:

    Additional Information

    • 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:

    See Also

    • Data Type Storage Requirements

    This page is licensed: CC BY-SA / Gnu FDL

    TIME
    DATETIME
    TIMESTAMP
    CAST
    dynamic columns
    CREATE TABLE
    Description

    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

    Examples

    This page is licensed: CC BY-SA / Gnu FDL

    TRUNC(date[,fmt])

    TIMESTAMPDIFF

    Calculate the difference between timestamps. This function returns the difference between two datetime expressions in the specified unit.

    Syntax

    TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

    Description

    Returns datetime_expr2 - datetime_expr1, where datetime_expr1 anddatetime_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 function, that is, MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.

    TIMESTAMPDIFF can also be used to calculate age.

    Examples

    Calculating age:

    Age as of 2014-08-02:

    This page is licensed: GPLv2, originally from

    UTC_TIMESTAMP

    Return the current UTC timestamp. This function returns the current Coordinated Universal Time date and time.

    Syntax

    UTC_TIMESTAMP
    UTC_TIMESTAMP([precision])

    Description

    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 .

    Examples

    With precision:

    See Also

    This page is licensed: GPLv2, originally from

    YEAR

    Extract the year. This function returns the year portion of a date as a number from 1000 to 9999.

    Syntax

    Description

    Returns the year for the given date, in the range 1000 to 9999, or 0 for the "zero" date.

    UNIX_TIMESTAMP

    Return a Unix timestamp. This function returns the number of seconds since the Unix Epoch ('1970-01-01 00:00:00' UTC).

    Syntax

    Description

    If called with no argument, returns a Unix timestamp (seconds since 1970-01-01 00:00:00

    WEEK

    Return the week number. This function returns the week number for a date, with an optional mode to define the start of the week.

    Syntax

    Description

    This function returns the week number for date

    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.3456
    SELECT TIME_TO_SEC('10:10:10.12345');
    --> 36610.12345
    SELECT TIME('10:10:10') + INTERVAL 100 MICROSECOND;
    --> 10:10:10.000100
    SELECT CAST('2009-12-31 23:59:59.998877' AS DATETIME(3));
    -> 2009-12-31 23:59:59.998
    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            |
    +--------------------------------+
    CURRENT_TIME()
    CURRENT_TIMESTAMP()
    LOCALTIME()
    LOCALTIMESTAMP()
    TIME_MS
    INFORMATION_SCHEMA.PROCESSLIST
    SHOW FULL PROCESSLIST
    TIMESTAMPADD()
    fill_help_tables.sql
    Microseconds in MariaDB
    Time Zones
    Microseconds in MariaDB
    fill_help_tables.sql
    SQL_TSI_YEAR is a synonym for YEAR:

    Examples

    YEAR Format

    The resulting output would look like this:

    YEAR Range

    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:

    Zero YEAR

    The resulting data would look like this:

    See Also

    • YEAR data type

    This page is licensed: GPLv2, originally from fill_help_tables.sql

    ) as an unsigned integer. 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. date may be a
    string, a
    string, a
    , or a number in the format YYMMDD or YYYYMMDD. The server interprets date as a value in the current
    and converts it to an internal value in
    . Clients can set their time zone as described in
    .

    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.

    Error Handling

    Returns NULL for wrong arguments to UNIX_TIMESTAMP().

    Examples

    See Also

    • FROM_UNIXTIME()

    This page is licensed: GPLv2, originally from fill_help_tables.sql

    UTC
    DATE
    DATETIME
    TIMESTAMP
    time zone
    UTC
    time zones
    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 |
    +---------+---------------+------+
    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 3
    Warning (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                                         |
    +-------------------------------------------------------------+
    . The two-argument form of
    WEEK()
    allows 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
    system variable is used.

    Modes

    Mode
    1st day of week
    Range
    Week 1 is the 1st week with

    0

    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.

    Examples

    This page is licensed: GPLv2, originally from fill_help_tables.sql

    default_week_format

    DATE_FORMAT

    Format a date according to a pattern. This function converts a date value into a specific string format using specifiers like %Y, %m, and %d.

    Syntax

    Description

    Formats the date value according to the format string.

    The language used for the names is controlled by the value of the system variable. See for more on the supported locales.

    The options that can be used by DATE_FORMAT(), as well as its inverse () and the function, are:

    Option
    Description

    To get a date in one of the standard formats, can be used.

    Examples

    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 :

    Timezone information from MariaDB 11.3:

    See Also

    This page is licensed: GPLv2, originally from

    FROM_UNIXTIME

    Convert a Unix timestamp to a datetime. This function formats a Unix timestamp as a date string or number in the current time zone.

    Syntax

    Description

    Converts the number of seconds from the epoch (1970-01-01 00:00:00 UTC) to aTIMESTAMP value, the opposite of what is doing. Returns NULL if the result would be outside of the valid range of 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 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 as a storage type if you require dates beyond this.

    The following options can be used by FROM_UNIXTIME(), as well as and :

    Option
    Description

    Performance Considerations

    If your 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 for details on how to do this.

    Examples

    See Also

    This page is licensed: GPLv2, originally from

    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 |
    +---------------------+-----------+-----------+
    DATE_FORMAT(date, format[, locale])
    FROM_UNIXTIME(unix_timestamp)
    FROM_UNIXTIME(unix_timestamp,format)

    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

    %f

    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 ).

    %m

    Month with 2 digits.

    %p

    AM/PM according to current locale (Variable ).

    %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 ).

    %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 (), skip all numbers.

    %.

    For (), skip all punctation characters.

    %@

    For (), skip all alpha characters.

    %%

    A literal % character.

    %a

    Short weekday name in current locale (Variable lc_time_names).

    %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.

    lc_time_names
    server locale
    STR_TO_DATE
    FROM_UNIXTIME()
    GET_FORMAT()
    generated-columns
    STR_TO_DATE()
    FROM_UNIXTIME()
    fill_help_tables.sql

    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.

    %f

    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 ).

    %m

    Month with 2 digits.

    %p

    AM/PM according to current locale (Variable ).

    %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 ).

    %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 (), skip all numbers.

    %.

    For (), skip all punctation characters.

    %@

    For (), skip all alpha characters.

    %%

    A literal % character.

    %a

    Short weekday name in current locale (Variable lc_time_names).

    %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.

    UNIX_TIMESTAMP()
    DATETIME
    DATETIME
    DATE_FORMAT()
    STR_TO_DATE()
    session time zone
    mysql time zone tables
    UNIX_TIMESTAMP()
    DATE_FORMAT()
    STR_TO_DATE()
    fill_help_tables.sql

    STR_TO_DATE

    Convert a string to a date. This function parses a string based on a specified format string and returns a DATE, TIME, or DATETIME value.

    Syntax

    Description

    This is the inverse of the DATE_FORMAT() function. It takes a string str and a format string format. STR_TO_DATE() returns aDATETIME value if the format string contains both date and time parts, or aDATE or TIME value if the string contains only date or time parts.

    The 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 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 and the function, are:

    Option
    Description

    Examples

    See Also

    This page is licensed: GPLv2, originally from

    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  |
    +--------------------------------------------------+
    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)
    Microseconds
    lc_time_names
    lc_time_names
    lc_time_names
    str_to_date
    str_to_date
    str_to_date

    %f

    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 ).

    %m

    Month with 2 digits.

    %p

    AM/PM according to current locale (Variable ).

    %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 ).

    %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 (), skip all numbers.

    %.

    For (), skip all punctation characters.

    %@

    For (), skip all alpha characters.

    %%

    A literal % character.

    %a

    Short weekday name in current locale (Variable lc_time_names).

    %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.

    SQL_MODE
    ALLOW_INVALID_DATES
    NO_ZERO_DATE
    NO_ZERO_IN_DATE
    DATE_FORMAT()
    FROM_UNIXTIME()
    DATE_FORMAT()
    FROM_UNIXTIME()
    fill_help_tables.sql
    Microseconds
    lc_time_names
    lc_time_names
    lc_time_names
    str_to_date
    str_to_date
    str_to_date
    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                                                     |
    +----------------------------------------------------------------+
    Microseconds
    lc_time_names
    lc_time_names
    lc_time_names
    str_to_date
    str_to_date
    str_to_date
    MariaDB 11.0.2
    SQL_MODE=ORACLE
    11.7
    MariaDB 12.2
    MariaDB 11.3.0
    MariaDB 11.3.0