All pages
Powered by GitBook
Couldn't generate the PDF for 366 pages, generation stopped at 100.
Extend with 50 more pages.
1 of 100

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

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

SQL Functions

Discover functions and procedures in MariaDB.

Aggregate Functions

Perform calculations on multiple rows to return a single value. Includes standard SQL functions like SUM, AVG, COUNT, MIN, and MAX, often used with GROUP BY.

AVG

Calculate the average value. This function computes the arithmetic mean of a numeric expression, ignoring NULL values.

Syntax

AVG([DISTINCT] expr)

Description

Returns the average value of expr. The DISTINCT option can be used to return the average of the distinct values of expr. NULL values are ignored. It is an aggregate function, and so can be used with the clause.

AVG() returns NULL if there were no matching rows.

AVG() can be used as a .

Examples

Commonly, AVG() is used with a clause:

Be careful to avoid this common mistake, not grouping correctly and returning mismatched data:

As a :

See Also

  • (maximum)

  • (minimum)

  • (sum total)

This page is licensed: GPLv2, originally from

COUNT DISTINCT

Count unique values. This function returns the number of distinct, non-NULL values found in the specified column or expression.

Syntax

COUNT(DISTINCT expr,[expr...])

Description

Returns a count of the number of different non-NULL values.

COUNT(DISTINCT) returns 0 if there were no matching rows.

Examples

See Also

This page is licensed: GPLv2, originally from

BIT_AND

Perform a bitwise AND operation. This function returns the result of performing a bitwise AND on all values in a given expression.

Syntax

BIT_AND(expr) [over_clause]

Description

Returns the bitwise AND of all bits in expr. The calculation is performed with 64-bit (BIGINT) precision. It is an , and so can be used with the clause.

If no rows match, BIT_AND will return a value with all bits set to 1. NULL values have no effect on the result unless all results are NULL, which is treated as no match.

BIT_AND can be used as a with the addition of the over_clause.

Examples

As an :

No match:

See Also

This page is licensed: GPLv2, originally from

BIT_OR

Perform a bitwise OR operation. This function returns the result of performing a bitwise OR on all values in a given expression.

Syntax

Description

Returns the bitwise OR of all bits in expr

BIT_XOR

Perform a bitwise XOR operation. This function returns the result of performing a bitwise XOR on all values in a given expression.

Syntax

Description

Returns the bitwise XOR of all bits in expr

Control Flow Functions

Learn about control flow functions in MariaDB Server. This section details SQL functions like IF, CASE, and NULLIF, which enable conditional logic within your queries and stored routines.

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.

. The calculation is performed with 64-bit (
) precision. It is an
, and so can be used with the
clause.

If no rows match, BIT_OR will return a value with all bits set to 0. NULL values have no effect on the result unless all results are NULL, which is treated as no match.

BIT_OR can be used as a window function with the addition of the over_clause.

Examples

As an aggregate function:

No match:

See Also

  • BIT_AND

  • BIT_XOR

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

BIGINT
aggregate function
GROUP BY
. The calculation is performed with 64-bit (
) precision. It is an
, and so can be used with the
clause.

If no rows match, BIT_XOR will return a value with all bits set to 0. NULL values have no effect on the result unless all results are NULL, which is treated as no match.

BIT_XOR can be used as a window function with the addition of the over_clause.

Examples

As an aggregate function:

No match:

See Also

  • BIT_AND

  • BIT_OR

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

BIGINT
aggregate function
GROUP BY
GROUP BY
window function
GROUP BY
window function
MAX
MIN
SUM
fill_help_tables.sql
SELECT
COUNT
fill_help_tables.sql
aggregate function
GROUP BY
window function
aggregate function
BIT_OR
BIT_XOR
fill_help_tables.sql

STDDEV_SAMP

Calculate sample standard deviation. This function computes the standard deviation assuming the set of values represents a sample of the population.

Syntax

STDDEV_SAMP(expr)

Description

Returns the sample standard deviation of expr (the square root of ).

It is an , and so can be used with the clause.

STDDEV_SAMP() can be used as a .

STDDEV_SAMP() returns NULL if there were no matching rows.

This page is licensed: GPLv2, originally from

DECODE_ORACLE

Compare a value against a list of conditions. This Oracle-compatible function returns a corresponding result when a match is found, or a default value otherwise.

DECODE_ORACLE is a synonym for the version of the DECODE function, and is available in all modes.

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

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

ADDTIME(expr1,expr2)

Description

ADDTIME() adds 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

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

DAY

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

Syntax

DAY(date)

Description

DAY() is a synonym for DAYOFMONTH().

This page is licensed: GPLv2, originally from

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

DAYOFYEAR(date)

Description

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

Examples

This page is licensed: GPLv2, originally from

MINUTE

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

Syntax

MINUTE(time)

Description

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

Examples

This page is licensed: GPLv2, originally from

NULLIF

Compare two expressions and return NULL if they are equal. If the expressions differ, the function returns the first expression.

Syntax

Description

Returns NULL if expr1 = expr2 is true, otherwise returns expr1. This is the same as

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

CURRENT_TIME

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

Syntax

Description

CURRENT_TIME and CURRENT_TIME()

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

Description

CURRENT_TIMESTAMP and

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

Description

CURDATE returns the current date as a value in

CURTIME

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

Syntax

Description

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

DATE FUNCTION

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

Syntax

Description

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

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

Description

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

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 (

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

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

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

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

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

Description

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

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

Description

Returns a date, given year

MAKETIME

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

Syntax

Description

Returns a time value calculated from the hour

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

PERIOD_DIFF

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

Syntax

Description

Returns the number of months between periods P1 and P2. P1 and P2 can be in the format YYMM

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

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

TIMEDIFF

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

Syntax

Description

TIMEDIFF() returns expr1 -

BIT_OR(expr) [over_clause]
CREATE TABLE vals (x INT);

INSERT INTO vals VALUES(111),(110),(100);

SELECT BIT_AND(x), BIT_OR(x), BIT_XOR(x) FROM vals;
+------------+-----------+------------+
| BIT_AND(x) | BIT_OR(x) | BIT_XOR(x) |
+------------+-----------+------------+
|        100 |       111 |        101 |
+------------+-----------+------------+
CREATE TABLE vals2 (category VARCHAR(1), x INT);

INSERT INTO vals2 VALUES
  ('a',111),('a',110),('a',100),
  ('b','000'),('b',001),('b',011);

SELECT category, BIT_AND(x), BIT_OR(x), BIT_XOR(x) 
  FROM vals GROUP BY category;
+----------+------------+-----------+------------+
| category | BIT_AND(x) | BIT_OR(x) | BIT_XOR(x) |
+----------+------------+-----------+------------+
| a        |        100 |       111 |        101 |
| b        |          0 |        11 |         10 |
+----------+------------+-----------+------------+
SELECT BIT_OR(NULL);
+--------------+
| BIT_OR(NULL) |
+--------------+
|            0 |
+--------------+
BIT_XOR(expr) [over_clause]
CREATE TABLE vals (x INT);

INSERT INTO vals VALUES(111),(110),(100);

SELECT BIT_AND(x), BIT_OR(x), BIT_XOR(x) FROM vals;
+------------+-----------+------------+
| BIT_AND(x) | BIT_OR(x) | BIT_XOR(x) |
+------------+-----------+------------+
|        100 |       111 |        101 |
+------------+-----------+------------+
CREATE TABLE vals2 (category VARCHAR(1), x INT);

INSERT INTO vals2 VALUES
  ('a',111),('a',110),('a',100),
  ('b','000'),('b',001),('b',011);

SELECT category, BIT_AND(x), BIT_OR(x), BIT_XOR(x) 
  FROM vals GROUP BY category;
+----------+------------+-----------+------------+
| category | BIT_AND(x) | BIT_OR(x) | BIT_XOR(x) |
+----------+------------+-----------+------------+
| a        |        100 |       111 |        101 |
| b        |          0 |        11 |         10 |
+----------+------------+-----------+------------+
SELECT BIT_XOR(NULL);
+---------------+
| BIT_XOR(NULL) |
+---------------+
|             0 |
+---------------+
CREATE TABLE sales (sales_value INT);

INSERT INTO sales VALUES(10),(20),(20),(40);

SELECT AVG(sales_value) FROM sales;
+------------------+
| AVG(sales_value) |
+------------------+
|          22.5000 |
+------------------+

SELECT AVG(DISTINCT(sales_value)) FROM sales;
+----------------------------+
| AVG(DISTINCT(sales_value)) |
+----------------------------+
|                    23.3333 |
+----------------------------+
CREATE TABLE student (name CHAR(10), test CHAR(10), score TINYINT); 

INSERT INTO student VALUES 
  ('Chun', 'SQL', 75), ('Chun', 'Tuning', 73), 
  ('Esben', 'SQL', 43), ('Esben', 'Tuning', 31), 
  ('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88), 
  ('Tatiana', 'SQL', 87), ('Tatiana', 'Tuning', 83);

SELECT name, AVG(score) FROM student GROUP BY name;
+---------+------------+
| name    | AVG(score) |
+---------+------------+
| Chun    |    74.0000 |
| Esben   |    37.0000 |
| Kaolin  |    72.0000 |
| Tatiana |    85.0000 |
+---------+------------+
SELECT name,test,AVG(score) FROM student;
+------+------+------------+
| name | test | MIN(score) |
+------+------+------------+
| Chun | SQL  |         31 |
+------+------+------------+
CREATE TABLE student_test (name CHAR(10), test CHAR(10), score TINYINT); 

INSERT INTO student_test VALUES 
    ('Chun', 'SQL', 75), ('Chun', 'Tuning', 73), 
    ('Esben', 'SQL', 43), ('Esben', 'Tuning', 31), 
    ('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88), 
    ('Tatiana', 'SQL', 87), ('Tatiana', 'Tuning', 83);

SELECT name, test, score, AVG(score) OVER (PARTITION BY test) 
    AS average_by_test FROM student_test;
+---------+--------+-------+-----------------+
| name    | test   | score | average_by_test |
+---------+--------+-------+-----------------+
| Chun    | SQL    |    75 |         65.2500 |
| Chun    | Tuning |    73 |         68.7500 |
| Esben   | SQL    |    43 |         65.2500 |
| Esben   | Tuning |    31 |         68.7500 |
| Kaolin  | SQL    |    56 |         65.2500 |
| Kaolin  | Tuning |    88 |         68.7500 |
| Tatiana | SQL    |    87 |         65.2500 |
| Tatiana | Tuning |    83 |         68.7500 |
+---------+--------+-------+-----------------+
CREATE TABLE student (name CHAR(10), test CHAR(10), score TINYINT); 

INSERT INTO student VALUES 
  ('Chun', 'SQL', 75), ('Chun', 'Tuning', 73), 
  ('Esben', 'SQL', 43), ('Esben', 'Tuning', 31), 
  ('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88), 
  ('Tatiana', 'SQL', 87), ('Tatiana', 'Tuning', 83);

SELECT COUNT(*) FROM student;
+----------+
| COUNT(*) |
+----------+
|        8 |
+----------+

SELECT COUNT(DISTINCT (name)) FROM student;
+------------------------+
| COUNT(DISTINCT (name)) |
+------------------------+
|                      4 |
+------------------------+
CREATE TABLE vals (x INT);

INSERT INTO vals VALUES(111),(110),(100);

SELECT BIT_AND(x), BIT_OR(x), BIT_XOR(x) FROM vals;
+------------+-----------+------------+
| BIT_AND(x) | BIT_OR(x) | BIT_XOR(x) |
+------------+-----------+------------+
|        100 |       111 |        101 |
+------------+-----------+------------+
CREATE TABLE vals2 (category VARCHAR(1), x INT);

INSERT INTO vals2 VALUES
  ('a',111),('a',110),('a',100),
  ('b','000'),('b',001),('b',011);

SELECT category, BIT_AND(x), BIT_OR(x), BIT_XOR(x) 
  FROM vals GROUP BY category;
+----------+------------+-----------+------------+
| category | BIT_AND(x) | BIT_OR(x) | BIT_XOR(x) |
+----------+------------+-----------+------------+
| a        |        100 |       111 |        101 |
| b        |          0 |        11 |         10 |
+----------+------------+-----------+------------+
SELECT BIT_AND(NULL);
+----------------------+
| BIT_AND(NULL)        |
+----------------------+
| 18446744073709551615 |
+----------------------+

Numeric Functions

Learn about numeric functions in MariaDB Server. This section details SQL functions for performing mathematical calculations, rounding, and manipulating numeric values in your queries.

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:

SELECT MONTHS_BETWEEN
       (TO_DATE('02-02-1995','MM-DD-YYYY'),
        TO_DATE('01-01-1995','MM-DD-YYYY') 
       );

The result is 1.03225806.

WHEN expr1 = expr2 THEN NULL ELSE expr1 END.

Examples

See Also

  • NULL values

  • IS NULL operator

  • IS NOT NULL operator

  • COALESCE function

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

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

See Also

  • Microseconds in MariaDB

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

CURRENT_TIME
CURRENT_TIME([precision])
CURTIME()
CURRENT_TIMESTAMP()
are synonyms for
.

See Also

  • Microseconds in MariaDB

  • The TIMESTAMP data type

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

CURRENT_TIMESTAMP
CURRENT_TIMESTAMP([precision])
NOW()
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 fill_help_tables.sql

CURDATE()
CURRENT_DATE
CURRENT_DATE()
SELECT CURDATE();
+------------+
| CURDATE()  |
+------------+
| 2019-03-05 |
+------------+
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 Microseconds in MariaDB.

Examples

With precision:

See Also

  • Microseconds in MariaDB

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

time zone

Examples

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

DATE(expr)
date
datetime
SELECT DATE('2013-07-18 12:21:32');
+-----------------------------+
| DATE('2013-07-18 12:21:32') |
+-----------------------------+
| 2013-07-18                  |
+-----------------------------+
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 DATE_ADD().

Examples

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

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

  • s - seconds

  • min - minutes

  • h - hours

  • d - days

With the exception of results under one nanosecond, which are not rounded and are represented as whole numbers, the result is rounded to 2 decimal places, with a minimum of 3 significant digits.

Returns NULL if the argument is NULL.

This function is very similar to the Sys Schema FORMAT_TIME function, but with the following differences:

  • Represents minutes as min rather than m.

  • Does not represent weeks.

Examples

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

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 TO_DAYS() function.

Examples

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

FROM_DAYS(N)
SELECT FROM_DAYS(730669);
+-------------------+
| FROM_DAYS(730669) |
+-------------------+
| 2000-07-03        |
+-------------------+
if the argument is invalid.

Examples

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

LAST_DAY(date)
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 fill_help_tables.sql

MAKEDATE(year,dayofyear)
SELECT MAKEDATE(2011,31), MAKEDATE(2011,32);
+-------------------+-------------------+
| MAKEDATE(2011,31) | MAKEDATE(2011,32) |
+-------------------+-------------------+
| 2011-01-31        | 2011-02-01        |
+-------------------+-------------------+
,
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 fill_help_tables.sql

MAKETIME(hour,minute,second)
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)
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 fill_help_tables.sql

PERIOD_DIFF(P1,P2)

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                    |
+------------------------------------+
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 |
+------------+----------------+
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 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                               |
+-----------------------------------------------+
fill_help_tables.sql
SELECT DAYOFYEAR('2018-02-16');
+-------------------------+
| DAYOFYEAR('2018-02-16') |
+-------------------------+
|                      47 |
+-------------------------+
fill_help_tables.sql
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 |
+---------------------+
fill_help_tables.sql
VAR_SAMP()
aggregate function
GROUP BY
window function
fill_help_tables.sql
CURDATE()
fill_help_tables.sql
fill_help_tables.sql

VAR_SAMP

Calculate sample variance. This function computes the statistical variance for a set of values assumed to be a sample of the population.

Syntax

VAR_SAMP(expr)

Description

Returns the sample variance of expr. That is, the denominator is the number of rows minus one.

It is an , and so can be used with the clause.

VAR_SAMP() can be used as a .

VAR_SAMP() returns NULL if there were no matching rows.

Examples

As an :

As a :

See Also

  • (variance)

  • (population standard deviation)

This page is licensed: GPLv2, originally from

CASE OPERATOR

Implement conditional logic in SQL queries. This operator evaluates conditions and returns a specific value when the first true condition is met.

Syntax

CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN
result ...] [ELSE result] END

CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...]
[ELSE result] END

Description

The first version returns the result for the first value=compare_value comparison that is true. The second version returns the result for the first condition that is true. If there was no matching result value, the result after ELSE is returned, or NULL if there is no ELSE part.

There is also a , which differs from the CASE operator described here.

Examples

Only the first matching condition is processed:

See Also

  • The , which differs from the CASE operator described above.

  • The .

This page is licensed: GPLv2, originally from

IF Function

Return one of two values based on a condition. This function evaluates a boolean expression and returns the first value if true, or the second if false.

Syntax

IF(expr1,expr2,expr3)

Description

If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then IF() returns expr2; otherwise it returns expr3. IF() returns a numeric or string value, depending on the context in which it is used.

Note: There is also an which differs from theIF() function described here.

Examples

See Also

There is also an , which differs from the IF() function described above.

This page is licensed: GPLv2, originally from

IFNULL

Replace NULL values with a fallback. This function returns the first argument if it's not NULL; otherwise, it returns the specified replacement value.

Syntax

Description

If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns_expr2_. IFNULL() returns a numeric or string value, depending on the context in which it is used.

NVL() is an alias for IFNULL().

Examples

See Also

This page is licensed: GPLv2, originally from

NVL2

Return values based on NULL status. This function returns the second argument if the first is not NULL, and the third argument if the first is NULL.

Syntax

NVL2(expr1,expr2,expr3)

Description

The NVL2 function returns a value based on whether a specified expression is NULL or not. If expr1 is not NULL, then NVL2 returns expr2. If expr1 is NULL, then NVL2 returns expr3.

Examples

See Also

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

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

ADDDATE(date,INTERVAL expr unit), ADDDATE(expr,days)

Description

When invoked with the INTERVAL form of the second argument, ADDDATE() is a synonym for . The related function is a synonym for . For information on the INTERVAL unit argument, see the discussion for .

When invoked with the days form of the second argument, MariaDB treats it as an integer number of days to be added to expr.

Examples

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

DAYOFWEEK

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

Syntax

DAYOFWEEK(date)

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.

This contrasts with which follows a different index numbering (0 = Monday, 1 = Tuesday, ... 6 = Sunday).

Examples

This page is licensed: GPLv2, originally from

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

LOCALTIME

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

Syntax

LOCALTIME
LOCALTIME([precision])

Description

LOCALTIME and LOCALTIME() are synonyms for .

See Also

This page is licensed: GPLv2, originally from

MICROSECOND

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

Syntax

MICROSECOND(expr)

Description

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

If expr is a time with no microseconds, zero is returned, while if expr is a date with no time, zero with a warning is returned.

Examples

See Also

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

MONTHNAME(date)

Description

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

Examples

Changing the locale:

This page is licensed: GPLv2, originally from

TIME_TO_SEC

Syntax

TIME_TO_SEC(time)

Description

Returns the time argument, converted to seconds.

The value returned by TIME_TO_SEC is of type . The returned value preserves microseconds of the argument. See also .

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

SUBTIME

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

Syntax

SUBTIME(expr1,expr2)

Description

SUBTIME() returns expr1 - expr2 expressed as a value in the same format as expr1. expr1 is a time or datetime expression, and expr2 is a time expression.

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

ATAN

Calculate arc tangent. This function returns the angle in radians whose tangent is the given number.

Syntax

ATAN(X)

Description

Returns the arc tangent of X, that is, the value whose tangent is X.

Examples

This page is licensed: GPLv2, originally from

STD

Calculate population standard deviation. This function returns the square root of the population variance. It is a synonym for STDDEV_POP().

Syntax

Description

Returns the population standard deviation of

STDDEV

Calculate population standard deviation. This function is a synonym for STD() and STDDEV_POP(), returning the square root of the population variance.

Syntax

Description

Returns the population standard deviation of

STDDEV_POP

Calculate population standard deviation. This function computes the standard deviation assuming the set of values represents the entire population.

Syntax

Description

Returns the population standard deviation of

VAR_POP

Calculate population variance. This function computes the statistical variance for a set of values assumed to be the entire population.

Syntax

Description

Returns the population standard variance of expr

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

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

Description

The EXTRACT() function extracts the required unit from the date. See for a complete list of permitted units.

TO_DAYS

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

Syntax

Description

Given a date date, returns the number of days since the start of the current calendar (0000-00-00).

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 .

Syntax

UTC_TIMESTAMP

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

Syntax

Description

Returns the current date and time as a value in YYYY-MM-DD HH:MM:SS

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

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

ASIN

Calculate arc cosine. This function returns the angle in radians whose cosine is the given number.

Syntax

Description

Returns the arc sine of X, that is, the value whose sine is X. Returns NULL

YEARWEEK

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

Syntax

Description

Returns year and week for a date. The mode argument works exactly like the mode argument to . 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.

ACOS

Calculate arc cosine. This function returns the angle in radians whose cosine is the given number.

Syntax

Description

Returns the arc cosine of X, that is, the value whose cosine is

NULLIF(expr1,expr2)
SELECT NULLIF(1,1);
+-------------+
| NULLIF(1,1) |
+-------------+
|        NULL |
+-------------+

SELECT NULLIF(1,2);
+-------------+
| NULLIF(1,2) |
+-------------+
|           1 |
+-------------+
SELECT CONVERT_TZ('2016-01-01 12:00:00','+00:00','+10:00');
+-----------------------------------------------------+
| CONVERT_TZ('2016-01-01 12:00:00','+00:00','+10:00') |
+-----------------------------------------------------+
| 2016-01-01 22:00:00                                 |
+-----------------------------------------------------+
SELECT CONVERT_TZ('2016-01-01 12:00:00','GMT','Africa/Johannesburg');
+---------------------------------------------------------------+
| CONVERT_TZ('2016-01-01 12:00:00','GMT','Africa/Johannesburg') |
+---------------------------------------------------------------+
| 2016-01-01 14:00:00                                           |
+---------------------------------------------------------------+
SELECT CONVERT_TZ('1969-12-31 22:00:00','+00:00','+10:00');
+-----------------------------------------------------+
| CONVERT_TZ('1969-12-31 22:00:00','+00:00','+10:00') |
+-----------------------------------------------------+
| 1969-12-31 22:00:00                                 |
+-----------------------------------------------------+
SELECT CURDATE() +0;
+--------------+
| CURDATE() +0 |
+--------------+
|     20190305 |
+--------------+
SELECT CURDATE() - INTERVAL 5 DAY;
+----------------------------+
| CURDATE() - INTERVAL 5 DAY |
+----------------------------+
| 2019-02-28                 |
+----------------------------+
CURTIME([precision])
SELECT CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 12:45:39  |
+-----------+

SELECT CURTIME() + 0;
+---------------+
| CURTIME() + 0 |
+---------------+
| 124545.000000 |
+---------------+
SELECT CURTIME(2);
+-------------+
| CURTIME(2)  |
+-------------+
| 09:49:08.09 |
+-------------+
SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
+-----------------------------------------+
| DATE_SUB('1998-01-02', INTERVAL 31 DAY) |
+-----------------------------------------+
| 1997-12-02                              |
+-----------------------------------------+
SELECT DATE_SUB('2005-01-01 00:00:00', INTERVAL '1 1:1:1' DAY_SECOND);
+----------------------------------------------------------------+
| DATE_SUB('2005-01-01 00:00:00', INTERVAL '1 1:1:1' DAY_SECOND) |
+----------------------------------------------------------------+
| 2004-12-30 22:58:59                                            |
+----------------------------------------------------------------+
CREATE TABLE t1 (d DATETIME);
INSERT INTO t1 VALUES
    ("2007-01-30 21:31:07"),
    ("1983-10-15 06:42:51"),
    ("2011-04-21 12:34:56"),
    ("2011-10-30 06:31:41"),
    ("2011-01-30 14:03:25"),
    ("2004-10-07 11:19:34");
SELECT 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 |
+---------------------+-------------------+
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 |
+---------------------+
FORMAT_PICO_TIME(time_val)
SELECT
    FORMAT_PICO_TIME(43) AS ps,
    FORMAT_PICO_TIME(4321) AS ns, 
    FORMAT_PICO_TIME(43211234) AS us,
    FORMAT_PICO_TIME(432112344321) AS ms,
    FORMAT_PICO_TIME(43211234432123) AS s,
    FORMAT_PICO_TIME(432112344321234) AS m,
    FORMAT_PICO_TIME(4321123443212345) AS h,
    FORMAT_PICO_TIME(432112344321234545) AS d;
+--------+---------+----------+-----------+---------+----------+--------+--------+
| ps     | ns      | us       | ms        | s       | m        | h      | d      |
+--------+---------+----------+-----------+---------+----------+--------+--------+
|  43 ps | 4.32 ns | 43.21 us | 432.11 ms | 43.21 s | 7.20 min | 1.20 h | 5.00 d |
+--------+---------+----------+-----------+---------+----------+--------+--------+
SELECT LAST_DAY('2003-02-05');
+------------------------+
| LAST_DAY('2003-02-05') |
+------------------------+
| 2003-02-28             |
+------------------------+

SELECT LAST_DAY('2004-02-05');
+------------------------+
| LAST_DAY('2004-02-05') |
+------------------------+
| 2004-02-29             |
+------------------------+

SELECT LAST_DAY('2004-01-01 01:01:01');
+---------------------------------+
| LAST_DAY('2004-01-01 01:01:01') |
+---------------------------------+
| 2004-01-31                      |
+---------------------------------+

SELECT LAST_DAY('2003-03-32');
+------------------------+
| LAST_DAY('2003-03-32') |
+------------------------+
| NULL                   |
+------------------------+
1 row in set, 1 warning (0.00 sec)

Warning (Code 1292): Incorrect datetime value: '2003-03-32'
SELECT 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             |
+------------------+
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 PERIOD_ADD(200801,2);
+----------------------+
| PERIOD_ADD(200801,2) |
+----------------------+
|               200803 |
+----------------------+

SELECT PERIOD_ADD(6910,2);
+--------------------+
| PERIOD_ADD(6910,2) |
+--------------------+
|             206912 |
+--------------------+

SELECT PERIOD_ADD(7010,2);
+--------------------+
| PERIOD_ADD(7010,2) |
+--------------------+
|             197012 |
+--------------------+
SELECT PERIOD_DIFF(200802,200703);
+----------------------------+
| PERIOD_DIFF(200802,200703) |
+----------------------------+
|                         11 |
+----------------------------+

SELECT PERIOD_DIFF(6902,6803);
+------------------------+
| PERIOD_DIFF(6902,6803) |
+------------------------+
|                     11 |
+------------------------+

SELECT PERIOD_DIFF(7002,6803);
+------------------------+
| PERIOD_DIFF(7002,6803) |
+------------------------+
|                  -1177 |
+------------------------+
SELECT 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' |
+---------+------+-------------------------------------------+
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                                                      |
+----------------------------------------------------------------------+
IFNULL(expr1,expr2)
NVL(expr1,expr2)
IFNULL function
CONNECT data types
NOW()
Microseconds in MariaDB
fill_help_tables.sql

NVL

Synonym for IFNULL. This Oracle-compatible function returns the first argument if it is not NULL, or the second argument if the first is NULL.

NVL is a synonym for IFNULL.

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

aggregate function
GROUP BY
window function
aggregate function
window function
VAR_POP
STDDEV_POP
fill_help_tables.sql
CASE statement
CASE statement
IF() function
Operator Precedence
fill_help_tables.sql
IF statement
IF statement
fill_help_tables.sql
NULLIF function
  • CONNECT data types

  • NULL values
    IS NULL operator
    IS NOT NULL operator
    COALESCE function
    fill_help_tables.sql
    IFNULL (or NVL)
    SELECT ADDDATE('2008-01-02', 31);
    +---------------------------+
    | ADDDATE('2008-01-02', 31) |
    +---------------------------+
    | 2008-02-02                |
    +---------------------------+
    DATE_ADD()
    SUBDATE()
    DATE_SUB()
    DATE_ADD()
    fill_help_tables.sql
    SELECT DAYNAME('2007-02-03');
    +-----------------------+
    | DAYNAME('2007-02-03') |
    +-----------------------+
    | Saturday              |
    +-----------------------+
    fill_help_tables.sql
    SELECT DAYOFWEEK('2007-02-03');
    +-------------------------+
    | DAYOFWEEK('2007-02-03') |
    +-------------------------+
    |                       7 |
    +-------------------------+
    WEEKDAY()
    fill_help_tables.sql
    Date and Time Units
    Date and Time Literals
    EXTRACT()
    fill_help_tables.sql
    Microseconds in MariaDB
    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
    SELECT MONTHNAME('2019-02-03');
    +-------------------------+
    | MONTHNAME('2019-02-03') |
    +-------------------------+
    | February                |
    +-------------------------+
    fill_help_tables.sql
    SELECT TIME_TO_SEC('22:23:00');
    +-------------------------+
    | TIME_TO_SEC('22:23:00') |
    +-------------------------+
    |                   80580 |
    +-------------------------+
    DOUBLE
    Microseconds in MariaDB
    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 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                              |
    +-----------------------------------------------+
    fill_help_tables.sql
    fill_help_tables.sql
    SELECT ATAN(2);
    +--------------------+
    | ATAN(2)            |
    +--------------------+
    | 1.1071487177940904 |
    +--------------------+
    
    SELECT ATAN(-2);
    +---------------------+
    | ATAN(-2)            |
    +---------------------+
    | -1.1071487177940904 |
    +---------------------+
    fill_help_tables.sql
    expr
    . This is an extension to standard SQL. The standard SQL function
    can be used instead.

    It is an aggregate function, and so can be used with the GROUP BY clause.

    STD() can be used as a window function.

    This function returns NULL if there were no matching rows.

    Examples

    As an aggregate function:

    As a window function:

    See Also

    • STDDEV_POP (equivalent, standard SQL)

    • STDDEV (equivalent, Oracle-compatible non-standard SQL)

    • VAR_POP (variance)

    • STDDEV_SAMP (sample standard deviation)

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

    STDDEV_POP()
    expr
    . This function is provided for compatibility with Oracle. The standard SQL function
    can be used instead.

    It is an aggregate function, and so can be used with the GROUP BY clause.

    STDDEV() can be used as a window function.

    This function returns NULL if there were no matching rows.

    Examples

    As an aggregate function:

    As a window function:

    See Also

    • STDDEV_POP (equivalent, standard SQL)

    • STD (equivalent, non-standard SQL)

    • VAR_POP (variance)

    • STDDEV_SAMP (sample standard deviation)

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

    STDDEV_POP()
    expr
    (the square root of
    ). You can also use
    or
    , which are equivalent but not standard SQL.

    It is an aggregate function, and so can be used with the GROUP BY clause.

    STDDEV_POP() can be used as a window function.

    STDDEV_POP() returns NULL if there were no matching rows.

    Examples

    As an aggregate function:

    As a window function:

    See Also

    • STD (equivalent, non-standard SQL)

    • STDDEV (equivalent, Oracle-compatible non-standard SQL)

    • VAR_POP (variance)

    • STDDEV_SAMP (sample standard deviation)

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

    VAR_POP()
    STD()
    STDDEV()
    . It considers rows as the whole population, not as a sample, so it has the number of rows as the denominator. You can also use
    , which is equivalent but is not standard SQL.

    Variance is calculated by

    • working out the mean for the set;

    • for each number, subtracting the mean and squaring the result;

    • calculating the average of the resulting differences.

    It is an aggregate function, and so can be used with the GROUP BY clause.

    VAR_POP() can be used as a window function.

    VAR_POP() returns NULL if there were no matching rows.

    Examples

    As an aggregate function:

    As a window function:

    See Also

    • VARIANCE (equivalent, non-standard SQL)

    • STDDEV_POP (population standard deviation)

    • STDDEV_SAMP (sample standard deviation)

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

    VARIANCE()
    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
    HOUR() 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

    • Date and Time Units

    • Date and Time Literals

    • HOUR()

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

    Date and Time Units

    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 FROM_DAYS() function.

    Examples

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

    TO_DAYS(date)
    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 fill_help_tables.sql

    TIMESTAMP(expr), TIMESTAMP(expr1,expr2)
    TIMESTAMP
    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 Microseconds in MariaDB.

    Examples

    With precision:

    See Also

    • Time Zones

    • Microseconds in MariaDB

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

    UTC
    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 Microseconds in MariaDB.

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

    In addition, changing the timestamp system variable with a SET timestamp statement affects the value returned by NOW() but not by SYSDATE(). This means that timestamp settings in the binary log have no effect on invocations of SYSDATE().

    Because SYSDATE() can return different values even within the same statement, and is not affected by SET TIMESTAMP, it is non-deterministic and therefore unsafe for replication if statement-based binary logging is used. If that is a problem, you can use row-based logging, or start the server with the --sysdate-is-now mariadbd option to cause SYSDATE() to be an alias for NOW(). The non-deterministic nature of SYSDATE() also means that indexes cannot be used for evaluating expressions that refer to it, and that statements using the SYSDATE() function are unsafe for statement-based replication.

    Examples

    Difference between NOW() and SYSDATE():

    With precision:

    See Also

    • Microseconds in MariaDB

    • timestamp server system variable

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

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

    Examples

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

    TO_SECONDS(expr)
    datetime
    if X is not in the range -1 to 1.

    Examples

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

    ASIN(X)

    Examples

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

    YEARWEEK(date), YEARWEEK(date,mode)
    WEEK()
    SELECT YEARWEEK('1987-01-01');
    +------------------------+
    | YEARWEEK('1987-01-01') |
    +------------------------+
    |                 198652 |
    +------------------------+
    X
    . Returns
    NULL
    if
    X
    is not in the range
    -1
    to
    1
    .

    Examples

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

    ACOS(X)
    CREATE OR REPLACE TABLE stats (category VARCHAR(2), x INT);
    
    INSERT INTO stats VALUES 
      ('a',1),('a',2),('a',3),
      ('b',11),('b',12),('b',20),('b',30),('b',60);
    
    SELECT category, STDDEV_POP(x), STDDEV_SAMP(x), VAR_POP(x) 
      FROM stats GROUP BY category;
    +----------+---------------+----------------+------------+
    | category | STDDEV_POP(x) | STDDEV_SAMP(x) | VAR_POP(x) |
    +----------+---------------+----------------+------------+
    | a        |        0.8165 |         1.0000 |     0.6667 |
    | b        |       18.0400 |        20.1693 |   325.4400 |
    +----------+---------------+----------------+------------+
    CREATE OR REPLACE TABLE student_test (name CHAR(10), test CHAR(10), score TINYINT);
    
    INSERT INTO student_test VALUES 
        ('Chun', 'SQL', 75), ('Chun', 'Tuning', 73), 
        ('Esben', 'SQL', 43), ('Esben', 'Tuning', 31), 
        ('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88), 
        ('Tatiana', 'SQL', 87);
    
    SELECT name, test, score, VAR_SAMP(score) 
      OVER (PARTITION BY test) AS variance_results FROM student_test;
    +---------+--------+-------+------------------+
    | name    | test   | score | variance_results |
    +---------+--------+-------+------------------+
    | Chun    | SQL    |    75 |         382.9167 |
    | Chun    | Tuning |    73 |         873.0000 |
    | Esben   | SQL    |    43 |         382.9167 |
    | Esben   | Tuning |    31 |         873.0000 |
    | Kaolin  | SQL    |    56 |         382.9167 |
    | Kaolin  | Tuning |    88 |         873.0000 |
    | Tatiana | SQL    |    87 |         382.9167 |
    +---------+--------+-------+------------------+
    SELECT CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END;
    +------------------------------------------------------------+
    | CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END |
    +------------------------------------------------------------+
    | one                                                        |
    +------------------------------------------------------------+
    
    SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;
    +--------------------------------------------+
    | CASE WHEN 1>0 THEN 'true' ELSE 'false' END |
    +--------------------------------------------+
    | true                                       |
    +--------------------------------------------+
    
    
    SELECT CASE BINARY 'B' WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;
    +-----------------------------------------------------+
    | CASE BINARY 'B' WHEN 'a' THEN 1 WHEN 'b' THEN 2 END |
    +-----------------------------------------------------+
    |                                                NULL |
    +-----------------------------------------------------+
    SELECT 
      CASE true 
         WHEN (1=1) THEN '1=1' -- result is returned 
         WHEN (1=1 OR 2=2) THEN '1=1 OR 2=2' -- condition not processed
         ELSE 'else'
      END 
    ;
    +-------------------------------------------------------------------------------------+
    | CASE true WHEN (1=1) THEN '1=1' WHEN (1=1 OR 2=2) THEN '1=1 OR 2=2' ELSE 'else' END |
    +-------------------------------------------------------------------------------------+
    + 1=1                                                                                 +
    +-------------------------------------------------------------------------------------+
    SELECT IF(1>2,2,3);
    +-------------+
    | IF(1>2,2,3) |
    +-------------+
    |           3 |
    +-------------+
    SELECT IF(1<2,'yes','no');
    +--------------------+
    | IF(1<2,'yes','no') |
    +--------------------+
    | yes                |
    +--------------------+
    SELECT IF(STRCMP('test','test1'),'no','yes');
    +---------------------------------------+
    | IF(STRCMP('test','test1'),'no','yes') |
    +---------------------------------------+
    | no                                    |
    +---------------------------------------+
    SELECT IFNULL(1,0); 
    +-------------+
    | IFNULL(1,0) |
    +-------------+
    |           1 |
    +-------------+
    
    SELECT IFNULL(NULL,10);
    +-----------------+
    | IFNULL(NULL,10) |
    +-----------------+
    |              10 |
    +-----------------+
    
    SELECT IFNULL(1/0,10);
    +----------------+
    | IFNULL(1/0,10) |
    +----------------+
    |        10.0000 |
    +----------------+
    
    SELECT IFNULL(1/0,'yes');
    +-------------------+
    | IFNULL(1/0,'yes') |
    +-------------------+
    | yes               |
    +-------------------+
    SELECT NVL2(NULL,1,2);
    +----------------+
    | NVL2(NULL,1,2) |
    +----------------+
    |              2 |
    +----------------+
    
    SELECT NVL2('x',1,2);
    +---------------+
    | NVL2('x',1,2) |
    +---------------+
    |             1 |
    +---------------+
    SELECT DATE_ADD('2008-01-02', INTERVAL 31 DAY);
    +-----------------------------------------+
    | DATE_ADD('2008-01-02', INTERVAL 31 DAY) |
    +-----------------------------------------+
    | 2008-02-02                              |
    +-----------------------------------------+
    
    SELECT ADDDATE('2008-01-02', INTERVAL 31 DAY);
    +----------------------------------------+
    | ADDDATE('2008-01-02', INTERVAL 31 DAY) |
    +----------------------------------------+
    | 2008-02-02                             |
    +----------------------------------------+
    CREATE TABLE t1 (d DATETIME);
    INSERT INTO t1 VALUES
        ("2007-01-30 21:31:07"),
        ("1983-10-15 06:42:51"),
        ("2011-04-21 12:34:56"),
        ("2011-10-30 06:31:41"),
        ("2011-01-30 14:03:25"),
        ("2004-10-07 11:19:34");
    SELECT d, ADDDATE(d, 10) from t1;
    +---------------------+---------------------+
    | d                   | ADDDATE(d, 10)      |
    +---------------------+---------------------+
    | 2007-01-30 21:31:07 | 2007-02-09 21:31:07 |
    | 1983-10-15 06:42:51 | 1983-10-25 06:42:51 |
    | 2011-04-21 12:34:56 | 2011-05-01 12:34:56 |
    | 2011-10-30 06:31:41 | 2011-11-09 06:31:41 |
    | 2011-01-30 14:03:25 | 2011-02-09 14:03:25 |
    | 2004-10-07 11:19:34 | 2004-10-17 11:19:34 |
    +---------------------+---------------------+
    
    SELECT d, ADDDATE(d, INTERVAL 10 HOUR) from t1;
    +---------------------+------------------------------+
    | d                   | ADDDATE(d, INTERVAL 10 HOUR) |
    +---------------------+------------------------------+
    | 2007-01-30 21:31:07 | 2007-01-31 07:31:07          |
    | 1983-10-15 06:42:51 | 1983-10-15 16:42:51          |
    | 2011-04-21 12:34:56 | 2011-04-21 22:34:56          |
    | 2011-10-30 06:31:41 | 2011-10-30 16:31:41          |
    | 2011-01-30 14:03:25 | 2011-01-31 00:03:25          |
    | 2004-10-07 11:19:34 | 2004-10-07 21:19:34          |
    +---------------------+------------------------------+
    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                 |
    +-----------------------+
    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 |
    +---------------------+------------+--------------+------------+
    SELECT HOUR('10:05:03');
    +------------------+
    | HOUR('10:05:03') |
    +------------------+
    |               10 |
    +------------------+
    
    SELECT HOUR('272:59:59');
    +-------------------+
    | HOUR('272:59:59') |
    +-------------------+
    |               272 |
    +-------------------+
    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' |
    +---------+------+----------------------------------------------+
    SET lc_time_names = 'fr_CA';
    
    SELECT MONTHNAME('2019-05-21');
    +-------------------------+
    | MONTHNAME('2019-05-21') |
    +-------------------------+
    | mai                     |
    +-------------------------+
    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 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                        |
    +-----------------------------------+
    STD(expr)
    CREATE OR REPLACE TABLE stats (category VARCHAR(2), x INT);
    
    INSERT INTO stats VALUES 
      ('a',1),('a',2),('a',3),
      ('b',11),('b',12),('b',20),('b',30),('b',60);
    
    SELECT category, STDDEV_POP(x), STDDEV_SAMP(x), VAR_POP(x) 
      FROM stats GROUP BY category;
    +----------+---------------+----------------+------------+
    | category | STDDEV_POP(x) | STDDEV_SAMP(x) | VAR_POP(x) |
    +----------+---------------+----------------+------------+
    | a        |        0.8165 |         1.0000 |     0.6667 |
    | b        |       18.0400 |        20.1693 |   325.4400 |
    +----------+---------------+----------------+------------+
    CREATE OR REPLACE TABLE student_test (name CHAR(10), test CHAR(10), score TINYINT);
    
    INSERT INTO student_test VALUES 
        ('Chun', 'SQL', 75), ('Chun', 'Tuning', 73), 
        ('Esben', 'SQL', 43), ('Esben', 'Tuning', 31), 
        ('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88), 
        ('Tatiana', 'SQL', 87);
    
    SELECT name, test, score, STDDEV_POP(score) 
      OVER (PARTITION BY test) AS stddev_results FROM student_test;
    +---------+--------+-------+----------------+
    | name    | test   | score | stddev_results |
    +---------+--------+-------+----------------+
    | Chun    | SQL    |    75 |        16.9466 |
    | Chun    | Tuning |    73 |        24.1247 |
    | Esben   | SQL    |    43 |        16.9466 |
    | Esben   | Tuning |    31 |        24.1247 |
    | Kaolin  | SQL    |    56 |        16.9466 |
    | Kaolin  | Tuning |    88 |        24.1247 |
    | Tatiana | SQL    |    87 |        16.9466 |
    +---------+--------+-------+----------------+
    STDDEV(expr)
    CREATE OR REPLACE TABLE stats (category VARCHAR(2), x INT);
    
    INSERT INTO stats VALUES 
      ('a',1),('a',2),('a',3),
      ('b',11),('b',12),('b',20),('b',30),('b',60);
    
    SELECT category, STDDEV_POP(x), STDDEV_SAMP(x), VAR_POP(x) 
      FROM stats GROUP BY category;
    +----------+---------------+----------------+------------+
    | category | STDDEV_POP(x) | STDDEV_SAMP(x) | VAR_POP(x) |
    +----------+---------------+----------------+------------+
    | a        |        0.8165 |         1.0000 |     0.6667 |
    | b        |       18.0400 |        20.1693 |   325.4400 |
    +----------+---------------+----------------+------------+
    CREATE OR REPLACE TABLE student_test (name CHAR(10), test CHAR(10), score TINYINT);
    
    INSERT INTO student_test VALUES 
        ('Chun', 'SQL', 75), ('Chun', 'Tuning', 73), 
        ('Esben', 'SQL', 43), ('Esben', 'Tuning', 31), 
        ('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88), 
        ('Tatiana', 'SQL', 87);
    
    SELECT name, test, score, STDDEV_POP(score) 
      OVER (PARTITION BY test) AS stddev_results FROM student_test;
    +---------+--------+-------+----------------+
    | name    | test   | score | stddev_results |
    +---------+--------+-------+----------------+
    | Chun    | SQL    |    75 |        16.9466 |
    | Chun    | Tuning |    73 |        24.1247 |
    | Esben   | SQL    |    43 |        16.9466 |
    | Esben   | Tuning |    31 |        24.1247 |
    | Kaolin  | SQL    |    56 |        16.9466 |
    | Kaolin  | Tuning |    88 |        24.1247 |
    | Tatiana | SQL    |    87 |        16.9466 |
    +---------+--------+-------+----------------+
    STDDEV_POP(expr)
    CREATE OR REPLACE TABLE stats (category VARCHAR(2), x INT);
    
    INSERT INTO stats VALUES 
      ('a',1),('a',2),('a',3),
      ('b',11),('b',12),('b',20),('b',30),('b',60);
    
    SELECT category, STDDEV_POP(x), STDDEV_SAMP(x), VAR_POP(x) 
      FROM stats GROUP BY category;
    +----------+---------------+----------------+------------+
    | category | STDDEV_POP(x) | STDDEV_SAMP(x) | VAR_POP(x) |
    +----------+---------------+----------------+------------+
    | a        |        0.8165 |         1.0000 |     0.6667 |
    | b        |       18.0400 |        20.1693 |   325.4400 |
    +----------+---------------+----------------+------------+
    CREATE OR REPLACE TABLE student_test (name CHAR(10), test CHAR(10), score TINYINT);
    
    INSERT INTO student_test VALUES 
        ('Chun', 'SQL', 75), ('Chun', 'Tuning', 73), 
        ('Esben', 'SQL', 43), ('Esben', 'Tuning', 31), 
        ('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88), 
        ('Tatiana', 'SQL', 87);
    
    SELECT name, test, score, STDDEV_POP(score) 
      OVER (PARTITION BY test) AS stddev_results FROM student_test;
    +---------+--------+-------+----------------+
    | name    | test   | score | stddev_results |
    +---------+--------+-------+----------------+
    | Chun    | SQL    |    75 |        16.9466 |
    | Chun    | Tuning |    73 |        24.1247 |
    | Esben   | SQL    |    43 |        16.9466 |
    | Esben   | Tuning |    31 |        24.1247 |
    | Kaolin  | SQL    |    56 |        16.9466 |
    | Kaolin  | Tuning |    88 |        24.1247 |
    | Tatiana | SQL    |    87 |        16.9466 |
    +---------+--------+-------+----------------+
    VAR_POP(expr)
    CREATE TABLE v(i tinyint);
    
    INSERT INTO v VALUES(101),(99);
    
    SELECT VAR_POP(i) FROM v;
    +------------+
    | VAR_POP(i) |
    +------------+
    |     1.0000 |
    +------------+
    
    INSERT INTO v VALUES(120),(80);
    
    SELECT VAR_POP(i) FROM v;
    +------------+
    | VAR_POP(i) |
    +------------+
    |   200.5000 |
    +------------+
    CREATE OR REPLACE TABLE stats (category VARCHAR(2), x INT);
    
    INSERT INTO stats VALUES 
      ('a',1),('a',2),('a',3),
      ('b',11),('b',12),('b',20),('b',30),('b',60);
    
    SELECT category, STDDEV_POP(x), STDDEV_SAMP(x), VAR_POP(x) 
      FROM stats GROUP BY category;
    +----------+---------------+----------------+------------+
    | category | STDDEV_POP(x) | STDDEV_SAMP(x) | VAR_POP(x) |
    +----------+---------------+----------------+------------+
    | a        |        0.8165 |         1.0000 |     0.6667 |
    | b        |       18.0400 |        20.1693 |   325.4400 |
    +----------+---------------+----------------+------------+
    CREATE OR REPLACE TABLE student_test (name CHAR(10), test CHAR(10), score TINYINT);
    
    INSERT INTO student_test VALUES 
        ('Chun', 'SQL', 75), ('Chun', 'Tuning', 73), 
        ('Esben', 'SQL', 43), ('Esben', 'Tuning', 31), 
        ('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88), 
        ('Tatiana', 'SQL', 87);
    
    SELECT name, test, score, VAR_POP(score) 
      OVER (PARTITION BY test) AS variance_results FROM student_test;
    +---------+--------+-------+------------------+
    | name    | test   | score | variance_results |
    +---------+--------+-------+------------------+
    | Chun    | SQL    |    75 |         287.1875 |
    | Esben   | SQL    |    43 |         287.1875 |
    | Kaolin  | SQL    |    56 |         287.1875 |
    | Tatiana | SQL    |    87 |         287.1875 |
    | Chun    | Tuning |    73 |         582.0000 |
    | Esben   | Tuning |    31 |         582.0000 |
    | Kaolin  | Tuning |    88 |         582.0000 |
    +---------+--------+-------+------------------+
    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                                                     |
    +--------------------------------------------------------------------------------+
    EXTRACT(unit FROM date)
    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 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 |
    +-----------------+
    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                        |
    +--------------------------------------------+
    UTC_TIMESTAMP
    UTC_TIMESTAMP([precision])
    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 |
    +--------------------------+
    SYSDATE([precision])
    SELECT NOW(), SLEEP(2), NOW();
    +---------------------+----------+---------------------+
    | NOW()               | SLEEP(2) | NOW()               |
    +---------------------+----------+---------------------+
    | 2010-03-27 13:23:40 |        0 | 2010-03-27 13:23:40 |
    +---------------------+----------+---------------------+
    
    SELECT SYSDATE(), SLEEP(2), SYSDATE();
    +---------------------+----------+---------------------+
    | SYSDATE()           | SLEEP(2) | SYSDATE()           |
    +---------------------+----------+---------------------+
    | 2010-03-27 13:23:52 |        0 | 2010-03-27 13:23:54 |
    +---------------------+----------+---------------------+
    SELECT SYSDATE(4);
    +--------------------------+
    | SYSDATE(4)               |
    +--------------------------+
    | 2018-07-10 10:17:13.1689 |
    +--------------------------+
    SELECT TO_SECONDS('2013-06-13');
    +--------------------------+
    | TO_SECONDS('2013-06-13') |
    +--------------------------+
    |              63538300800 |
    +--------------------------+
    
    SELECT TO_SECONDS('2013-06-13 21:45:13');
    +-----------------------------------+
    | TO_SECONDS('2013-06-13 21:45:13') |
    +-----------------------------------+
    |                       63538379113 |
    +-----------------------------------+
    
    SELECT TO_SECONDS(NOW());
    +-------------------+
    | TO_SECONDS(NOW()) |
    +-------------------+
    |       63543530875 |
    +-------------------+
    
    SELECT TO_SECONDS(20130513);
    +----------------------+
    | TO_SECONDS(20130513) |
    +----------------------+
    |          63535622400 |
    +----------------------+
    1 row in set (0.00 sec)
    
    SELECT TO_SECONDS(130513);
    +--------------------+
    | TO_SECONDS(130513) |
    +--------------------+
    |        63535622400 |
    +--------------------+
    SELECT ASIN(0.2);
    +--------------------+
    | ASIN(0.2)          |
    +--------------------+
    | 0.2013579207903308 |
    +--------------------+
    
    SELECT ASIN('foo');
    +-------------+
    | ASIN('foo') |
    +-------------+
    |           0 |
    +-------------+
    
    SHOW WARNINGS;
    +---------+------+-----------------------------------------+
    | Level   | Code | Message                                 |
    +---------+------+-----------------------------------------+
    | Warning | 1292 | Truncated incorrect DOUBLE value: 'foo' |
    +---------+------+-----------------------------------------+
    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)
    SELECT ACOS(1);
    +---------+
    | ACOS(1) |
    +---------+
    |       0 |
    +---------+
    
    SELECT ACOS(1.0001);
    +--------------+
    | ACOS(1.0001) |
    +--------------+
    |         NULL |
    +--------------+
    
    SELECT ACOS(0);
    +-----------------+
    | ACOS(0)         |
    +-----------------+
    | 1.5707963267949 |
    +-----------------+
    
    SELECT ACOS(0.234);
    +------------------+
    | ACOS(0.234)      |
    +------------------+
    | 1.33460644244679 |
    +------------------+

    COUNT

    Count rows or values. This function returns the number of rows that match criteria or the number of non-NULL values in an expression.

    Syntax

    COUNT(expr)

    Description

    Returns a count of the number of non-NULL values of expr in the rows retrieved by a SELECT statement. The result is a BIGINT value. It is an , and so can be used with the clause.

    COUNT(*) counts the total number of rows in a table.

    COUNT() returns 0 if there were no matching rows.

    COUNT() can be used as a .

    Examples

    example:

    As a

    See Also

    This page is licensed: GPLv2, originally from

    SUM

    Calculate the total sum. This function returns the sum of all values in a numeric expression, ignoring NULL values.

    Syntax

    Description

    Returns the sum of expr. If the return set has no rows, SUM() returnsNULL. The DISTINCT keyword can be used to sum only the distinct values of expr.

    SUM() can be used as a , although not with the DISTINCT specifier.

    Examples

    Commonly, SUM is used with a clause:

    The clause is required when using an aggregate function along with regular column data, otherwise the result will be a mismatch, as in the following common type of mistake:

    As a :

    See Also

    • (average)

    • (maximum)

    • (minimum)

    This page is licensed: GPLv2, originally from

    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

    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

    GET_FORMAT({DATE|DATETIME|TIME}, {'EUR'|'USA'|'JIS'|'ISO'|'INTERNAL'})

    Description

    Returns a format string. This function is useful in combination with the DATE_FORMAT() and the STR_TO_DATE() functions.

    Possible result formats are:

    Function Call
    Result Format

    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

    WEEKOFYEAR

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

    Syntax

    WEEKOFYEAR(date)

    Description

    Returns the calendar week of the date as a number in the range from 1 sqto 53. WEEKOFYEAR() is a compatibility function that is equivalent to .

    Examples

    This page is licensed: GPLv2, originally from

    ABS

    Calculate absolute value. This function returns the non-negative value of a number, removing any negative sign.

    Syntax

    ABS(X)

    Description

    Returns the absolute (non-negative) value of X. If X is not a number, it is converted to a numeric type.

    Examples

    See Also

    This page is licensed: GPLv2, originally from

    TRUNC

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

    Introduced in .

    Syntax

    TRUNC(date[,fmt])

    Description

    Returns a 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

    GROUP_CONCAT

    Concatenate strings from a group. This function joins non-NULL values from multiple rows into a single string, with an optional separator.

    Syntax

    Description

    This function returns a string result with the concatenated non-NULL values from a group. If any expr in GROUP_CONCAT evaluates to NULL, that tuple is not present in the list returned by GROUP_CONCAT.

    MAX

    Find the maximum value. This function returns the highest value in a set of values, applicable to numbers, strings, and dates.

    Syntax

    Description

    Returns the largest, or maximum, value of

    MIN

    Find the minimum value. This function returns the lowest value in a set of values, applicable to numbers, strings, and dates.

    Syntax

    Description

    Returns the minimum value of

    VARIANCE

    Calculate population variance. This function is a synonym for VAR_POP() and returns the variance of a set of values.

    Syntax

    Description

    Returns the population standard variance of expr

    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

    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

    TIMESTAMPDIFF

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

    Syntax

    Description

    Returns datetime_expr2

    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

    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

    SUM([DISTINCT] expr)
    aggregate function
    GROUP BY
    window function
    COUNT(DISTINCT)
    window function
    SELECT
    COUNT DISTINCT
    Window Functions
    fill_help_tables.sql
    window function
    GROUP BY
    GROUP BY
    window function
    AVG
    MAX
    MIN
    fill_help_tables.sql

    GET_FORMAT(DATETIME,'USA')

    '%Y-%m-%d %H.%i.%s'

    GET_FORMAT(DATETIME,'JIS')

    '%Y-%m-%d %H:%i:%s'

    GET_FORMAT(DATETIME,'ISO')

    '%Y-%m-%d %H:%i:%s'

    GET_FORMAT(DATETIME,'INTERNAL')

    '%Y%m%d%H%i%s'

    GET_FORMAT(TIME,'EUR')

    '%H.%i.%s'

    GET_FORMAT(TIME,'USA')

    '%h:%i:%s %p'

    GET_FORMAT(TIME,'JIS')

    '%H:%i:%s'

    GET_FORMAT(TIME,'ISO')

    '%H:%i:%s'

    GET_FORMAT(TIME,'INTERNAL')

    '%H%i%s'

    GET_FORMAT(DATE,'EUR')

    '%d.%m.%Y'

    GET_FORMAT(DATE,'USA')

    '%m.%d.%Y'

    GET_FORMAT(DATE,'JIS')

    '%Y-%m-%d'

    GET_FORMAT(DATE,'ISO')

    '%Y-%m-%d'

    GET_FORMAT(DATE,'INTERNAL')

    '%Y%m%d'

    GET_FORMAT(DATETIME,'EUR')

    '%Y-%m-%d %H.%i.%s'

    fill_help_tables.sql
    SELECT WEEKOFYEAR('2008-02-20');
    +--------------------------+
    | WEEKOFYEAR('2008-02-20') |
    +--------------------------+
    |                        8 |
    +--------------------------+
    WEEK(date,3)
    fill_help_tables.sql
    SIGN()
    fill_help_tables.sql

    It returns NULL if all arguments are NULL, or there are no matching rows.

    The maximum returned length in bytes is determined by the group_concat_max_len server system variable, which defaults to 1M.

    If group_concat_max_len <= 512, the return type is VARBINARY or VARCHAR; otherwise, the return type is BLOB or TEXT. The choice between binary or non-binary types depends from the input.

    The full syntax is as follows:

    DISTINCT eliminates duplicate values from the output string.

    ORDER BY determines the order of returned values.

    SEPARATOR specifies a separator between the values. The default separator is a comma (,). It is possible to avoid using a separator by specifying an empty string.

    LIMIT

    The LIMIT clause can be used with GROUP_CONCAT.

    Examples

    Get a readable list of MariaDB users from the mysql.user table:

    In the former example, DISTINCT is used because the same user may occur more than once. The new line () used as a SEPARATOR makes the results easier to read.

    Get a readable list of hosts from which each user can connect:

    The former example shows the difference between the GROUP_CONCAT's ORDER BY (which sorts the concatenated hosts), and the SELECT's ORDER BY (which sorts the rows).

    LIMIT can be used with GROUP_CONCAT, so, for example, given the following table:

    the following query:

    can be more simply rewritten as:

    NULLS:

    See Also

    • CONCAT()

    • CONCAT_WS()

    • SELECT

    • ORDER BY

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

    expr
    .
    MAX()
    can also take a string argument in which case it returns the maximum string value. The
    DISTINCT
    keyword can be used to find the maximum of the distinct values of
    expr
    , however, this produces the same result as omitting
    DISTINCT
    .

    Note that SET and ENUM fields are currently compared by their string value rather than their relative position in the set, so MAX() may produce a different highest result than ORDER BY DESC.

    It is an aggregate function, and so can be used with the GROUP BY clause.

    MAX() can be used as a window function.

    MAX() returns NULL if there were no matching rows.

    Not only ascending, but also descending indexes can be used to optimize MAX.

    Only ascending indexes can be used to optimize MAX.

    Examples

    MAX string:

    Be careful to avoid this common mistake, not grouping correctly and returning mismatched data:

    Difference between ORDER BY DESC and MAX():

    As a window function:

    See Also

    • AVG (average)

    • MIN (minimum)

    • SUM (sum total)

    • GREATEST() returns the largest value from a list

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

    expr
    .
    MIN()
    may take a string argument, in which case it returns the minimum string value. The
    DISTINCT
    keyword can be used to find the minimum of the distinct values of
    expr
    , however, this produces the same result as omitting
    DISTINCT
    .

    Note that SET and ENUM fields are currently compared by their string value rather than their relative position in the set, so MIN() may produce a different lowest result than ORDER BY ASC.

    It is an aggregate function, and so can be used with the GROUP BY clause.

    MIN() can be used as a window function.

    MIN() returns NULL if there were no matching rows.

    Not only ascending, but also descending indexes can be used to optimize MIN.

    Only ascending indexes can be used to optimize MIN.

    Examples

    MIN() with a string:

    Be careful to avoid this common mistake, not grouping correctly and returning mismatched data:

    Difference between ORDER BY ASC and MIN():

    As a window function:

    See Also

    • AVG (average)

    • MAX (maximum)

    • SUM (sum total)

    • LEAST() returns the smallest value from a list.

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

    . This is an extension to standard SQL. The standard SQL function
    can be used instead.

    Variance is calculated by

    • working out the mean for the set;

    • for each number, subtracting the mean and squaring the result;

    • calculating the average of the resulting differences.

    It is an aggregate function, and so can be used with the GROUP BY clause.

    VARIANCE() can be used as a window function.

    VARIANCE() returns NULL if there were no matching rows.

    Examples

    As an aggregate function:

    As a window function:

    See Also

    • VAR_POP (equivalent, standard SQL)

    • STDDEV_POP (population standard deviation)

    • STDDEV_SAMP (sample standard deviation)

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

    VAR_POP()
    -
    datetime_expr1
    , where
    datetime_expr1
    and
    datetime_expr2
    are date or datetime expressions. One expression may be a date and the other a datetime; a date value is treated as a datetime having the time part '00:00:00' where necessary. The unit for the result (an integer) is given by the unit argument. The legal values for unit are the same as those listed in the description of the
    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 fill_help_tables.sql

    TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)
    TIMESTAMPADD()
    ) 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
    (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
    CREATE TABLE student (name CHAR(10), test CHAR(10), score TINYINT); 
    
    INSERT INTO student VALUES 
      ('Chun', 'SQL', 75), ('Chun', 'Tuning', 73), 
      ('Esben', 'SQL', 43), ('Esben', 'Tuning', 31), 
      ('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88), 
      ('Tatiana', 'SQL', 87), ('Tatiana', 'Tuning', 83);
    
    SELECT COUNT(*) FROM student;
    +----------+
    | COUNT(*) |
    +----------+
    |        8 |
    +----------+
    SELECT COUNT(DISTINCT (name)) FROM student;
    +------------------------+
    | COUNT(DISTINCT (name)) |
    +------------------------+
    |                      4 |
    +------------------------+
    CREATE OR REPLACE TABLE student_test (name CHAR(10), test CHAR(10), score TINYINT);
    
    INSERT INTO student_test VALUES 
        ('Chun', 'SQL', 75), ('Chun', 'Tuning', 73), 
        ('Esben', 'SQL', 43), ('Esben', 'Tuning', 31), 
        ('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88), 
        ('Tatiana', 'SQL', 87);
    
    SELECT name, test, score, COUNT(score) OVER (PARTITION BY name) 
        AS tests_written FROM student_test;
    +---------+--------+-------+---------------+
    | name    | test   | score | tests_written |
    +---------+--------+-------+---------------+
    | Chun    | SQL    |    75 |             2 |
    | Chun    | Tuning |    73 |             2 |
    | Esben   | SQL    |    43 |             2 |
    | Esben   | Tuning |    31 |             2 |
    | Kaolin  | SQL    |    56 |             2 |
    | Kaolin  | Tuning |    88 |             2 |
    | Tatiana | SQL    |    87 |             1 |
    +---------+--------+-------+---------------+
    CREATE TABLE sales (sales_value INT);
    INSERT INTO sales VALUES(10),(20),(20),(40);
    
    SELECT SUM(sales_value) FROM sales;
    +------------------+
    | SUM(sales_value) |
    +------------------+
    |               90 |
    +------------------+
    
    SELECT SUM(DISTINCT(sales_value)) FROM sales;
    +----------------------------+
    | SUM(DISTINCT(sales_value)) |
    +----------------------------+
    |                         70 |
    +----------------------------+
    CREATE TABLE sales (name CHAR(10), month CHAR(10), units INT);
    
    INSERT INTO sales VALUES 
      ('Chun', 'Jan', 75), ('Chun', 'Feb', 73),
      ('Esben', 'Jan', 43), ('Esben', 'Feb', 31),
      ('Kaolin', 'Jan', 56), ('Kaolin', 'Feb', 88),
      ('Tatiana', 'Jan', 87), ('Tatiana', 'Feb', 83);
    
    SELECT name, SUM(units) FROM sales GROUP BY name;
    +---------+------------+
    | name    | SUM(units) |
    +---------+------------+
    | Chun    |        148 |
    | Esben   |         74 |
    | Kaolin  |        144 |
    | Tatiana |        170 |
    +---------+------------+
    SELECT name,SUM(units) FROM sales
    ;+------+------------+
    | name | SUM(units) |
    +------+------------+
    | Chun |        536 |
    +------+------------+
    CREATE OR REPLACE TABLE student_test (name CHAR(10), test CHAR(10), score TINYINT);
    INSERT INTO student_test VALUES 
        ('Chun', 'SQL', 75), ('Chun', 'Tuning', 73), 
        ('Esben', 'SQL', 43), ('Esben', 'Tuning', 31), 
        ('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88), 
        ('Tatiana', 'SQL', 87);
    
    SELECT name, test, score, SUM(score) OVER (PARTITION BY name) AS total_score FROM student_test;
    +---------+--------+-------+-------------+
    | name    | test   | score | total_score |
    +---------+--------+-------+-------------+
    | Chun    | SQL    |    75 |         148 |
    | Chun    | Tuning |    73 |         148 |
    | Esben   | SQL    |    43 |          74 |
    | Esben   | Tuning |    31 |          74 |
    | Kaolin  | SQL    |    56 |         144 |
    | Kaolin  | Tuning |    88 |         144 |
    | Tatiana | SQL    |    87 |          87 |
    +---------+--------+-------+-------------+
    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                                       |
    +--------------------------------------------------+
    CREATE TABLE t1 (d DATETIME);
    INSERT INTO t1 VALUES
        ("2007-01-30 21:31:07"),
        ("1983-10-15 06:42:51"),
        ("2011-04-21 12:34:56"),
        ("2011-10-30 06:31:41"),
        ("2011-01-30 14:03:25"),
        ("2004-10-07 11:19:34");
    SELECT * FROM t1;
    +---------------------+
    | d                   |
    +---------------------+
    | 2007-01-30 21:31:07 |
    | 1983-10-15 06:42:51 |
    | 2011-04-21 12:34:56 |
    | 2011-10-30 06:31:41 |
    | 2011-01-30 14:03:25 |
    | 2004-10-07 11:19:34 |
    +---------------------+
    SELECT d, WEEKOFYEAR(d), WEEK(d,3) from t1;
    +---------------------+---------------+-----------+
    | d                   | WEEKOFYEAR(d) | WEEK(d,3) |
    +---------------------+---------------+-----------+
    | 2007-01-30 21:31:07 |             5 |         5 |
    | 1983-10-15 06:42:51 |            41 |        41 |
    | 2011-04-21 12:34:56 |            16 |        16 |
    | 2011-10-30 06:31:41 |            43 |        43 |
    | 2011-01-30 14:03:25 |             4 |         4 |
    | 2004-10-07 11:19:34 |            41 |        41 |
    +---------------------+---------------+-----------+
    SELECT ABS(42);
    +---------+
    | ABS(42) |
    +---------+
    |      42 |
    +---------+
    
    SELECT ABS(-42);
    +----------+
    | ABS(-42) |
    +----------+
    |       42 |
    +----------+
    
    SELECT ABS(DATE '1994-01-01');
    +------------------------+
    | ABS(DATE '1994-01-01') |
    +------------------------+
    |               19940101 |
    +------------------------+
    GROUP_CONCAT(expr)
    GROUP_CONCAT([DISTINCT] expr [,expr ...]
                 [ORDER BY {unsigned_integer | col_name | expr}
                     [ASC | DESC] [,col_name ...]]
                 [SEPARATOR str_val]
                 [LIMIT {[offset,] row_count | row_count OFFSET offset}])
    SELECT student_name,
           GROUP_CONCAT(test_score)
           FROM student
           GROUP BY student_name;
    SELECT GROUP_CONCAT(DISTINCT User ORDER BY User SEPARATOR '\n')
       FROM mysql.user;
    SELECT User, GROUP_CONCAT(Host ORDER BY Host SEPARATOR ', ') 
       FROM mysql.user GROUP BY User ORDER BY User;
    CREATE TABLE d (dd DATE, cc INT);
    
    INSERT INTO d VALUES ('2017-01-01',1);
    INSERT INTO d VALUES ('2017-01-02',2);
    INSERT INTO d VALUES ('2017-01-04',3);
    SELECT SUBSTRING_INDEX(GROUP_CONCAT(CONCAT_WS(":",dd,cc) 
    ORDER BY cc DESC),",",1) FROM d;
    +----------------------------------------------------------------------------+
    | SUBSTRING_INDEX(GROUP_CONCAT(CONCAT_WS(":",dd,cc) ORDER BY cc DESC),",",1) |
    +----------------------------------------------------------------------------+
    | 2017-01-04:3                                                               |
    +----------------------------------------------------------------------------+
    SELECT GROUP_CONCAT(CONCAT_WS(":",dd,cc) ORDER BY cc DESC LIMIT 1) FROM d;
    +-------------------------------------------------------------+
    | GROUP_CONCAT(CONCAT_WS(":",dd,cc) ORDER BY cc DESC LIMIT 1) |
    +-------------------------------------------------------------+
    | 2017-01-04:3                                                |
    +-------------------------------------------------------------+
    CREATE OR REPLACE TABLE t1 (a int, b char);
    
    INSERT INTO t1 VALUES (1, 'a'), (2, NULL);
    
    SELECT GROUP_CONCAT(a, b) FROM t1;
    +--------------------+
    | GROUP_CONCAT(a, b) |
    +--------------------+
    | 1a                 |
    +--------------------+
    MAX([DISTINCT] expr)
    CREATE TABLE student (name CHAR(10), test CHAR(10), score TINYINT); 
    
    INSERT INTO student VALUES 
      ('Chun', 'SQL', 75), ('Chun', 'Tuning', 73), 
      ('Esben', 'SQL', 43), ('Esben', 'Tuning', 31), 
      ('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88), 
      ('Tatiana', 'SQL', 87), ('Tatiana', 'Tuning', 83);
    
    SELECT name, MAX(score) FROM student GROUP BY name;
    +---------+------------+
    | name    | MAX(score) |
    +---------+------------+
    | Chun    |         75 |
    | Esben   |         43 |
    | Kaolin  |         88 |
    | Tatiana |         87 |
    +---------+------------+
    SELECT MAX(name) FROM student;
    +-----------+
    | MAX(name) |
    +-----------+
    | Tatiana   |
    +-----------+
    SELECT name,test,MAX(SCORE) FROM student;
    +------+------+------------+
    | name | test | MAX(SCORE) |
    +------+------+------------+
    | Chun | SQL  |         88 |
    +------+------+------------+
    CREATE TABLE student2(name CHAR(10),grade ENUM('b','c','a'));
    
    INSERT INTO student2 VALUES('Chun','b'),('Esben','c'),('Kaolin','a');
    
    SELECT MAX(grade) FROM student2;
    +------------+
    | MAX(grade) |
    +------------+
    | c          |
    +------------+
    
    SELECT grade FROM student2 ORDER BY grade DESC LIMIT 1;
    +-------+
    | grade |
    +-------+
    | a     |
    +-------+
    CREATE OR REPLACE TABLE student_test (name CHAR(10), test CHAR(10), score TINYINT);
    INSERT INTO student_test VALUES 
        ('Chun', 'SQL', 75), ('Chun', 'Tuning', 73), 
        ('Esben', 'SQL', 43), ('Esben', 'Tuning', 31), 
        ('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88), 
        ('Tatiana', 'SQL', 87);
    
    SELECT name, test, score, MAX(score) 
      OVER (PARTITION BY name) AS highest_score FROM student_test;
    +---------+--------+-------+---------------+
    | name    | test   | score | highest_score |
    +---------+--------+-------+---------------+
    | Chun    | SQL    |    75 |            75 |
    | Chun    | Tuning |    73 |            75 |
    | Esben   | SQL    |    43 |            43 |
    | Esben   | Tuning |    31 |            43 |
    | Kaolin  | SQL    |    56 |            88 |
    | Kaolin  | Tuning |    88 |            88 |
    | Tatiana | SQL    |    87 |            87 |
    +---------+--------+-------+---------------+
    MIN([DISTINCT] expr)
    CREATE TABLE student (name CHAR(10), test CHAR(10), score TINYINT); 
    
    INSERT INTO student VALUES 
      ('Chun', 'SQL', 75), ('Chun', 'Tuning', 73), 
      ('Esben', 'SQL', 43), ('Esben', 'Tuning', 31), 
      ('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88), 
      ('Tatiana', 'SQL', 87), ('Tatiana', 'Tuning', 83);
    
    SELECT name, MIN(score) FROM student GROUP BY name;
    +---------+------------+
    | name    | MIN(score) |
    +---------+------------+
    | Chun    |         73 |
    | Esben   |         31 |
    | Kaolin  |         56 |
    | Tatiana |         83 |
    +---------+------------+
    SELECT MIN(name) FROM student;
    +-----------+
    | MIN(name) |
    +-----------+
    | Chun      |
    +-----------+
    SELECT name,test,MIN(score) FROM student;
    +------+------+------------+
    | name | test | MIN(score) |
    +------+------+------------+
    | Chun | SQL  |         31 |
    +------+------+------------+
    CREATE TABLE student2(name CHAR(10),grade ENUM('b','c','a'));
    
    INSERT INTO student2 VALUES('Chun','b'),('Esben','c'),('Kaolin','a');
    
    SELECT MIN(grade) FROM student2;
    +------------+
    | MIN(grade) |
    +------------+
    | a          |
    +------------+
    
    SELECT grade FROM student2 ORDER BY grade ASC LIMIT 1;
    +-------+
    | grade |
    +-------+
    | b     |
    +-------+
    CREATE OR REPLACE TABLE student_test (name CHAR(10), test CHAR(10), score TINYINT);
    INSERT INTO student_test VALUES 
        ('Chun', 'SQL', 75), ('Chun', 'Tuning', 73), 
        ('Esben', 'SQL', 43), ('Esben', 'Tuning', 31), 
        ('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88), 
        ('Tatiana', 'SQL', 87);
    
    
    SELECT name, test, score, MIN(score) 
      OVER (PARTITION BY name) AS lowest_score FROM student_test;
    +---------+--------+-------+--------------+
    | name    | test   | score | lowest_score |
    +---------+--------+-------+--------------+
    | Chun    | SQL    |    75 |           73 |
    | Chun    | Tuning |    73 |           73 |
    | Esben   | SQL    |    43 |           31 |
    | Esben   | Tuning |    31 |           31 |
    | Kaolin  | SQL    |    56 |           56 |
    | Kaolin  | Tuning |    88 |           56 |
    | Tatiana | SQL    |    87 |           87 |
    +---------+--------+-------+--------------+
    VARIANCE(expr)
    CREATE TABLE v(i tinyint);
    
    INSERT INTO v VALUES(101),(99);
    
    SELECT VARIANCE(i) FROM v;
    +-------------+
    | VARIANCE(i) |
    +-------------+
    |      1.0000 |
    +-------------+
    
    INSERT INTO v VALUES(120),(80);
    
    SELECT VARIANCE(i) FROM v;
    +-------------+
    | VARIANCE(i) |
    +-------------+
    |    200.5000 |
    +-------------+
    CREATE OR REPLACE TABLE stats (category VARCHAR(2), x INT);
    
    INSERT INTO stats VALUES 
      ('a',1),('a',2),('a',3),
      ('b',11),('b',12),('b',20),('b',30),('b',60);
    
    SELECT category, STDDEV_POP(x), STDDEV_SAMP(x), VAR_POP(x) 
      FROM stats GROUP BY category;
    +----------+---------------+----------------+------------+
    | category | STDDEV_POP(x) | STDDEV_SAMP(x) | VAR_POP(x) |
    +----------+---------------+----------------+------------+
    | a        |        0.8165 |         1.0000 |     0.6667 |
    | b        |       18.0400 |        20.1693 |   325.4400 |
    +----------+---------------+----------------+------------+
    CREATE OR REPLACE TABLE student_test (name CHAR(10), test CHAR(10), score TINYINT);
    
    INSERT INTO student_test VALUES 
        ('Chun', 'SQL', 75), ('Chun', 'Tuning', 73), 
        ('Esben', 'SQL', 43), ('Esben', 'Tuning', 31), 
        ('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88), 
        ('Tatiana', 'SQL', 87);
    
    SELECT name, test, score, VAR_POP(score) 
      OVER (PARTITION BY test) AS variance_results FROM student_test;
    +---------+--------+-------+------------------+
    | name    | test   | score | variance_results |
    +---------+--------+-------+------------------+
    | Chun    | SQL    |    75 |         287.1875 |
    | Chun    | Tuning |    73 |         582.0000 |
    | Esben   | SQL    |    43 |         287.1875 |
    | Esben   | Tuning |    31 |         582.0000 |
    | Kaolin  | SQL    |    56 |         287.1875 |
    | Kaolin  | Tuning |    88 |         582.0000 |
    | Tatiana | SQL    |    87 |         287.1875 |
    +---------+--------+-------+------------------+
    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 |
    +---------+---------------+------+
    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                                         |
    +-------------------------------------------------------------+
    SELECT SECOND('10:05:03');
    +--------------------+
    | SECOND('10:05:03') |
    +--------------------+
    |                  3 |
    +--------------------+
    
    SELECT SECOND('10:05:01.999999');
    +---------------------------+
    | SECOND('10:05:01.999999') |
    +---------------------------+
    |                         1 |
    +---------------------------+

    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

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

    The optional precision determines the microsecond precision. See Microseconds in MariaDB.

    NOW() (or its synonyms) can be used as the default value for TIMESTAMP columns as well as.

    When displayed in the INFORMATION_SCHEMA.COLUMNS table, a default CURRENT TIMESTAMP is displayed as current_timestamp() .

    Changing the timestamp system variable with a SET timestamp statement affects the value returned by NOW(), but not by SYSDATE().

    Examples

    With precision:

    Used as a default TIMESTAMP:

    See Also

    • Microseconds in MariaDB

    • timestamp server system variable

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

    time zone
    DATE
    DATETIME
    TIMESTAMP
    DATETIME

    Microseconds in MariaDB

    Understand microsecond precision. This concept page explains how MariaDB stores and handles fractional seconds in time data types.

    The TIME, DATETIME, and TIMESTAMP types, along with the temporal functions, CAST and dynamic columns, support microseconds. The datetime precision of a column can be specified when creating the table with CREATE TABLE, 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:

    • 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 , and functions, for example:

    Additional Information

    • When comparing anything to a temporal value (DATETIME, TIME, , or TIMESTAMP), both values are compared as temporal values, not as strings.

    • The has a new column DATETIME_PRECISION

    • , , , ,

    • and preserve microseconds of the argument. These functions will return a number if the result non-zero datetime precision and an 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 table and the start_time, query_time, and lock_time fields in the table now store values with microsecond precision.

    • The old syntax TIMESTAMP(N), where N is the display width, is no longer supported.

    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

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

    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.

    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

    This page is licensed: GPLv2, originally from

    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

    TIME_FORMAT(time,format)

    Description

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

    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

    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

    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

    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
    NOW([precision])
    CURRENT_TIMESTAMP
    CURRENT_TIMESTAMP([precision])
    LOCALTIME, LOCALTIME([precision])
    LOCALTIMESTAMP
    LOCALTIMESTAMP([precision])
    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()
    ...
    ADD_MONTHS(date, months)
    SELECT ADD_MONTHS('2012-01-31', 2);
    +-----------------------------+
    | ADD_MONTHS('2012-01-31', 2) |
    +-----------------------------+
    | 2012-03-31                  |
    +-----------------------------+
    
    SELECT ADD_MONTHS('2012-01-31', -5);
    +------------------------------+
    | ADD_MONTHS('2012-01-31', -5) |
    +------------------------------+
    | 2011-08-31                   |
    +------------------------------+
    
    SELECT ADD_MONTHS('2011-01-31', 1);
    +-----------------------------+
    | ADD_MONTHS('2011-01-31', 1) |
    +-----------------------------+
    | 2011-02-28                  |
    +-----------------------------+
    
    SELECT ADD_MONTHS('2012-01-31', 1);
    +-----------------------------+
    | ADD_MONTHS('2012-01-31', 1) |
    +-----------------------------+
    | 2012-02-29                  |
    +-----------------------------+
    
    SELECT ADD_MONTHS('2012-01-31', 2);
    +-----------------------------+
    | ADD_MONTHS('2012-01-31', 2) |
    +-----------------------------+
    | 2012-03-31                  |
    +-----------------------------+
    
    SELECT ADD_MONTHS('2012-01-31', 3);
    +-----------------------------+
    | ADD_MONTHS('2012-01-31', 3) |
    +-----------------------------+
    | 2012-04-30                  |
    +-----------------------------+
    
    SELECT ADD_MONTHS('2011-01-15', 2.5);
    +-------------------------------+
    | ADD_MONTHS('2011-01-15', 2.5) |
    +-------------------------------+
    | 2011-04-15                    |
    +-------------------------------+
    1 row in set (0.001 sec)
    
    SELECT ADD_MONTHS('2011-01-15', 2.6);
    +-------------------------------+
    | ADD_MONTHS('2011-01-15', 2.6) |
    +-------------------------------+
    | 2011-04-15                    |
    +-------------------------------+
    1 row in set (0.001 sec)
    
    SELECT ADD_MONTHS('2011-01-15', 2.1);
    +-------------------------------+
    | ADD_MONTHS('2011-01-15', 2.1) |
    +-------------------------------+
    | 2011-03-15                    |
    +-------------------------------+
    1 row in set (0.004 sec)
    SELECT 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            |
    +--------------------------------+
    CREATE TABLE example(
      col_microsec DATETIME(6),
      col_millisec TIME(3)
    );
    YEAR(date)
    partitioning
    ,
    ,
    and
    accept datetime precision as an optional argument. For example:

    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 TIME_MS has been added to the INFORMATION_SCHEMA.PROCESSLIST table, as well as to the output of SHOW FULL PROCESSLIST.

  • STR_TO_DATE
    DATE_FORMAT
    FROM_UNIXTIME
    DATE
    INFORMATION_SCHEMA.COLUMNS table
    NOW()
    CURTIME()
    UTC_TIMESTAMP()
    UTC_TIME()
    TIME_TO_SEC()
    UNIX_TIMESTAMP()
    decimal
    integer
    mysql.general_log
    mysql.slow_log
    Data Type Storage Requirements
    CURRENT_TIME()
    CURRENT_TIMESTAMP()
    LOCALTIME()
    LOCALTIMESTAMP()
    YEAR data type
    fill_help_tables.sql
    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                            |
    +--------------------------------------------+
    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
    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
    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
    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 |
    +----------------------+---------+
    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 |
    +---------------------+
    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 |
    +----------------+
    . 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

    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

    SUBDATE(date,INTERVAL expr unit), SUBDATE(expr,days)

    Description

    When invoked with the INTERVAL 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

    LOCALTIMESTAMP

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

    Syntax

    LOCALTIMESTAMP
    LOCALTIMESTAMP([precision])

    Description

    LOCALTIMESTAMP and LOCALTIMESTAMP() are synonyms for .

    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

    WEEK(date[,mode])
    SELECT WEEK('2008-02-20');
    +--------------------+
    | WEEK('2008-02-20') |
    +--------------------+
    |                  7 |
    +--------------------+
    
    SELECT WEEK('2008-02-20',0);
    +----------------------+
    | WEEK('2008-02-20',0) |
    +----------------------+
    |                    7 |
    +----------------------+
    
    SELECT WEEK('2008-02-20',1);
    +----------------------+
    | WEEK('2008-02-20',1) |
    +----------------------+
    |                    8 |
    +----------------------+
    
    SELECT WEEK('2008-12-31',0);
    +----------------------+
    | WEEK('2008-12-31',0) |
    +----------------------+
    |                   52 |
    +----------------------+
    
    SELECT WEEK('2008-12-31',1);
    +----------------------+
    | WEEK('2008-12-31',1) |
    +----------------------+
    |                   53 |
    +----------------------+
    
     SELECT WEEK('2019-12-30',3);
    +----------------------+
    | WEEK('2019-12-30',3) |
    +----------------------+
    |                    1 |
    +----------------------+
    CREATE TABLE t1 (d DATETIME);
    INSERT INTO t1 VALUES
        ("2007-01-30 21:31:07"),
        ("1983-10-15 06:42:51"),
        ("2011-04-21 12:34:56"),
        ("2011-10-30 06:31:41"),
        ("2011-01-30 14:03:25"),
        ("2004-10-07 11:19:34");
    SELECT d, WEEK(d,0), WEEK(d,1) from t1;
    +---------------------+-----------+-----------+
    | d                   | WEEK(d,0) | WEEK(d,1) |
    +---------------------+-----------+-----------+
    | 2007-01-30 21:31:07 |         4 |         5 |
    | 1983-10-15 06:42:51 |        41 |        41 |
    | 2011-04-21 12:34:56 |        16 |        16 |
    | 2011-10-30 06:31:41 |        44 |        43 |
    | 2011-01-30 14:03:25 |         5 |         4 |
    | 2004-10-07 11:19:34 |        40 |        41 |
    +---------------------+-----------+-----------+

    a Sunday in this year

    3

    Monday

    1-53

    more than 3 days this year

    4

    Sunday

    0-53

    more than 3 days this year

    5

    Monday

    0-53

    a Monday in this year

    6

    Sunday

    1-53

    more than 3 days this year

    7

    Monday

    1-53

    a Monday in this year

    NOW()
    Microseconds in MariaDB
    fill_help_tables.sql
    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                             |
    +----------------------------------------+
    DATE_SUB()
    Date and Time Units
    fill_help_tables.sql
    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            |
    +---------------------+--------------------------------+
    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 DATETIME as a storage type if you require dates beyond this.

    The one-argument form of FROM_UNIXTIME() returns aDATETIME. This means that it can return values outside of valid TIMESTAMP range, in particular 1970-01-01 00:00:00. And it can return the same result for different values of unix_timestamp (around DST changes).

    Timestamps in MariaDB have a maximum value of 4294967295, equivalent to 2106-02-07 06:28:15. This is due to the underlying 32-bit limitation. Using the function on a timestamp beyond this will result in NULL being returned. Use DATETIME as a storage type if you require dates beyond this.

    The one-argument form of FROM_UNIXTIME() returns aDATETIME. This means that it can return values outside of valid TIMESTAMP range, in particular 1970-01-01 00:00:00. And it can return the same result for different values of unix_timestamp (around DST changes).

    The maximum value is 2147483647, equivalent to 2038-01-19 05:14:07.

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

    Option
    Description

    %a

    Short weekday name in current locale (Variable ).

    %b

    Short form month name in current locale. For locale en_US this is one of: Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov or Dec.

    %c

    Month with 1 or 2 digits.

    %D

    Day with English suffix 'th', 'nd', 'st' or 'rd''. (1st, 2nd, 3rd...).

    %d

    Day with 2 digits.

    %e

    Day with 1 or 2 digits.

    Performance Considerations

    If your session time zone is set to SYSTEM (the default), FROM_UNIXTIME() will call the OS function to convert the data using the system time zone. At least on Linux, the corresponding function (localtime_r) uses a global mutex inside glibc that can cause contention under high concurrent load.

    Set your time zone to a named time zone to avoid this issue. See mysql time zone tables for details on how to do this.

    Examples

    See Also

    • UNIX_TIMESTAMP()

    • DATE_FORMAT()

    • STR_TO_DATE()

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

    UNIX_TIMESTAMP()

    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

    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

    FROM_UNIXTIME(unix_timestamp)
    FROM_UNIXTIME(unix_timestamp,format)
    DATE_FORMAT(FROM_UNIXTIME(unix_timestamp), format)
    SELECT FROM_UNIXTIME(1196440219);
    +---------------------------+
    | FROM_UNIXTIME(1196440219) |
    +---------------------------+
    | 2007-11-30 11:30:19       |
    +---------------------------+
    
    SELECT FROM_UNIXTIME(1196440219) + 0;
    +-------------------------------+
    | FROM_UNIXTIME(1196440219) + 0 |
    +-------------------------------+
    |         20071130113019.000000 |
    +-------------------------------+
    
    SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y %D %M %h:%i:%s %x');
    +---------------------------------------------------------+
    | FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y %D %M %h:%i:%s %x') |
    +---------------------------------------------------------+
    | 2010 27th March 01:03:47 2010                           |
    +---------------------------------------------------------+
    DATE_FORMAT(date, format[, locale])
    STR_TO_DATE(str,format)

    %f

    Microseconds 6 digits.

    %H

    Hour with 2 digits between 00-23.

    %h

    Hour with 2 digits between 01-12.

    %I

    Hour with 2 digits between 01-12.

    %i

    Minute with 2 digits.

    %j

    Day of the year (001-366)

    %k

    Hour with 1 digits between 0-23.

    %l

    Hour with 1 digits between 1-12.

    %M

    Full month name in current locale (Variable lc_time_names).

    %m

    Month with 2 digits.

    %p

    AM/PM according to current locale (Variable lc_time_names).

    %r

    Time in 12 hour format, followed by AM/PM. Short for '%I:%i:%S %p'.

    %S

    Seconds with 2 digits.

    %s

    Seconds with 2 digits.

    %T

    Time in 24 hour format. Short for '%H:%i:%S'.

    %U

    Week number (00-53), when first day of the week is Sunday.

    %u

    Week number (00-53), when first day of the week is Monday.

    %V

    Week number (01-53), when first day of the week is Sunday. Used with %X.

    %v

    Week number (01-53), when first day of the week is Monday. Used with %x.

    %W

    Full weekday name in current locale (Variable lc_time_names).

    %w

    Day of the week. 0 = Sunday, 6 = Saturday.

    %X

    Year with 4 digits when first day of the week is Sunday. Used with %V.

    %x

    Year with 4 digits when first day of the week is Sunday. Used with %v.

    %Y

    Year with 4 digits.

    %y

    Year with 2 digits.

    %#

    For str_to_date(), skip all numbers.

    %.

    For str_to_date(), skip all punctation characters.

    %@

    For str_to_date(), skip all alpha characters.

    %%

    A literal % character.

    lc_time_names

    %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

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

    Functions & Operators

    Comprehensive reference of all SQL functions and operators. This index lists built-in functions for string manipulation, math, date/time, and more.

    Name
    Description
    Added

    Addition operator

    Division operator

    Multiplication operator

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

    %

    Modulo operator. Returns the remainder of N divided by M

    -

    Subtraction operator

    !=

    Not equals

    <

    Less than

    <=

    Less than or equal

    <=>

    NULL-safe equal

    =

    Equal

    >

    Greater than

    >=

    Greater than or equal

    &

    Bitwise AND

    <<

    Shift left

    >>

    Shift right

    ^

    Bitwise XOR

    !

    Logical NOT

    &&

    Logical AND

    XOR

    Logical XOR

    |

    Bitwise OR

    :=

    Assignment operator

    =

    Assignment and comparison operator

    ~

    Bitwise NOT

    ABS

    Returns an absolute value

    ACOS

    Returns an arc cosine

    ADD_MONTHS

    Add months to a date

    ADDDATE

    Add days or another interval to a date

    ADDTIME

    Adds a time to a time or datetime

    AES_DECRYPT

    Decryption data encrypted with AES_ENCRYPT

    AES_ENCRYPT

    Encrypts a string with the AES algorithm

    AREA

    Synonym for ST_AREA

    AsBinary

    Synonym for ST_AsBinary

    ASCII

    Numeric ASCII value of leftmost character

    ASIN

    Returns the arc sine

    AsText

    Synonym for ST_AsText

    AsWKB

    Synonym for ST_AsBinary

    AsWKT

    Synonym for ST_AsText

    ATAN

    Returns the arc tangent

    ATAN2

    Returns the arc tangent of two variables

    AVG

    Returns the average value

    BENCHMARK

    Executes an expression repeatedly

    BETWEEN AND

    True if expression between two values

    BIN

    Returns binary value

    BINARY OPERATOR

    Casts to a binary string

    BINLOG_GTID_POS

    Returns a string representation of the corresponding GTID position

    BIT_AND

    Bitwise AND

    BIT_COUNT

    Returns the number of set bits

    BIT_LENGTH

    Returns the length of a string in bits

    BIT_OR

    Bitwise OR

    BIT_XOR

    Bitwise XOR

    BOUNDARY

    Synonym for ST_BOUNDARY

    BUFFER

    Synonym for ST_BUFFER

    CASE

    Returns the result where value=compare_value or for the first condition that is true

    CAST

    Casts a value of one type to another type

    CEIL

    Synonym for CEILING()

    CEILING

    Returns the smallest integer not less than X

    CENTROID

    Synonym for ST_CENTROID

    CHAR Function

    Returns string based on the integer values for the individual characters

    CHARACTER_LENGTH

    Synonym for CHAR_LENGTH()

    CHAR_LENGTH

    Length of the string in characters

    CHARSET

    Returns the character set

    CHR

    Returns a string consisting of the character given by the code values of the integer

    COALESCE

    Returns the first non-NULL parameter

    COERCIBILITY

    Returns the collation coercibility value

    COLLATION

    Collation of the string argument

    COLUMN_ADD

    Adds or updates dynamic columns

    COLUMN_CHECK

    Checks if a dynamic column blob is valid

    COLUMN_CREATE

    Returns a dynamic columns blob

    COLUMN_DELETE

    Deletes a dynamic column

    COLUMN_EXISTS

    Checks is a column exists

    COLUMN_GET

    Gets a dynamic column value by name

    COLUMN_JSON

    Returns a JSON representation of dynamic column blob data

    COLUMN_LIST

    Returns comma-separated list

    COMPRESS

    Returns a binary, compressed string

    CONCAT

    Returns concatenated string

    CONCAT_WS

    Concatenate with separator

    CONNECTION_ID

    Connection thread ID

    CONTAINS

    Whether one geometry contains another

    CONVERT

    Convert a value from one type to another type

    CONV

    Converts numbers between different number bases

    CONVERT_TZ

    Converts a datetime from on time zone to another

    CONVEXHULL

    Synonym for ST_CONVEXHULL

    COS

    Returns the cosine

    COT

    Returns the cotangent

    COUNT

    Returns count of non-null values

    COUNT DISTINCT

    Returns count of number of different non-NULL values

    CRC32

    Computes a cyclic redundancy check value

    CRC32C

    Computes a cyclic redundancy check value

    CROSSES

    Whether two geometries spatially cross

    CUME_DIST

    Window function that returns the cumulative distribution of a given row

    CURDATE

    Returns the current date

    CURRENT_DATE

    Synonym for CURDATE()

    CURRENT_ROLE

    Current role name

    CURRENT_TIME

    Synonym for CURTIME()

    CURRENT_TIMESTAMP

    Synonym for NOW()

    CURRENT_USER

    Username/host that authenicated the current client

    CURTIME

    Returns the current time

    DATABASE

    Current default database

    DATE FUNCTION

    Extracts the date portion of a datetime

    DATEDIFF

    Difference in days between two date/time values

    DATE_ADD

    Date arithmetic - addition

    DATE_FORMAT

    Formats the date value according to the format string

    DATE_SUB

    Date arithmetic - subtraction

    DAY

    Synonym for DAYOFMONTH()

    DAYNAME

    Return the name of the weekday

    DAYOFMONTH

    Returns the day of the month

    DAYOFWEEK

    Returns the day of the week index

    DAYOFYEAR

    Returns the day of the year

    DECODE

    Decrypts a string encoded with ENCODE()

    DECODE_HISTOGRAM

    Returns comma separated numerics corresponding to a probability distribution represented by a histogram

    DEFAULT

    Returns column default

    DEGREES

    Converts from radians to degrees

    DENSE_RANK

    Rank of a given row with identical values receiving the same result, no skipping

    DES_DECRYPT

    Decrypts a string encrypted with DES_ENCRYPT()

    DES_ENCRYPT

    Encrypts a string using the Triple-DES algorithm

    DIMENSION

    Synonym for ST_DIMENSION

    DISJOINT

    Whether the two elements do not intersect

    DIV

    Integer division

    ELT

    Returns the N'th element from a set of strings

    ENCODE

    Encrypts a string

    ENCRYPT

    Encrypts a string with Unix crypt()

    ENDPOINT

    Synonym for ST_ENDPOINT

    ENVELOPE

    Synonym for ST_ENVELOPE

    EQUALS

    Indicates whether two geometries are spatially equal

    EXP

    e raised to the power of the argument

    EXPORT_SET

    Returns an on string for every bit set, an off string for every bit not set

    ExteriorRing

    Synonym for ST_ExteriorRing

    EXTRACT

    Extracts a portion of the date

    EXTRACTVALUE

    Returns the text of the first text node matched by the XPath expression

    FIELD

    Returns the index position of a string in a list

    FIND_IN_SET

    Returns the position of a string in a set of strings

    FLOOR

    Largest integer value not greater than the argument

    FORMAT

    Formats a number

    FORMAT_BYTES

    Given a byte count, returns a string consisting of a value and the units in a human-readable format.

    MariaDB 11.8

    FORMAT_PICO_TIME

    Given a time in picoseconds, returns a human-readable time value and unit indicator

    FOUND_ROWS

    Number of (potentially) returned rows

    FROM_BASE64

    Given a base-64 encoded string, returns the decoded result as a binary string

    FROM_DAYS

    Returns a date given a day

    FROM_UNIXTIME

    Returns a datetime from a Unix timestamp

    GeomCollFromText

    Synonym for ST_GeomCollFromText

    GeomCollFromWKB

    Synonym for ST_GeomCollFromWKB

    GEOMETRYCOLLECTION

    Constructs a WKB GeometryCollection

    GeometryCollectionFromText

    Synonym for ST_GeomCollFromText

    GeometryCollectionFromWKB

    Synonym for ST_GeomCollFromWKB

    GeometryFromText

    Synonym for ST_GeomFromText

    GeometryFromWKB

    Synonym for ST_GeomFromWKB

    GeomFromText

    Synonym for ST_GeomFromText

    GeomFromWKB

    Synonym for ST_GeomFromWKB

    GeometryN

    Synonym for ST_GeometryN

    GeometryType

    Synonym for ST_GeometryType

    GET_FORMAT

    Returns a format string

    GET_LOCK

    Obtain LOCK

    GLENGTH

    Length of a LineString value

    GREATEST

    Returns the largest argument

    GROUP_CONCAT

    Returns string with concatenated values from a group

    HEX

    Returns hexadecimal value

    HOUR

    Returns the hour

    IF

    If expr1 is TRUE, returns expr2; otherwise returns expr3

    IFNULL

    Check whether an expression is NULL

    IN

    True if expression equals any of the values in the list

    INTERVAL

    Index of the argument that is less than the first argument

    INET6_ATON

    Given an IPv6 or IPv4 network address, returns a VARBINARY numeric value

    INET6_NTOA

    Given an IPv6 or IPv4 network address, returns the address as a nonbinary string

    INET_ATON

    Returns numeric value of IPv4 address

    INET_NTOA

    Returns dotted-quad representation of IPv4 address

    INSERT Function

    Replaces a part of a string with another string

    INSTR

    Returns the position of a string withing a string

    InteriorRingN

    Synonym for ST_InteriorRingN

    INTERSECTS

    Indicates whether two geometries spatially intersect

    IS

    Tests whether a boolean is TRUE, FALSE, or UNKNOWN

    IsClosed

    Synonym for ST_IsClosed

    IsEmpty

    Synonym for ST_IsEmpty

    IS_FREE_LOCK

    Checks whether lock is free to use

    IS_IPV4

    Whether or not an expression is a valid IPv4 address

    IS_IPV4_COMPAT

    Whether or not an IPv6 address is IPv4-compatible

    IS_IPV4_MAPPED

    Whether an IPv6 address is a valid IPv4-mapped address

    IS_IPV6

    Whether or not an expression is a valid IPv6 address

    IS NOT

    Tests whether a boolean value is not TRUE, FALSE, or UNKNOWN

    IS NOT NULL

    Tests whether a value is not NULL

    IS NULL

    Tests whether a value is NULL

    ISNULL

    Checks if an expression is NULL

    IsRing

    Synonym for ST_IsRing

    IsSimple

    Synonym for ST_IsSimple

    IS_USED_LOCK

    Check if lock is in use

    JSON_ARRAY

    Returns a JSON array containing the listed values

    JSON_ARRAYAGG

    Returns a JSON array containing an element for each value in a given set of JSON or SQL values.

    JSON_ARRAY_INTERSECT

    JSON_ARRAY_APPEND

    Appends values to the end of the given arrays within a JSON document

    JSON_ARRAY_INSERT

    Inserts a value into a JSON document

    JSON_COMPACT

    Removes all unnecessary spaces so the json document is as short as possible

    JSON_CONTAINS

    Whether a value is found in a given JSON document or at a specified path within the document

    JSON_CONTAINS_PATH

    Indicates whether the given JSON document contains data at the specified path or paths

    JSON_DEPTH

    Maximum depth of a JSON document

    JSON_DETAILED

    Represents JSON in the most understandable way emphasizing nested structures

    JSON_EQUALS

    Check for equality between JSON objects.

    JSON_EXISTS

    Determines whether a specified JSON value exists in the given data

    JSON_EXTRACT

    Extracts data from a JSON document.

    JSON_INSERT

    Inserts data into a JSON document

    JSON_KEY_VALUE

    Extracts key/value pairs from a JSON object

    JSON_KEYS

    Returns keys from top-level value of a JSON object or top-level keys from the path

    JSON_LENGTH

    Returns the length of a JSON document, or the length of a value within the document

    JSON_LOOSE

    Adds spaces to a JSON document to make it look more readable

    JSON_MERGE

    Merges the given JSON documents

    JSON_MERGE_PATCH

    RFC 7396-compliant merge of the given JSON documents

    JSON_MERGE_PRESERVE

    Synonym for JSON_MERGE_PATCH.

    JSON_NORMALIZE

    Recursively sorts keys and removes spaces, allowing comparison of json documents for equality

    JSON_OBJECT

    Returns a JSON object containing the given key/value pairs

    JSON_OBJECT_FILTER_KEYS

    JSON_OBJECT_TO_ARRAY

    JSON_OBJECTAGG

    Returns a JSON object containing key-value pairs

    JSON_OVERLAPS

    Compares two json documents for overlaps

    JSON_PRETTY

    Alias for json_detailed

    , , , , MariaDB 10.6.12

    JSON_QUERY

    Given a JSON document, returns an object or array specified by the path

    JSON_QUOTE

    Quotes a string as a JSON value

    JSON_REMOVE

    Removes data from a JSON document

    JSON_REPLACE

    Replaces existing values in a JSON document

    JSON_SCHEMA_VALID

    Validates a JSON schema

    JSON_SEARCH

    Returns the path to the given string within a JSON document

    JSON_SET

    Updates or inserts data into a JSON document

    JSON_TABLE

    Returns a representation of a JSON document as a relational table

    MariaDB 10.6

    JSON_TYPE

    Returns the type of a JSON value

    JSON_UNQUOTE

    Unquotes a JSON value, returning a string

    JSON_VALID

    Whether a value is a valid JSON document or not

    JSON_VALUE

    Given a JSON document, returns the specified scalar

    KDF

    Key derivation function

    LAST_DAY

    Returns the last day of the month

    LAST_INSERT_ID

    Last inserted autoinc value

    LAST_VALUE

    Returns the last value in a list

    LASTVAL

    Get last value generated from a sequence

    LCASE

    Synonym for [LOWER()

    LEAST

    Returns the smallest argument

    LEFT

    Returns the leftmost characters from a string

    LENGTH

    Length of the string in bytes

    LIKE

    Whether expression matches a pattern

    LineFromText

    Synonym for ST_LineFromText

    LineFromWKB

    Synonym for ST_LineFromWKB

    LINESTRING

    Constructs a WKB LineString value from a number of WKB Point arguments

    LineStringFromText

    Synonym for ST_LineFromText

    LineStringFromWKB

    Synonym for ST_LineFromWKB

    LN

    Returns natural logarithm

    LOAD_FILE

    Returns file contents as a string

    LOCALTIME

    Synonym for NOW()

    LOCALTIMESTAMP

    Synonym for NOW()

    LOCATE

    Returns the position of a substring in a string

    LOG

    Returns the natural logarithm

    LOG10

    Returns the base-10 logarithm

    LOG2

    Returns the base-2 logarithm

    LOWER

    Returns a string with all characters changed to lowercase

    LPAD

    Returns the string left-padded with another string to a given length

    LTRIM

    Returns the string with leading space characters removed

    MAKE_SET

    Make a set of strings that matches a bitmask

    MAKEDATE

    Returns a date given a year and day

    MAKETIME

    Returns a time

    MASTER_GTID_WAIT

    Wait until slave reaches the GTID position

    MASTER_POS_WAIT

    Blocks until the slave has applied all specified updates

    MATCH AGAINST

    Perform a fulltext search on a fulltext index

    MAX

    Returns the maximum value

    MBRContains

    Whether one Minimum Bounding Rectangle contains another.

    MBRCoveredBy

    Whether one Minimum Bounding Rectangle is covered by another.

    MariaDB 11.8

    MBRDisjoint

    Whether the Minimum Bounding Rectangles of two geometries are disjoint

    MBREqual

    Whether the Minimum Bounding Rectangles of two geometries are the same.

    MBREquals

    Synonym for MBREqual.

    MBRIntersects

    Indicates whether the Minimum Bounding Rectangles of the two geometries intersect

    MBROverlaps

    Whether the Minimum Bounding Rectangles of two geometries overlap

    MBRTouches

    Whether the Minimum Bounding Rectangles of two geometries touch.

    MBRWithin

    Indicates whether one Minimum Bounding Rectangle is within another

    MD5

    MD5 checksum

    MEDIAN

    Window function that returns the median value of a range of values

    MICROSECOND

    Returns microseconds from a date or datetime

    MID

    Synonym for SUBSTRING(str,pos,len)

    MIN

    Returns the minimum value

    MINUTE

    Returns a minute from 0 to 59

    MLineFromText

    Constructs MULTILINESTRING using its WKT representation and SRID

    MLineFromWKB

    Constructs a MULTILINESTRING

    MOD

    Modulo operation. Remainder of N divided by M

    MONTH

    Returns a month from 1 to 12

    MONTHNAME

    Returns the full name of the month

    MPointFromText

    Constructs a MULTIPOINT value using its WKT and SRID

    MPointFromWKB

    Constructs a MULTIPOINT value using its WKB representation and SRID

    MPolyFromText

    Constructs a MULTIPOLYGON value

    MPolyFromWKB

    Constructs a MULTIPOLYGON value using its WKB representation and SRID

    MULTILINESTRING

    Constructs a MultiLineString value

    MultiLineStringFromText

    Synonym for MLineFromText

    MultiLineStringFromWKB

    A synonym for MLineFromWKB

    MULTIPOINT

    Constructs a WKB MultiPoint value

    MultiPointFromText

    Synonym for MPointFromText

    MultiPointFromWKB

    Synonym for MPointFromWKB

    MULTIPOLYGON

    Constructs a WKB MultiPolygon

    MultiPolygonFromText

    Synonym for MPolyFromText

    MultiPolygonFromWKB

    Synonym for MPolyFromWKB

    NAME_CONST

    Returns the given value

    NATURAL_SORT_KEY

    Sorting that is more similar to natural human sorting

    NOT LIKE

    Same as NOT(expr LIKE pat [ESCAPE 'escape_char'])

    NOT REGEXP

    Same as NOT (expr REGEXP pat)

    NULLIF

    Returns NULL if expr1 = expr2

    NEXTVAL

    Generate next value for sequence

    NOT BETWEEN

    Same as NOT (expr BETWEEN min AND max)

    NOT IN

    Same as NOT (expr IN (value,...))

    NOW

    Returns the current date and time

    NTILE

    Returns an integer indicating which group a given row falls into

    NumGeometries

    Synonym for ST_NumGeometries

    NumInteriorRings

    Synonym for NumInteriorRings

    NumPoints

    Synonym for ST_NumPoints

    OCT

    Returns octal value

    OCTET_LENGTH

    Synonym for LENGTH()

    OLD_PASSWORD

    Pre MySQL 4.1 password implementation

    ORD

    Return ASCII or character code

    OVERLAPS

    Indicates whether two elements spatially overlap

    PASSWORD

    Calculates a password string

    PERCENT_RANK

    Window function that returns the relative percent rank of a given row

    PERCENTILE_CONT

    Returns a value which corresponds to the given fraction in the sort order.

    PERCENTILE_DISC

    Returns the first value in the set whose ordered position is the same or more than the specified fraction.

    PERIOD_ADD

    Add months to a period

    PERIOD_DIFF

    Number of months between two periods

    PI

    Returns the value of π (pi)

    POINT

    Constructs a WKB Point

    PointFromText

    Synonym for ST_PointFromText

    PointFromWKB

    Synonym for PointFromWKB

    PointN

    Synonym for PointN

    PointOnSurface

    Synonym for ST_PointOnSurface

    POLYGON

    Constructs a WKB Polygon value from a number of WKB LineString arguments

    PolyFromText

    Synonym for ST_PolyFromText

    PolyFromWKB

    Synonym for ST_PolyFromWKB

    PolygonFromText

    Synonym for ST_PolyFromText

    PolygonFromWKB

    Synonym for ST_PolyFromWKB

    POSITION

    Returns the position of a substring in a string

    POW

    Returns X raised to the power of Y

    POWER

    Synonym for POW()

    QUARTER

    Returns year quarter from 1 to 4

    QUOTE

    Returns quoted, properly escaped string

    RADIANS

    Converts from degrees to radians

    RAND

    Random floating-point value

    RANK

    Rank of a given row with identical values receiving the same result

    REGEXP

    Performs pattern matching

    REGEXP_INSTR

    Position of the first appearance of a regex

    REGEXP_REPLACE

    Replaces all occurrences of a pattern

    REGEXP_SUBSTR

    Returns the matching part of a string

    RELEASE_LOCK

    Releases lock obtained with GET_LOCK()

    REPEAT Function

    Returns a string repeated a number of times

    REPLACE Function

    Replace occurrences of a string

    REVERSE

    Reverses the order of a string

    RIGHT

    Returns the rightmost N characters from a string

    RLIKE

    Synonym for REGEXP()

    RPAD

    Returns the string right-padded with another string to a given length

    ROUND

    Rounds a number

    ROW_COUNT

    Number of rows affected by previous statement

    ROW_NUMBER

    Row number of a given row with identical values receiving a different result

    RTRIM

    Returns the string with trailing space characters removed

    SCHEMA

    Synonym for DATABASE()

    SECOND

    Returns the second of a time

    SEC_TO_TIME

    Converts a second to a time

    SETVAL

    Set the next value to be returned by a sequence

    SESSION_USER

    Synonym for USER()

    sha

    Synonym for SHA1()

    SHA1

    Calculates an SHA-1 checksum

    SHA2

    Calculates an SHA-2 checksum

    SIGN

    Returns 1, 0 or -1

    SIN

    Returns the sine

    SLEEP

    Pauses for the given number of seconds

    SOUNDEX

    Returns a string based on how the string sounds

    SOUNDS LIKE

    SOUNDEX(expr1) = SOUNDEX(expr2)

    SPACE

    Returns a string of space characters

    SPIDER_BG_DIRECT_SQL

    Background SQL execution

    SPIDER_COPY_TABLES

    Copy table data

    SPIDER_DIRECT_SQL

    Execute SQL on the remote server

    SPIDER_FLUSH_TABLE_MON_CACHE

    Refreshing Spider monitoring server information

    SQRT

    Square root

    SRID

    Synonym for ST_SRID

    ST_AREA

    Area of a Polygon

    ST_AsBinary

    Converts a value to its WKB representation

    ST_AsGeoJson

    Returns a GeoJSON element from a given geometry.

    ST_AsText

    Converts a value to its WKT-Definition

    ST_AsWKB

    Synonym for ST_AsBinary

    ST_ASWKT

    Synonym for ST_ASTEXT()

    ST_BOUNDARY

    Returns a geometry that is the closure of a combinatorial boundary

    ST_BUFFER

    A new geometry with a buffer added to the original geometry

    ST_CENTROID

    The mathematical centroid (geometric center) for a MultiPolygon

    ST_Collect

    Returns the aggregation of the distinct geometry arguments

    MariaDB 11.8

    ST_CONTAINS

    Whether one geometry is contained by another

    ST_CONVEXHULL

    The minimum convex geometry enclosing all geometries within the set

    ST_CROSSES

    Whether two geometries spatially cross

    ST_DIFFERENCE

    Point set difference

    ST_DIMENSION

    Inherent dimension of a geometry value

    ST_DISJOINT

    Whether one geometry is spatially disjoint from another

    ST_DISTANCE

    The distance between two geometries

    ST_DISTANCE_SPHERE

    The spherical distance between two geometries

    ST_ENDPOINT

    Returns the endpoint of a LineString

    ST_ENVELOPE

    Returns the Minimum Bounding Rectangle for a geometry value

    ST_EQUALS

    Whether two geometries are spatoially equal

    ST_ExteriorRing

    Returns the exterior ring of a Polygon as a LineString

    ST_GeoHash

    Returns a geohash.

    MariaDB 11.8

    ST_GeomCollFromText

    Constructs a GEOMETRYCOLLECTION value

    ST_GeomCollFromWKB

    Constructs a GEOMETRYCOLLECTION value from a WKB

    ST_GeometryCollectionFromText

    Synonym for ST_GeomCollFromText

    ST_GeometryCollectionFromWKB

    Synonym for ST_GeomCollFromWKB

    ST_GeometryFromText

    Synonym for ST_GeomFromText

    ST_GeometryFromWKB

    Synonym for ST_GeomFromWKB

    ST_GEOMETRYN

    Returns the N-th geometry in a GeometryCollection

    ST_GEOMETRYTYPE

    Returns name of the geometry type of which a given geometry instance is a member

    ST_GeomFromGeoJSON

    Returns a geometry object from a GeoJSON input

    ST_GeomFromText

    Constructs a geometry value using its WKT and SRID

    ST_GeomFromWKB

    Constructs a geometry value using its WKB representation and SRID

    ST_InteriorRingN

    Returns the N-th interior ring for a Polygon

    ST_INTERSECTION

    The intersection, or shared portion, of two geometries

    ST_INTERSECTS

    Whether two geometries spatially intersect

    ST_ISCLOSED

    Returns true if a given LINESTRING's start and end points are the same

    ST_ISEMPTY

    Indicated validity of geometry value

    ST_IsRing

    Returns true if a given LINESTRING is both ST_IsClosed and ST_IsSimple

    ST_IsSimple

    Returns true if the given Geometry has no anomalous geometric points

    ST_IsValid

    Returns 1 if the argument is geometrically valid, 0 if not.

    MariaDB 11.8

    ST_LatFromGeoHash

    Returns a latitude from the given geohash.

    MariaDB 11.8

    ST_LongFromGeoHash

    Returns a longitude from the given geohash.

    MariaDB 11.8

    ST_LENGTH

    Length of a LineString value

    ST_LineFromText

    Creates a linestring value

    ST_LineFromWKB

    Constructs a LINESTRING using its WKB and SRID

    ST_LineStringFromText

    Synonym for ST_LineFromText

    ST_LineStringFromWKB

    Synonym for ST_LineFromWKB

    ST_MLineFromText

    Synonym for ST_GeomFromText

    ST_MLineFromWKB

    Synonym for ST_GeomFromWKB

    ST_MPointFromText

    Synonym for ST_GeomFromText

    ST_MPointFromWKB

    Synonym for ST_GeomFromWKB

    ST_MPolyFromText

    Synonym for ST_GeomFromText

    ST_MPolyFromWKB

    Synonym for ST_GeomFromWKB

    ST_MultiLineStringFromText

    Synonym for ST_GeomFromText

    ST_MultiLineStringFromWKB

    Synonym for ST_GeomFromWKB

    ST_MultiPointFromText

    Synonym for ST_GeomFromText

    ST_MultiPolygonFromText

    Synonym for ST_GeomFromText

    ST_MultiPolygonFromWKB

    Synonym for ST_GeomFromWKB

    ST_MultiPointFromWKB

    Synonym for ST_GeomFromWKB

    ST_NUMGEOMETRIES

    Number of geometries in a GeometryCollection

    ST_NumInteriorRings

    Number of interior rings in a Polygon

    ST_NUMPOINTS

    Returns the number of Point objects in a LineString

    ST_OVERLAPS

    Whether two geometries overlap

    ST_PointFromGeoHash

    Returns a point from the given geohash.

    MariaDB 11.8

    ST_PointFromText

    Constructs a POINT value

    ST_PointFromWKB

    Constructs POINT using its WKB and SRID

    ST_POINTN

    Returns the N-th Point in the LineString

    ST_POINTONSURFACE

    Returns a POINT guaranteed to intersect a surface

    ST_PolyFromText

    Constructs a POLYGON value

    ST_PolyFromWKB

    Constructs POLYGON value using its WKB representation and SRID

    ST_PolygonFromText

    Synonym for ST_PolyFromText

    ST_PolygonFromWKB

    Synonym for ST_PolyFromWKB

    ST_RELATE

    Returns true if two geometries are related

    ST_Simplify

    Applies the Ramer–Douglas–Peucker algorithm to provide generalized simplifications.

    MariaDB 11.8

    ST_SRID

    Returns a Spatial Reference System ID

    ST_STARTPOINT

    Returns the start point of a LineString

    ST_SYMDIFFERENCE

    Portions of two geometries that don't intersect

    ST_TOUCHES

    Whether one geometry g1 spatially touches another

    ST_UNION

    Union of two geometries

    ST_Validate

    Whether a geometry is compliant with the WKB format and SRID syntax, and is geometrically valid.

    MariaDB 11.8

    ST_WITHIN

    Whether one geometry is within another

    ST_X

    X-coordinate value for a point

    ST_Y

    Y-coordinate for a point

    STARTPOINT

    Synonym for ST_StartPoint

    STD

    Population standard deviation

    STDDEV

    Population standard deviation

    STDDEV_POP

    Returns the population standard deviation

    STDDEV_SAMP

    Standard deviation

    STR_TO_DATE

    Converts a string to date

    STRCMP

    Compares two strings in sort order

    SUBDATE

    Subtract a date unit or number of days

    SUBSTR

    Returns a substring from string starting at a given position

    SUBSTRING

    Returns a substring from string starting at a given position

    SUBSTRING_INDEX

    Returns the substring from string before count occurrences of a delimiter

    SUBTIME

    Subtracts a time from a date/time

    SUM

    Sum total

    SYS.EXTRACT_SCHEMA_FROM_FILE_NAME

    Given a file path, returns the schema (database) name

    MariaDB 10.6

    SYS.EXTRACT_TABLE_FROM_FILE_NAME

    Given a file path, returns the table name

    MariaDB 10.6

    SYS.FORMAT_BYTES

    Returns a string consisting of a value and the units in a human-readable format

    MariaDB 10.6

    SYS.FORMAT_PATH

    Returns a modified path after replacing subpaths matching the values of various system variables with the variable name

    MariaDB 10.6

    SYS.FORMAT_STATEMENT

    Returns a reduced length string

    MariaDB 10.6

    SYS.FORMAT_TIME

    Returns a human-readable time value and unit indicator

    MariaDB 10.6

    SYS.LIST_ADD

    Adds a value to a given list

    MariaDB 10.6

    SYS.LIST_DROP

    Drops a value from a given list

    MariaDB 10.6

    SYS.PS_IS_ACCOUNT_ENABLED

    Whether Performance Schema instrumentation for the given account is enabled

    MariaDB 10.6

    SYS.PS_IS_CONSUMER_ENABLED

    Whether Performance Schema instrumentation for the given consumer is enabled

    MariaDB 10.6

    SYS.PS_IS_INSTRUMENT_DEFAULT_ENABLED

    Whether a given Performance Schema instrument is enabled by default

    MariaDB 10.6

    SYS.PS_IS_INSTRUMENT_DEFAULT_TIMED

    Returns whether a given Performance Schema instrument is timed by default

    MariaDB 10.6

    SYS.PS_IS_THREAD_INSTRUMENTED

    Returns whether or not Performance Schema instrumentation for the given connection_id is enabled

    MariaDB 10.6

    SYS.PS_THREAD_ACCOUNT

    Returns the account (username@hostname) associated with the given thread_id

    MariaDB 10.6

    SYS.PS_THREAD_ID

    Returns the thread_id associated with the given connection_id

    MariaDB 10.6

    SYS.PS_THREAD_STACK

    Returns all statements, stages, and events within the Performance Schema for a given thread_id

    MariaDB 10.6

    SYS.PS_THREAD_TRX_INFO

    Returns a JSON object with information about the thread specified by the given thread_id

    MariaDB 10.6

    SYS.QUOTE_IDENTIFIER

    Quotes a string to produce a result that can be used as an identifier in an SQL statement

    MariaDB 10.6

    SYS.SYS_GET_CONFIG

    Returns a configuration option value from the sys_config table

    MariaDB 10.6

    SYS.VERSION_MAJOR

    Returns the MariaDB Server major release version

    MariaDB 10.6

    SYS.VERSION_MINOR

    Returns the MariaDB Server minor release version

    MariaDB 10.6

    SYS.VERSION_PATCH

    Returns the MariaDB Server patch release version

    MariaDB 10.6

    SYS_GUID

    Generates a globally unique identifier

    SYSDATE

    Returns the current date and time

    SYSTEM_USER

    Synonym for USER()

    TAN

    Returns the tangent

    TIME function

    Extracts the time

    TIMEDIFF

    Returns the difference between two date/times

    TIMESTAMP FUNCTION

    Return the datetime, or add a time to a date/time

    TIMESTAMPADD

    Add interval to a date or datetime

    TIMESTAMPDIFF

    Difference between two datetimes

    TIME_FORMAT

    Formats the time value according to the format string

    TIME_TO_SEC

    Returns the time argument, converted to seconds

    TO_BASE64

    Converts a string to its base-64 encoded form

    TO_CHAR

    Converts a date/time type to a char

    TO_DAYS

    Number of days since year 0

    TO_NUMBER

    Converts and expression to NUMERIC

    MariaDB 12.2

    TO_SECONDS

    Number of seconds since year 0

    TOUCHES

    Whether two geometries spatially touch

    TRIM

    Returns a string with all given prefixes or suffixes removed

    TRUNC

    Returns a DATETIME truncated according to a given format.

    MariaDB 12.2

    TRUNCATE

    Truncates X to D decimal places

    UCASE

    Synonym for UPPER]]()

    UNHEX

    Interprets pairs of hex digits as a number and converts to the character represented by the number

    UNCOMPRESS

    Uncompresses string compressed with COMPRESS()

    UNCOMPRESSED_LENGTH

    Returns length of a string before being compressed with COMPRESS()

    UNIX_TIMESTAMP

    Returns a Unix timestamp

    UPDATEXML

    Replace XML

    UPPER

    Changes string to uppercase

    USER

    Current user/host

    UTC_DATE

    Returns the current UTC date

    UTC_TIME

    Returns the current UTC time

    UTC_TIMESTAMP

    Returns the current UTC date and time

    UUID

    Returns a Universal Unique Identifier v1

    UUIDv4

    Returns a Universal Unique Identifier v4

    UUIDv7

    Returns a Universal Unique Identifier v7

    UUID_SHORT

    Return short universal identifier

    VALUES or VALUE

    Refer to columns in INSERT ... ON DUPLICATE KEY UPDATE

    VAR_POP

    Population standard variance

    VAR_SAMP

    Returns the sample variance

    VARIANCE

    Population standard variance

    VEC_DISTANCE

    Calculates either a Euclidean or Cosine distance between two vectors.

    MariaDB 11.8

    VEC_DISTANCE_COSINE

    Calculates a Cosine distance between two vectors.

    VEC_DISTANCE_EUCLIDEAN

    Calculates a Euclidean (L2) distance between two points.

    VEC_FromText

    Converts a text representation of the vector to a vector.

    Vector

    VEC_ToText

    Converts a binary vector into a json array of numbers (floats).

    Vector

    VERSION

    MariaDB server version

    WEEK

    Returns the week number

    WEEKDAY

    Returns the weekday index

    WEEKOFYEAR

    Returns the calendar week of the date as a number in the range from 1 to 53

    WEIGHT_STRING

    Weight of the input string

    WITHIN

    Indicate whether a geographic element is spacially within another

    WSREP_LAST_SEEN_GTID

    Returns the Global Transaction ID of the most recent write transaction observed by the client.

    WSREP_LAST_WRITTEN_GTID

    Returns the Global Transaction ID of the most recent write transaction performed by the client.

    WSREP_SYNC_WAIT_UPTO_GTID

    Blocks the client until the transaction specified by the given Global Transaction ID is applied and committed by the node

    X

    Synonym for ST_X

    Y

    Synonym for ST_Y

    YEAR

    Returns the year for the given date

    YEARWEEK

    Returns year and week for a date

    +
    /
    *
    Oracle mode
    MariaDB 11.0.2
    MariaDB 12.2
    SQL_MODE=ORACLE
    11.7
    MariaDB 11.3.0
    MariaDB 11.3.0
    MariaDB 10.8
    MariaDB 11.0.2
    MariaDB 11.2.0
    MariaDB 10.7
    MariaDB 11.2
    MariaDB 11.2.0
    MariaDB 11.2.0
    MariaDB 10.9
    MariaDB 10.10.3
    MariaDB 10.9.5
    MariaDB 10.8.7
    MariaDB 10.7.8
    MariaDB 11.1.0
    MariaDB 11.3.0
    MariaDB 11.7
    MariaDB 11.7
    MariaDB 11.7
    MariaDB 11.7
    MariaDB 11.6.0
    MariaDB 11.6.0