Functions and procedures in MariaDB
Returns comma separated numerics corresponding to a probability distribution represented by a histogram
Given a byte count, returns a string consisting of a value and the units in a human-readable format.
Given a time in picoseconds, returns a human-readable time value and unit indicator
Returns a JSON array containing an element for each value in a given set of JSON or SQL values.
Whether a value is found in a given JSON document or at a specified path within the document
Indicates whether the given JSON document contains data at the specified path or paths
Recursively sorts keys and removes spaces, allowing comparison of json documents for equality
Alias for json_detailed
Returns the first value in the set whose ordered position is the same or more than the specified fraction.
Applies the Ramer–Douglas–Peucker algorithm to provide generalized simplifications.
Whether a geometry is compliant with the WKB format and SRID syntax, and is geometrically valid.
Returns a string consisting of a value and the units in a human-readable format
Returns a modified path after replacing subpaths matching the values of various system variables with the variable name
Whether Performance Schema instrumentation for the given account is enabled
Whether Performance Schema instrumentation for the given consumer is enabled
Whether a given Performance Schema instrument is enabled by default
Returns whether a given Performance Schema instrument is timed by default
Returns whether or not Performance Schema instrumentation for the given connection_id is enabled
Returns the account (username@hostname) associated with the given thread_id
Returns all statements, stages, and events within the Performance Schema for a given thread_id
Returns a JSON object with information about the thread specified by the given thread_id
Quotes a string to produce a result that can be used as an identifier in an SQL statement
Interprets pairs of hex digits as a number and converts to the character represented by the number
Returns the Global Transaction ID of the most recent write transaction observed by the client.
Returns the Global Transaction ID of the most recent write transaction performed by the client.
Blocks the client until the transaction specified by the given Global Transaction ID is applied and committed by the node
This page is licensed: CC BY-SA / Gnu FDL
Learn about aggregate functions in MariaDB Server. This section details SQL functions like COUNT(), SUM(), AVG(), MIN(), and MAX() for performing calculations on sets of rows.
AVG([DISTINCT] expr)
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 GROUP BY clause.
AVG() returns NULL if there were no matching rows.
AVG() can be used as a window function.
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 |
+----------------------------+
Commonly, AVG() is used with a GROUP BY clause:
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 |
+---------+------------+
Be careful to avoid this common mistake, not grouping correctly and returning mismatched data:
SELECT name,test,AVG(score) FROM student;
+------+------+------------+
| name | test | MIN(score) |
+------+------+------------+
| Chun | SQL | 31 |
+------+------+------------+
As a window function:
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 |
+---------+--------+-------+-----------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
BIT_AND(expr) [over_clause]
Returns the bitwise AND of all bits in expr. The calculation is performed with 64-bit (BIGINT) precision. It is an aggregate function, and so can be used with the GROUP BY 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 window function with the addition of the 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 |
+------------+-----------+------------+
As an aggregate function:
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 |
+----------+------------+-----------+------------+
No match:
SELECT BIT_AND(NULL);
+----------------------+
| BIT_AND(NULL) |
+----------------------+
| 18446744073709551615 |
+----------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
BIT_OR(expr) [over_clause]
Returns the bitwise OR of all bits in expr
. The calculation is performed with 64-bit (BIGINT) precision. It is an aggregate function, and so can be used with the GROUP BY 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.
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 |
+------------+-----------+------------+
As an aggregate function:
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 |
+----------+------------+-----------+------------+
No match:
SELECT BIT_OR(NULL);
+--------------+
| BIT_OR(NULL) |
+--------------+
| 0 |
+--------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
BIT_XOR(expr) [over_clause]
Returns the bitwise XOR of all bits in expr
. The calculation is performed with 64-bit (BIGINT) precision. It is an aggregate function, and so can be used with the GROUP BY 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.
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 |
+------------+-----------+------------+
As an aggregate function:
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 |
+----------+------------+-----------+------------+
No match:
SELECT BIT_XOR(NULL);
+---------------+
| BIT_XOR(NULL) |
+---------------+
| 0 |
+---------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
COUNT(DISTINCT expr,[expr...])
Returns a count of the number of different non-NULL values.
COUNT(DISTINCT) returns 0 if there were no matching rows.
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 |
+------------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
COUNT(expr)
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 aggregate function, and so can be used with the GROUP BY 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 window function.
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 |
+----------+
COUNT(DISTINCT) example:
SELECT COUNT(DISTINCT (name)) FROM student;
+------------------------+
| COUNT(DISTINCT (name)) |
+------------------------+
| 4 |
+------------------------+
As a window function
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 |
+---------+--------+-------+---------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
GROUP_CONCAT(expr)
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.
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:
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}])
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.
The LIMIT clause can be used with GROUP_CONCAT
.
SELECT student_name,
GROUP_CONCAT(test_score)
FROM student
GROUP BY student_name;
Get a readable list of MariaDB users from the mysql.user table:
SELECT GROUP_CONCAT(DISTINCT User ORDER BY User SEPARATOR '\n')
FROM mysql.user;
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:
SELECT User, GROUP_CONCAT(Host ORDER BY Host SEPARATOR ', ')
FROM mysql.user GROUP BY User ORDER BY User;
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:
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);
the following query:
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 |
+----------------------------------------------------------------------------+
can be more simply rewritten as:
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 |
+-------------------------------------------------------------+
NULLS:
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 |
+--------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
MAX([DISTINCT] expr)
Returns the largest, or maximum, value of 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
.
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 |
+---------+------------+
MAX
string:
SELECT MAX(name) FROM student;
+-----------+
| MAX(name) |
+-----------+
| Tatiana |
+-----------+
Be careful to avoid this common mistake, not grouping correctly and returning mismatched data:
SELECT name,test,MAX(SCORE) FROM student;
+------+------+------------+
| name | test | MAX(SCORE) |
+------+------+------------+
| Chun | SQL | 88 |
+------+------+------------+
Difference between ORDER BY DESC
and MAX()
:
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 |
+-------+
As a window function:
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 |
+---------+--------+-------+---------------+
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
MIN([DISTINCT] expr)
Returns the minimum value of 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
.
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 |
+---------+------------+
MIN()
with a string:
SELECT MIN(name) FROM student;
+-----------+
| MIN(name) |
+-----------+
| Chun |
+-----------+
Be careful to avoid this common mistake, not grouping correctly and returning mismatched data:
SELECT name,test,MIN(score) FROM student;
+------+------+------------+
| name | test | MIN(score) |
+------+------+------------+
| Chun | SQL | 31 |
+------+------+------------+
Difference between ORDER BY ASC
and MIN()
:
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 |
+-------+
As a window function:
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 |
+---------+--------+-------+--------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
STD(expr)
Returns the population standard deviation of expr
. This is an extension to standard SQL. The standard SQL function STDDEV_POP() 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.
As an aggregate function:
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 |
+----------+---------------+----------------+------------+
As a window function:
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 (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(expr)
Returns the population standard deviation of expr
. This function is provided for compatibility with Oracle. The standard SQL functionSTDDEV_POP() 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.
As an aggregate function:
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 |
+----------+---------------+----------------+------------+
As a window function:
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 (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)
Returns the population standard deviation of expr
(the square root of VAR_POP()). You can also use STD() or STDDEV(), 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.
As an aggregate function:
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 |
+----------+---------------+----------------+------------+
As a window function:
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 |
+---------+--------+-------+----------------+
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
STDDEV_SAMP(expr)
Returns the sample standard deviation of expr
(the square root of VAR_SAMP()).
It is an aggregate function, and so can be used with the GROUP BY clause.
STDDEV_SAMP()
can be used as a window function.
STDDEV_SAMP()
returns NULL
if there were no matching rows.
This page is licensed: GPLv2, originally from fill_help_tables.sql
SUM([DISTINCT] expr)
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 window function, although not with the DISTINCT
specifier.
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 |
+----------------------------+
Commonly, SUM
is used with a GROUP BY clause:
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 |
+---------+------------+
The GROUP BY 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:
SELECT name,SUM(units) FROM sales
;+------+------------+
| name | SUM(units) |
+------+------------+
| Chun | 536 |
+------+------------+
As a window function:
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 |
+---------+--------+-------+-------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
VAR_POP(expr)
Returns the population standard variance of expr
. 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 VARIANCE(), 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.
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 |
+------------+
As an aggregate function:
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 |
+----------+---------------+----------------+------------+
As a window function:
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 |
+---------+--------+-------+------------------+
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
VAR_SAMP(expr)
Returns the sample variance of expr
. That is, the denominator is the number of rows minus one.
It is an aggregate function, and so can be used with the GROUP BY clause.
VAR_SAMP() can be used as a window function.
VAR_SAMP() returns NULL
if there were no matching rows.
As an aggregate function:
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 |
+----------+---------------+----------------+------------+
As a window function:
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 |
+---------+--------+-------+------------------+
VAR_POP (variance)
STDDEV_POP (population standard deviation)
This page is licensed: GPLv2, originally from fill_help_tables.sql
VARIANCE(expr)
Returns the population standard variance of expr
. This is an extension to standard SQL. The standard SQL function VAR_POP() 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.
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 |
+-------------+
As an aggregate function:
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 |
+----------+---------------+----------------+------------+
As a window function:
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 |
+---------+--------+-------+------------------+
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
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.
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
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 CASE statement, which differs from the CASE operator described here.
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 |
+-----------------------------------------------------+
Only the first matching condition is processed:
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 +
+-------------------------------------------------------------------------------------+
The CASE statement, which differs from the CASE operator described above.
The IF() function.
This page is licensed: GPLv2, originally from fill_help_tables.sql
DECODE_ORACLE
is a synonym for the Oracle mode version of the DECODE function, and is available in all modes.
This page is licensed: CC BY-SA / Gnu FDL
IF(expr1,expr2,expr3)
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 IF statement which differs from theIF()
function described here.
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 |
+---------------------------------------+
There is also an IF statement, which differs from the IF()
function described above.
This page is licensed: GPLv2, originally from fill_help_tables.sql
IFNULL(expr1,expr2)
NVL(expr1,expr2)
If expr1
is not NULL
, IFNULL()
returns expr1
; otherwise it returnsexpr2
. IFNULL()
returns a numeric or string value, depending on the context in which it is used.
NVL()
is an alias for IFNULL()
.
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 |
+-------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
NULLIF(expr1,expr2)
Returns NULL
if expr1 = expr2 is true, otherwise returns expr1. This is the same as CASE WHEN
expr1 = expr2 THEN NULL ELSE
expr1 END
.
SELECT NULLIF(1,1);
+-------------+
| NULLIF(1,1) |
+-------------+
| NULL |
+-------------+
SELECT NULLIF(1,2);
+-------------+
| NULLIF(1,2) |
+-------------+
| 1 |
+-------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
NVL
is a synonym for IFNULL.
This page is licensed: CC BY-SA / Gnu FDL
NVL2(expr1,expr2,expr3)
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.
SELECT NVL2(NULL,1,2);
+----------------+
| NVL2(NULL,1,2) |
+----------------+
| 2 |
+----------------+
SELECT NVL2('x',1,2);
+---------------+
| NVL2('x',1,2) |
+---------------+
| 1 |
+---------------+
This page is licensed: CC BY-SA / Gnu FDL
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 INTERVAL
keyword can be used to add or subtract a time interval of time to a DATETIME, DATE or TIME value.
The syntax is:
INTERVAL time_quantity time_unit
For example, the SECOND
unit is used below by the DATE_ADD() function:
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 |
+-------------------------------------------+
The following units are valid:
MICROSECOND
Microseconds
SECOND
Seconds
MINUTE
Minutes
HOUR
Hours
DAY
Days
WEEK
Weeks
MONTH
Months
QUARTER
Quarters
YEAR
Years
SECOND_MICROSECOND
Seconds.Microseconds
MINUTE_MICROSECOND
Minutes.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:
INTERVAL '2:2' YEAR_MONTH
INTERVAL '1:30:30' HOUR_SECOND
INTERVAL '1!30!30' HOUR_SECOND -- same as above
Time units can be used in the following contexts:
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 partitioning BY SYSTEM_TIME
.
This page is licensed: CC BY-SA / Gnu FDL
The ADD_MONTHS
function was introduced to enhance Oracle compatibility. Similar functionality can be achieved with the DATE_ADD function.
ADD_MONTHS(date, months)
ADD_MONTHS
adds an integer months to a given date (DATE, DATETIME or TIMESTAMP), returning the resulting date.
months can be positive or negative. If months is not a whole number, then it will be rounded to the nearest whole number (not truncated).
The resulting day component will remain the same as that specified in date, unless the resulting month has fewer days than the day component of the given date, in which case the day will be the last day of the resulting month.
Returns NULL if given an invalid date, or a NULL
argument.
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)
This page is licensed: CC BY-SA / Gnu FDL
ADDDATE(date,INTERVAL expr unit), ADDDATE(expr,days)
When invoked with the INTERVAL
form of the second argument, ADDDATE()
is a synonym for DATE_ADD(). The related functionSUBDATE() is a synonym for DATE_SUB(). For information on the INTERVAL
unit argument, see the discussion forDATE_ADD().
When invoked with the days form of the second argument, MariaDB treats it as an integer number of days to be added to expr.
SELECT DATE_ADD('2008-01-02', INTERVAL 31 DAY);
+-----------------------------------------+
| DATE_ADD('2008-01-02', INTERVAL 31 DAY) |
+-----------------------------------------+
| 2008-02-02 |
+-----------------------------------------+
SELECT ADDDATE('2008-01-02', INTERVAL 31 DAY);
+----------------------------------------+
| ADDDATE('2008-01-02', INTERVAL 31 DAY) |
+----------------------------------------+
| 2008-02-02 |
+----------------------------------------+
SELECT ADDDATE('2008-01-02', 31);
+---------------------------+
| ADDDATE('2008-01-02', 31) |
+---------------------------+
| 2008-02-02 |
+---------------------------+
CREATE TABLE t1 (d DATETIME);
INSERT INTO t1 VALUES
("2007-01-30 21:31:07"),
("1983-10-15 06:42:51"),
("2011-04-21 12:34:56"),
("2011-10-30 06:31:41"),
("2011-01-30 14:03:25"),
("2004-10-07 11:19:34");
SELECT d, ADDDATE(d, 10) from t1;
+---------------------+---------------------+
| d | ADDDATE(d, 10) |
+---------------------+---------------------+
| 2007-01-30 21:31:07 | 2007-02-09 21:31:07 |
| 1983-10-15 06:42:51 | 1983-10-25 06:42:51 |
| 2011-04-21 12:34:56 | 2011-05-01 12:34:56 |
| 2011-10-30 06:31:41 | 2011-11-09 06:31:41 |
| 2011-01-30 14:03:25 | 2011-02-09 14:03:25 |
| 2004-10-07 11:19:34 | 2004-10-17 11:19:34 |
+---------------------+---------------------+
SELECT d, ADDDATE(d, INTERVAL 10 HOUR) from t1;
+---------------------+------------------------------+
| d | ADDDATE(d, INTERVAL 10 HOUR) |
+---------------------+------------------------------+
| 2007-01-30 21:31:07 | 2007-01-31 07:31:07 |
| 1983-10-15 06:42:51 | 1983-10-15 16:42:51 |
| 2011-04-21 12:34:56 | 2011-04-21 22:34:56 |
| 2011-10-30 06:31:41 | 2011-10-30 16:31:41 |
| 2011-01-30 14:03:25 | 2011-01-31 00:03:25 |
| 2004-10-07 11:19:34 | 2004-10-07 21:19:34 |
+---------------------+------------------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
ADDTIME(expr1,expr2)
ADDTIME()
adds expr2 to expr1 and returns the result. expr1 is a time or datetime expression, and expr2 is a time expression.
SELECT ADDTIME('2007-12-31 23:59:59.999999', '1 1:1:1.000002');
+---------------------------------------------------------+
| ADDTIME('2007-12-31 23:59:59.999999', '1 1:1:1.000002') |
+---------------------------------------------------------+
| 2008-01-02 01:01:01.000001 |
+---------------------------------------------------------+
SELECT ADDTIME('01:00:00.999999', '02:00:00.999998');
+-----------------------------------------------+
| ADDTIME('01:00:00.999999', '02:00:00.999998') |
+-----------------------------------------------+
| 03:00:01.999997 |
+-----------------------------------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
CONVERT_TZ(dt,from_tz,to_tz)
CONVERT_TZ()
converts a datetime value dt from the time zone 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.
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 |
+-----------------------------------------------------+
Using named time zones (with the time zone tables loaded):
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 |
+---------------------------------------------------------------+
The value is out of the TIMESTAMP
range, so no conversion takes place:
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 |
+-----------------------------------------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
CURDATE()
CURRENT_DATE
CURRENT_DATE()
CURDATE
returns the current date as a value in YYYY-MM-DD
or YYYYMMDD
format, depending on whether the function is used in a string or numeric context.
CURRENT_DATE
and CURRENT_DATE()
are synonyms.
SELECT CURDATE();
+------------+
| CURDATE() |
+------------+
| 2019-03-05 |
+------------+
In a numeric context (note this is not performing date calculations):
SELECT CURDATE() +0;
+--------------+
| CURDATE() +0 |
+--------------+
| 20190305 |
+--------------+
Date calculation:
SELECT CURDATE() - INTERVAL 5 DAY;
+----------------------------+
| CURDATE() - INTERVAL 5 DAY |
+----------------------------+
| 2019-02-28 |
+----------------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
CURRENT_DATE, CURRENT_DATE()
CURRENT_DATE
and CURRENT_DATE()
are synonyms for CURDATE().
This page is licensed: GPLv2, originally from fill_help_tables.sql
CURRENT_TIME
CURRENT_TIME([precision])
CURRENT_TIME
and CURRENT_TIME()
are synonyms for CURTIME().
This page is licensed: GPLv2, originally from fill_help_tables.sql
CURRENT_TIMESTAMP
CURRENT_TIMESTAMP([precision])
CURRENT_TIMESTAMP
and CURRENT_TIMESTAMP()
are synonyms for NOW().
The TIMESTAMP data type
This page is licensed: GPLv2, originally from fill_help_tables.sql
CURTIME([precision])
Returns the current time as a value in HH:MM:SS
or HHMMSS.uuuuuu
format, depending on whether the function is used in a string or numeric context. The value is expressed in the current time zone.
The optional precision determines the microsecond precision. See Microseconds in MariaDB.
SELECT CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 12:45:39 |
+-----------+
SELECT CURTIME() + 0;
+---------------+
| CURTIME() + 0 |
+---------------+
| 124545.000000 |
+---------------+
With precision:
SELECT CURTIME(2);
+-------------+
| CURTIME(2) |
+-------------+
| 09:49:08.09 |
+-------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
DATE(expr)
Extracts the date part of the date or datetime expression expr. Returns NULL and throws a warning when passed an invalid date.
SELECT DATE('2013-07-18 12:21:32');
+-----------------------------+
| DATE('2013-07-18 12:21:32') |
+-----------------------------+
| 2013-07-18 |
+-----------------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
DATE_ADD(date,INTERVAL expr unit)
Performs date arithmetic. The date argument specifies the starting date or datetime value. expr is an expression specifying the interval value to be added to the starting date. expr is a string; it may start with a "-
" for negative intervals. unit is a keyword indicating the units in which the expression should be interpreted. See Date and Time Units 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.
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 |
+--------------------------------------------------------------------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
DATE_FORMAT(date, format[, locale])
Formats the date value according to the format string.
The language used for the names is controlled by the value of the lc_time_names system variable. See server locale for more on the supported locales.
The options that can be used by DATE_FORMAT(), as well as its inverse STR_TO_DATE() and the FROM_UNIXTIME() function, are:
%a
Short weekday name in current locale (Variable 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.
%f
Microseconds 6 digits.
%H
Hour with 2 digits between 00-23.
%h
Hour with 2 digits between 01-12.
%I
Hour with 2 digits between 01-12.
%i
Minute with 2 digits.
%j
Day of the year (001-366)
%k
Hour with 1 digits between 0-23.
%l
Hour with 1 digits between 1-12.
%M
Full month name in current locale (Variable lc_time_names).
%m
Month with 2 digits.
%p
AM/PM according to current locale (Variable lc_time_names).
%r
Time in 12 hour format, followed by AM/PM. Short for '%I:%i:%S %p'.
%S
Seconds with 2 digits.
%s
Seconds with 2 digits.
%T
Time in 24 hour format. Short for '%H:%i:%S'.
%U
Week number (00-53), when first day of the week is Sunday.
%u
Week number (00-53), when first day of the week is Monday.
%V
Week number (01-53), when first day of the week is Sunday. Used with %X.
%v
Week number (01-53), when first day of the week is Monday. Used with %x.
%W
Full weekday name in current locale (Variable lc_time_names).
%w
Day of the week. 0 = Sunday, 6 = Saturday.
%X
Year with 4 digits when first day of the week is Sunday. Used with %V.
%x
Year with 4 digits when first day of the week is Monday. Used with %v.
%Y
Year with 4 digits.
%y
Year with 2 digits.
%Z
Timezone abbreviation. From MariaDB 11.3.0.
%z
Numeric timezone +hhmm or -hhmm presenting the hour and minute offset from UTC. From MariaDB 11.3.0.
%#
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.
To get a date in one of the standard formats, GET_FORMAT() can be used.
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 |
+---------------------------------+
Optionally, the locale can be explicitly specified as the third DATE_FORMAT() argument. Doing so makes the function independent from the session settings, and the three argument version of DATE_FORMAT() can be used in virtual indexed and persistent generated-columns:
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 |
+--------------------------------------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
DATE_SUB(date,INTERVAL expr unit)
Performs date arithmetic. The date argument specifies the starting date or datetime value. expr is an expression specifying the interval value to be subtracted from the starting date. expr is a string; it may start with a "-
" for negative intervals. unit is a keyword indicating the units in which the expression should be interpreted. See Date and Time Units for a complete list of permitted units.
See also DATE_ADD().
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 |
+----------------------------------------------------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
DATEDIFF(expr1,expr2)
DATEDIFF()
returns (expr1 – expr2) expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation.
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 |
+----------------------------------------------+
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 |
+---------------------+-------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
DAY(date)
DAY()
is a synonym for DAYOFMONTH().
This page is licensed: GPLv2, originally from fill_help_tables.sql
DAYNAME(date)
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.
SELECT DAYNAME('2007-02-03');
+-----------------------+
| DAYNAME('2007-02-03') |
+-----------------------+
| Saturday |
+-----------------------+
CREATE TABLE t1 (d DATETIME);
INSERT INTO t1 VALUES
("2007-01-30 21:31:07"),
("1983-10-15 06:42:51"),
("2011-04-21 12:34:56"),
("2011-10-30 06:31:41"),
("2011-01-30 14:03:25"),
("2004-10-07 11:19:34");
SELECT d, DAYNAME(d) FROM t1;
+---------------------+------------+
| d | DAYNAME(d) |
+---------------------+------------+
| 2007-01-30 21:31:07 | Tuesday |
| 1983-10-15 06:42:51 | Saturday |
| 2011-04-21 12:34:56 | Thursday |
| 2011-10-30 06:31:41 | Sunday |
| 2011-01-30 14:03:25 | Sunday |
| 2004-10-07 11:19:34 | Thursday |
+---------------------+------------+
Changing the locale:
SET lc_time_names = 'fr_CA';
SELECT DAYNAME('2013-04-01');
+-----------------------+
| DAYNAME('2013-04-01') |
+-----------------------+
| lundi |
+-----------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
DAYOFMONTH(date)
Returns the day of the month for date, in the range 1
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.
SELECT DAYOFMONTH('2007-02-03');
+--------------------------+
| DAYOFMONTH('2007-02-03') |
+--------------------------+
| 3 |
+--------------------------+
CREATE TABLE t1 (d DATETIME);
INSERT INTO t1 VALUES
("2007-01-30 21:31:07"),
("1983-10-15 06:42:51"),
("2011-04-21 12:34:56"),
("2011-10-30 06:31:41"),
("2011-01-30 14:03:25"),
("2004-10-07 11:19:34");
SELECT 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 |
+---------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
DAYOFWEEK(date)
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 WEEKDAY() which follows a different index numbering (0
= Monday, 1
= Tuesday, ... 6
= Sunday).
SELECT DAYOFWEEK('2007-02-03');
+-------------------------+
| DAYOFWEEK('2007-02-03') |
+-------------------------+
| 7 |
+-------------------------+
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 |
+---------------------+------------+--------------+------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
DAYOFYEAR(date)
Returns the day of the year for date, in the range 1 to 366.
SELECT DAYOFYEAR('2018-02-16');
+-------------------------+
| DAYOFYEAR('2018-02-16') |
+-------------------------+
| 47 |
+-------------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
EXTRACT(unit FROM date)
The EXTRACT() function extracts the required unit from the date. See Date and Time Units for a complete list of permitted units.
HOUR() is not a standard SQL function, so continues to adhere to the old behavior inherited from MySQL.
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 |
+--------------------------------------------------------+
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.
SELECT EXTRACT(HOUR FROM '26:30:00'), HOUR('26:30:00');
+-------------------------------+------------------+
| EXTRACT(HOUR FROM '26:30:00') | HOUR('26:30:00') |
+-------------------------------+------------------+
| 2 | 26 |
+-------------------------------+------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
FORMAT_PICO_TIME(time_val)
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.
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 |
+--------+---------+----------+-----------+---------+----------+--------+--------+
This page is licensed: CC BY-SA / Gnu FDL
FROM_DAYS(N)
Given a day number N, returns a DATE value. The day count is based on the number of days from the start of the standard calendar (0000-00-00).
The function is not designed for use with dates before the advent of the Gregorian calendar in October 1582. Results will not be reliable since it doesn't account for the lost days when the calendar changed from the Julian calendar.
This is the converse of the TO_DAYS() function.
SELECT FROM_DAYS(730669);
+-------------------+
| FROM_DAYS(730669) |
+-------------------+
| 2000-07-03 |
+-------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
FROM_UNIXTIME(unix_timestamp)
FROM_UNIXTIME(unix_timestamp,format)
Converts the number of seconds from the epoch (1970-01-01 00:00:00 UTC) to aTIMESTAMP
value, the opposite of what UNIX_TIMESTAMP() 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
DATE_FORMAT(FROM_UNIXTIME(unix_timestamp), format)
Timestamps in MariaDB have a maximum value of 4294967295, equivalent to 2106-02-07 06:28:15
. This is due to the underlying 32-bit limitation. Using the function on a timestamp beyond this will result in NULL being returned. Use DATETIME as a storage type if you require dates beyond this.
The one-argument form of FROM_UNIXTIME()
returns aDATETIME
. This means that it can return values outside of valid TIMESTAMP
range, in particular 1970-01-01 00:00:00
. And it can return the same result for different values of unix_timestamp (around DST changes).
Timestamps in MariaDB have a maximum value of 4294967295, equivalent to 2106-02-07 06:28:15
. This is due to the underlying 32-bit limitation. Using the function on a timestamp beyond this will result in NULL being returned. Use DATETIME as a storage type if you require dates beyond this.
The one-argument form of FROM_UNIXTIME()
returns aDATETIME
. This means that it can return values outside of valid TIMESTAMP
range, in particular 1970-01-01 00:00:00
. And it can return the same result for different values of unix_timestamp (around DST changes).
The maximum value is 2147483647, equivalent to 2038-01-19 05:14:07
.
The following options can be used by FROM_UNIXTIME()
, as well as DATE_FORMAT() and STR_TO_DATE():
%a
Short weekday name in current locale (Variable 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.
%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.
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.
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 |
+---------------------------------------------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
GET_FORMAT({DATE|DATETIME|TIME}, {'EUR'|'USA'|'JIS'|'ISO'|'INTERNAL'})
Returns a format string. This function is useful in combination with the DATE_FORMAT() and the STR_TO_DATE() functions.
Possible result formats are:
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'
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'
Obtaining the string matching to the standard European date format:
SELECT GET_FORMAT(DATE, 'EUR');
+-------------------------+
| GET_FORMAT(DATE, 'EUR') |
+-------------------------+
| %d.%m.%Y |
+-------------------------+
Using the same string to format a date:
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 |
+--------------------------------------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
HOUR(time)
Returns the hour for time. The range of the return value is 0 to 23 for time-of-day values. However, the range of TIME
values actually is much larger, so HOUR can return values greater than 23.
The return value is always positive, even if a negative TIME
value is provided.
SELECT HOUR('10:05:03');
+------------------+
| HOUR('10:05:03') |
+------------------+
| 10 |
+------------------+
SELECT HOUR('272:59:59');
+-------------------+
| HOUR('272:59:59') |
+-------------------+
| 272 |
+-------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
LAST_DAY(date)
Takes a date or datetime value and returns the corresponding value for the last day of the month. Returns NULL
if the argument is invalid.
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'
This page is licensed: GPLv2, originally from fill_help_tables.sql
LOCALTIME
LOCALTIME([precision])
LOCALTIME
and LOCALTIME()
are synonyms for NOW().
This page is licensed: GPLv2, originally from fill_help_tables.sql
LOCALTIMESTAMP
LOCALTIMESTAMP([precision])
LOCALTIMESTAMP
and LOCALTIMESTAMP()
are synonyms for NOW().
This page is licensed: GPLv2, originally from fill_help_tables.sql
MAKEDATE(year,dayofyear)
Returns a date, given year
and day-of-year values
. dayofyear
must be greater than 0 or the result is NULL
.
SELECT MAKEDATE(2011,31), MAKEDATE(2011,32);
+-------------------+-------------------+
| MAKEDATE(2011,31) | MAKEDATE(2011,32) |
+-------------------+-------------------+
| 2011-01-31 | 2011-02-01 |
+-------------------+-------------------+
2012 is a leap year:
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 |
+------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
MAKETIME(hour,minute,second)
Returns a time value calculated from the hour
, minute
, and second
arguments.
If minute
or second
are out of the range 0 to 60, NULL
is returned. The hour
can be in the range -838 to 838, outside of which the value is truncated with a warning.
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' |
+---------+------+-----------------------------------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
MICROSECOND(expr)
Returns the microseconds from the time or datetime expression expr as a number in the range from 0 to 999999.
If expr is a time with no microseconds, zero is returned, while if expr is a date with no time, zero with a warning is returned.
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' |
+---------+------+----------------------------------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
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:
CREATE TABLE example(
col_microsec DATETIME(6),
col_millisec TIME(3)
);
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.
CREATE FUNCTION example(x DATETIME(5)) RETURNS TIME(4)
BEGIN
DECLARE y TIMESTAMP(6);
RETURN CAST(x AS time(2));
END;
%f
is used as the formatting option for microseconds in the STR_TO_DATE, DATE_FORMAT and FROM_UNIXTIME functions, for example:
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 |
+-----------------------------------------------------+
When comparing anything to a temporal value (DATETIME
, TIME
, DATE, or TIMESTAMP
), both values are compared as temporal values, not as strings.
The INFORMATION_SCHEMA.COLUMNS table has a new column DATETIME_PRECISION
NOW(), CURTIME(), UTC_TIMESTAMP(), UTC_TIME(), CURRENT_TIME(), CURRENT_TIMESTAMP(), LOCALTIME() and LOCALTIMESTAMP() accept datetime precision as an optional argument. For example:
SELECT CURTIME(4);
--> 10:11:12.3456
TIME_TO_SEC() and UNIX_TIMESTAMP() preserve microseconds of the argument. These functions will return a decimal number if the result non-zero datetime precision and an integer otherwise (for backward compatibility).
SELECT TIME_TO_SEC('10:10:10.12345');
--> 36610.12345
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).
SELECT TIME('10:10:10') + INTERVAL 100 MICROSECOND;
--> 10:10:10.000100
The event_time
field in the mysql.general_log table and the start_time
, query_time
, and lock_time
fields in the mysql.slow_log table now store values with microsecond precision.
The old syntax TIMESTAMP(N)
, where N
is the display width, is no longer supported.
When a DATETIME
value is compared to a TIME
value, the latter is treated as a full datetime with a zero date part, similar to comparing DATE
to a DATETIME
, or to comparing DECIMAL
numbers.
Earlier versions of MariaDB used to compare only the time part of both operands in such a case.
In MariaDB, an extra column TIME_MS has been added to the INFORMATION_SCHEMA.PROCESSLIST table, as well as to the output of SHOW FULL PROCESSLIST.
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:
SELECT CAST('2009-12-31 23:59:59.998877' AS DATETIME(3));
-> 2009-12-31 23:59:59.998
This page is licensed: CC BY-SA / Gnu FDL
MINUTE(time)
Returns the minute for time, in the range 0 to 59.
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 |
+---------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
MONTH(date)
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.
SELECT MONTH('2019-01-03');
+---------------------+
| MONTH('2019-01-03') |
+---------------------+
| 1 |
+---------------------+
SELECT MONTH('2019-00-03');
+---------------------+
| MONTH('2019-00-03') |
+---------------------+
| 0 |
+---------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
MONTHNAME(date)
Returns the full name of the month for date. The language used for the name is controlled by the value of the lc_time_names system variable. See server locale for more on the supported locales.
SELECT MONTHNAME('2019-02-03');
+-------------------------+
| MONTHNAME('2019-02-03') |
+-------------------------+
| February |
+-------------------------+
Changing the locale:
SET lc_time_names = 'fr_CA';
SELECT MONTHNAME('2019-05-21');
+-------------------------+
| MONTHNAME('2019-05-21') |
+-------------------------+
| mai |
+-------------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
NOW([precision])
CURRENT_TIMESTAMP
CURRENT_TIMESTAMP([precision])
LOCALTIME, LOCALTIME([precision])
LOCALTIMESTAMP
LOCALTIMESTAMP([precision])
Returns the current date and time as a value in YYYY-MM-DD HH:MM:SS
or YYYYMMDDHHMMSS.uuuuuu
format, depending on whether the function is used in a string or numeric context. The value is expressed in the current time zone.
MariaDB starting with 11.7
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().
SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2010-03-27 13:13:25 |
+---------------------+
SELECT NOW() + 0;
+-----------------------+
| NOW() + 0 |
+-----------------------+
| 20100327131329.000000 |
+-----------------------+
With precision:
SELECT CURRENT_TIMESTAMP(2);
+------------------------+
| CURRENT_TIMESTAMP(2) |
+------------------------+
| 2018-07-10 09:47:26.24 |
+------------------------+
Used as a default TIMESTAMP:
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()
...
This page is licensed: GPLv2, originally from fill_help_tables.sql
PERIOD_ADD(P,N)
Adds N
months to period 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
.
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 |
+--------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
PERIOD_DIFF(P1,P2)
Returns the number of months between periods P1 and P2. P1 and P2 can be in the format YYMM
or YYYYMM
, and are not date values.
If P1 or P2 contains a two-digit year, values from 00 to 69 are converted to from 2000 to 2069, while values from 70 are converted to 1970 upwards.
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 |
+------------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
QUARTER(date)
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).
SELECT QUARTER('2008-04-01');
+-----------------------+
| QUARTER('2008-04-01') |
+-----------------------+
| 2 |
+-----------------------+
SELECT QUARTER('2019-00-01');
+-----------------------+
| QUARTER('2019-00-01') |
+-----------------------+
| 0 |
+-----------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
SEC_TO_TIME(seconds)
Returns the seconds argument, converted to hours, minutes, and seconds, as a TIME value. The range of the result is constrained to that of the TIME data type. A warning occurs if the argument corresponds to a value outside that range.
The time will be returned in the format hh:mm:ss
, or hhmmss
if used in a numeric calculation.
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' |
+---------+------+-------------------------------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
SECOND(time)
Returns the second for a given time
(which can include microseconds), in the range 0 to 59, or NULL
if not given a valid time value.
SELECT SECOND('10:05:03');
+--------------------+
| SECOND('10:05:03') |
+--------------------+
| 3 |
+--------------------+
SELECT SECOND('10:05:01.999999');
+---------------------------+
| SECOND('10:05:01.999999') |
+---------------------------+
| 1 |
+---------------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
STR_TO_DATE(str,format)
This is the inverse of the DATE_FORMAT() function. It takes a string str
and a format string format
. STR_TO_DATE()
returns 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 SQL_MODE settings an error may also be generated if the str
isn't a valid date:
The options that can be used by STR_TO_DATE(), as well as its inverse DATE_FORMAT() and the FROM_UNIXTIME() function, are:
%a
Short weekday name in current locale (Variable 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.
%f
Microseconds 6 digits.
%H
Hour with 2 digits between 00-23.
%h
Hour with 2 digits between 01-12.
%I
Hour with 2 digits between 01-12.
%i
Minute with 2 digits.
%j
Day of the year (001-366)
%k
Hour with 1 digits between 0-23.
%l
Hour with 1 digits between 1-12.
%M
Full month name in current locale (Variable lc_time_names).
%m
Month with 2 digits.
%p
AM/PM according to current locale (Variable lc_time_names).
%r
Time in 12 hour format, followed by AM/PM. Short for '%I:%i:%S %p'.
%S
Seconds with 2 digits.
%s
Seconds with 2 digits.
%T
Time in 24 hour format. Short for '%H:%i:%S'.
%U
Week number (00-53), when first day of the week is Sunday.
%u
Week number (00-53), when first day of the week is Monday.
%V
Week number (01-53), when first day of the week is Sunday. Used with %X.
%v
Week number (01-53), when first day of the week is Monday. Used with %x.
%W
Full weekday name in current locale (Variable lc_time_names).
%w
Day of the week. 0 = Sunday, 6 = Saturday.
%X
Year with 4 digits when first day of the week is Sunday. Used with %V.
%x
Year with 4 digits when first day of the week is Monday. Used with %v.
%Y
Year with 4 digits.
%y
Year with 2 digits.
%#
For str_to_date(), skip all numbers.
%.
For str_to_date(), skip all punctation characters.
%@
For str_to_date(), skip all alpha characters.
%%
A literal % character.
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 |
+----------------------------------------------------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
SUBDATE(date,INTERVAL expr unit), SUBDATE(expr,days)
When invoked with the INTERVAL
form of the second argument, SUBDATE()
is a synonym for DATE_SUB(). See Date and Time Units 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.
SELECT DATE_SUB('2008-01-02', INTERVAL 31 DAY);
+-----------------------------------------+
| DATE_SUB('2008-01-02', INTERVAL 31 DAY) |
+-----------------------------------------+
| 2007-12-02 |
+-----------------------------------------+
SELECT SUBDATE('2008-01-02', INTERVAL 31 DAY);
+----------------------------------------+
| SUBDATE('2008-01-02', INTERVAL 31 DAY) |
+----------------------------------------+
| 2007-12-02 |
+----------------------------------------+
SELECT SUBDATE('2008-01-02 12:00:00', 31);
+------------------------------------+
| SUBDATE('2008-01-02 12:00:00', 31) |
+------------------------------------+
| 2007-12-02 12:00:00 |
+------------------------------------+
CREATE TABLE t1 (d DATETIME);
INSERT INTO t1 VALUES
("2007-01-30 21:31:07"),
("1983-10-15 06:42:51"),
("2011-04-21 12:34:56"),
("2011-10-30 06:31:41"),
("2011-01-30 14:03:25"),
("2004-10-07 11:19:34");
SELECT d, SUBDATE(d, 10) from t1;
+---------------------+---------------------+
| d | SUBDATE(d, 10) |
+---------------------+---------------------+
| 2007-01-30 21:31:07 | 2007-01-20 21:31:07 |
| 1983-10-15 06:42:51 | 1983-10-05 06:42:51 |
| 2011-04-21 12:34:56 | 2011-04-11 12:34:56 |
| 2011-10-30 06:31:41 | 2011-10-20 06:31:41 |
| 2011-01-30 14:03:25 | 2011-01-20 14:03:25 |
| 2004-10-07 11:19:34 | 2004-09-27 11:19:34 |
+---------------------+---------------------+
SELECT d, SUBDATE(d, INTERVAL 10 MINUTE) from t1;
+---------------------+--------------------------------+
| d | SUBDATE(d, INTERVAL 10 MINUTE) |
+---------------------+--------------------------------+
| 2007-01-30 21:31:07 | 2007-01-30 21:21:07 |
| 1983-10-15 06:42:51 | 1983-10-15 06:32:51 |
| 2011-04-21 12:34:56 | 2011-04-21 12:24:56 |
| 2011-10-30 06:31:41 | 2011-10-30 06:21:41 |
| 2011-01-30 14:03:25 | 2011-01-30 13:53:25 |
| 2004-10-07 11:19:34 | 2004-10-07 11:09:34 |
+---------------------+--------------------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
SUBTIME(expr1,expr2)
SUBTIME() returns expr1
- expr2
expressed as a value in the same format as expr1
. expr1
is a time or datetime expression, and expr2 is a time expression.
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 |
+-----------------------------------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
SYSDATE([precision])
Returns the current date and time as a value in YYYY-MM-DD HH:MM:SS
or YYYYMMDDHHMMSS.uuuuuu
format, depending on whether the function is used in a string or numeric context.
The optional precision determines the microsecond precision. See Microseconds in MariaDB.
SYSDATE()
returns the time at which it executes. This differs from the behavior for NOW(), which returns a constant time that indicates the time at which the statement began to execute. (Within a stored routine or trigger, NOW() returns the time at which the routine or triggering statement began to execute.)
In addition, changing the timestamp system variable with a SET timestamp
statement affects the value returned by NOW()
but not by SYSDATE()
. This means that timestamp settings in the binary log have no effect on invocations of SYSDATE()
.
Because SYSDATE()
can return different values even within the same statement, and is not affected by SET TIMESTAMP
, it is non-deterministic and therefore unsafe for replication if statement-based binary logging is used. If that is a problem, you can use row-based logging, or start the server with the --sysdate-is-now mariadbd option to cause SYSDATE()
to be an alias for NOW()
. The non-deterministic nature of SYSDATE()
also means that indexes cannot be used for evaluating expressions that refer to it, and that statements using the SYSDATE()
function are unsafe for statement-based replication.
Difference between NOW()
and SYSDATE()
:
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 |
+---------------------+----------+---------------------+
With precision:
SELECT SYSDATE(4);
+--------------------------+
| SYSDATE(4) |
+--------------------------+
| 2018-07-10 10:17:13.1689 |
+--------------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
TIME(expr)
Extracts the time part of the time or datetime expression expr
and returns it as a string.
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 |
+------------------------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
TIME_FORMAT(time,format)
This is used like the DATE_FORMAT() function, but the format string may contain format specifiers only for hours, minutes, and seconds. Other specifiers produce a NULL value or 0.
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 |
+--------------------------------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
TIME_TO_SEC(time)
Returns the time argument, converted to seconds.
The value returned by TIME_TO_SEC
is of type DOUBLE. The returned value preserves microseconds of the argument. See also Microseconds in MariaDB.
SELECT TIME_TO_SEC('22:23:00');
+-------------------------+
| TIME_TO_SEC('22:23:00') |
+-------------------------+
| 80580 |
+-------------------------+
SELECT TIME_TO_SEC('00:39:38');
+-------------------------+
| TIME_TO_SEC('00:39:38') |
+-------------------------+
| 2378 |
+-------------------------+
SELECT TIME_TO_SEC('09:12:55.2355');
+------------------------------+
| TIME_TO_SEC('09:12:55.2355') |
+------------------------------+
| 33175.2355 |
+------------------------------+
1 row in set (0.000 sec)
This page is licensed: GPLv2, originally from fill_help_tables.sql
TIMEDIFF(expr1,expr2)
TIMEDIFF() returns expr1
- expr2
expressed as a time value. expr1
andexpr2
are time or date-and-time expressions, but both must be of the
same type.
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 |
+----------------------------------------------------------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
TIMESTAMP(expr), TIMESTAMP(expr1,expr2)
With a single argument, this function returns the date or datetime expression expr
as a datetime value. With two arguments, it adds the time expression expr2
to the date or datetime expression expr1
and returns the result as a datetime value.
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 |
+--------------------------------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
TIMESTAMPADD(unit,interval,datetime_expr)
Adds the integer expression interval to the date or datetime expression datetime_expr. The unit for interval is given by the unit argument, which should be one of the following values: MICROSECOND
, 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.
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 |
+-----------------------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)
Returns datetime_expr2
- datetime_expr1
, where datetime_expr1
anddatetime_expr2
are date or datetime expressions. One expression may be a date and the other a datetime; a date value is treated as a datetime having the time part '00:00:00' where necessary. The unit for the result (an integer) is given by the unit argument. The legal values for unit are the same as those listed in the description of the TIMESTAMPADD() function, that is, MICROSECOND
, SECOND
, MINUTE
, HOUR
, DAY
, WEEK
, MONTH
, QUARTER
, or YEAR
.
TIMESTAMPDIFF
can also be used to calculate age.
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 |
+----------------------------------------------------------+
Calculating age:
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 |
+------+
Age as of 2014-08-02:
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 |
+---------+---------------+------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
TO_DAYS(date)
Given a date date
, returns the number of days since the start of the current calendar (0000-00-00).
The function is not designed for use with dates before the advent of the Gregorian calendar in October 1582. Results will not be reliable since it doesn't account for the lost days when the calendar changed from the Julian calendar.
This is the converse of the FROM_DAYS() function.
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 |
+-----------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
TO_SECONDS(expr)
Returns the number of seconds from year 0 till expr
, or NULL if expr
is not a valid date or datetime.
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 |
+--------------------+
This page is licensed: CC BY-SA / Gnu FDL
UNIX_TIMESTAMP()
UNIX_TIMESTAMP(date)
If called with no argument, returns a Unix timestamp (seconds since 1970-01-01 00:00:00
UTC) 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 DATE string, aDATETIME string, a TIMESTAMP, or a number in
the format YYMMDD
or YYYYMMDD
. The server interprets date as a value in the current time zone and converts it to an internal value in UTC. Clients can set their time zone as described in time zones.
The inverse function of UNIX_TIMESTAMP()
is FROM_UNIXTIME()
UNIX_TIMESTAMP()
supports microseconds.
Timestamps in MariaDB have a maximum value of 4294967295, equivalent to 2106-02-07 06:28:15
. This is due to the underlying 32-bit limitation. Using the function on a timestamp beyond this will result in NULL
being returned. Use DATETIME as a storage type if you require dates beyond this.
Timestamps in MariaDB have a maximum value of 2147483647, equivalent to 2038-01-19 05:14:07
. This is due to the underlying 32-bit limitation. Using the function on a timestamp beyond this will result in NULL
being returned. Use DATETIME as a storage type if you require dates beyond this.
Returns NULL
for wrong arguments to UNIX_TIMESTAMP()
.
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 |
+-------------------------------------------------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
UTC_DATE, UTC_DATE()
Returns the current UTC date as a value in YYYY-MM-DD
or YYYYMMDD
format, depending on whether the function is used in a string or numeric context.
SELECT UTC_DATE(), UTC_DATE() + 0;
+------------+----------------+
| UTC_DATE() | UTC_DATE() + 0 |
+------------+----------------+
| 2010-03-27 | 20100327 |
+------------+----------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
UTC_TIME
UTC_TIME([precision])
Returns the current UTC time as a value in HH:MM:SS
or HHMMSS.uuuuuu
format, depending on whether the function is used in a string or numeric context.
The optional precision determines the microsecond precision. See Microseconds in MariaDB.
SELECT UTC_TIME(), UTC_TIME() + 0;
+------------+----------------+
| UTC_TIME() | UTC_TIME() + 0 |
+------------+----------------+
| 17:32:34 | 173234.000000 |
+------------+----------------+
With precision:
SELECT UTC_TIME(5);
+----------------+
| UTC_TIME(5) |
+----------------+
| 07:52:50.78369 |
+----------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
UTC_TIMESTAMP
UTC_TIMESTAMP([precision])
Returns the current UTC date and time as a value in YYYY-MM-DD HH:MM:SS
or YYYYMMDDHHMMSS.uuuuuu
format, depending on whether the function is used in a string or numeric context.
The optional precision determines the microsecond precision. See Microseconds in MariaDB.
SELECT UTC_TIMESTAMP(), UTC_TIMESTAMP() + 0;
+---------------------+-----------------------+
| UTC_TIMESTAMP() | UTC_TIMESTAMP() + 0 |
+---------------------+-----------------------+
| 2010-03-27 17:33:16 | 20100327173316.000000 |
+---------------------+-----------------------+
With precision:
SELECT UTC_TIMESTAMP(4);
+--------------------------+
| UTC_TIMESTAMP(4) |
+--------------------------+
| 2018-07-10 07:51:09.1019 |
+--------------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
WEEK(date[,mode])
This function returns the week number for date
. The two-argument form ofWEEK()
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 default_week_format system variable is used.
0
Sunday
0-53
a Sunday in this year
1
Monday
0-53
more than 3 days this year
2
Sunday
1-53
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
With the mode value of 3, which means 'more than 3 days this year', weeks are numbered according to ISO 8601:1988.
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 |
+---------------------+-----------+-----------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
WEEKDAY(date)
Returns the weekday index for date
(0
= Monday, 1
= Tuesday, ... 6
= Sunday).
This contrasts with DAYOFWEEK() which follows the ODBC standard (1
= Sunday, 2
= Monday, ..., 7
= Saturday).
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 |
+-----------------------+
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 |
+---------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
WEEKOFYEAR(date)
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 toWEEK(date,3).
SELECT WEEKOFYEAR('2008-02-20');
+--------------------------+
| WEEKOFYEAR('2008-02-20') |
+--------------------------+
| 8 |
+--------------------------+
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 |
+---------------------+---------------+-----------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
YEAR(date)
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
:
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');
The resulting output would look like this:
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);
If SQL_MODE
is strict (the default), the example above generates the following error and no values are inserted:
ERROR 1264 (22003): Out of range value for column 'example' at row 3
If SQL_MODE
is not strict, the example above generates a warning and (possibly modified) values are inserted:
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');
The resulting data would look like this:
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 |
+----------------------+---------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
YEARWEEK(date), YEARWEEK(date,mode)
Returns year and week for a date. The mode argument works exactly like the mode argument to WEEK(). The year in the result may be different from the year in the date argument for the first and the last week of the year.
SELECT YEARWEEK('1987-01-01');
+------------------------+
| YEARWEEK('1987-01-01') |
+------------------------+
| 198652 |
+------------------------+
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)
This page is licensed: GPLv2, originally from fill_help_tables.sql
Learn about numeric functions in MariaDB Server. This section details SQL functions for performing mathematical calculations, rounding, and manipulating numeric values in your queries.
ABS(X)
Returns the absolute (non-negative) value of X
. If X
is not a number, it is converted to a numeric type.
SELECT ABS(42);
+---------+
| ABS(42) |
+---------+
| 42 |
+---------+
SELECT ABS(-42);
+----------+
| ABS(-42) |
+----------+
| 42 |
+----------+
SELECT ABS(DATE '1994-01-01');
+------------------------+
| ABS(DATE '1994-01-01') |
+------------------------+
| 19940101 |
+------------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
ACOS(X)
Returns the arc cosine of X
, that is, the value whose cosine is X
. Returns NULL
if X
is not in the range -1
to 1
.
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 |
+------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
+
Addition.
If both operands are integers, the result is calculated with BIGINT precision. If either integer is unsigned, the result is also an unsigned integer.
For real or string operands, the operand with the highest precision determines the result precision.
SELECT 3+5;
+-----+
| 3+5 |
+-----+
| 8 |
+-----+
This page is licensed: GPLv2, originally from fill_help_tables.sql
ASIN(X)
Returns the arc sine of X, that is, the value whose sine is X. Returns NULL
if X is not in the range -1 to 1.
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' |
+---------+------+-----------------------------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
ATAN(X)
Returns the arc tangent of X, that is, the value whose tangent is X.
SELECT ATAN(2);
+--------------------+
| ATAN(2) |
+--------------------+
| 1.1071487177940904 |
+--------------------+
SELECT ATAN(-2);
+---------------------+
| ATAN(-2) |
+---------------------+
| -1.1071487177940904 |
+---------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
ATAN(Y,X), ATAN2(Y,X)
Returns the arc tangent of the two variables X and Y. It is similar to calculating the arc tangent of Y / X, except that the signs of both arguments are used to determine the quadrant of the result.
SELECT ATAN(-2,2);
+---------------------+
| ATAN(-2,2) |
+---------------------+
| -0.7853981633974483 |
+---------------------+
SELECT ATAN2(PI(),0);
+--------------------+
| ATAN2(PI(),0) |
+--------------------+
| 1.5707963267948966 |
+--------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql