Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Perform calculations on multiple rows to return a single value. Includes standard SQL functions like SUM, AVG, COUNT, MIN, and MAX, often used with GROUP BY.
Discover functions and procedures in MariaDB.
Count unique values. This function returns the number of distinct, non-NULL values found in the specified column or expression.
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.
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.
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.
As an aggregate function:
No match:
This page is licensed: GPLv2, originally from fill_help_tables.sql
Calculate the average value. This function computes the arithmetic mean of a numeric expression, ignoring NULL values.
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 clause.
AVG() returns NULL if there were no matching rows.
AVG() can be used as a .
Commonly, AVG() is used with a clause:
Be careful to avoid this common mistake, not grouping correctly and returning mismatched data:
As a :
(maximum)
(minimum)
(sum total)
This page is licensed: GPLv2, originally from
Perform a bitwise OR operation. This function returns the result of performing a bitwise OR on all values in a given expression.
BIT_OR(expr) [over_clause]Returns the bitwise OR of all bits in expr. The calculation is performed with 64-bit () precision. It is an , and so can be used with the clause.
If no rows match, BIT_OR will return a value with all bits set to 0. NULL values have no effect on the result unless all results are NULL, which is treated as no match.
BIT_OR can be used as a with the addition of the over_clause.
As an :
No match:
This page is licensed: GPLv2, originally from
Compare a value against a list of conditions. This Oracle-compatible function returns a corresponding result when a match is found, or a default value otherwise.
DECODE_ORACLE is a synonym for the version of the DECODE function, and is available in all modes.
This page is licensed: CC BY-SA / Gnu FDL
Synonym for IFNULL. This Oracle-compatible function returns the first argument if it is not NULL, or the second argument if the first is NULL.
NVL is a synonym for IFNULL.
This page is licensed: CC BY-SA / Gnu FDL
Convert a day number to a date. This function returns a DATE value corresponding to the number of days since year 0.
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 function.
This page is licensed: GPLv2, originally from
Extract the month. This function returns the month portion of a date as a number from 1 (January) to 12 (December).
Return the name of the weekday. This function returns the full name of the day, such as 'Monday' or 'Sunday', for a given date.
Calculate the difference between two months.
MONTHS_BETWEEN returns the number of months between dates two dates. If the first date given is later than the second date, the result is positive; otherwise, the result is negative. If both dates are the same days of the month, or both are last days of months, the result is always an integer. Otherwise, the fractional portion of the result based on a 31-day month is calculated, and considered the difference in time components between the dates.
The following example calculates the months between two dates:
The result is 1.03225806.
Format a time. This function formats a time value according to a format string, similar to DATE_FORMAT but for time values.
BIT_AND(expr) [over_clause]CREATE TABLE vals (x INT);
INSERT INTO vals VALUES(111),(110),(100);
SELECT BIT_AND(x), BIT_OR(x), BIT_XOR(x) FROM vals;
+------------+-----------+------------+
| BIT_AND(x) | BIT_OR(x) | BIT_XOR(x) |
+------------+-----------+------------+
| 100 | 111 | 101 |
+------------+-----------+------------+CREATE TABLE vals2 (category VARCHAR(1), x INT);
INSERT INTO vals2 VALUES
('a',111),('a',110),('a',100),
('b','000'),('b',001),('b',011);
SELECT category, BIT_AND(x), BIT_OR(x), BIT_XOR(x)
FROM vals GROUP BY category;
+----------+------------+-----------+------------+
| category | BIT_AND(x) | BIT_OR(x) | BIT_XOR(x) |
+----------+------------+-----------+------------+
| a | 100 | 111 | 101 |
| b | 0 | 11 | 10 |
+----------+------------+-----------+------------+SELECT BIT_AND(NULL);
+----------------------+
| BIT_AND(NULL) |
+----------------------+
| 18446744073709551615 |
+----------------------+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 |
+------------------------+SELECT MONTHS_BETWEEN
(TO_DATE('02-02-1995','MM-DD-YYYY'),
TO_DATE('01-01-1995','MM-DD-YYYY')
);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
STDDEV_SAMP(expr)There is also a CASE statement, which differs from the CASE operator described here.
Only the first matching condition is processed:
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
TRUEexpr1 <> 0expr1 <> NULLIF()expr2expr3IF()Note: There is also an IF statement which differs from theIF() function described here.
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
WHEN expr1 = expr2 THEN NULL ELSE expr1 END.This page is licensed: GPLv2, originally from fill_help_tables.sql
NULLNULLNVL2NULLNVL2This page is licensed: CC BY-SA / Gnu FDL
NVL2(expr1,expr2,expr3)This page is licensed: GPLv2, originally from fill_help_tables.sql
ADDTIME(expr1,expr2)SELECT ADDTIME('2007-12-31 23:59:59.999999', '1 1:1:1.000002');
+---------------------------------------------------------+
| ADDTIME('2007-12-31 23:59:59.999999', '1 1:1:1.000002') |
+---------------------------------------------------------+
| 2008-01-02 01:01:01.000001 |
+---------------------------------------------------------+
SELECT ADDTIME('01:00:00.999999', '02:00:00.999998');
+-----------------------------------------------+
| ADDTIME('01:00:00.999999', '02:00:00.999998') |
+-----------------------------------------------+
| 03:00:01.999997 |
+-----------------------------------------------+In order to use named time zones, such as GMT, MET or Africa/Johannesburg, the time_zone tables must be loaded (see mysql_tzinfo_to_sql).
No conversion will take place if the value falls outside of the supported TIMESTAMP range ('1970-01-01 00:00:01' to '2038-01-19 05:14:07' UTC) when converted from from_tz to UTC.
This function returns NULL if the arguments are invalid (or named time zones have not been loaded).
See time zones for more information.
Using named time zones (with the time zone tables loaded):
The value is out of the TIMESTAMP range, so no conversion takes place:
This page is licensed: GPLv2, originally from fill_help_tables.sql
CONVERT_TZ(dt,from_tz,to_tz)YYYY-MM-DDYYYYMMDDCURRENT_DATE and CURRENT_DATE() are synonyms.
In a numeric context (note this is not performing date calculations):
Date calculation:
This page is licensed: GPLv2, originally from fill_help_tables.sql
CURDATE()
CURRENT_DATE
CURRENT_DATE()SELECT CURDATE();
+------------+
| CURDATE() |
+------------+
| 2019-03-05 |
+------------+This page is licensed: GPLv2, originally from fill_help_tables.sql
CURRENT_DATE, CURRENT_DATE()CURRENT_TIMESTAMP()The TIMESTAMP data type
This page is licensed: GPLv2, originally from fill_help_tables.sql
CURRENT_TIMESTAMP
CURRENT_TIMESTAMP([precision])This page is licensed: GPLv2, originally from fill_help_tables.sql
DATEDIFF(expr1,expr2)SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
+----------------------------------------------+
| DATEDIFF('2007-12-31 23:59:59','2007-12-30') |
+----------------------------------------------+
| 1 |
+----------------------------------------------+
SELECT DATEDIFF('2010-11-30 23:59:59','2010-12-31');
+----------------------------------------------+
| DATEDIFF('2010-11-30 23:59:59','2010-12-31') |
+----------------------------------------------+
| -31 |
+----------------------------------------------+Changing the locale:
This page is licensed: GPLv2, originally from fill_help_tables.sql
DAYNAME(date)SELECT DAYNAME('2007-02-03');
+-----------------------+
| DAYNAME('2007-02-03') |
+-----------------------+
| Saturday |
+-----------------------+310'0000-00-00''2008-00-00'DAY() is a synonym.
This page is licensed: GPLv2, originally from fill_help_tables.sql
DAYOFMONTH(date)SELECT DAYOFMONTH('2007-02-03');
+--------------------------+
| DAYOFMONTH('2007-02-03') |
+--------------------------+
| 3 |
+--------------------------+0 = Monday, 1 = Tuesday, ... 6 = Sunday).This page is licensed: GPLv2, originally from fill_help_tables.sql
DAYOFWEEK(date)SELECT DAYOFWEEK('2007-02-03');
+-------------------------+
| DAYOFWEEK('2007-02-03') |
+-------------------------+
| 7 |
+-------------------------+The return value is always positive, even if a negative TIME value is provided.
This page is licensed: GPLv2, originally from fill_help_tables.sql
This page is licensed: GPLv2, originally from fill_help_tables.sql
LAST_DAY(date)This page is licensed: GPLv2, originally from fill_help_tables.sql
LOCALTIME
LOCALTIME([precision])This page is licensed: GPLv2, originally from fill_help_tables.sql
TIME(expr)SELECT TIME('2003-12-31 01:02:03');
+-----------------------------+
| TIME('2003-12-31 01:02:03') |
+-----------------------------+
| 01:02:03 |
+-----------------------------+
SELECT TIME('2003-12-31 01:02:03.000123');
+------------------------------------+
| TIME('2003-12-31 01:02:03.000123') |
+------------------------------------+
| 01:02:03.000123 |
+------------------------------------+NULLThis page is licensed: GPLv2, originally from fill_help_tables.sql
QUARTER(date)SELECT QUARTER('2008-04-01');
+-----------------------+
| QUARTER('2008-04-01') |
+-----------------------+
| 2 |
+-----------------------+
SELECT QUARTER('2019-00-01');
+-----------------------+
| QUARTER('2019-00-01') |
+-----------------------+
| 0 |
+-----------------------+NULL if not given a valid time value.This page is licensed: GPLv2, originally from fill_help_tables.sql
SECOND(time)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
TIME_FORMAT(time,format)SELECT TIME_FORMAT('100:00:00', '%H %k %h %I %l');
+--------------------------------------------+
| TIME_FORMAT('100:00:00', '%H %k %h %I %l') |
+--------------------------------------------+
| 100 100 04 04 4 |
+--------------------------------------------+SELECT DAYOFYEAR('2018-02-16');
+-------------------------+
| DAYOFYEAR('2018-02-16') |
+-------------------------+
| 47 |
+-------------------------+SELECT FROM_DAYS(730669);
+-------------------+
| FROM_DAYS(730669) |
+-------------------+
| 2000-07-03 |
+-------------------+SELECT MONTH('2019-01-03');
+---------------------+
| MONTH('2019-01-03') |
+---------------------+
| 1 |
+---------------------+
SELECT MONTH('2019-00-03');
+---------------------+
| MONTH('2019-00-03') |
+---------------------+
| 0 |
+---------------------+Calculate sample variance. This function computes the statistical variance for a set of values assumed to be a sample of the population.
VAR_SAMP(expr)Returns the sample variance of expr. That is, the denominator is the number of rows minus one.
It is an , and so can be used with the clause.
VAR_SAMP() can be used as a .
VAR_SAMP() returns NULL if there were no matching rows.
As an :
As a :
(variance)
(population standard deviation)
This page is licensed: GPLv2, originally from
Replace NULL values with a fallback. This function returns the first argument if it's not NULL; otherwise, it returns the specified replacement value.
Add a time interval to a date. This function performs date arithmetic, adding a specified value like days or hours to a starting date.
ADDDATE(date,INTERVAL expr unit), ADDDATE(expr,days)When invoked with the INTERVAL form of the second argument, ADDDATE() is a synonym for . The related function is a synonym for . For information on the INTERVAL unit argument, see the discussion for .
When invoked with the days form of the second argument, MariaDB treats it as an integer number of days to be added to expr.
This page is licensed: GPLv2, originally from
Return the current time. This function outputs the current time of day as a value in 'HH:MM:SS' or HHMMSS format.
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 .
The optional precision determines the microsecond precision. See .
With precision:
This page is licensed: GPLv2, originally from
Extract the date part from a datetime expression. This function returns the year, month, and day portions, discarding the time component.
Subtract a time interval from a date. This function calculates a past date by subtracting a specified unit, such as days, from a starting value.
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 for a complete list of permitted units.
See also .
This page is licensed: GPLv2, originally from
Synonym for DAYOFMONTH(). Returns the day of the month (1-31) for a given date.
DAY(date)DAY() is a synonym for DAYOFMONTH().
This page is licensed: GPLv2, originally from
Format a time in picoseconds. This function converts a numeric picosecond value into a human-readable string with units like ps, ns, us, ms, s, m, h, or d.
Given a time in picoseconds, returns a human-readable time value and unit indicator. Resulting unit is dependent on the length of the argument, and can be:
ps - picoseconds
ns - nanoseconds
us - microseconds
ms - milliseconds
With the exception of results under one nanosecond, which are not rounded and are represented as whole numbers, the result is rounded to 2 decimal places, with a minimum of 3 significant digits.
Returns NULL if the argument is NULL.
This function is very similar to the function, but with the following differences:
Represents minutes as min rather than m.
Does not represent weeks.
This page is licensed: CC BY-SA / Gnu FDL
Create a time from hour, minute, and second. This function constructs a TIME value from three numeric arguments.
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.
This page is licensed: GPLv2, originally from
Extract microseconds. This function returns the microsecond part of a time or datetime expression as a number from 0 to 999999.
MICROSECOND(expr)Returns the microseconds from the time or datetime expression expr as a number in the range from 0 to 999999.
If expr is a time with no microseconds, zero is returned, while if expr is a date with no time, zero with a warning is returned.
This page is licensed: GPLv2, originally from
Return the name of the month. This function returns the full name of the month, such as 'January' or 'December', for a given date.
MONTHNAME(date)Returns the full name of the month for date. The language used for the name is controlled by the value of the lc_time_names system variable. See server locale for more on the supported locales.
Changing the locale:
This page is licensed: GPLv2, originally from
Perform a bitwise XOR operation. This function returns the result of performing a bitwise XOR on all values in a given expression.
BIT_XOR(expr) [over_clause]Returns the bitwise XOR of all bits in expr. The calculation is performed with 64-bit () precision. It is an , and so can be used with the clause.
If no rows match, BIT_XOR will return a value with all bits set to 0. NULL values have no effect on the result unless all results are NULL, which is treated as no match.
BIT_XOR can be used as a with the addition of the over_clause.
As an :
No match:
This page is licensed: GPLv2, originally from
Return the year and week. This function returns the year and week number for a date, useful for grouping results by week.
YEARWEEK(date), YEARWEEK(date,mode)Returns year and week for a date. The mode argument works exactly like the mode argument to WEEK(). The year in the result may be different from the year in the date argument for the first and the last week of the year.
This page is licensed: GPLv2, originally from
Convert seconds to time. This function returns a TIME value corresponding to the number of seconds elapsed from the start of the day.
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.
This page is licensed: GPLv2, originally from
Calculate the difference between periods. This function returns the number of months between two periods formatted as YYMM or YYYYMM.
PERIOD_DIFF(P1,P2)Returns the number of months between periods P1 and P2. P1 and P2 can be in the format YYMM or YYYYMM, and are not date values.
If P1 or P2 contains a two-digit year, values from 00 to 69 are converted to from 2000 to 2069, while values from 70 are converted to 1970 upwards.
This page is licensed: GPLv2, originally from
Subtract two time values. This function calculates the difference between two time or datetime expressions.
Convert to datetime or add time. With one argument, it returns a datetime; with two, it adds a time expression to a date or datetime.
TIMESTAMP(expr), TIMESTAMP(expr1,expr2)With a single argument, this function returns the date or datetime expression expr as a datetime value. With two arguments, it adds the time expression expr2 to the date or datetime expression expr1 and returns the result as a datetime value.
This page is licensed: GPLv2, originally from
Convert a date to a day number. This function returns the number of days between year 0 and the given date.
TO_DAYS(date)Given a date date, returns the number of days since the start of the current calendar (0000-00-00).
The function is not designed for use with dates before the advent of the Gregorian calendar in October 1582. Results will not be reliable since it doesn't account for the lost days when the calendar changed from the Julian calendar.
This is the converse of the function.
This page is licensed: GPLv2, originally from
Add an interval to a timestamp. This function adds a specified integer number of units (like MONTH or SECOND) to a datetime expression.
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.
This page is licensed: GPLv2, originally from
Return the current UTC date. This function returns the current Coordinated Universal Time date in 'YYYY-MM-DD' or YYYYMMDD format.
UTC_DATE, UTC_DATE()Returns the current UTC date as a value in YYYY-MM-DD or YYYYMMDD format, depending on whether the function is used in a string or numeric context.
This page is licensed: GPLv2, originally from
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] ENDSELECT CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END;
+------------------------------------------------------------+
| CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END |
+------------------------------------------------------------+
| one |
+------------------------------------------------------------+
SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;
+--------------------------------------------+
| CASE WHEN 1>0 THEN 'true' ELSE 'false' END |
+--------------------------------------------+
| true |
+--------------------------------------------+
SELECT CASE BINARY 'B' WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;
+-----------------------------------------------------+
| CASE BINARY 'B' WHEN 'a' THEN 1 WHEN 'b' THEN 2 END |
+-----------------------------------------------------+
| NULL |
+-----------------------------------------------------+SELECT
CASE true
WHEN (1=1) THEN '1=1' -- result is returned
WHEN (1=1 OR 2=2) THEN '1=1 OR 2=2' -- condition not processed
ELSE 'else'
END
;
+-------------------------------------------------------------------------------------+
| CASE true WHEN (1=1) THEN '1=1' WHEN (1=1 OR 2=2) THEN '1=1 OR 2=2' ELSE 'else' END |
+-------------------------------------------------------------------------------------+
+ 1=1 +
+-------------------------------------------------------------------------------------+IF(expr1,expr2,expr3)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 |
+---------------------------------------+NULLIF(expr1,expr2)SELECT NULLIF(1,1);
+-------------+
| NULLIF(1,1) |
+-------------+
| NULL |
+-------------+
SELECT NULLIF(1,2);
+-------------+
| NULLIF(1,2) |
+-------------+
| 1 |
+-------------+SELECT NVL2(NULL,1,2);
+----------------+
| NVL2(NULL,1,2) |
+----------------+
| 2 |
+----------------+
SELECT NVL2('x',1,2);
+---------------+
| NVL2('x',1,2) |
+---------------+
| 1 |
+---------------+SELECT CONVERT_TZ('2016-01-01 12:00:00','+00:00','+10:00');
+-----------------------------------------------------+
| CONVERT_TZ('2016-01-01 12:00:00','+00:00','+10:00') |
+-----------------------------------------------------+
| 2016-01-01 22:00:00 |
+-----------------------------------------------------+SELECT CONVERT_TZ('2016-01-01 12:00:00','GMT','Africa/Johannesburg');
+---------------------------------------------------------------+
| CONVERT_TZ('2016-01-01 12:00:00','GMT','Africa/Johannesburg') |
+---------------------------------------------------------------+
| 2016-01-01 14:00:00 |
+---------------------------------------------------------------+SELECT CONVERT_TZ('1969-12-31 22:00:00','+00:00','+10:00');
+-----------------------------------------------------+
| CONVERT_TZ('1969-12-31 22:00:00','+00:00','+10:00') |
+-----------------------------------------------------+
| 1969-12-31 22:00:00 |
+-----------------------------------------------------+SELECT CURDATE() +0;
+--------------+
| CURDATE() +0 |
+--------------+
| 20190305 |
+--------------+SELECT CURDATE() - INTERVAL 5 DAY;
+----------------------------+
| CURDATE() - INTERVAL 5 DAY |
+----------------------------+
| 2019-02-28 |
+----------------------------+CREATE TABLE t1 (d DATETIME);
INSERT INTO t1 VALUES
("2007-01-30 21:31:07"),
("1983-10-15 06:42:51"),
("2011-04-21 12:34:56"),
("2011-10-30 06:31:41"),
("2011-01-30 14:03:25"),
("2004-10-07 11:19:34");SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2011-05-23 10:56:05 |
+---------------------+
SELECT d, DATEDIFF(NOW(),d) FROM t1;
+---------------------+-------------------+
| d | DATEDIFF(NOW(),d) |
+---------------------+-------------------+
| 2007-01-30 21:31:07 | 1574 |
| 1983-10-15 06:42:51 | 10082 |
| 2011-04-21 12:34:56 | 32 |
| 2011-10-30 06:31:41 | -160 |
| 2011-01-30 14:03:25 | 113 |
| 2004-10-07 11:19:34 | 2419 |
+---------------------+-------------------+CREATE TABLE t1 (d DATETIME);
INSERT INTO t1 VALUES
("2007-01-30 21:31:07"),
("1983-10-15 06:42:51"),
("2011-04-21 12:34:56"),
("2011-10-30 06:31:41"),
("2011-01-30 14:03:25"),
("2004-10-07 11:19:34");SELECT d, DAYNAME(d) FROM t1;
+---------------------+------------+
| d | DAYNAME(d) |
+---------------------+------------+
| 2007-01-30 21:31:07 | Tuesday |
| 1983-10-15 06:42:51 | Saturday |
| 2011-04-21 12:34:56 | Thursday |
| 2011-10-30 06:31:41 | Sunday |
| 2011-01-30 14:03:25 | Sunday |
| 2004-10-07 11:19:34 | Thursday |
+---------------------+------------+SET lc_time_names = 'fr_CA';
SELECT DAYNAME('2013-04-01');
+-----------------------+
| DAYNAME('2013-04-01') |
+-----------------------+
| lundi |
+-----------------------+CREATE TABLE t1 (d DATETIME);
INSERT INTO t1 VALUES
("2007-01-30 21:31:07"),
("1983-10-15 06:42:51"),
("2011-04-21 12:34:56"),
("2011-10-30 06:31:41"),
("2011-01-30 14:03:25"),
("2004-10-07 11:19:34");SELECT d 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 |
+---------------------+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 |
+---------------------+------------+--------------+------------+HOUR(time)SELECT HOUR('10:05:03');
+------------------+
| HOUR('10:05:03') |
+------------------+
| 10 |
+------------------+
SELECT HOUR('272:59:59');
+-------------------+
| HOUR('272:59:59') |
+-------------------+
| 272 |
+-------------------+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'CREATE TABLE sales (sales_value INT);
INSERT INTO sales VALUES(10),(20),(20),(40);
SELECT AVG(sales_value) FROM sales;
+------------------+
| AVG(sales_value) |
+------------------+
| 22.5000 |
+------------------+
SELECT AVG(DISTINCT(sales_value)) FROM sales;
+----------------------------+
| AVG(DISTINCT(sales_value)) |
+----------------------------+
| 23.3333 |
+----------------------------+CREATE TABLE student (name CHAR(10), test CHAR(10), score TINYINT);
INSERT INTO student VALUES
('Chun', 'SQL', 75), ('Chun', 'Tuning', 73),
('Esben', 'SQL', 43), ('Esben', 'Tuning', 31),
('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88),
('Tatiana', 'SQL', 87), ('Tatiana', 'Tuning', 83);
SELECT name, AVG(score) FROM student GROUP BY name;
+---------+------------+
| name | AVG(score) |
+---------+------------+
| Chun | 74.0000 |
| Esben | 37.0000 |
| Kaolin | 72.0000 |
| Tatiana | 85.0000 |
+---------+------------+SELECT name,test,AVG(score) FROM student;
+------+------+------------+
| name | test | MIN(score) |
+------+------+------------+
| Chun | SQL | 31 |
+------+------+------------+CREATE TABLE student_test (name CHAR(10), test CHAR(10), score TINYINT);
INSERT INTO student_test VALUES
('Chun', 'SQL', 75), ('Chun', 'Tuning', 73),
('Esben', 'SQL', 43), ('Esben', 'Tuning', 31),
('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88),
('Tatiana', 'SQL', 87), ('Tatiana', 'Tuning', 83);
SELECT name, test, score, AVG(score) OVER (PARTITION BY test)
AS average_by_test FROM student_test;
+---------+--------+-------+-----------------+
| name | test | score | average_by_test |
+---------+--------+-------+-----------------+
| Chun | SQL | 75 | 65.2500 |
| Chun | Tuning | 73 | 68.7500 |
| Esben | SQL | 43 | 65.2500 |
| Esben | Tuning | 31 | 68.7500 |
| Kaolin | SQL | 56 | 65.2500 |
| Kaolin | Tuning | 88 | 68.7500 |
| Tatiana | SQL | 87 | 65.2500 |
| Tatiana | Tuning | 83 | 68.7500 |
+---------+--------+-------+-----------------+CREATE TABLE vals (x INT);
INSERT INTO vals VALUES(111),(110),(100);
SELECT BIT_AND(x), BIT_OR(x), BIT_XOR(x) FROM vals;
+------------+-----------+------------+
| BIT_AND(x) | BIT_OR(x) | BIT_XOR(x) |
+------------+-----------+------------+
| 100 | 111 | 101 |
+------------+-----------+------------+CREATE TABLE vals2 (category VARCHAR(1), x INT);
INSERT INTO vals2 VALUES
('a',111),('a',110),('a',100),
('b','000'),('b',001),('b',011);
SELECT category, BIT_AND(x), BIT_OR(x), BIT_XOR(x)
FROM vals GROUP BY category;
+----------+------------+-----------+------------+
| category | BIT_AND(x) | BIT_OR(x) | BIT_XOR(x) |
+----------+------------+-----------+------------+
| a | 100 | 111 | 101 |
| b | 0 | 11 | 10 |
+----------+------------+-----------+------------+SELECT BIT_OR(NULL);
+--------------+
| BIT_OR(NULL) |
+--------------+
| 0 |
+--------------+IFNULL(expr1,expr2)
NVL(expr1,expr2)FORMAT_PICO_TIME(time_val)Learn about numeric functions in MariaDB Server. This section details SQL functions for performing mathematical calculations, rounding, and manipulating numeric values in your queries.
SELECT ADDDATE('2008-01-02', 31);
+---------------------------+
| ADDDATE('2008-01-02', 31) |
+---------------------------+
| 2008-02-02 |
+---------------------------+SELECT DATE('2013-07-18 12:21:32');
+-----------------------------+
| DATE('2013-07-18 12:21:32') |
+-----------------------------+
| 2013-07-18 |
+-----------------------------+SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
+-----------------------------------------+
| DATE_SUB('1998-01-02', INTERVAL 31 DAY) |
+-----------------------------------------+
| 1997-12-02 |
+-----------------------------------------+SELECT MONTHNAME('2019-02-03');
+-------------------------+
| MONTHNAME('2019-02-03') |
+-------------------------+
| February |
+-------------------------+SELECT YEARWEEK('1987-01-01');
+------------------------+
| YEARWEEK('1987-01-01') |
+------------------------+
| 198652 |
+------------------------+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 |
+---------------------+SELECT PERIOD_DIFF(200802,200703);
+----------------------------+
| PERIOD_DIFF(200802,200703) |
+----------------------------+
| 11 |
+----------------------------+
SELECT PERIOD_DIFF(6902,6803);
+------------------------+
| PERIOD_DIFF(6902,6803) |
+------------------------+
| 11 |
+------------------------+
SELECT PERIOD_DIFF(7002,6803);
+------------------------+
| PERIOD_DIFF(7002,6803) |
+------------------------+
| -1177 |
+------------------------+SELECT 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 |
+----------------------------------------------------------------------+SELECT TO_SECONDS('2013-06-13');
+--------------------------+
| TO_SECONDS('2013-06-13') |
+--------------------------+
| 63538300800 |
+--------------------------+
SELECT TO_SECONDS('2013-06-13 21:45:13');
+-----------------------------------+
| TO_SECONDS('2013-06-13 21:45:13') |
+-----------------------------------+
| 63538379113 |
+-----------------------------------+
SELECT TO_SECONDS(NOW());
+-------------------+
| TO_SECONDS(NOW()) |
+-------------------+
| 63543530875 |
+-------------------+
SELECT TO_SECONDS(20130513);
+----------------------+
| TO_SECONDS(20130513) |
+----------------------+
| 63535622400 |
+----------------------+
1 row in set (0.00 sec)
SELECT TO_SECONDS(130513);
+--------------------+
| TO_SECONDS(130513) |
+--------------------+
| 63535622400 |
+--------------------+SELECT TO_DAYS('2007-10-07');
+-----------------------+
| TO_DAYS('2007-10-07') |
+-----------------------+
| 733321 |
+-----------------------+
SELECT TO_DAYS('0000-01-01');
+-----------------------+
| TO_DAYS('0000-01-01') |
+-----------------------+
| 1 |
+-----------------------+
SELECT TO_DAYS(950501);
+-----------------+
| TO_DAYS(950501) |
+-----------------+
| 728779 |
+-----------------+SELECT UTC_DATE(), UTC_DATE() + 0;
+------------+----------------+
| UTC_DATE() | UTC_DATE() + 0 |
+------------+----------------+
| 2010-03-27 | 20100327 |
+------------+----------------+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:
As a window function:
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
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:
As a window function:
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
-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.
This page is licensed: GPLv2, originally from fill_help_tables.sql
day-of-year valuesdayofyearNULL2012 is a leap year:
This page is licensed: GPLv2, originally from fill_help_tables.sql
MAKEDATE(year,dayofyear)SELECT MAKEDATE(2011,31), MAKEDATE(2011,32);
+-------------------+-------------------+
| MAKEDATE(2011,31) | MAKEDATE(2011,32) |
+-------------------+-------------------+
| 2011-01-31 | 2011-02-01 |
+-------------------+-------------------+SUBDATE()The second form allows the use of an integer value for days. In such cases, it is interpreted as the number of days to be subtracted from the date or datetime expression expr.
This page is licensed: GPLv2, originally from fill_help_tables.sql
SUBDATE(date,INTERVAL expr unit), SUBDATE(expr,days)YYYYMMDDHHMMSS.uuuuuuThe optional precision determines the microsecond precision. See Microseconds in MariaDB.
SYSDATE() returns the time at which it executes. This differs from the behavior for NOW(), which returns a constant time that indicates the time at which the statement began to execute. (Within a stored routine or trigger, NOW() returns the time at which the routine or triggering statement began to execute.)
In addition, changing the timestamp system variable with a SET timestamp statement affects the value returned by NOW() but not by SYSDATE(). This means that timestamp settings in the binary log have no effect on invocations of SYSDATE().
Because SYSDATE() can return different values even within the same statement, and is not affected by SET TIMESTAMP, it is non-deterministic and therefore unsafe for replication if statement-based binary logging is used. If that is a problem, you can use row-based logging, or start the server with the --sysdate-is-now mariadbd option to cause SYSDATE() to be an alias for NOW(). The non-deterministic nature of SYSDATE() also means that indexes cannot be used for evaluating expressions that refer to it, and that statements using the SYSDATE() function are unsafe for statement-based replication.
Difference between NOW() and SYSDATE():
With precision:
This page is licensed: GPLv2, originally from fill_help_tables.sql
PPPReturns a value in the format YYYYMM.
This page is licensed: GPLv2, originally from fill_help_tables.sql
PERIOD_ADD(P,N)This page is licensed: GPLv2, originally from fill_help_tables.sql
ASIN(X)XNULLX-11This page is licensed: GPLv2, originally from fill_help_tables.sql
ACOS(X)016This contrasts with DAYOFWEEK() which follows the ODBC standard (1 = Sunday, 2 = Monday, ..., 7 = Saturday).
This page is licensed: GPLv2, originally from fill_help_tables.sql
WEEKDAY(date)This page is licensed: GPLv2, originally from fill_help_tables.sql
ATAN(X)SELECT ATAN(2);
+--------------------+
| ATAN(2) |
+--------------------+
| 1.1071487177940904 |
+--------------------+
SELECT ATAN(-2);
+---------------------+
| ATAN(-2) |
+---------------------+
| -1.1071487177940904 |
+---------------------+CREATE OR REPLACE TABLE stats (category VARCHAR(2), x INT);
INSERT INTO stats VALUES
('a',1),('a',2),('a',3),
('b',11),('b',12),('b',20),('b',30),('b',60);
SELECT category, STDDEV_POP(x), STDDEV_SAMP(x), VAR_POP(x)
FROM stats GROUP BY category;
+----------+---------------+----------------+------------+
| category | STDDEV_POP(x) | STDDEV_SAMP(x) | VAR_POP(x) |
+----------+---------------+----------------+------------+
| a | 0.8165 | 1.0000 | 0.6667 |
| b | 18.0400 | 20.1693 | 325.4400 |
+----------+---------------+----------------+------------+CREATE OR REPLACE TABLE student_test (name CHAR(10), test CHAR(10), score TINYINT);
INSERT INTO student_test VALUES
('Chun', 'SQL', 75), ('Chun', 'Tuning', 73),
('Esben', 'SQL', 43), ('Esben', 'Tuning', 31),
('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88),
('Tatiana', 'SQL', 87);
SELECT name, test, score, VAR_SAMP(score)
OVER (PARTITION BY test) AS variance_results FROM student_test;
+---------+--------+-------+------------------+
| name | test | score | variance_results |
+---------+--------+-------+------------------+
| Chun | SQL | 75 | 382.9167 |
| Chun | Tuning | 73 | 873.0000 |
| Esben | SQL | 43 | 382.9167 |
| Esben | Tuning | 31 | 873.0000 |
| Kaolin | SQL | 56 | 382.9167 |
| Kaolin | Tuning | 88 | 873.0000 |
| Tatiana | SQL | 87 | 382.9167 |
+---------+--------+-------+------------------+SELECT IFNULL(1,0);
+-------------+
| IFNULL(1,0) |
+-------------+
| 1 |
+-------------+
SELECT IFNULL(NULL,10);
+-----------------+
| IFNULL(NULL,10) |
+-----------------+
| 10 |
+-----------------+
SELECT IFNULL(1/0,10);
+----------------+
| IFNULL(1/0,10) |
+----------------+
| 10.0000 |
+----------------+
SELECT IFNULL(1/0,'yes');
+-------------------+
| IFNULL(1/0,'yes') |
+-------------------+
| yes |
+-------------------+SELECT DATE_ADD('2008-01-02', INTERVAL 31 DAY);
+-----------------------------------------+
| DATE_ADD('2008-01-02', INTERVAL 31 DAY) |
+-----------------------------------------+
| 2008-02-02 |
+-----------------------------------------+
SELECT ADDDATE('2008-01-02', INTERVAL 31 DAY);
+----------------------------------------+
| ADDDATE('2008-01-02', INTERVAL 31 DAY) |
+----------------------------------------+
| 2008-02-02 |
+----------------------------------------+CREATE TABLE t1 (d DATETIME);
INSERT INTO t1 VALUES
("2007-01-30 21:31:07"),
("1983-10-15 06:42:51"),
("2011-04-21 12:34:56"),
("2011-10-30 06:31:41"),
("2011-01-30 14:03:25"),
("2004-10-07 11:19:34");SELECT d, ADDDATE(d, 10) from t1;
+---------------------+---------------------+
| d | ADDDATE(d, 10) |
+---------------------+---------------------+
| 2007-01-30 21:31:07 | 2007-02-09 21:31:07 |
| 1983-10-15 06:42:51 | 1983-10-25 06:42:51 |
| 2011-04-21 12:34:56 | 2011-05-01 12:34:56 |
| 2011-10-30 06:31:41 | 2011-11-09 06:31:41 |
| 2011-01-30 14:03:25 | 2011-02-09 14:03:25 |
| 2004-10-07 11:19:34 | 2004-10-17 11:19:34 |
+---------------------+---------------------+
SELECT d, ADDDATE(d, INTERVAL 10 HOUR) from t1;
+---------------------+------------------------------+
| d | ADDDATE(d, INTERVAL 10 HOUR) |
+---------------------+------------------------------+
| 2007-01-30 21:31:07 | 2007-01-31 07:31:07 |
| 1983-10-15 06:42:51 | 1983-10-15 16:42:51 |
| 2011-04-21 12:34:56 | 2011-04-21 22:34:56 |
| 2011-10-30 06:31:41 | 2011-10-30 16:31:41 |
| 2011-01-30 14:03:25 | 2011-01-31 00:03:25 |
| 2004-10-07 11:19:34 | 2004-10-07 21:19:34 |
+---------------------+------------------------------+SELECT CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 12:45:39 |
+-----------+
SELECT CURTIME() + 0;
+---------------+
| CURTIME() + 0 |
+---------------+
| 124545.000000 |
+---------------+SELECT CURTIME(2);
+-------------+
| CURTIME(2) |
+-------------+
| 09:49:08.09 |
+-------------+SELECT DATE_SUB('2005-01-01 00:00:00', INTERVAL '1 1:1:1' DAY_SECOND);
+----------------------------------------------------------------+
| DATE_SUB('2005-01-01 00:00:00', INTERVAL '1 1:1:1' DAY_SECOND) |
+----------------------------------------------------------------+
| 2004-12-30 22:58:59 |
+----------------------------------------------------------------+SELECT MAKETIME(13,57,33);
+--------------------+
| MAKETIME(13,57,33) |
+--------------------+
| 13:57:33 |
+--------------------+
SELECT MAKETIME(-13,57,33);
+---------------------+
| MAKETIME(-13,57,33) |
+---------------------+
| -13:57:33 |
+---------------------+
SELECT MAKETIME(13,67,33);
+--------------------+
| MAKETIME(13,67,33) |
+--------------------+
| NULL |
+--------------------+
SELECT MAKETIME(-1000,57,33);
+-----------------------+
| MAKETIME(-1000,57,33) |
+-----------------------+
| -838:59:59 |
+-----------------------+
1 row in set, 1 warning (0.00 sec)
SHOW WARNINGS;
+---------+------+-----------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------+
| Warning | 1292 | Truncated incorrect time value: '-1000:57:33' |
+---------+------+-----------------------------------------------+SELECT MICROSECOND('12:00:00.123456');
+--------------------------------+
| MICROSECOND('12:00:00.123456') |
+--------------------------------+
| 123456 |
+--------------------------------+
SELECT MICROSECOND('2009-12-31 23:59:59.000010');
+-------------------------------------------+
| MICROSECOND('2009-12-31 23:59:59.000010') |
+-------------------------------------------+
| 10 |
+-------------------------------------------+
SELECT MICROSECOND('2013-08-07 12:13:14');
+------------------------------------+
| MICROSECOND('2013-08-07 12:13:14') |
+------------------------------------+
| 0 |
+------------------------------------+
SELECT MICROSECOND('2013-08-07');
+---------------------------+
| MICROSECOND('2013-08-07') |
+---------------------------+
| 0 |
+---------------------------+
1 row in set, 1 warning (0.00 sec)
SHOW WARNINGS;
+---------+------+----------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------+
| Warning | 1292 | Truncated incorrect time value: '2013-08-07' |
+---------+------+----------------------------------------------+SET lc_time_names = 'fr_CA';
SELECT MONTHNAME('2019-05-21');
+-------------------------+
| MONTHNAME('2019-05-21') |
+-------------------------+
| mai |
+-------------------------+CREATE TABLE vals (x INT);
INSERT INTO vals VALUES(111),(110),(100);
SELECT BIT_AND(x), BIT_OR(x), BIT_XOR(x) FROM vals;
+------------+-----------+------------+
| BIT_AND(x) | BIT_OR(x) | BIT_XOR(x) |
+------------+-----------+------------+
| 100 | 111 | 101 |
+------------+-----------+------------+CREATE TABLE vals2 (category VARCHAR(1), x INT);
INSERT INTO vals2 VALUES
('a',111),('a',110),('a',100),
('b','000'),('b',001),('b',011);
SELECT category, BIT_AND(x), BIT_OR(x), BIT_XOR(x)
FROM vals GROUP BY category;
+----------+------------+-----------+------------+
| category | BIT_AND(x) | BIT_OR(x) | BIT_XOR(x) |
+----------+------------+-----------+------------+
| a | 100 | 111 | 101 |
| b | 0 | 11 | 10 |
+----------+------------+-----------+------------+SELECT BIT_XOR(NULL);
+---------------+
| BIT_XOR(NULL) |
+---------------+
| 0 |
+---------------+CREATE TABLE t1 (d DATETIME);
INSERT INTO t1 VALUES
("2007-01-30 21:31:07"),
("1983-10-15 06:42:51"),
("2011-04-21 12:34:56"),
("2011-10-30 06:31:41"),
("2011-01-30 14:03:25"),
("2004-10-07 11:19:34");SELECT * FROM t1;
+---------------------+
| d |
+---------------------+
| 2007-01-30 21:31:07 |
| 1983-10-15 06:42:51 |
| 2011-04-21 12:34:56 |
| 2011-10-30 06:31:41 |
| 2011-01-30 14:03:25 |
| 2004-10-07 11:19:34 |
+---------------------+
6 rows in set (0.02 sec)SELECT YEARWEEK(d) FROM t1 WHERE YEAR(d) = 2011;
+-------------+
| YEARWEEK(d) |
+-------------+
| 201116 |
| 201144 |
| 201105 |
+-------------+
3 rows in set (0.03 sec)SELECT SEC_TO_TIME(12414);
+--------------------+
| SEC_TO_TIME(12414) |
+--------------------+
| 03:26:54 |
+--------------------+
SELECT SEC_TO_TIME(12414)+0;
+----------------------+
| SEC_TO_TIME(12414)+0 |
+----------------------+
| 32654 |
+----------------------+
SELECT SEC_TO_TIME(9999999);
+----------------------+
| SEC_TO_TIME(9999999) |
+----------------------+
| 838:59:59 |
+----------------------+
1 row in set, 1 warning (0.00 sec)
SHOW WARNINGS;
+---------+------+-------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------+
| Warning | 1292 | Truncated incorrect time value: '9999999' |
+---------+------+-------------------------------------------+SELECT TIMESTAMP('2003-12-31');
+-------------------------+
| TIMESTAMP('2003-12-31') |
+-------------------------+
| 2003-12-31 00:00:00 |
+-------------------------+
SELECT TIMESTAMP('2003-12-31 12:00:00','6:30:00');
+--------------------------------------------+
| TIMESTAMP('2003-12-31 12:00:00','6:30:00') |
+--------------------------------------------+
| 2003-12-31 18:30:00 |
+--------------------------------------------+SELECT TIMESTAMPADD(MINUTE,1,'2003-01-02');
+-------------------------------------+
| TIMESTAMPADD(MINUTE,1,'2003-01-02') |
+-------------------------------------+
| 2003-01-02 00:01:00 |
+-------------------------------------+
SELECT TIMESTAMPADD(WEEK,1,'2003-01-02');
+-----------------------------------+
| TIMESTAMPADD(WEEK,1,'2003-01-02') |
+-----------------------------------+
| 2003-01-09 |
+-----------------------------------+STDDEV(expr)CREATE OR REPLACE TABLE stats (category VARCHAR(2), x INT);
INSERT INTO stats VALUES
('a',1),('a',2),('a',3),
('b',11),('b',12),('b',20),('b',30),('b',60);
SELECT category, STDDEV_POP(x), STDDEV_SAMP(x), VAR_POP(x)
FROM stats GROUP BY category;
+----------+---------------+----------------+------------+
| category | STDDEV_POP(x) | STDDEV_SAMP(x) | VAR_POP(x) |
+----------+---------------+----------------+------------+
| a | 0.8165 | 1.0000 | 0.6667 |
| b | 18.0400 | 20.1693 | 325.4400 |
+----------+---------------+----------------+------------+CREATE OR REPLACE TABLE student_test (name CHAR(10), test CHAR(10), score TINYINT);
INSERT INTO student_test VALUES
('Chun', 'SQL', 75), ('Chun', 'Tuning', 73),
('Esben', 'SQL', 43), ('Esben', 'Tuning', 31),
('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88),
('Tatiana', 'SQL', 87);
SELECT name, test, score, STDDEV_POP(score)
OVER (PARTITION BY test) AS stddev_results FROM student_test;
+---------+--------+-------+----------------+
| name | test | score | stddev_results |
+---------+--------+-------+----------------+
| Chun | SQL | 75 | 16.9466 |
| Chun | Tuning | 73 | 24.1247 |
| Esben | SQL | 43 | 16.9466 |
| Esben | Tuning | 31 | 24.1247 |
| Kaolin | SQL | 56 | 16.9466 |
| Kaolin | Tuning | 88 | 24.1247 |
| Tatiana | SQL | 87 | 16.9466 |
+---------+--------+-------+----------------+STDDEV_POP(expr)CREATE OR REPLACE TABLE stats (category VARCHAR(2), x INT);
INSERT INTO stats VALUES
('a',1),('a',2),('a',3),
('b',11),('b',12),('b',20),('b',30),('b',60);
SELECT category, STDDEV_POP(x), STDDEV_SAMP(x), VAR_POP(x)
FROM stats GROUP BY category;
+----------+---------------+----------------+------------+
| category | STDDEV_POP(x) | STDDEV_SAMP(x) | VAR_POP(x) |
+----------+---------------+----------------+------------+
| a | 0.8165 | 1.0000 | 0.6667 |
| b | 18.0400 | 20.1693 | 325.4400 |
+----------+---------------+----------------+------------+CREATE OR REPLACE TABLE student_test (name CHAR(10), test CHAR(10), score TINYINT);
INSERT INTO student_test VALUES
('Chun', 'SQL', 75), ('Chun', 'Tuning', 73),
('Esben', 'SQL', 43), ('Esben', 'Tuning', 31),
('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88),
('Tatiana', 'SQL', 87);
SELECT name, test, score, STDDEV_POP(score)
OVER (PARTITION BY test) AS stddev_results FROM student_test;
+---------+--------+-------+----------------+
| name | test | score | stddev_results |
+---------+--------+-------+----------------+
| Chun | SQL | 75 | 16.9466 |
| Chun | Tuning | 73 | 24.1247 |
| Esben | SQL | 43 | 16.9466 |
| Esben | Tuning | 31 | 24.1247 |
| Kaolin | SQL | 56 | 16.9466 |
| Kaolin | Tuning | 88 | 24.1247 |
| Tatiana | SQL | 87 | 16.9466 |
+---------+--------+-------+----------------+DATE_ADD(date,INTERVAL expr unit)SELECT '2008-12-31 23:59:59' + INTERVAL 1 SECOND;
+-------------------------------------------+
| '2008-12-31 23:59:59' + INTERVAL 1 SECOND |
+-------------------------------------------+
| 2009-01-01 00:00:00 |
+-------------------------------------------+SELECT INTERVAL 1 DAY + '2008-12-31';
+-------------------------------+
| INTERVAL 1 DAY + '2008-12-31' |
+-------------------------------+
| 2009-01-01 |
+-------------------------------+SELECT '2005-01-01' - INTERVAL 1 SECOND;
+----------------------------------+
| '2005-01-01' - INTERVAL 1 SECOND |
+----------------------------------+
| 2004-12-31 23:59:59 |
+----------------------------------+SELECT DATE_ADD('2000-12-31 23:59:59', INTERVAL 1 SECOND);
+----------------------------------------------------+
| DATE_ADD('2000-12-31 23:59:59', INTERVAL 1 SECOND) |
+----------------------------------------------------+
| 2001-01-01 00:00:00 |
+----------------------------------------------------+SELECT DATE_ADD('2010-12-31 23:59:59', INTERVAL 1 DAY);
+-------------------------------------------------+
| DATE_ADD('2010-12-31 23:59:59', INTERVAL 1 DAY) |
+-------------------------------------------------+
| 2011-01-01 23:59:59 |
+-------------------------------------------------+SELECT DATE_ADD('2100-12-31 23:59:59', INTERVAL '1:1' MINUTE_SECOND);
+---------------------------------------------------------------+
| DATE_ADD('2100-12-31 23:59:59', INTERVAL '1:1' MINUTE_SECOND) |
+---------------------------------------------------------------+
| 2101-01-01 00:01:00 |
+---------------------------------------------------------------+SELECT DATE_ADD('1900-01-01 00:00:00', INTERVAL '-1 10' DAY_HOUR);
+------------------------------------------------------------+
| DATE_ADD('1900-01-01 00:00:00', INTERVAL '-1 10' DAY_HOUR) |
+------------------------------------------------------------+
| 1899-12-30 14:00:00 |
+------------------------------------------------------------+SELECT DATE_ADD('1992-12-31 23:59:59.000002', INTERVAL '1.999999' SECOND_MICROSECOND);
+--------------------------------------------------------------------------------+
| DATE_ADD('1992-12-31 23:59:59.000002', INTERVAL '1.999999' SECOND_MICROSECOND) |
+--------------------------------------------------------------------------------+
| 1993-01-01 00:00:01.000001 |
+--------------------------------------------------------------------------------+SELECT MAKEDATE(2011,365), MAKEDATE(2012,365);
+--------------------+--------------------+
| MAKEDATE(2011,365) | MAKEDATE(2012,365) |
+--------------------+--------------------+
| 2011-12-31 | 2012-12-30 |
+--------------------+--------------------+
SELECT MAKEDATE(2011,366), MAKEDATE(2012,366);
+--------------------+--------------------+
| MAKEDATE(2011,366) | MAKEDATE(2012,366) |
+--------------------+--------------------+
| 2012-01-01 | 2012-12-31 |
+--------------------+--------------------+
SELECT MAKEDATE(2011,0);
+------------------+
| MAKEDATE(2011,0) |
+------------------+
| NULL |
+------------------+SELECT 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 |
+---------------------+--------------------------------+SYSDATE([precision])SELECT NOW(), SLEEP(2), NOW();
+---------------------+----------+---------------------+
| NOW() | SLEEP(2) | NOW() |
+---------------------+----------+---------------------+
| 2010-03-27 13:23:40 | 0 | 2010-03-27 13:23:40 |
+---------------------+----------+---------------------+
SELECT SYSDATE(), SLEEP(2), SYSDATE();
+---------------------+----------+---------------------+
| SYSDATE() | SLEEP(2) | SYSDATE() |
+---------------------+----------+---------------------+
| 2010-03-27 13:23:52 | 0 | 2010-03-27 13:23:54 |
+---------------------+----------+---------------------+SELECT SYSDATE(4);
+--------------------------+
| SYSDATE(4) |
+--------------------------+
| 2018-07-10 10:17:13.1689 |
+--------------------------+SELECT PERIOD_ADD(200801,2);
+----------------------+
| PERIOD_ADD(200801,2) |
+----------------------+
| 200803 |
+----------------------+
SELECT PERIOD_ADD(6910,2);
+--------------------+
| PERIOD_ADD(6910,2) |
+--------------------+
| 206912 |
+--------------------+
SELECT PERIOD_ADD(7010,2);
+--------------------+
| PERIOD_ADD(7010,2) |
+--------------------+
| 197012 |
+--------------------+SELECT 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' |
+---------+------+-----------------------------------------+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 |
+------------------+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 |
+---------------------+min - minutes
h - hours
d - days
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'
Obtaining the string matching to the standard European date format:
Using the same string to format a date:
This page is licensed: GPLv2, originally from fill_help_tables.sql
Returns a DATETIME truncated according to fmt.
Supported formats:
Truncate to day: DD, DDD, J
Truncate to month: MM, MON, MONTH, RM
Truncate to year: SYEAR, SYYYY, Y,YEAR, YY, YYY, YYYY
This page is licensed: CC BY-SA / Gnu FDL
TRUNC(date[,fmt])Count rows or values. This function returns the number of rows that match criteria or the number of non-NULL values in an expression.
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 , and so can be used with the clause.
COUNT(*) counts the total number of rows in a table.
COUNT() returns 0 if there were no matching rows.
COUNT() can be used as a .
example:
As a
This page is licensed: GPLv2, originally from
Find the minimum value. This function returns the lowest value in a set of values, applicable to numbers, strings, and dates.
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 and 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 , and so can be used with the clause.
MIN() can be used as a .
MIN() returns NULL if there were no matching rows.
Not only ascending, but also can be used to optimize MIN.
Only ascending indexes can be used to optimize MIN.
MIN() with a string:
Be careful to avoid this common mistake, not grouping correctly and returning mismatched data:
Difference between ORDER BY ASC and MIN():
As a :
(average)
(maximum)
(sum total)
returns the smallest value from a list.
This page is licensed: GPLv2, originally from
Calculate population standard deviation. This function returns the square root of the population variance. It is a synonym for STDDEV_POP().
STD(expr)Returns the population standard deviation of expr. This is an extension to standard SQL. The standard SQL function can be used instead.
It is an , and so can be used with the clause.
STD() can be used as a .
This function returns NULL if there were no matching rows.
As an :
As a :
(equivalent, standard SQL)
(equivalent, Oracle-compatible non-standard SQL)
(variance)
(sample standard deviation)
This page is licensed: GPLv2, originally from
Calculate the total sum. This function returns the sum of all values in a numeric expression, ignoring NULL values.
Returns the sum of expr. If the return set has no rows, SUM() returnsNULL. The DISTINCT keyword can be used to sum only the distinct values of expr.
SUM() can be used as a , although not with the DISTINCT specifier.
Commonly, SUM is used with a clause:
The clause is required when using an aggregate function along with regular column data, otherwise the result will be a mismatch, as in the following common type of mistake:
As a :
(average)
(maximum)
(minimum)
This page is licensed: GPLv2, originally from
Calculate population variance. This function computes the statistical variance for a set of values assumed to be the entire population.
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 , 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 , and so can be used with the clause.
VAR_POP() can be used as a .
VAR_POP() returns NULL if there were no matching rows.
As an :
As a :
(equivalent, non-standard SQL)
(population standard deviation)
(sample standard deviation)
This page is licensed: GPLv2, originally from
Calculate the difference between timestamps. This function returns the difference between two datetime expressions in the specified unit.
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 function, that is, MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.
TIMESTAMPDIFF can also be used to calculate age.
Calculating age:
Age as of 2014-08-02:
This page is licensed: GPLv2, originally from
Subtract a time value. This function subtracts one time or datetime expression from another and returns the result.
Return the current UTC time. This function returns the current Coordinated Universal Time in 'HH:MM:SS' or HHMMSS format.
UTC_TIME
UTC_TIME([precision])Returns the current UTC time as a value in HH:MM:SS or HHMMSS.uuuuuu format, depending on whether the function is used in a string or numeric context.
The optional precision determines the microsecond precision. See .
With precision:
This page is licensed: GPLv2, originally from
Return the current UTC timestamp. This function returns the current Coordinated Universal Time date and time.
UTC_TIMESTAMP
UTC_TIMESTAMP([precision])Returns the current UTC date and time as a value in YYYY-MM-DD HH:MM:SS or YYYYMMDDHHMMSS.uuuuuu format, depending on whether the function is used in a string or numeric context.
The optional precision determines the microsecond precision. See .
With precision:
This page is licensed: GPLv2, originally from
Calculate absolute value. This function returns the non-negative value of a number, removing any negative sign.
Extract a specific part of a date. This function retrieves components like YEAR, MONTH, DAY, or HOUR from a date or datetime expression.
EXTRACT(unit FROM date)The EXTRACT() function extracts the required unit from the date. See Date and Time Units for a complete list of permitted units.
is not a standard SQL function, so continues to adhere to the old behavior inherited from MySQL.
EXTRACT (HOUR FROM...) returns a value from 0 to 23, as per the SQL standard. HOUR is not a standard function, so continues to adhere to the old behaviour inherited from MySQL.
This page is licensed: GPLv2, originally from
Return the calendar week. This function returns the week number of the date (1-53), equivalent to WEEK(date, 3).
Concatenate strings from a group. This function joins non-NULL values from multiple rows into a single string, with an optional separator.
Reference keywords for date arithmetic. These units, such as DAY, HOUR, and MINUTE, specify the interval type used in functions like DATE_ADD and EXTRACT.
The INTERVAL keyword can be used to add or subtract a time interval of time to a , or value.
The syntax is:
For example, the SECOND unit is used below by the function:
The following units are valid:
SELECT
FORMAT_PICO_TIME(43) AS ps,
FORMAT_PICO_TIME(4321) AS ns,
FORMAT_PICO_TIME(43211234) AS us,
FORMAT_PICO_TIME(432112344321) AS ms,
FORMAT_PICO_TIME(43211234432123) AS s,
FORMAT_PICO_TIME(432112344321234) AS m,
FORMAT_PICO_TIME(4321123443212345) AS h,
FORMAT_PICO_TIME(432112344321234545) AS d;
+--------+---------+----------+-----------+---------+----------+--------+--------+
| ps | ns | us | ms | s | m | h | d |
+--------+---------+----------+-----------+---------+----------+--------+--------+
| 43 ps | 4.32 ns | 43.21 us | 432.11 ms | 43.21 s | 7.20 min | 1.20 h | 5.00 d |
+--------+---------+----------+-----------+---------+----------+--------+--------+GET_FORMAT({DATE|DATETIME|TIME}, {'EUR'|'USA'|'JIS'|'ISO'|'INTERNAL'})SELECT GET_FORMAT(DATE, 'EUR');
+-------------------------+
| GET_FORMAT(DATE, 'EUR') |
+-------------------------+
| %d.%m.%Y |
+-------------------------+SELECT DATE_FORMAT('2003-10-03',GET_FORMAT(DATE,'EUR'));
+--------------------------------------------------+
| DATE_FORMAT('2003-10-03',GET_FORMAT(DATE,'EUR')) |
+--------------------------------------------------+
| 03.10.2003 |
+--------------------------------------------------+
SELECT STR_TO_DATE('10.31.2003',GET_FORMAT(DATE,'USA'));
+--------------------------------------------------+
| STR_TO_DATE('10.31.2003',GET_FORMAT(DATE,'USA')) |
+--------------------------------------------------+
| 2003-10-31 |
+--------------------------------------------------+SELECT TRUNC('2025-09-24 12:43','DD');
+--------------------------------+
| TRUNC('2025-09-24 12:43','DD') |
+--------------------------------+
| 2025-09-24 00:00:00 |
+--------------------------------+
SELECT TRUNC('2025-09-24 12:43','MM');
+--------------------------------+
| TRUNC('2025-09-24 12:43','MM') |
+--------------------------------+
| 2025-09-01 00:00:00 |
+--------------------------------+
SELECT TRUNC('2025-09-24 12:43','YY');
+--------------------------------+
| TRUNC('2025-09-24 12:43','YY') |
+--------------------------------+
| 2025-01-01 00:00:00 |
+--------------------------------+MIN([DISTINCT] expr)SUM([DISTINCT] expr)VAR_POP(expr)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'
SELECT SUBTIME('2007-12-31 23:59:59.999999','1 1:1:1.000002');
+--------------------------------------------------------+
| SUBTIME('2007-12-31 23:59:59.999999','1 1:1:1.000002') |
+--------------------------------------------------------+
| 2007-12-30 22:58:58.999997 |
+--------------------------------------------------------+
SELECT SUBTIME('01:00:00.999999', '02:00:00.999998');
+-----------------------------------------------+
| SUBTIME('01:00:00.999999', '02:00:00.999998') |
+-----------------------------------------------+
| -00:59:59.999999 |
+-----------------------------------------------+SELECT WEEKOFYEAR('2008-02-20');
+--------------------------+
| WEEKOFYEAR('2008-02-20') |
+--------------------------+
| 8 |
+--------------------------+MAX()DISTINCTexprDISTINCTNote 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.
MAX string:
Be careful to avoid this common mistake, not grouping correctly and returning mismatched data:
Difference between ORDER BY DESC and MAX():
As a window function:
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
It returns NULL if all arguments are NULL, or there are no matching rows.
The maximum returned length in bytes is determined by the group_concat_max_len server system variable, which defaults to 1M.
If group_concat_max_len <= 512, the return type is VARBINARY or VARCHAR; otherwise, the return type is BLOB or TEXT. The choice between binary or non-binary types depends from the input.
The full syntax is as follows:
DISTINCT eliminates duplicate values from the output string.
ORDER BY determines the order of returned values.
SEPARATOR specifies a separator between the values. The default separator is a comma (,). It is possible to avoid using a separator by specifying an empty string.
The LIMIT clause can be used with GROUP_CONCAT.
Get a readable list of MariaDB users from the mysql.user table:
In the former example, DISTINCT is used because the same user may occur more than once. The new line () used as a SEPARATOR makes the results easier to read.
Get a readable list of hosts from which each user can connect:
The former example shows the difference between the GROUP_CONCAT's ORDER BY (which sorts the concatenated hosts), and the SELECT's ORDER BY (which sorts the rows).
LIMIT can be used with GROUP_CONCAT, so, for example, given the following table:
the following query:
can be more simply rewritten as:
NULLS:
This page is licensed: GPLv2, originally from fill_help_tables.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.
VARIANCE() can be used as a window function.
VARIANCE() returns NULL if there were no matching rows.
As an aggregate function:
As a window function:
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
This page is licensed: GPLv2, originally from fill_help_tables.sql
TIME_TO_SEC(time)CREATE TABLE student (name CHAR(10), test CHAR(10), score TINYINT);
INSERT INTO student VALUES
('Chun', 'SQL', 75), ('Chun', 'Tuning', 73),
('Esben', 'SQL', 43), ('Esben', 'Tuning', 31),
('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88),
('Tatiana', 'SQL', 87), ('Tatiana', 'Tuning', 83);
SELECT COUNT(*) FROM student;
+----------+
| COUNT(*) |
+----------+
| 8 |
+----------+SELECT COUNT(DISTINCT (name)) FROM student;
+------------------------+
| COUNT(DISTINCT (name)) |
+------------------------+
| 4 |
+------------------------+CREATE OR REPLACE TABLE student_test (name CHAR(10), test CHAR(10), score TINYINT);
INSERT INTO student_test VALUES
('Chun', 'SQL', 75), ('Chun', 'Tuning', 73),
('Esben', 'SQL', 43), ('Esben', 'Tuning', 31),
('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88),
('Tatiana', 'SQL', 87);
SELECT name, test, score, COUNT(score) OVER (PARTITION BY name)
AS tests_written FROM student_test;
+---------+--------+-------+---------------+
| name | test | score | tests_written |
+---------+--------+-------+---------------+
| Chun | SQL | 75 | 2 |
| Chun | Tuning | 73 | 2 |
| Esben | SQL | 43 | 2 |
| Esben | Tuning | 31 | 2 |
| Kaolin | SQL | 56 | 2 |
| Kaolin | Tuning | 88 | 2 |
| Tatiana | SQL | 87 | 1 |
+---------+--------+-------+---------------+CREATE TABLE student (name CHAR(10), test CHAR(10), score TINYINT);
INSERT INTO student VALUES
('Chun', 'SQL', 75), ('Chun', 'Tuning', 73),
('Esben', 'SQL', 43), ('Esben', 'Tuning', 31),
('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88),
('Tatiana', 'SQL', 87), ('Tatiana', 'Tuning', 83);
SELECT name, MIN(score) FROM student GROUP BY name;
+---------+------------+
| name | MIN(score) |
+---------+------------+
| Chun | 73 |
| Esben | 31 |
| Kaolin | 56 |
| Tatiana | 83 |
+---------+------------+SELECT MIN(name) FROM student;
+-----------+
| MIN(name) |
+-----------+
| Chun |
+-----------+SELECT name,test,MIN(score) FROM student;
+------+------+------------+
| name | test | MIN(score) |
+------+------+------------+
| Chun | SQL | 31 |
+------+------+------------+CREATE TABLE student2(name CHAR(10),grade ENUM('b','c','a'));
INSERT INTO student2 VALUES('Chun','b'),('Esben','c'),('Kaolin','a');
SELECT MIN(grade) FROM student2;
+------------+
| MIN(grade) |
+------------+
| a |
+------------+
SELECT grade FROM student2 ORDER BY grade ASC LIMIT 1;
+-------+
| grade |
+-------+
| b |
+-------+CREATE OR REPLACE TABLE student_test (name CHAR(10), test CHAR(10), score TINYINT);
INSERT INTO student_test VALUES
('Chun', 'SQL', 75), ('Chun', 'Tuning', 73),
('Esben', 'SQL', 43), ('Esben', 'Tuning', 31),
('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88),
('Tatiana', 'SQL', 87);
SELECT name, test, score, MIN(score)
OVER (PARTITION BY name) AS lowest_score FROM student_test;
+---------+--------+-------+--------------+
| name | test | score | lowest_score |
+---------+--------+-------+--------------+
| Chun | SQL | 75 | 73 |
| Chun | Tuning | 73 | 73 |
| Esben | SQL | 43 | 31 |
| Esben | Tuning | 31 | 31 |
| Kaolin | SQL | 56 | 56 |
| Kaolin | Tuning | 88 | 56 |
| Tatiana | SQL | 87 | 87 |
+---------+--------+-------+--------------+CREATE OR REPLACE TABLE stats (category VARCHAR(2), x INT);
INSERT INTO stats VALUES
('a',1),('a',2),('a',3),
('b',11),('b',12),('b',20),('b',30),('b',60);
SELECT category, STDDEV_POP(x), STDDEV_SAMP(x), VAR_POP(x)
FROM stats GROUP BY category;
+----------+---------------+----------------+------------+
| category | STDDEV_POP(x) | STDDEV_SAMP(x) | VAR_POP(x) |
+----------+---------------+----------------+------------+
| a | 0.8165 | 1.0000 | 0.6667 |
| b | 18.0400 | 20.1693 | 325.4400 |
+----------+---------------+----------------+------------+CREATE OR REPLACE TABLE student_test (name CHAR(10), test CHAR(10), score TINYINT);
INSERT INTO student_test VALUES
('Chun', 'SQL', 75), ('Chun', 'Tuning', 73),
('Esben', 'SQL', 43), ('Esben', 'Tuning', 31),
('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88),
('Tatiana', 'SQL', 87);
SELECT name, test, score, STDDEV_POP(score)
OVER (PARTITION BY test) AS stddev_results FROM student_test;
+---------+--------+-------+----------------+
| name | test | score | stddev_results |
+---------+--------+-------+----------------+
| Chun | SQL | 75 | 16.9466 |
| Chun | Tuning | 73 | 24.1247 |
| Esben | SQL | 43 | 16.9466 |
| Esben | Tuning | 31 | 24.1247 |
| Kaolin | SQL | 56 | 16.9466 |
| Kaolin | Tuning | 88 | 24.1247 |
| Tatiana | SQL | 87 | 16.9466 |
+---------+--------+-------+----------------+CREATE TABLE sales (sales_value INT);
INSERT INTO sales VALUES(10),(20),(20),(40);
SELECT SUM(sales_value) FROM sales;
+------------------+
| SUM(sales_value) |
+------------------+
| 90 |
+------------------+
SELECT SUM(DISTINCT(sales_value)) FROM sales;
+----------------------------+
| SUM(DISTINCT(sales_value)) |
+----------------------------+
| 70 |
+----------------------------+CREATE TABLE sales (name CHAR(10), month CHAR(10), units INT);
INSERT INTO sales VALUES
('Chun', 'Jan', 75), ('Chun', 'Feb', 73),
('Esben', 'Jan', 43), ('Esben', 'Feb', 31),
('Kaolin', 'Jan', 56), ('Kaolin', 'Feb', 88),
('Tatiana', 'Jan', 87), ('Tatiana', 'Feb', 83);
SELECT name, SUM(units) FROM sales GROUP BY name;
+---------+------------+
| name | SUM(units) |
+---------+------------+
| Chun | 148 |
| Esben | 74 |
| Kaolin | 144 |
| Tatiana | 170 |
+---------+------------+SELECT name,SUM(units) FROM sales
;+------+------------+
| name | SUM(units) |
+------+------------+
| Chun | 536 |
+------+------------+CREATE OR REPLACE TABLE student_test (name CHAR(10), test CHAR(10), score TINYINT);
INSERT INTO student_test VALUES
('Chun', 'SQL', 75), ('Chun', 'Tuning', 73),
('Esben', 'SQL', 43), ('Esben', 'Tuning', 31),
('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88),
('Tatiana', 'SQL', 87);
SELECT name, test, score, SUM(score) OVER (PARTITION BY name) AS total_score FROM student_test;
+---------+--------+-------+-------------+
| name | test | score | total_score |
+---------+--------+-------+-------------+
| Chun | SQL | 75 | 148 |
| Chun | Tuning | 73 | 148 |
| Esben | SQL | 43 | 74 |
| Esben | Tuning | 31 | 74 |
| Kaolin | SQL | 56 | 144 |
| Kaolin | Tuning | 88 | 144 |
| Tatiana | SQL | 87 | 87 |
+---------+--------+-------+-------------+CREATE TABLE v(i tinyint);
INSERT INTO v VALUES(101),(99);
SELECT VAR_POP(i) FROM v;
+------------+
| VAR_POP(i) |
+------------+
| 1.0000 |
+------------+
INSERT INTO v VALUES(120),(80);
SELECT VAR_POP(i) FROM v;
+------------+
| VAR_POP(i) |
+------------+
| 200.5000 |
+------------+CREATE OR REPLACE TABLE stats (category VARCHAR(2), x INT);
INSERT INTO stats VALUES
('a',1),('a',2),('a',3),
('b',11),('b',12),('b',20),('b',30),('b',60);
SELECT category, STDDEV_POP(x), STDDEV_SAMP(x), VAR_POP(x)
FROM stats GROUP BY category;
+----------+---------------+----------------+------------+
| category | STDDEV_POP(x) | STDDEV_SAMP(x) | VAR_POP(x) |
+----------+---------------+----------------+------------+
| a | 0.8165 | 1.0000 | 0.6667 |
| b | 18.0400 | 20.1693 | 325.4400 |
+----------+---------------+----------------+------------+CREATE OR REPLACE TABLE student_test (name CHAR(10), test CHAR(10), score TINYINT);
INSERT INTO student_test VALUES
('Chun', 'SQL', 75), ('Chun', 'Tuning', 73),
('Esben', 'SQL', 43), ('Esben', 'Tuning', 31),
('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88),
('Tatiana', 'SQL', 87);
SELECT name, test, score, VAR_POP(score)
OVER (PARTITION BY test) AS variance_results FROM student_test;
+---------+--------+-------+------------------+
| name | test | score | variance_results |
+---------+--------+-------+------------------+
| Chun | SQL | 75 | 287.1875 |
| Esben | SQL | 43 | 287.1875 |
| Kaolin | SQL | 56 | 287.1875 |
| Tatiana | SQL | 87 | 287.1875 |
| Chun | Tuning | 73 | 582.0000 |
| Esben | Tuning | 31 | 582.0000 |
| Kaolin | Tuning | 88 | 582.0000 |
+---------+--------+-------+------------------+SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');
+------------------------------------------------+
| TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01') |
+------------------------------------------------+
| 3 |
+------------------------------------------------+
SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01');
+-----------------------------------------------+
| TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01') |
+-----------------------------------------------+
| -1 |
+-----------------------------------------------+
SELECT TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55');
+----------------------------------------------------------+
| TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55') |
+----------------------------------------------------------+
| 128885 |
+----------------------------------------------------------+SELECT CURDATE();
+------------+
| CURDATE() |
+------------+
| 2019-05-27 |
+------------+
SELECT TIMESTAMPDIFF(YEAR, '1971-06-06', CURDATE()) AS age;
+------+
| age |
+------+
| 47 |
+------+
SELECT TIMESTAMPDIFF(YEAR, '1971-05-06', CURDATE()) AS age;
+------+
| age |
+------+
| 48 |
+------+SELECT name, date_of_birth, TIMESTAMPDIFF(YEAR,date_of_birth,'2014-08-02') AS age
FROM student_details;
+---------+---------------+------+
| name | date_of_birth | age |
+---------+---------------+------+
| Chun | 1993-12-31 | 20 |
| Esben | 1946-01-01 | 68 |
| Kaolin | 1996-07-16 | 18 |
| Tatiana | 1988-04-13 | 26 |
+---------+---------------+------+SELECT UTC_TIME(), UTC_TIME() + 0;
+------------+----------------+
| UTC_TIME() | UTC_TIME() + 0 |
+------------+----------------+
| 17:32:34 | 173234.000000 |
+------------+----------------+SELECT UTC_TIME(5);
+----------------+
| UTC_TIME(5) |
+----------------+
| 07:52:50.78369 |
+----------------+SELECT UTC_TIMESTAMP(), UTC_TIMESTAMP() + 0;
+---------------------+-----------------------+
| UTC_TIMESTAMP() | UTC_TIMESTAMP() + 0 |
+---------------------+-----------------------+
| 2010-03-27 17:33:16 | 20100327173316.000000 |
+---------------------+-----------------------+SELECT UTC_TIMESTAMP(4);
+--------------------------+
| UTC_TIMESTAMP(4) |
+--------------------------+
| 2018-07-10 07:51:09.1019 |
+--------------------------+SELECT ABS(42);
+---------+
| ABS(42) |
+---------+
| 42 |
+---------+
SELECT ABS(-42);
+----------+
| ABS(-42) |
+----------+
| 42 |
+----------+
SELECT ABS(DATE '1994-01-01');
+------------------------+
| ABS(DATE '1994-01-01') |
+------------------------+
| 19940101 |
+------------------------+SELECT EXTRACT(YEAR FROM '2009-07-02');
+---------------------------------+
| EXTRACT(YEAR FROM '2009-07-02') |
+---------------------------------+
| 2009 |
+---------------------------------+
SELECT EXTRACT(YEAR_MONTH FROM '2009-07-02 01:02:03');
+------------------------------------------------+
| EXTRACT(YEAR_MONTH FROM '2009-07-02 01:02:03') |
+------------------------------------------------+
| 200907 |
+------------------------------------------------+
SELECT EXTRACT(DAY_MINUTE FROM '2009-07-02 01:02:03');
+------------------------------------------------+
| EXTRACT(DAY_MINUTE FROM '2009-07-02 01:02:03') |
+------------------------------------------------+
| 20102 |
+------------------------------------------------+
SELECT EXTRACT(MICROSECOND FROM '2003-01-02 10:30:00.000123');
+--------------------------------------------------------+
| EXTRACT(MICROSECOND FROM '2003-01-02 10:30:00.000123') |
+--------------------------------------------------------+
| 123 |
+--------------------------------------------------------+SELECT EXTRACT(HOUR FROM '26:30:00'), HOUR('26:30:00');
+-------------------------------+------------------+
| EXTRACT(HOUR FROM '26:30:00') | HOUR('26:30:00') |
+-------------------------------+------------------+
| 2 | 26 |
+-------------------------------+------------------+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 |
+---------------------+---------------+-----------+MAX([DISTINCT] expr)CREATE TABLE student (name CHAR(10), test CHAR(10), score TINYINT);
INSERT INTO student VALUES
('Chun', 'SQL', 75), ('Chun', 'Tuning', 73),
('Esben', 'SQL', 43), ('Esben', 'Tuning', 31),
('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88),
('Tatiana', 'SQL', 87), ('Tatiana', 'Tuning', 83);
SELECT name, MAX(score) FROM student GROUP BY name;
+---------+------------+
| name | MAX(score) |
+---------+------------+
| Chun | 75 |
| Esben | 43 |
| Kaolin | 88 |
| Tatiana | 87 |
+---------+------------+SELECT MAX(name) FROM student;
+-----------+
| MAX(name) |
+-----------+
| Tatiana |
+-----------+SELECT name,test,MAX(SCORE) FROM student;
+------+------+------------+
| name | test | MAX(SCORE) |
+------+------+------------+
| Chun | SQL | 88 |
+------+------+------------+CREATE TABLE student2(name CHAR(10),grade ENUM('b','c','a'));
INSERT INTO student2 VALUES('Chun','b'),('Esben','c'),('Kaolin','a');
SELECT MAX(grade) FROM student2;
+------------+
| MAX(grade) |
+------------+
| c |
+------------+
SELECT grade FROM student2 ORDER BY grade DESC LIMIT 1;
+-------+
| grade |
+-------+
| a |
+-------+CREATE OR REPLACE TABLE student_test (name CHAR(10), test CHAR(10), score TINYINT);
INSERT INTO student_test VALUES
('Chun', 'SQL', 75), ('Chun', 'Tuning', 73),
('Esben', 'SQL', 43), ('Esben', 'Tuning', 31),
('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88),
('Tatiana', 'SQL', 87);
SELECT name, test, score, MAX(score)
OVER (PARTITION BY name) AS highest_score FROM student_test;
+---------+--------+-------+---------------+
| name | test | score | highest_score |
+---------+--------+-------+---------------+
| Chun | SQL | 75 | 75 |
| Chun | Tuning | 73 | 75 |
| Esben | SQL | 43 | 43 |
| Esben | Tuning | 31 | 43 |
| Kaolin | SQL | 56 | 88 |
| Kaolin | Tuning | 88 | 88 |
| Tatiana | SQL | 87 | 87 |
+---------+--------+-------+---------------+GROUP_CONCAT(expr)GROUP_CONCAT([DISTINCT] expr [,expr ...]
[ORDER BY {unsigned_integer | col_name | expr}
[ASC | DESC] [,col_name ...]]
[SEPARATOR str_val]
[LIMIT {[offset,] row_count | row_count OFFSET offset}])SELECT student_name,
GROUP_CONCAT(test_score)
FROM student
GROUP BY student_name;SELECT GROUP_CONCAT(DISTINCT User ORDER BY User SEPARATOR '\n')
FROM mysql.user;SELECT User, GROUP_CONCAT(Host ORDER BY Host SEPARATOR ', ')
FROM mysql.user GROUP BY User ORDER BY User;CREATE TABLE d (dd DATE, cc INT);
INSERT INTO d VALUES ('2017-01-01',1);
INSERT INTO d VALUES ('2017-01-02',2);
INSERT INTO d VALUES ('2017-01-04',3);SELECT SUBSTRING_INDEX(GROUP_CONCAT(CONCAT_WS(":",dd,cc)
ORDER BY cc DESC),",",1) FROM d;
+----------------------------------------------------------------------------+
| SUBSTRING_INDEX(GROUP_CONCAT(CONCAT_WS(":",dd,cc) ORDER BY cc DESC),",",1) |
+----------------------------------------------------------------------------+
| 2017-01-04:3 |
+----------------------------------------------------------------------------+SELECT GROUP_CONCAT(CONCAT_WS(":",dd,cc) ORDER BY cc DESC LIMIT 1) FROM d;
+-------------------------------------------------------------+
| GROUP_CONCAT(CONCAT_WS(":",dd,cc) ORDER BY cc DESC LIMIT 1) |
+-------------------------------------------------------------+
| 2017-01-04:3 |
+-------------------------------------------------------------+CREATE OR REPLACE TABLE t1 (a int, b char);
INSERT INTO t1 VALUES (1, 'a'), (2, NULL);
SELECT GROUP_CONCAT(a, b) FROM t1;
+--------------------+
| GROUP_CONCAT(a, b) |
+--------------------+
| 1a |
+--------------------+VARIANCE(expr)CREATE TABLE v(i tinyint);
INSERT INTO v VALUES(101),(99);
SELECT VARIANCE(i) FROM v;
+-------------+
| VARIANCE(i) |
+-------------+
| 1.0000 |
+-------------+
INSERT INTO v VALUES(120),(80);
SELECT VARIANCE(i) FROM v;
+-------------+
| VARIANCE(i) |
+-------------+
| 200.5000 |
+-------------+CREATE OR REPLACE TABLE stats (category VARCHAR(2), x INT);
INSERT INTO stats VALUES
('a',1),('a',2),('a',3),
('b',11),('b',12),('b',20),('b',30),('b',60);
SELECT category, STDDEV_POP(x), STDDEV_SAMP(x), VAR_POP(x)
FROM stats GROUP BY category;
+----------+---------------+----------------+------------+
| category | STDDEV_POP(x) | STDDEV_SAMP(x) | VAR_POP(x) |
+----------+---------------+----------------+------------+
| a | 0.8165 | 1.0000 | 0.6667 |
| b | 18.0400 | 20.1693 | 325.4400 |
+----------+---------------+----------------+------------+CREATE OR REPLACE TABLE student_test (name CHAR(10), test CHAR(10), score TINYINT);
INSERT INTO student_test VALUES
('Chun', 'SQL', 75), ('Chun', 'Tuning', 73),
('Esben', 'SQL', 43), ('Esben', 'Tuning', 31),
('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88),
('Tatiana', 'SQL', 87);
SELECT name, test, score, VAR_POP(score)
OVER (PARTITION BY test) AS variance_results FROM student_test;
+---------+--------+-------+------------------+
| name | test | score | variance_results |
+---------+--------+-------+------------------+
| Chun | SQL | 75 | 287.1875 |
| Chun | Tuning | 73 | 582.0000 |
| Esben | SQL | 43 | 287.1875 |
| Esben | Tuning | 31 | 582.0000 |
| Kaolin | SQL | 56 | 287.1875 |
| Kaolin | Tuning | 88 | 582.0000 |
| Tatiana | SQL | 87 | 287.1875 |
+---------+--------+-------+------------------+SELECT 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)SECOND
Seconds
MINUTE
Minutes
HOUR
Hours
DAY
Days
WEEK
Weeks
MONTH
Months
QUARTER
Quarters
YEAR
Years
SECOND_MICROSECOND
Seconds.Microseconds
MINUTE_MICROSECOND
Minutes.Seconds.Microseconds
MINUTE_SECOND
Minutes.Seconds
HOUR_MICROSECOND
Hours.Minutes.Seconds.Microseconds
HOUR_SECOND
Hours.Minutes.Seconds
HOUR_MINUTE
Hours.Minutes
DAY_MICROSECOND
Days Hours.Minutes.Seconds.Microseconds
DAY_SECOND
Days Hours.Minutes.Seconds
DAY_MINUTE
Days Hours.Minutes
DAY_HOUR
Days Hours
YEAR_MONTH
Years-Months
The time units containing an underscore are composite; that is, they consist of multiple base time units. For base time units, time_quantity is an integer number. For composite units, the quantity must be expressed as a string with multiple integer numbers separated by any punctuation character.
Example of composite units:
Time units can be used in the following contexts:
with the following DATE or TIME functions: ADDDATE(), SUBDATE(), DATE_ADD(), DATE_SUB(), TIMESTAMPADD(), TIMESTAMPDIFF(), EXTRACT();
in the ON SCHEDULE clause of CREATE EVENT and ALTER EVENT;
when defining a BY SYSTEM_TIME .
This page is licensed: CC BY-SA / Gnu FDL
MICROSECOND
Microseconds
ADD_MONTHS adds an integer months to a given date (DATE, DATETIME or TIMESTAMP), returning the resulting date.
months can be positive or negative. If months is not a whole number, then it will be rounded to the nearest whole number (not truncated).
The resulting day component will remain the same as that specified in date, unless the resulting month has fewer days than the day component of the given date, in which case the day will be the last day of the resulting month.
Returns NULL if given an invalid date, or a NULL argument.
This page is licensed: CC BY-SA / Gnu FDL
YYYYMMDDHHMMSS.uuuuuuMariaDB starting with
These functions return SQL standard compliant types:
NOW() and CURRENT_TIMESTAMP() return a TIMESTAMP value (analogous to the standard type TIMESTAMP WITH LOCAL TIME ZONE) which corresponds to the current point in time and is unambiguous around DST changes.
LOCALTIMESTAMP returns a DATETIME value (analogous to the standard type TIMESTAMP WITHOUT TIME ZONE). Storing its result in a TIMESTAMP column can result in a data loss around DST changes.
These functions do not return SQL standard compliant types:
NOW()
CURRENT_TIMESTAMP()
LOCALTIMESTAMP
The optional precision determines the microsecond precision. See Microseconds in MariaDB.
NOW() (or its synonyms) can be used as the default value for TIMESTAMP columns as well as.
When displayed in the INFORMATION_SCHEMA.COLUMNS table, a default CURRENT TIMESTAMP is displayed as current_timestamp() .
Changing the timestamp system variable with a SET timestamp statement affects the value returned by NOW(), but not by SYSDATE().
With precision:
Used as a default TIMESTAMP:
This page is licensed: GPLv2, originally from fill_help_tables.sql
Understand microsecond precision. This concept page explains how MariaDB stores and handles fractional seconds in time data types.
The TIME, DATETIME, and TIMESTAMP types, along with the temporal functions, CAST and dynamic columns, support microseconds. The datetime precision of a column can be specified when creating the table with CREATE TABLE, for example:
Generally, the precision can be specified for any TIME, DATETIME, or TIMESTAMP column, in parentheses, after the type name. The datetime precision specifies number of digits after the decimal dot and can be any integer number from 0 to 6. If no precision is specified it is assumed to be 0, for backward compatibility reasons.
A datetime precision can be specified wherever a type name is used. For example:
when declaring arguments of stored routines;
when specifying a return type of a stored function;
when declaring variables;
in a CAST function.
%f is used as the formatting option for microseconds in the , and functions, for example:
When comparing anything to a temporal value (DATETIME, TIME, , or TIMESTAMP), both values are compared as temporal values, not as strings.
The has a new column DATETIME_PRECISION
, , , ,
and preserve microseconds of the argument. These functions will return a number if the result non-zero datetime precision and an otherwise (for backward compatibility).
Current versions of this patch fix a bug in the following optimization: In certain queries with DISTINCT MariaDB can ignore this clause if it can prove that all result rows are unique anyway, for example, when a primary key is compared with a constant. Sometimes this optimization was applied incorrectly, though — for example, when comparing a string with a date constant. This is now fixed.
DATE_ADD() and DATE_SUB() functions can now take a TIME expression as an argument (not just DATETIME as before).
The event_time field in the table and the start_time, query_time, and lock_time fields in the table now store values with microsecond precision.
The old syntax TIMESTAMP(N), where N is the display width, is no longer supported.
Note: When you convert a temporal value to a value with a smaller precision, it will be truncated, not rounded. This is done to guarantee that the date part is not changed. For example:
This page is licensed: CC BY-SA / Gnu FDL
Return the week number. This function returns the week number for a date, with an optional mode to define the start of the week.
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 system variable is used.
With the mode value of 3, which means 'more than 3 days this year', weeks are numbered according to ISO 8601:1988.
This page is licensed: GPLv2, originally from
Return a Unix timestamp. This function returns the number of seconds since the Unix Epoch ('1970-01-01 00:00:00' UTC).
If called with no argument, returns a Unix timestamp (seconds since 1970-01-01 00:00:00 ) as an unsigned integer. If UNIX_TIMESTAMP()
is called with a date argument, it returns the value of the argument as seconds since 1970-01-01 00:00:00 UTC. date may be a string, a string, a , or a number in
the format YYMMDD or YYYYMMDD. The server interprets date as a value in the current and converts it to an internal value in . Clients can set their time zone as described in .
The inverse function of UNIX_TIMESTAMP() is
UNIX_TIMESTAMP() supports .
Timestamps in MariaDB have a maximum value of 4294967295, equivalent to 2106-02-07 06:28:15. This is due to the underlying 32-bit limitation. Using the function on a timestamp beyond this will result in NULL being returned. Use as a storage type if you require dates beyond this.
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 as a storage type if you require dates beyond this.
Returns NULL for wrong arguments to UNIX_TIMESTAMP().
This page is licensed: GPLv2, originally from
INTERVAL time_quantity time_unitSELECT '2008-12-31 23:59:59' + INTERVAL 1 SECOND;
+-------------------------------------------+
| '2008-12-31 23:59:59' + INTERVAL 1 SECOND |
+-------------------------------------------+
| 2009-01-01 00:00:00 |
+-------------------------------------------+INTERVAL '2:2' YEAR_MONTH
INTERVAL '1:30:30' HOUR_SECOND
INTERVAL '1!30!30' HOUR_SECOND -- same as aboveADD_MONTHS(date, months)SELECT ADD_MONTHS('2012-01-31', 2);
+-----------------------------+
| ADD_MONTHS('2012-01-31', 2) |
+-----------------------------+
| 2012-03-31 |
+-----------------------------+
SELECT ADD_MONTHS('2012-01-31', -5);
+------------------------------+
| ADD_MONTHS('2012-01-31', -5) |
+------------------------------+
| 2011-08-31 |
+------------------------------+
SELECT ADD_MONTHS('2011-01-31', 1);
+-----------------------------+
| ADD_MONTHS('2011-01-31', 1) |
+-----------------------------+
| 2011-02-28 |
+-----------------------------+
SELECT ADD_MONTHS('2012-01-31', 1);
+-----------------------------+
| ADD_MONTHS('2012-01-31', 1) |
+-----------------------------+
| 2012-02-29 |
+-----------------------------+
SELECT ADD_MONTHS('2012-01-31', 2);
+-----------------------------+
| ADD_MONTHS('2012-01-31', 2) |
+-----------------------------+
| 2012-03-31 |
+-----------------------------+
SELECT ADD_MONTHS('2012-01-31', 3);
+-----------------------------+
| ADD_MONTHS('2012-01-31', 3) |
+-----------------------------+
| 2012-04-30 |
+-----------------------------+
SELECT ADD_MONTHS('2011-01-15', 2.5);
+-------------------------------+
| ADD_MONTHS('2011-01-15', 2.5) |
+-------------------------------+
| 2011-04-15 |
+-------------------------------+
1 row in set (0.001 sec)
SELECT ADD_MONTHS('2011-01-15', 2.6);
+-------------------------------+
| ADD_MONTHS('2011-01-15', 2.6) |
+-------------------------------+
| 2011-04-15 |
+-------------------------------+
1 row in set (0.001 sec)
SELECT ADD_MONTHS('2011-01-15', 2.1);
+-------------------------------+
| ADD_MONTHS('2011-01-15', 2.1) |
+-------------------------------+
| 2011-03-15 |
+-------------------------------+
1 row in set (0.004 sec)NOW([precision])
CURRENT_TIMESTAMP
CURRENT_TIMESTAMP([precision])
LOCALTIME, LOCALTIME([precision])
LOCALTIMESTAMP
LOCALTIMESTAMP([precision])SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2010-03-27 13:13:25 |
+---------------------+
SELECT NOW() + 0;
+-----------------------+
| NOW() + 0 |
+-----------------------+
| 20100327131329.000000 |
+-----------------------+SELECT CURRENT_TIMESTAMP(2);
+------------------------+
| CURRENT_TIMESTAMP(2) |
+------------------------+
| 2018-07-10 09:47:26.24 |
+------------------------+CREATE TABLE t (createdTS TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP);SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='test'
AND COLUMN_NAME LIKE '%ts%'\G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test
TABLE_NAME: t
COLUMN_NAME: ts
ORDINAL_POSITION: 1
COLUMN_DEFAULT: current_timestamp()
...CREATE TABLE example(
col_microsec DATETIME(6),
col_millisec TIME(3)
);WEEK(date[,mode])UNIX_TIMESTAMP()
UNIX_TIMESTAMP(date)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.
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
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
SQL_TSI_YEAR is a synonym for YEAR:The resulting output would look like this:
If SQL_MODE is strict (the default), the example above generates the following error and no values are inserted:
If SQL_MODE is not strict, the example above generates a warning and (possibly modified) values are inserted:
The resulting data would look like this:
This page is licensed: GPLv2, originally from fill_help_tables.sql
CREATE FUNCTION example(x DATETIME(5)) RETURNS TIME(4)
BEGIN
DECLARE y TIMESTAMP(6);
RETURN CAST(x AS time(2));
END;SELECT STR_TO_DATE('20200809 020917076','%Y%m%d %H%i%s%f');
+-----------------------------------------------------+
| STR_TO_DATE('20200809 020917076','%Y%m%d %H%i%s%f') |
+-----------------------------------------------------+
| 2020-08-09 02:09:17.076000 |
+-----------------------------------------------------+SELECT CURTIME(4);
--> 10:11:12.3456SELECT TIME_TO_SEC('10:10:10.12345');
--> 36610.12345SELECT TIME('10:10:10') + INTERVAL 100 MICROSECOND;
--> 10:10:10.000100SELECT CAST('2009-12-31 23:59:59.998877' AS DATETIME(3));
-> 2009-12-31 23:59:59.998SELECT 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 |
+---------------------+-----------+-----------+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 |
+-------------------------------------------------------------+YEAR(date)CREATE TABLE t1 (d DATETIME);
INSERT INTO t1 VALUES
("2007-01-30 21:31:07"),
("1983-10-15 06:42:51"),
("2011-04-21 12:34:56"),
("2011-10-30 06:31:41"),
("2011-01-30 14:03:25"),
("2004-10-07 11:19:34");SELECT * FROM t1;
+---------------------+
| d |
+---------------------+
| 2007-01-30 21:31:07 |
| 1983-10-15 06:42:51 |
| 2011-04-21 12:34:56 |
| 2011-10-30 06:31:41 |
| 2011-01-30 14:03:25 |
| 2004-10-07 11:19:34 |
+---------------------+
SELECT * FROM t1 WHERE YEAR(d) = 2011;
+---------------------+
| d |
+---------------------+
| 2011-04-21 12:34:56 |
| 2011-10-30 06:31:41 |
| 2011-01-30 14:03:25 |
+---------------------+SELECT YEAR('1987-01-01');
+--------------------+
| YEAR('1987-01-01') |
+--------------------+
| 1987 |
+--------------------+CREATE TABLE year_format_example (
description VARCHAR(30),
example YEAR
);INSERT INTO year_format_example VALUES
('4-digit numeric year', 1966),
('2-digit numeric year', 66),
('4-digit string year', '1966'),
('2-digit string year', '66');SELECT * FROM year_format_example;
+----------------------+---------+
| description | example |
+----------------------+---------+
| 4-digit numeric year | 1966 |
| 2-digit numeric year | 2066 |
| 4-digit string year | 1966 |
| 2-digit string year | 2066 |
+----------------------+---------+CREATE TABLE year_range_example (
description VARCHAR(30),
example YEAR
);INSERT INTO year_range_example VALUES
('minimum', 1901),
('maximum', 2155),
('below minimum', 1900),
('above maximum', 2156);ERROR 1264 (22003): Out of range value for column 'example' at row 3Warning (sql 1264): Out of range value for column 'example' at row 3
Warning (sql 1264): Out of range value for column 'example' at row 4
The resulting data would look like this:SELECT * FROM year_range_example;
+---------------+---------+
| description | example |
+---------------+---------+
| minimum | 1901 |
| maximum | 2155 |
| below minimum | 0000 |
| above maximum | 0000 |
+---------------+---------+CREATE TABLE year_zero_example (
description VARCHAR(30),
example YEAR
);INSERT INTO year_zero_example VALUES
('4-digit numeric zero', 0000),
('3-digit numeric zero', 000),
('2-digit numeric zero', 00),
('1-digit numeric zero', 0),
('4-digit string zero', '0000'),
('3-digit string zero', '000'),
('2-digit string zero', '00'),
('1-digit string zero', '0');SELECT * FROM year_zero_example;
+----------------------+---------+
| description | example |
+----------------------+---------+
| 4-digit numeric zero | 0000 |
| 3-digit numeric zero | 0000 |
| 2-digit numeric zero | 0000 |
| 1-digit numeric zero | 0000 |
| 4-digit string zero | 0000 |
| 3-digit string zero | 2000 |
| 2-digit string zero | 2000 |
| 1-digit string zero | 2000 |
+----------------------+---------+Convert a Unix timestamp to a datetime. This function formats a Unix timestamp as a date string or number in the current time zone.
Converts the number of seconds from the epoch (1970-01-01 00:00:00 UTC) to aTIMESTAMP value, the opposite of what is doing. Returns NULL if the result would be outside of the valid range of TIMESTAMP values.
If format is given, the result is exactly equivalent to
Timestamps in MariaDB have a maximum value of 4294967295, equivalent to 2106-02-07 06:28:15. This is due to the underlying 32-bit limitation. Using the function on a timestamp beyond this will result in NULL being returned. Use as a storage type if you require dates beyond this.
The one-argument form of FROM_UNIXTIME() returns aDATETIME. This means that it can return values outside of valid TIMESTAMP range, in particular 1970-01-01 00:00:00. And it can return the same result for different values of unix_timestamp (around DST changes).
Timestamps in MariaDB have a maximum value of 4294967295, equivalent to 2106-02-07 06:28:15. This is due to the underlying 32-bit limitation. Using the function on a timestamp beyond this will result in NULL being returned. Use as a storage type if you require dates beyond this.
The following options can be used by FROM_UNIXTIME(), as well as and :
If your is set to SYSTEM (the default), FROM_UNIXTIME() will call the OS function to convert the data using the system time zone. At least on Linux, the corresponding function (localtime_r) uses a global mutex inside glibc that can cause contention under high concurrent load.
Set your time zone to a named time zone to avoid this issue. See for details on how to do this.
This page is licensed: GPLv2, originally from
FROM_UNIXTIME(unix_timestamp)
FROM_UNIXTIME(unix_timestamp,format)The one-argument form of FROM_UNIXTIME() returns aDATETIME. This means that it can return values outside of valid TIMESTAMP range, in particular 1970-01-01 00:00:00. And it can return the same result for different values of unix_timestamp (around DST changes).
The maximum value is 2147483647, equivalent to 2038-01-19 05:14:07.
%f
6 digits.
%H
Hour with 2 digits between 00-23.
%h
Hour with 2 digits between 01-12.
%I
Hour with 2 digits between 01-12.
%i
Minute with 2 digits.
%j
Day of the year (001-366)
%k
Hour with 1 digits between 0-23.
%l
Hour with 1 digits between 1-12.
%M
Full month name in current locale (Variable ).
%m
Month with 2 digits.
%p
AM/PM according to current locale (Variable ).
%r
Time in 12 hour format, followed by AM/PM. Short for '%I:%i:%S %p'.
%S
Seconds with 2 digits.
%s
Seconds with 2 digits.
%T
Time in 24 hour format. Short for '%H:%i:%S'.
%U
Week number (00-53), when first day of the week is Sunday.
%u
Week number (00-53), when first day of the week is Monday.
%V
Week number (01-53), when first day of the week is Sunday. Used with %X.
%v
Week number (01-53), when first day of the week is Monday. Used with %x.
%W
Full weekday name in current locale (Variable ).
%w
Day of the week. 0 = Sunday, 6 = Saturday.
%X
Year with 4 digits when first day of the week is Sunday. Used with %V.
%x
Year with 4 digits when first day of the week is Sunday. Used with %v.
%Y
Year with 4 digits.
%y
Year with 2 digits.
%#
For (), skip all numbers.
%.
For (), skip all punctation characters.
%@
For (), skip all alpha characters.
%%
A literal % character.
%a
Short weekday name in current locale (Variable lc_time_names).
%b
Short form month name in current locale. For locale en_US this is one of: Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov or Dec.
%c
Month with 1 or 2 digits.
%D
Day with English suffix 'th', 'nd', 'st' or 'rd''. (1st, 2nd, 3rd...).
%d
Day with 2 digits.
%e
Day with 1 or 2 digits.
formatSTR_TO_DATE()DATETIMEDATETIMEThe date, time, or datetime values contained in str should be given in the format indicated by format. If str contains an illegal date, time, or datetime value, STR_TO_DATE() returns NULL. An illegal value also produces a warning.
Under specific SQL_MODE settings an error may also be generated if the str isn't a valid date:
The options that can be used by STR_TO_DATE(), as well as its inverse DATE_FORMAT() and the FROM_UNIXTIME() function, are:
%a
Short weekday name in current locale (Variable ).
%b
Short form month name in current locale. For locale en_US this is one of: Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov or Dec.
%c
Month with 1 or 2 digits.
%D
Day with English suffix 'th', 'nd', 'st' or 'rd''. (1st, 2nd, 3rd...).
%d
Day with 2 digits.
%e
Day with 1 or 2 digits.
This page is licensed: GPLv2, originally from fill_help_tables.sql
Format a date according to a pattern. This function converts a date value into a specific string format using specifiers like %Y, %m, and %d.
Formats the date value according to the format string.
The language used for the names is controlled by the value of the system variable. See for more on the supported locales.
The options that can be used by DATE_FORMAT(), as well as its inverse () and the function, are:
To get a date in one of the standard formats, can be used.
Optionally, the locale can be explicitly specified as the third DATE_FORMAT() argument. Doing so makes the function independent from the session settings, and the three argument version of DATE_FORMAT() can be used in virtual indexed and persistent :
This page is licensed: GPLv2, originally from
DATE_FORMAT(FROM_UNIXTIME(unix_timestamp), format)SELECT FROM_UNIXTIME(1196440219);
+---------------------------+
| FROM_UNIXTIME(1196440219) |
+---------------------------+
| 2007-11-30 11:30:19 |
+---------------------------+
SELECT FROM_UNIXTIME(1196440219) + 0;
+-------------------------------+
| FROM_UNIXTIME(1196440219) + 0 |
+-------------------------------+
| 20071130113019.000000 |
+-------------------------------+
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y %D %M %h:%i:%s %x');
+---------------------------------------------------------+
| FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y %D %M %h:%i:%s %x') |
+---------------------------------------------------------+
| 2010 27th March 01:03:47 2010 |
+---------------------------------------------------------+STR_TO_DATE(str,format)SELECT STR_TO_DATE('Wednesday, June 2, 2014', '%W, %M %e, %Y');
+---------------------------------------------------------+
| STR_TO_DATE('Wednesday, June 2, 2014', '%W, %M %e, %Y') |
+---------------------------------------------------------+
| 2014-06-02 |
+---------------------------------------------------------+
SELECT STR_TO_DATE('Wednesday23423, June 2, 2014', '%W, %M %e, %Y');
+--------------------------------------------------------------+
| STR_TO_DATE('Wednesday23423, June 2, 2014', '%W, %M %e, %Y') |
+--------------------------------------------------------------+
| NULL |
+--------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
SHOW WARNINGS;
+---------+------+-----------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------------------------------------+
| Warning | 1411 | Incorrect datetime value: 'Wednesday23423, June 2, 2014' for function str_to_date |
+---------+------+-----------------------------------------------------------------------------------+
SELECT STR_TO_DATE('Wednesday23423, June 2, 2014', '%W%#, %M %e, %Y');
+----------------------------------------------------------------+
| STR_TO_DATE('Wednesday23423, June 2, 2014', '%W%#, %M %e, %Y') |
+----------------------------------------------------------------+
| 2014-06-02 |
+----------------------------------------------------------------+DATE_FORMAT(date, format[, locale])%f
Microseconds 6 digits.
%H
Hour with 2 digits between 00-23.
%h
Hour with 2 digits between 01-12.
%I
Hour with 2 digits between 01-12.
%i
Minute with 2 digits.
%j
Day of the year (001-366)
%k
Hour with 1 digits between 0-23.
%l
Hour with 1 digits between 1-12.
%M
Full month name in current locale (Variable lc_time_names).
%m
Month with 2 digits.
%p
AM/PM according to current locale (Variable lc_time_names).
%r
Time in 12 hour format, followed by AM/PM. Short for '%I:%i:%S %p'.
%S
Seconds with 2 digits.
%s
Seconds with 2 digits.
%T
Time in 24 hour format. Short for '%H:%i:%S'.
%U
Week number (00-53), when first day of the week is Sunday.
%u
Week number (00-53), when first day of the week is Monday.
%V
Week number (01-53), when first day of the week is Sunday. Used with %X.
%v
Week number (01-53), when first day of the week is Monday. Used with %x.
%W
Full weekday name in current locale (Variable lc_time_names).
%w
Day of the week. 0 = Sunday, 6 = Saturday.
%X
Year with 4 digits when first day of the week is Sunday. Used with %V.
%x
Year with 4 digits when first day of the week is Monday. Used with %v.
%Y
Year with 4 digits.
%y
Year with 2 digits.
%#
For str_to_date(), skip all numbers.
%.
For str_to_date(), skip all punctation characters.
%@
For str_to_date(), skip all alpha characters.
%%
A literal % character.
%f
6 digits.
%H
Hour with 2 digits between 00-23.
%h
Hour with 2 digits between 01-12.
%I
Hour with 2 digits between 01-12.
%i
Minute with 2 digits.
%j
Day of the year (001-366)
%k
Hour with 1 digits between 0-23.
%l
Hour with 1 digits between 1-12.
%M
Full month name in current locale (Variable ).
%m
Month with 2 digits.
%p
AM/PM according to current locale (Variable ).
%r
Time in 12 hour format, followed by AM/PM. Short for '%I:%i:%S %p'.
%S
Seconds with 2 digits.
%s
Seconds with 2 digits.
%T
Time in 24 hour format. Short for '%H:%i:%S'.
%U
Week number (00-53), when first day of the week is Sunday.
%u
Week number (00-53), when first day of the week is Monday.
%V
Week number (01-53), when first day of the week is Sunday. Used with %X.
%v
Week number (01-53), when first day of the week is Monday. Used with %x.
%W
Full weekday name in current locale (Variable ).
%w
Day of the week. 0 = Sunday, 6 = Saturday.
%X
Year with 4 digits when first day of the week is Sunday. Used with %V.
%x
Year with 4 digits when first day of the week is Monday. Used with %v.
%Y
Year with 4 digits.
%y
Year with 2 digits.
%Z
Timezone abbreviation. From .
%z
Numeric timezone +hhmm or -hhmm presenting the hour and minute offset from UTC. From .
%#
For (), skip all numbers.
%.
For (), skip all punctation characters.
%@
For (), skip all alpha characters.
%%
A literal % character.
%a
Short weekday name in current locale (Variable lc_time_names).
%b
Short form month name in current locale. For locale en_US this is one of: Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov or Dec.
%c
Month with 1 or 2 digits.
%D
Day with English suffix 'th', 'nd', 'st' or 'rd''. (1st, 2nd, 3rd...).
%d
Day with 2 digits.
%e
Day with 1 or 2 digits.
SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
+------------------------------------------------+
| DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y') |
+------------------------------------------------+
| Sunday October 2009 |
+------------------------------------------------+
SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
+------------------------------------------------+
| DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s') |
+------------------------------------------------+
| 22:23:00 |
+------------------------------------------------+
SELECT DATE_FORMAT('1900-10-04 22:23:00', '%D %y %a %d %m %b %j');
+------------------------------------------------------------+
| DATE_FORMAT('1900-10-04 22:23:00', '%D %y %a %d %m %b %j') |
+------------------------------------------------------------+
| 4th 00 Thu 04 10 Oct 277 |
+------------------------------------------------------------+
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H %k %I %r %T %S %w');
+------------------------------------------------------------+
| DATE_FORMAT('1997-10-04 22:23:00', '%H %k %I %r %T %S %w') |
+------------------------------------------------------------+
| 22 22 10 10:23:00 PM 22:23:00 00 6 |
+------------------------------------------------------------+
SELECT DATE_FORMAT('1999-01-01', '%X %V');
+------------------------------------+
| DATE_FORMAT('1999-01-01', '%X %V') |
+------------------------------------+
| 1998 52 |
+------------------------------------+
SELECT DATE_FORMAT('2006-06-00', '%d');
+---------------------------------+
| DATE_FORMAT('2006-06-00', '%d') |
+---------------------------------+
| 00 |
+---------------------------------+SELECT DATE_FORMAT('2006-01-01', '%W', 'el_GR');
+------------------------------------------+
| DATE_FORMAT('2006-01-01', '%W', 'el_GR') |
+------------------------------------------+
| Κυριακή |
+------------------------------------------+SELECT DATE_FORMAT(NOW(), '%W %d %M %Y %H:%i:%s %Z %z');
+--------------------------------------------------+
| DATE_FORMAT(NOW(), '%W %d %M %Y %H:%i:%s %Z %z') |
+--------------------------------------------------+
| Wednesday 20 September 2023 15:00:23 SAST +0200 |
+--------------------------------------------------+Comprehensive reference of all SQL functions and operators. This index lists built-in functions for string manipulation, math, date/time, and more.
Addition operator
Division operator
Multiplication operator
This page is licensed: CC BY-SA / Gnu FDL
Modulo operator. Returns the remainder of N divided by M
Subtraction operator
Not equals
Less than
Less than or equal
NULL-safe equal
Equal
Greater than
Greater than or equal
Bitwise AND
Shift left
Shift right
Bitwise XOR
Logical NOT
Logical AND
Logical XOR
|
Assignment operator
Assignment and comparison operator
~
Returns an absolute value
Returns an arc cosine
Add months to a date
Add days or another interval to a date
Adds a time to a time or datetime
Decryption data encrypted with AES_ENCRYPT
Encrypts a string with the AES algorithm
Synonym for ST_AREA
Synonym for ST_AsBinary
Numeric ASCII value of leftmost character
Returns the arc sine
Synonym for ST_AsText
Synonym for ST_AsBinary
Synonym for ST_AsText
Returns the arc tangent
Returns the arc tangent of two variables
Returns the average value
Executes an expression repeatedly
True if expression between two values
Returns binary value
Casts to a binary string
Returns a string representation of the corresponding GTID position
Bitwise AND
Returns the number of set bits
Returns the length of a string in bits
Bitwise OR
Bitwise XOR
Synonym for ST_BOUNDARY
Synonym for ST_BUFFER
Returns the result where value=compare_value or for the first condition that is true
Casts a value of one type to another type
Synonym for CEILING()
Returns the smallest integer not less than X
Synonym for ST_CENTROID
Returns string based on the integer values for the individual characters
Synonym for CHAR_LENGTH()
Length of the string in characters
Returns the character set
Returns a string consisting of the character given by the code values of the integer
Returns the first non-NULL parameter
Returns the collation coercibility value
Collation of the string argument
Adds or updates dynamic columns
Checks if a dynamic column blob is valid
Returns a dynamic columns blob
Deletes a dynamic column
Checks is a column exists
Gets a dynamic column value by name
Returns a JSON representation of dynamic column blob data
Returns comma-separated list
Returns a binary, compressed string
Returns concatenated string
Concatenate with separator
Connection thread ID
Whether one geometry contains another
Convert a value from one type to another type
Converts numbers between different number bases
Converts a datetime from on time zone to another
Synonym for ST_CONVEXHULL
Returns the cosine
Returns the cotangent
Returns count of non-null values
Returns count of number of different non-NULL values
Computes a cyclic redundancy check value
Computes a cyclic redundancy check value
Whether two geometries spatially cross
Window function that returns the cumulative distribution of a given row
Returns the current date
Synonym for CURDATE()
Current role name
Synonym for CURTIME()
Synonym for NOW()
Username/host that authenicated the current client
Returns the current time
Current default database
Extracts the date portion of a datetime
Difference in days between two date/time values
Date arithmetic - addition
Formats the date value according to the format string
Date arithmetic - subtraction
Synonym for DAYOFMONTH()
Return the name of the weekday
Returns the day of the month
Returns the day of the week index
Returns the day of the year
Decrypts a string encoded with ENCODE()
Returns comma separated numerics corresponding to a probability distribution represented by a histogram
Returns column default
Converts from radians to degrees
Rank of a given row with identical values receiving the same result, no skipping
Decrypts a string encrypted with DES_ENCRYPT()
Encrypts a string using the Triple-DES algorithm
Synonym for ST_DIMENSION
Whether the two elements do not intersect
Integer division
Returns the N'th element from a set of strings
Encrypts a string
Encrypts a string with Unix crypt()
Synonym for ST_ENDPOINT
Synonym for ST_ENVELOPE
Indicates whether two geometries are spatially equal
e raised to the power of the argument
Returns an on string for every bit set, an off string for every bit not set
Synonym for ST_ExteriorRing
Extracts a portion of the date
Returns the text of the first text node matched by the XPath expression
Returns the index position of a string in a list
Returns the position of a string in a set of strings
Largest integer value not greater than the argument
Formats a number
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
Number of (potentially) returned rows
Given a base-64 encoded string, returns the decoded result as a binary string
Returns a date given a day
Returns a datetime from a Unix timestamp
Synonym for ST_GeomCollFromText
Synonym for ST_GeomCollFromWKB
Constructs a WKB GeometryCollection
Synonym for ST_GeomCollFromText
Synonym for ST_GeomCollFromWKB
Synonym for ST_GeomFromText
Synonym for ST_GeomFromWKB
Synonym for ST_GeomFromText
Synonym for ST_GeomFromWKB
Synonym for ST_GeometryN
Synonym for ST_GeometryType
Returns a format string
Obtain LOCK
Length of a LineString value
Returns the largest argument
Returns string with concatenated values from a group
Returns hexadecimal value
Returns the hour
If expr1 is TRUE, returns expr2; otherwise returns expr3
Check whether an expression is NULL
True if expression equals any of the values in the list
Index of the argument that is less than the first argument
Given an IPv6 or IPv4 network address, returns a VARBINARY numeric value
Given an IPv6 or IPv4 network address, returns the address as a nonbinary string
Returns numeric value of IPv4 address
Returns dotted-quad representation of IPv4 address
Replaces a part of a string with another string
Returns the position of a string withing a string
Synonym for ST_InteriorRingN
Indicates whether two geometries spatially intersect
Tests whether a boolean is TRUE, FALSE, or UNKNOWN
Synonym for ST_IsClosed
Synonym for ST_IsEmpty
Checks whether lock is free to use
Whether or not an expression is a valid IPv4 address
Whether or not an IPv6 address is IPv4-compatible
Whether an IPv6 address is a valid IPv4-mapped address
Whether or not an expression is a valid IPv6 address
Tests whether a boolean value is not TRUE, FALSE, or UNKNOWN
Tests whether a value is not NULL
Tests whether a value is NULL
Checks if an expression is NULL
Synonym for ST_IsRing
Synonym for ST_IsSimple
Check if lock is in use
Returns a JSON array containing the listed values
Returns a JSON array containing an element for each value in a given set of JSON or SQL values.
Appends values to the end of the given arrays within a JSON document
Inserts a value into a JSON document
Removes all unnecessary spaces so the json document is as short as possible
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
Maximum depth of a JSON document
Represents JSON in the most understandable way emphasizing nested structures
Check for equality between JSON objects.
Determines whether a specified JSON value exists in the given data
Extracts data from a JSON document.
Inserts data into a JSON document
Extracts key/value pairs from a JSON object
Returns keys from top-level value of a JSON object or top-level keys from the path
Returns the length of a JSON document, or the length of a value within the document
Adds spaces to a JSON document to make it look more readable
Merges the given JSON documents
RFC 7396-compliant merge of the given JSON documents
Synonym for JSON_MERGE_PATCH.
Recursively sorts keys and removes spaces, allowing comparison of json documents for equality
Returns a JSON object containing the given key/value pairs
Returns a JSON object containing key-value pairs
Compares two json documents for overlaps
Alias for json_detailed
, , , , MariaDB 10.6.12
Given a JSON document, returns an object or array specified by the path
Quotes a string as a JSON value
Removes data from a JSON document
Replaces existing values in a JSON document
Validates a JSON schema
Returns the path to the given string within a JSON document
Updates or inserts data into a JSON document
Returns a representation of a JSON document as a relational table
Returns the type of a JSON value
Unquotes a JSON value, returning a string
Whether a value is a valid JSON document or not
Given a JSON document, returns the specified scalar
Key derivation function
Returns the last day of the month
Last inserted autoinc value
Returns the last value in a list
Get last value generated from a sequence
Synonym for [LOWER()
Returns the smallest argument
Returns the leftmost characters from a string
Length of the string in bytes
Whether expression matches a pattern
Synonym for ST_LineFromText
Synonym for ST_LineFromWKB
Constructs a WKB LineString value from a number of WKB Point arguments
Synonym for ST_LineFromText
Synonym for ST_LineFromWKB
Returns natural logarithm
Returns file contents as a string
Synonym for NOW()
Synonym for NOW()
Returns the position of a substring in a string
Returns the natural logarithm
Returns the base-10 logarithm
Returns the base-2 logarithm
Returns a string with all characters changed to lowercase
Returns the string left-padded with another string to a given length
Returns the string with leading space characters removed
Make a set of strings that matches a bitmask
Returns a date given a year and day
Returns a time
Wait until slave reaches the GTID position
Blocks until the slave has applied all specified updates
Perform a fulltext search on a fulltext index
Returns the maximum value
Whether one Minimum Bounding Rectangle contains another.
Whether one Minimum Bounding Rectangle is covered by another.
Whether the Minimum Bounding Rectangles of two geometries are disjoint
Whether the Minimum Bounding Rectangles of two geometries are the same.
Synonym for MBREqual.
Indicates whether the Minimum Bounding Rectangles of the two geometries intersect
Whether the Minimum Bounding Rectangles of two geometries overlap
Whether the Minimum Bounding Rectangles of two geometries touch.
Indicates whether one Minimum Bounding Rectangle is within another
MD5 checksum
Window function that returns the median value of a range of values
Returns microseconds from a date or datetime
Synonym for SUBSTRING(str,pos,len)
Returns the minimum value
Returns a minute from 0 to 59
Constructs MULTILINESTRING using its WKT representation and SRID
Constructs a MULTILINESTRING
Modulo operation. Remainder of N divided by M
Returns a month from 1 to 12
Returns the full name of the month
Constructs a MULTIPOINT value using its WKT and SRID
Constructs a MULTIPOINT value using its WKB representation and SRID
Constructs a MULTIPOLYGON value
Constructs a MULTIPOLYGON value using its WKB representation and SRID
Constructs a MultiLineString value
Synonym for MLineFromText
A synonym for MLineFromWKB
Constructs a WKB MultiPoint value
Synonym for MPointFromText
Synonym for MPointFromWKB
Constructs a WKB MultiPolygon
Synonym for MPolyFromText
Synonym for MPolyFromWKB
Returns the given value
Sorting that is more similar to natural human sorting
Same as NOT(expr LIKE pat [ESCAPE 'escape_char'])
Same as NOT (expr REGEXP pat)
Returns NULL if expr1 = expr2
Generate next value for sequence
Same as NOT (expr BETWEEN min AND max)
Same as NOT (expr IN (value,...))
Returns the current date and time
Returns an integer indicating which group a given row falls into
Synonym for ST_NumGeometries
Synonym for NumInteriorRings
Synonym for ST_NumPoints
Returns octal value
Synonym for LENGTH()
Pre MySQL 4.1 password implementation
Return ASCII or character code
Indicates whether two elements spatially overlap
Calculates a password string
Window function that returns the relative percent rank of a given row
Returns a value which corresponds to the given fraction in the sort order.
Returns the first value in the set whose ordered position is the same or more than the specified fraction.
Add months to a period
Number of months between two periods
Returns the value of π (pi)
Constructs a WKB Point
Synonym for ST_PointFromText
Synonym for PointFromWKB
Synonym for PointN
Synonym for ST_PointOnSurface
Constructs a WKB Polygon value from a number of WKB LineString arguments
Synonym for ST_PolyFromText
Synonym for ST_PolyFromWKB
Synonym for ST_PolyFromText
Synonym for ST_PolyFromWKB
Returns the position of a substring in a string
Returns X raised to the power of Y
Synonym for POW()
Returns year quarter from 1 to 4
Returns quoted, properly escaped string
Converts from degrees to radians
Random floating-point value
Rank of a given row with identical values receiving the same result
Performs pattern matching
Position of the first appearance of a regex
Replaces all occurrences of a pattern
Returns the matching part of a string
Releases lock obtained with GET_LOCK()
Returns a string repeated a number of times
Replace occurrences of a string
Reverses the order of a string
Returns the rightmost N characters from a string
Synonym for REGEXP()
Returns the string right-padded with another string to a given length
Rounds a number
Number of rows affected by previous statement
Row number of a given row with identical values receiving a different result
Returns the string with trailing space characters removed
Synonym for DATABASE()
Returns the second of a time
Converts a second to a time
Set the next value to be returned by a sequence
Synonym for USER()
Synonym for SHA1()
Calculates an SHA-1 checksum
Calculates an SHA-2 checksum
Returns 1, 0 or -1
Returns the sine
Pauses for the given number of seconds
Returns a string based on how the string sounds
SOUNDEX(expr1) = SOUNDEX(expr2)
Returns a string of space characters
Background SQL execution
Copy table data
Execute SQL on the remote server
Refreshing Spider monitoring server information
Square root
Synonym for ST_SRID
Area of a Polygon
Converts a value to its WKB representation
Returns a GeoJSON element from a given geometry.
Converts a value to its WKT-Definition
Synonym for ST_AsBinary
Synonym for ST_ASTEXT()
Returns a geometry that is the closure of a combinatorial boundary
A new geometry with a buffer added to the original geometry
The mathematical centroid (geometric center) for a MultiPolygon
Returns the aggregation of the distinct geometry arguments
Whether one geometry is contained by another
The minimum convex geometry enclosing all geometries within the set
Whether two geometries spatially cross
Point set difference
Inherent dimension of a geometry value
Whether one geometry is spatially disjoint from another
The distance between two geometries
The spherical distance between two geometries
Returns the endpoint of a LineString
Returns the Minimum Bounding Rectangle for a geometry value
Whether two geometries are spatoially equal
Returns the exterior ring of a Polygon as a LineString
Returns a geohash.
Constructs a GEOMETRYCOLLECTION value
Constructs a GEOMETRYCOLLECTION value from a WKB
Synonym for ST_GeomCollFromText
Synonym for ST_GeomCollFromWKB
Synonym for ST_GeomFromText
Synonym for ST_GeomFromWKB
Returns the N-th geometry in a GeometryCollection
Returns name of the geometry type of which a given geometry instance is a member
Returns a geometry object from a GeoJSON input
Constructs a geometry value using its WKT and SRID
Constructs a geometry value using its WKB representation and SRID
Returns the N-th interior ring for a Polygon
The intersection, or shared portion, of two geometries
Whether two geometries spatially intersect
Returns true if a given LINESTRING's start and end points are the same
Indicated validity of geometry value
Returns true if a given LINESTRING is both ST_IsClosed and ST_IsSimple
Returns true if the given Geometry has no anomalous geometric points
Returns 1 if the argument is geometrically valid, 0 if not.
Returns a latitude from the given geohash.
Returns a longitude from the given geohash.
Length of a LineString value
Creates a linestring value
Constructs a LINESTRING using its WKB and SRID
Synonym for ST_LineFromText
Synonym for ST_LineFromWKB
Synonym for ST_GeomFromText
Synonym for ST_GeomFromWKB
Synonym for ST_GeomFromText
Synonym for ST_GeomFromWKB
Synonym for ST_GeomFromText
Synonym for ST_GeomFromWKB
Synonym for ST_GeomFromText
Synonym for ST_GeomFromWKB
Synonym for ST_GeomFromText
Synonym for ST_GeomFromText
Synonym for ST_GeomFromWKB
Synonym for ST_GeomFromWKB
Number of geometries in a GeometryCollection
Number of interior rings in a Polygon
Returns the number of Point objects in a LineString
Whether two geometries overlap
Returns a point from the given geohash.
Constructs a POINT value
Constructs POINT using its WKB and SRID
Returns the N-th Point in the LineString
Returns a POINT guaranteed to intersect a surface
Constructs a POLYGON value
Constructs POLYGON value using its WKB representation and SRID
Synonym for ST_PolyFromText
Synonym for ST_PolyFromWKB
Returns true if two geometries are related
Applies the Ramer–Douglas–Peucker algorithm to provide generalized simplifications.
Returns a Spatial Reference System ID
Returns the start point of a LineString
Portions of two geometries that don't intersect
Whether one geometry g1 spatially touches another
Union of two geometries
Whether a geometry is compliant with the WKB format and SRID syntax, and is geometrically valid.
Whether one geometry is within another
X-coordinate value for a point
Y-coordinate for a point
Synonym for ST_StartPoint
Population standard deviation
Population standard deviation
Returns the population standard deviation
Standard deviation
Converts a string to date
Compares two strings in sort order
Subtract a date unit or number of days
Returns a substring from string starting at a given position
Returns a substring from string starting at a given position
Returns the substring from string before count occurrences of a delimiter
Subtracts a time from a date/time
Sum total
Given a file path, returns the schema (database) name
Given a file path, returns the table name
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
Returns a reduced length string
Returns a human-readable time value and unit indicator
Adds a value to a given list
Drops a value from a given list
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 the thread_id associated with the given connection_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
Returns a configuration option value from the sys_config table
Returns the MariaDB Server major release version
Returns the MariaDB Server minor release version
Returns the MariaDB Server patch release version
Generates a globally unique identifier
Returns the current date and time
Synonym for USER()
Returns the tangent
Extracts the time
Returns the difference between two date/times
Return the datetime, or add a time to a date/time
Add interval to a date or datetime
Difference between two datetimes
Formats the time value according to the format string
Returns the time argument, converted to seconds
Converts a string to its base-64 encoded form
Converts a date/time type to a char
Number of days since year 0
Converts and expression to NUMERIC
Number of seconds since year 0
Whether two geometries spatially touch
Returns a string with all given prefixes or suffixes removed
Returns a DATETIME truncated according to a given format.
Truncates X to D decimal places
Synonym for UPPER]]()
Interprets pairs of hex digits as a number and converts to the character represented by the number
Uncompresses string compressed with COMPRESS()
Returns length of a string before being compressed with COMPRESS()
Returns a Unix timestamp
Replace XML
Changes string to uppercase
Current user/host
Returns the current UTC date
Returns the current UTC time
Returns the current UTC date and time
Returns a Universal Unique Identifier v1
Returns a Universal Unique Identifier v4
Returns a Universal Unique Identifier v7
Return short universal identifier
Refer to columns in INSERT ... ON DUPLICATE KEY UPDATE
Population standard variance
Returns the sample variance
Population standard variance
Calculates either a Euclidean or Cosine distance between two vectors.
Calculates a Cosine distance between two vectors.
Calculates a Euclidean (L2) distance between two points.
Converts a text representation of the vector to a vector.
Vector
Converts a binary vector into a json array of numbers (floats).
Vector
MariaDB server version
Returns the week number
Returns the weekday index
Returns the calendar week of the date as a number in the range from 1 to 53
Weight of the input string
Indicate whether a geographic element is spacially within another
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
Synonym for ST_X
Synonym for ST_Y
Returns the year for the given date
Returns year and week for a date