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

SQL Functions

Functions and procedures in MariaDB

Functions & Operators

Name
Description
Added

+

Addition operator

/

Division operator

*

Multiplication operator

%

Modulo operator. Returns the remainder of N divided by M

-

Subtraction operator

!=

Not equals

<

Less than

<=

Less than or equal

<=>

NULL-safe equal

=

Equal

>

Greater than

>=

Greater than or equal

&

Bitwise AND

<<

Shift left

>>

Shift right

^

Bitwise XOR

!

Logical NOT

&&

Logical AND

XOR

Logical XOR

|

Bitwise OR

:=

Assignment operator

=

Assignment and comparison operator

~

Bitwise NOT

ABS

Returns an absolute value

ACOS

Returns an arc cosine

ADD_MONTHS

Add months to a date

ADDDATE

Add days or another interval to a date

ADDTIME

Adds a time to a time or datetime

AES_DECRYPT

Decryption data encrypted with AES_ENCRYPT

AES_ENCRYPT

Encrypts a string with the AES algorithm

AREA

Synonym for ST_AREA

AsBinary

Synonym for ST_AsBinary

ASCII

Numeric ASCII value of leftmost character

ASIN

Returns the arc sine

AsText

Synonym for ST_AsText

AsWKB

Synonym for ST_AsBinary

AsWKT

Synonym for ST_AsText

ATAN

Returns the arc tangent

ATAN2

Returns the arc tangent of two variables

AVG

Returns the average value

BENCHMARK

Executes an expression repeatedly

BETWEEN AND

True if expression between two values

BIN

Returns binary value

BINARY OPERATOR

Casts to a binary string

BINLOG_GTID_POS

Returns a string representation of the corresponding GTID position

BIT_AND

Bitwise AND

BIT_COUNT

Returns the number of set bits

BIT_LENGTH

Returns the length of a string in bits

BIT_OR

Bitwise OR

BIT_XOR

Bitwise XOR

BOUNDARY

Synonym for ST_BOUNDARY

BUFFER

Synonym for ST_BUFFER

CASE

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

CAST

Casts a value of one type to another type

CEIL

Synonym for CEILING()

CEILING

Returns the smallest integer not less than X

CENTROID

Synonym for ST_CENTROID

CHAR Function

Returns string based on the integer values for the individual characters

CHARACTER_LENGTH

Synonym for CHAR_LENGTH()

CHAR_LENGTH

Length of the string in characters

CHARSET

Returns the character set

CHR

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

COALESCE

Returns the first non-NULL parameter

COERCIBILITY

Returns the collation coercibility value

COLLATION

Collation of the string argument

COLUMN_ADD

Adds or updates dynamic columns

COLUMN_CHECK

Checks if a dynamic column blob is valid

COLUMN_CREATE

Returns a dynamic columns blob

COLUMN_DELETE

Deletes a dynamic column

COLUMN_EXISTS

Checks is a column exists

COLUMN_GET

Gets a dynamic column value by name

COLUMN_JSON

Returns a JSON representation of dynamic column blob data

COLUMN_LIST

Returns comma-separated list

COMPRESS

Returns a binary, compressed string

CONCAT

Returns concatenated string

CONCAT_WS

Concatenate with separator

CONNECTION_ID

Connection thread ID

CONTAINS

Whether one geometry contains another

CONVERT

Convert a value from one type to another type

CONV

Converts numbers between different number bases

CONVERT_TZ

Converts a datetime from on time zone to another

CONVEXHULL

Synonym for ST_CONVEXHULL

COS

Returns the cosine

COT

Returns the cotangent

COUNT

Returns count of non-null values

COUNT DISTINCT

Returns count of number of different non-NULL values

CRC32

Computes a cyclic redundancy check value

CRC32C

Computes a cyclic redundancy check value

MariaDB 10.8

CROSSES

Whether two geometries spatially cross

CUME_DIST

Window function that returns the cumulative distribution of a given row

CURDATE

Returns the current date

CURRENT_DATE

Synonym for CURDATE()

CURRENT_ROLE

Current role name

CURRENT_TIME

Synonym for CURTIME()

CURRENT_TIMESTAMP

Synonym for NOW()

CURRENT_USER

Username/host that authenicated the current client

CURTIME

Returns the current time

DATABASE

Current default database

DATE FUNCTION

Extracts the date portion of a datetime

DATEDIFF

Difference in days between two date/time values

DATE_ADD

Date arithmetic - addition

DATE_FORMAT

Formats the date value according to the format string

DATE_SUB

Date arithmetic - subtraction

DAY

Synonym for DAYOFMONTH()

DAYNAME

Return the name of the weekday

DAYOFMONTH

Returns the day of the month

DAYOFWEEK

Returns the day of the week index

DAYOFYEAR

Returns the day of the year

DECODE

Decrypts a string encoded with ENCODE()

DECODE_HISTOGRAM

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

DEFAULT

Returns column default

DEGREES

Converts from radians to degrees

DENSE_RANK

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

DES_DECRYPT

Decrypts a string encrypted with DES_ENCRYPT()

DES_ENCRYPT

Encrypts a string using the Triple-DES algorithm

DIMENSION

Synonym for ST_DIMENSION

DISJOINT

Whether the two elements do not intersect

DIV

Integer division

ELT

Returns the N'th element from a set of strings

ENCODE

Encrypts a string

ENCRYPT

Encrypts a string with Unix crypt()

ENDPOINT

Synonym for ST_ENDPOINT

ENVELOPE

Synonym for ST_ENVELOPE

EQUALS

Indicates whether two geometries are spatially equal

EXP

e raised to the power of the argument

EXPORT_SET

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

ExteriorRing

Synonym for ST_ExteriorRing

EXTRACT

Extracts a portion of the date

EXTRACTVALUE

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

FIELD

Returns the index position of a string in a list

FIND_IN_SET

Returns the position of a string in a set of strings

FLOOR

Largest integer value not greater than the argument

FORMAT

Formats a number

FORMAT_BYTES

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

MariaDB 11.8

FORMAT_PICO_TIME

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

MariaDB 11.0.2

FOUND_ROWS

Number of (potentially) returned rows

FROM_BASE64

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

FROM_DAYS

Returns a date given a day

FROM_UNIXTIME

Returns a datetime from a Unix timestamp

GeomCollFromText

Synonym for ST_GeomCollFromText

GeomCollFromWKB

Synonym for ST_GeomCollFromWKB

GEOMETRYCOLLECTION

Constructs a WKB GeometryCollection

GeometryCollectionFromText

Synonym for ST_GeomCollFromText

GeometryCollectionFromWKB

Synonym for ST_GeomCollFromWKB

GeometryFromText

Synonym for ST_GeomFromText

GeometryFromWKB

Synonym for ST_GeomFromWKB

GeomFromText

Synonym for ST_GeomFromText

GeomFromWKB

Synonym for ST_GeomFromWKB

GeometryN

Synonym for ST_GeometryN

GeometryType

Synonym for ST_GeometryType

GET_FORMAT

Returns a format string

GET_LOCK

Obtain LOCK

GLENGTH

Length of a LineString value

GREATEST

Returns the largest argument

GROUP_CONCAT

Returns string with concatenated values from a group

HEX

Returns hexadecimal value

HOUR

Returns the hour

IF

If expr1 is TRUE, returns expr2; otherwise returns expr3

IFNULL

Check whether an expression is NULL

IN

True if expression equals any of the values in the list

INTERVAL

Index of the argument that is less than the first argument

INET6_ATON

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

INET6_NTOA

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

INET_ATON

Returns numeric value of IPv4 address

INET_NTOA

Returns dotted-quad representation of IPv4 address

INSERT Function

Replaces a part of a string with another string

INSTR

Returns the position of a string withing a string

InteriorRingN

Synonym for ST_InteriorRingN

INTERSECTS

Indicates whether two geometries spatially intersect

IS

Tests whether a boolean is TRUE, FALSE, or UNKNOWN

IsClosed

Synonym for ST_IsClosed

IsEmpty

Synonym for ST_IsEmpty

IS_FREE_LOCK

Checks whether lock is free to use

IS_IPV4

Whether or not an expression is a valid IPv4 address

IS_IPV4_COMPAT

Whether or not an IPv6 address is IPv4-compatible

IS_IPV4_MAPPED

Whether an IPv6 address is a valid IPv4-mapped address

IS_IPV6

Whether or not an expression is a valid IPv6 address

IS NOT

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

IS NOT NULL

Tests whether a value is not NULL

IS NULL

Tests whether a value is NULL

ISNULL

Checks if an expression is NULL

IsRing

Synonym for ST_IsRing

IsSimple

Synonym for ST_IsSimple

IS_USED_LOCK

Check if lock is in use

JSON_ARRAY

Returns a JSON array containing the listed values

JSON_ARRAYAGG

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

JSON_ARRAY_INTERSECT

MariaDB 11.2.0

JSON_ARRAY_APPEND

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

JSON_ARRAY_INSERT

Inserts a value into a JSON document

JSON_COMPACT

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

JSON_CONTAINS

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

JSON_CONTAINS_PATH

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

JSON_DEPTH

Maximum depth of a JSON document

JSON_DETAILED

Represents JSON in the most understandable way emphasizing nested structures

JSON_EQUALS

Check for equality between JSON objects.

MariaDB 10.7

JSON_EXISTS

Determines whether a specified JSON value exists in the given data

JSON_EXTRACT

Extracts data from a JSON document.

JSON_INSERT

Inserts data into a JSON document

JSON_KEY_VALUE

Extracts key/value pairs from a JSON object

MariaDB 11.2

JSON_KEYS

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

JSON_LENGTH

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

JSON_LOOSE

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

JSON_MERGE

Merges the given JSON documents

JSON_MERGE_PATCH

RFC 7396-compliant merge of the given JSON documents

JSON_MERGE_PRESERVE

Synonym for JSON_MERGE_PATCH.

JSON_NORMALIZE

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

JSON_OBJECT

Returns a JSON object containing the given key/value pairs

JSON_OBJECT_FILTER_KEYS

MariaDB 11.2.0

JSON_OBJECT_TO_ARRAY

MariaDB 11.2.0

JSON_OBJECTAGG

Returns a JSON object containing key-value pairs

JSON_OVERLAPS

Compares two json documents for overlaps

MariaDB 10.9

JSON_PRETTY

Alias for json_detailed

MariaDB 10.10.3, MariaDB 10.9.5, MariaDB 10.8.7, MariaDB 10.7.8, MariaDB 10.6.12

JSON_QUERY

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

JSON_QUOTE

Quotes a string as a JSON value

JSON_REMOVE

Removes data from a JSON document

JSON_REPLACE

Replaces existing values in a JSON document

JSON_SCHEMA_VALID

Validates a JSON schema

MariaDB 11.1.0

JSON_SEARCH

Returns the path to the given string within a JSON document

JSON_SET

Updates or inserts data into a JSON document

JSON_TABLE

Returns a representation of a JSON document as a relational table

MariaDB 10.6

JSON_TYPE

Returns the type of a JSON value

JSON_UNQUOTE

Unquotes a JSON value, returning a string

JSON_VALID

Whether a value is a valid JSON document or not

JSON_VALUE

Given a JSON document, returns the specified scalar

KDF

Key derivation function

MariaDB 11.3.0

LAST_DAY

Returns the last day of the month

LAST_INSERT_ID

Last inserted autoinc value

LAST_VALUE

Returns the last value in a list

LASTVAL

Get last value generated from a sequence

LCASE

Synonym for [LOWER()

LEAST

Returns the smallest argument

LEFT

Returns the leftmost characters from a string

LENGTH

Length of the string in bytes

LIKE

Whether expression matches a pattern

LineFromText

Synonym for ST_LineFromText

LineFromWKB

Synonym for ST_LineFromWKB

LINESTRING

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

LineStringFromText

Synonym for ST_LineFromText

LineStringFromWKB

Synonym for ST_LineFromWKB

LN

Returns natural logarithm

LOAD_FILE

Returns file contents as a string

LOCALTIME

Synonym for NOW()

LOCALTIMESTAMP

Synonym for NOW()

LOCATE

Returns the position of a substring in a string

LOG

Returns the natural logarithm

LOG10

Returns the base-10 logarithm

LOG2

Returns the base-2 logarithm

LOWER

Returns a string with all characters changed to lowercase

LPAD

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

LTRIM

Returns the string with leading space characters removed

MAKE_SET

Make a set of strings that matches a bitmask

MAKEDATE

Returns a date given a year and day

MAKETIME

Returns a time

MASTER_GTID_WAIT

Wait until slave reaches the GTID position

MASTER_POS_WAIT

Blocks until the slave has applied all specified updates

MATCH AGAINST

Perform a fulltext search on a fulltext index

MAX

Returns the maximum value

MBRContains

Whether one Minimum Bounding Rectangle contains another.

MBRCoveredBy

Whether one Minimum Bounding Rectangle is covered by another.

MariaDB 11.8

MBRDisjoint

Whether the Minimum Bounding Rectangles of two geometries are disjoint

MBREqual

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

MBREquals

Synonym for MBREqual.

MBRIntersects

Indicates whether the Minimum Bounding Rectangles of the two geometries intersect

MBROverlaps

Whether the Minimum Bounding Rectangles of two geometries overlap

MBRTouches

Whether the Minimum Bounding Rectangles of two geometries touch.

MBRWithin

Indicates whether one Minimum Bounding Rectangle is within another

MD5

MD5 checksum

MEDIAN

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

MICROSECOND

Returns microseconds from a date or datetime

MID

Synonym for SUBSTRING(str,pos,len)

MIN

Returns the minimum value

MINUTE

Returns a minute from 0 to 59

MLineFromText

Constructs MULTILINESTRING using its WKT representation and SRID

MLineFromWKB

Constructs a MULTILINESTRING

MOD

Modulo operation. Remainder of N divided by M

MONTH

Returns a month from 1 to 12

MONTHNAME

Returns the full name of the month

MPointFromText

Constructs a MULTIPOINT value using its WKT and SRID

MPointFromWKB

Constructs a MULTIPOINT value using its WKB representation and SRID

MPolyFromText

Constructs a MULTIPOLYGON value

MPolyFromWKB

Constructs a MULTIPOLYGON value using its WKB representation and SRID

MULTILINESTRING

Constructs a MultiLineString value

MultiLineStringFromText

Synonym for MLineFromText

MultiLineStringFromWKB

A synonym for MLineFromWKB

MULTIPOINT

Constructs a WKB MultiPoint value

MultiPointFromText

Synonym for MPointFromText

MultiPointFromWKB

Synonym for MPointFromWKB

MULTIPOLYGON

Constructs a WKB MultiPolygon

MultiPolygonFromText

Synonym for MPolyFromText

MultiPolygonFromWKB

Synonym for MPolyFromWKB

NAME_CONST

Returns the given value

NATURAL_SORT_KEY

Sorting that is more similar to natural human sorting

NOT LIKE

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

NOT REGEXP

Same as NOT (expr REGEXP pat)

NULLIF

Returns NULL if expr1 = expr2

NEXTVAL

Generate next value for sequence

NOT BETWEEN

Same as NOT (expr BETWEEN min AND max)

NOT IN

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

NOW

Returns the current date and time

NTILE

Returns an integer indicating which group a given row falls into

NumGeometries

Synonym for ST_NumGeometries

NumInteriorRings

Synonym for NumInteriorRings

NumPoints

Synonym for ST_NumPoints

OCT

Returns octal value

OCTET_LENGTH

Synonym for LENGTH()

OLD_PASSWORD

Pre MySQL 4.1 password implementation

ORD

Return ASCII or character code

OVERLAPS

Indicates whether two elements spatially overlap

PASSWORD

Calculates a password string

PERCENT_RANK

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

PERCENTILE_CONT

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

PERCENTILE_DISC

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

PERIOD_ADD

Add months to a period

PERIOD_DIFF

Number of months between two periods

PI

Returns the value of π (pi)

POINT

Constructs a WKB Point

PointFromText

Synonym for ST_PointFromText

PointFromWKB

Synonym for PointFromWKB

PointN

Synonym for PointN

PointOnSurface

Synonym for ST_PointOnSurface

POLYGON

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

PolyFromText

Synonym for ST_PolyFromText

PolyFromWKB

Synonym for ST_PolyFromWKB

PolygonFromText

Synonym for ST_PolyFromText

PolygonFromWKB

Synonym for ST_PolyFromWKB

POSITION

Returns the position of a substring in a string

POW

Returns X raised to the power of Y

POWER

Synonym for POW()

QUARTER

Returns year quarter from 1 to 4

QUOTE

Returns quoted, properly escaped string

RADIANS

Converts from degrees to radians

RAND

Random floating-point value

RANK

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

REGEXP

Performs pattern matching

REGEXP_INSTR

Position of the first appearance of a regex

REGEXP_REPLACE

Replaces all occurrences of a pattern

REGEXP_SUBSTR

Returns the matching part of a string

RELEASE_LOCK

Releases lock obtained with GET_LOCK()

REPEAT Function

Returns a string repeated a number of times

REPLACE Function

Replace occurrences of a string

REVERSE

Reverses the order of a string

RIGHT

Returns the rightmost N characters from a string

RLIKE

Synonym for REGEXP()

RPAD

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

ROUND

Rounds a number

ROW_COUNT

Number of rows affected by previous statement

ROW_NUMBER

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

RTRIM

Returns the string with trailing space characters removed

SCHEMA

Synonym for DATABASE()

SECOND

Returns the second of a time

SEC_TO_TIME

Converts a second to a time

SETVAL

Set the next value to be returned by a sequence

SESSION_USER

Synonym for USER()

sha

Synonym for SHA1()

SHA1

Calculates an SHA-1 checksum

SHA2

Calculates an SHA-2 checksum

SIGN

Returns 1, 0 or -1

SIN

Returns the sine

SLEEP

Pauses for the given number of seconds

SOUNDEX

Returns a string based on how the string sounds

SOUNDS LIKE

SOUNDEX(expr1) = SOUNDEX(expr2)

SPACE

Returns a string of space characters

SPIDER_BG_DIRECT_SQL

Background SQL execution

SPIDER_COPY_TABLES

Copy table data

SPIDER_DIRECT_SQL

Execute SQL on the remote server

SPIDER_FLUSH_TABLE_MON_CACHE

Refreshing Spider monitoring server information

SQRT

Square root

SRID

Synonym for ST_SRID

ST_AREA

Area of a Polygon

ST_AsBinary

Converts a value to its WKB representation

ST_AsGeoJson

Returns a GeoJSON element from a given geometry.

ST_AsText

Converts a value to its WKT-Definition

ST_AsWKB

Synonym for ST_AsBinary

ST_ASWKT

Synonym for ST_ASTEXT()

ST_BOUNDARY

Returns a geometry that is the closure of a combinatorial boundary

ST_BUFFER

A new geometry with a buffer added to the original geometry

ST_CENTROID

The mathematical centroid (geometric center) for a MultiPolygon

ST_Collect

Returns the aggregation of the distinct geometry arguments

MariaDB 11.8

ST_CONTAINS

Whether one geometry is contained by another

ST_CONVEXHULL

The minimum convex geometry enclosing all geometries within the set

ST_CROSSES

Whether two geometries spatially cross

ST_DIFFERENCE

Point set difference

ST_DIMENSION

Inherent dimension of a geometry value

ST_DISJOINT

Whether one geometry is spatially disjoint from another

ST_DISTANCE

The distance between two geometries

ST_DISTANCE_SPHERE

The spherical distance between two geometries

ST_ENDPOINT

Returns the endpoint of a LineString

ST_ENVELOPE

Returns the Minimum Bounding Rectangle for a geometry value

ST_EQUALS

Whether two geometries are spatoially equal

ST_ExteriorRing

Returns the exterior ring of a Polygon as a LineString

ST_GeoHash

Returns a geohash.

MariaDB 11.8

ST_GeomCollFromText

Constructs a GEOMETRYCOLLECTION value

ST_GeomCollFromWKB

Constructs a GEOMETRYCOLLECTION value from a WKB

ST_GeometryCollectionFromText

Synonym for ST_GeomCollFromText

ST_GeometryCollectionFromWKB

Synonym for ST_GeomCollFromWKB

ST_GeometryFromText

Synonym for ST_GeomFromText

ST_GeometryFromWKB

Synonym for ST_GeomFromWKB

ST_GEOMETRYN

Returns the N-th geometry in a GeometryCollection

ST_GEOMETRYTYPE

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

ST_GeomFromGeoJSON

Returns a geometry object from a GeoJSON input

ST_GeomFromText

Constructs a geometry value using its WKT and SRID

ST_GeomFromWKB

Constructs a geometry value using its WKB representation and SRID

ST_InteriorRingN

Returns the N-th interior ring for a Polygon

ST_INTERSECTION

The intersection, or shared portion, of two geometries

ST_INTERSECTS

Whether two geometries spatially intersect

ST_ISCLOSED

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

ST_ISEMPTY

Indicated validity of geometry value

ST_IsRing

Returns true if a given LINESTRING is both ST_IsClosed and ST_IsSimple

ST_IsSimple

Returns true if the given Geometry has no anomalous geometric points

ST_IsValid

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

MariaDB 11.8

ST_LatFromGeoHash

Returns a latitude from the given geohash.

MariaDB 11.8

ST_LongFromGeoHash

Returns a longitude from the given geohash.

MariaDB 11.8

ST_LENGTH

Length of a LineString value

ST_LineFromText

Creates a linestring value

ST_LineFromWKB

Constructs a LINESTRING using its WKB and SRID

ST_LineStringFromText

Synonym for ST_LineFromText

ST_LineStringFromWKB

Synonym for ST_LineFromWKB

ST_MLineFromText

Synonym for ST_GeomFromText

ST_MLineFromWKB

Synonym for ST_GeomFromWKB

ST_MPointFromText

Synonym for ST_GeomFromText

ST_MPointFromWKB

Synonym for ST_GeomFromWKB

ST_MPolyFromText

Synonym for ST_GeomFromText

ST_MPolyFromWKB

Synonym for ST_GeomFromWKB

ST_MultiLineStringFromText

Synonym for ST_GeomFromText

ST_MultiLineStringFromWKB

Synonym for ST_GeomFromWKB

ST_MultiPointFromText

Synonym for ST_GeomFromText

ST_MultiPolygonFromText

Synonym for ST_GeomFromText

ST_MultiPolygonFromWKB

Synonym for ST_GeomFromWKB

ST_MultiPointFromWKB

Synonym for ST_GeomFromWKB

ST_NUMGEOMETRIES

Number of geometries in a GeometryCollection

ST_NumInteriorRings

Number of interior rings in a Polygon

ST_NUMPOINTS

Returns the number of Point objects in a LineString

ST_OVERLAPS

Whether two geometries overlap

ST_PointFromGeoHash

Returns a point from the given geohash.

MariaDB 11.8

ST_PointFromText

Constructs a POINT value

ST_PointFromWKB

Constructs POINT using its WKB and SRID

ST_POINTN

Returns the N-th Point in the LineString

ST_POINTONSURFACE

Returns a POINT guaranteed to intersect a surface

ST_PolyFromText

Constructs a POLYGON value

ST_PolyFromWKB

Constructs POLYGON value using its WKB representation and SRID

ST_PolygonFromText

Synonym for ST_PolyFromText

ST_PolygonFromWKB

Synonym for ST_PolyFromWKB

ST_RELATE

Returns true if two geometries are related

ST_Simplify

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

MariaDB 11.8

ST_SRID

Returns a Spatial Reference System ID

ST_STARTPOINT

Returns the start point of a LineString

ST_SYMDIFFERENCE

Portions of two geometries that don't intersect

ST_TOUCHES

Whether one geometry g1 spatially touches another

ST_UNION

Union of two geometries

ST_Validate

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

MariaDB 11.8

ST_WITHIN

Whether one geometry is within another

ST_X

X-coordinate value for a point

ST_Y

Y-coordinate for a point

STARTPOINT

Synonym for ST_StartPoint

STD

Population standard deviation

STDDEV

Population standard deviation

STDDEV_POP

Returns the population standard deviation

STDDEV_SAMP

Standard deviation

STR_TO_DATE

Converts a string to date

STRCMP

Compares two strings in sort order

SUBDATE

Subtract a date unit or number of days

SUBSTR

Returns a substring from string starting at a given position

SUBSTRING

Returns a substring from string starting at a given position

SUBSTRING_INDEX

Returns the substring from string before count occurrences of a delimiter

SUBTIME

Subtracts a time from a date/time

SUM

Sum total

SYS.EXTRACT_SCHEMA_FROM_FILE_NAME

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

MariaDB 10.6

SYS.EXTRACT_TABLE_FROM_FILE_NAME

Given a file path, returns the table name

MariaDB 10.6

SYS.FORMAT_BYTES

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

MariaDB 10.6

SYS.FORMAT_PATH

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

MariaDB 10.6

SYS.FORMAT_STATEMENT

Returns a reduced length string

MariaDB 10.6

SYS.FORMAT_TIME

Returns a human-readable time value and unit indicator

MariaDB 10.6

SYS.LIST_ADD

Adds a value to a given list

MariaDB 10.6

SYS.LIST_DROP

Drops a value from a given list

MariaDB 10.6

SYS.PS_IS_ACCOUNT_ENABLED

Whether Performance Schema instrumentation for the given account is enabled

MariaDB 10.6

SYS.PS_IS_CONSUMER_ENABLED

Whether Performance Schema instrumentation for the given consumer is enabled

MariaDB 10.6

SYS.PS_IS_INSTRUMENT_DEFAULT_ENABLED

Whether a given Performance Schema instrument is enabled by default

MariaDB 10.6

SYS.PS_IS_INSTRUMENT_DEFAULT_TIMED

Returns whether a given Performance Schema instrument is timed by default

MariaDB 10.6

SYS.PS_IS_THREAD_INSTRUMENTED

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

MariaDB 10.6

SYS.PS_THREAD_ACCOUNT

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

MariaDB 10.6

SYS.PS_THREAD_ID

Returns the thread_id associated with the given connection_id

MariaDB 10.6

SYS.PS_THREAD_STACK

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

MariaDB 10.6

SYS.PS_THREAD_TRX_INFO

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

MariaDB 10.6

SYS.QUOTE_IDENTIFIER

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

MariaDB 10.6

SYS.SYS_GET_CONFIG

Returns a configuration option value from the sys_config table

MariaDB 10.6

SYS.VERSION_MAJOR

Returns the MariaDB Server major release version

MariaDB 10.6

SYS.VERSION_MINOR

Returns the MariaDB Server minor release version

MariaDB 10.6

SYS.VERSION_PATCH

Returns the MariaDB Server patch release version

MariaDB 10.6

SYS_GUID

Generates a globally unique identifier

SYSDATE

Returns the current date and time

SYSTEM_USER

Synonym for USER()

TAN

Returns the tangent

TIME function

Extracts the time

TIMEDIFF

Returns the difference between two date/times

TIMESTAMP FUNCTION

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

TIMESTAMPADD

Add interval to a date or datetime

TIMESTAMPDIFF

Difference between two datetimes

TIME_FORMAT

Formats the time value according to the format string

TIME_TO_SEC

Returns the time argument, converted to seconds

TO_BASE64

Converts a string to its base-64 encoded form

TO_CHAR

Converts a date/time type to a char

TO_DAYS

Number of days since year 0

TO_SECONDS

Number of seconds since year 0

TOUCHES

Whether two geometries spatially touch

TRIM

Returns a string with all given prefixes or suffixes removed

TRUNCATE

Truncates X to D decimal places

UCASE

Synonym for UPPER]]()

UNHEX

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

UNCOMPRESS

Uncompresses string compressed with COMPRESS()

UNCOMPRESSED_LENGTH

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

UNIX_TIMESTAMP

Returns a Unix timestamp

UPDATEXML

Replace XML

UPPER

Changes string to uppercase

USER

Current user/host

UTC_DATE

Returns the current UTC date

UTC_TIME

Returns the current UTC time

UTC_TIMESTAMP

Returns the current UTC date and time

UUID

Returns a Universal Unique Identifier v1

UUIDv4

Returns a Universal Unique Identifier v4

MariaDB 11.7

UUIDv7

Returns a Universal Unique Identifier v7

MariaDB 11.7

UUID_SHORT

Return short universal identifier

VALUES or VALUE

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

VAR_POP

Population standard variance

VAR_SAMP

Returns the sample variance

VARIANCE

Population standard variance

VEC_DISTANCE

Calculates either a Euclidean or Cosine distance between two vectors.

MariaDB 11.8

VEC_DISTANCE_COSINE

Calculates a Cosine distance between two vectors.

MariaDB 11.7

VEC_DISTANCE_EUCLIDEAN

Calculates a Euclidean (L2) distance between two points.

MariaDB 11.7

VEC_FromText

Converts a text representation of the vector to a vector.

MariaDB 11.6.0 Vector

VEC_ToText

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

MariaDB 11.6.0 Vector

VERSION

MariaDB server version

WEEK

Returns the week number

WEEKDAY

Returns the weekday index

WEEKOFYEAR

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

WEIGHT_STRING

Weight of the input string

WITHIN

Indicate whether a geographic element is spacially within another

WSREP_LAST_SEEN_GTID

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

WSREP_LAST_WRITTEN_GTID

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

WSREP_SYNC_WAIT_UPTO_GTID

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

X

Synonym for ST_X

Y

Synonym for ST_Y

YEAR

Returns the year for the given date

YEARWEEK

Returns year and week for a date

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

Aggregate Functions

Learn about aggregate functions in MariaDB Server. This section details SQL functions like COUNT(), SUM(), AVG(), MIN(), and MAX() for performing calculations on sets of rows.

AVG

Syntax

AVG([DISTINCT] expr)

Description

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

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

AVG() can be used as a window function.

Examples

CREATE TABLE sales (sales_value INT);

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

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

SELECT AVG(DISTINCT(sales_value)) FROM sales;
+----------------------------+
| AVG(DISTINCT(sales_value)) |
+----------------------------+
|                    23.3333 |
+----------------------------+

Commonly, AVG() is used with a GROUP BY clause:

CREATE TABLE student (name CHAR(10), test CHAR(10), score TINYINT); 

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

SELECT name, AVG(score) FROM student GROUP BY name;
+---------+------------+
| name    | AVG(score) |
+---------+------------+
| Chun    |    74.0000 |
| Esben   |    37.0000 |
| Kaolin  |    72.0000 |
| Tatiana |    85.0000 |
+---------+------------+

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

SELECT name,test,AVG(score) FROM student;
+------+------+------------+
| name | test | MIN(score) |
+------+------+------------+
| Chun | SQL  |         31 |
+------+------+------------+

As a window function:

CREATE TABLE student_test (name CHAR(10), test CHAR(10), score TINYINT); 

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

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

See Also

  • MAX (maximum)

  • MIN (minimum)

  • SUM (sum total)

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

BIT_AND

Syntax

BIT_AND(expr) [over_clause]

Description

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

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

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

Examples

CREATE TABLE vals (x INT);

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

SELECT BIT_AND(x), BIT_OR(x), BIT_XOR(x) FROM vals;
+------------+-----------+------------+
| BIT_AND(x) | BIT_OR(x) | BIT_XOR(x) |
+------------+-----------+------------+
|        100 |       111 |        101 |
+------------+-----------+------------+

As an aggregate function:

CREATE TABLE vals2 (category VARCHAR(1), x INT);

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

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

No match:

SELECT BIT_AND(NULL);
+----------------------+
| BIT_AND(NULL)        |
+----------------------+
| 18446744073709551615 |
+----------------------+

See Also

  • BIT_OR

  • BIT_XOR

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

BIT_OR

Syntax

BIT_OR(expr) [over_clause]

Description

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

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

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

Examples

CREATE TABLE vals (x INT);

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

SELECT BIT_AND(x), BIT_OR(x), BIT_XOR(x) FROM vals;
+------------+-----------+------------+
| BIT_AND(x) | BIT_OR(x) | BIT_XOR(x) |
+------------+-----------+------------+
|        100 |       111 |        101 |
+------------+-----------+------------+

As an aggregate function:

CREATE TABLE vals2 (category VARCHAR(1), x INT);

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

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

No match:

SELECT BIT_OR(NULL);
+--------------+
| BIT_OR(NULL) |
+--------------+
|            0 |
+--------------+

See Also

  • BIT_AND

  • BIT_XOR

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

BIT_XOR

Syntax

BIT_XOR(expr) [over_clause]

Description

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

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

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

Examples

CREATE TABLE vals (x INT);

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

SELECT BIT_AND(x), BIT_OR(x), BIT_XOR(x) FROM vals;
+------------+-----------+------------+
| BIT_AND(x) | BIT_OR(x) | BIT_XOR(x) |
+------------+-----------+------------+
|        100 |       111 |        101 |
+------------+-----------+------------+

As an aggregate function:

CREATE TABLE vals2 (category VARCHAR(1), x INT);

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

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

No match:

SELECT BIT_XOR(NULL);
+---------------+
| BIT_XOR(NULL) |
+---------------+
|             0 |
+---------------+

See Also

  • BIT_AND

  • BIT_OR

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

COUNT DISTINCT

Syntax

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

Description

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

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

Examples

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

See Also

  • SELECT

  • COUNT

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

COUNT

Syntax

COUNT(expr)

Description

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

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

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

COUNT() can be used as a window function.

Examples

CREATE TABLE student (name CHAR(10), test CHAR(10), score TINYINT); 

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

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

COUNT(DISTINCT) example:

SELECT COUNT(DISTINCT (name)) FROM student;
+------------------------+
| COUNT(DISTINCT (name)) |
+------------------------+
|                      4 |
+------------------------+

As a window function

CREATE OR REPLACE TABLE student_test (name CHAR(10), test CHAR(10), score TINYINT);

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

SELECT name, test, score, COUNT(score) OVER (PARTITION BY name) 
    AS tests_written FROM student_test;
+---------+--------+-------+---------------+
| name    | test   | score | tests_written |
+---------+--------+-------+---------------+
| Chun    | SQL    |    75 |             2 |
| Chun    | Tuning |    73 |             2 |
| Esben   | SQL    |    43 |             2 |
| Esben   | Tuning |    31 |             2 |
| Kaolin  | SQL    |    56 |             2 |
| Kaolin  | Tuning |    88 |             2 |
| Tatiana | SQL    |    87 |             1 |
+---------+--------+-------+---------------+

See Also

  • SELECT

  • COUNT DISTINCT

  • Window Functions

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

GROUP_CONCAT

Syntax

GROUP_CONCAT(expr)

Description

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

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

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

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

The full syntax is as follows:

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val]
             [LIMIT {[offset,] row_count | row_count OFFSET offset}])

DISTINCT eliminates duplicate values from the output string.

ORDER BY determines the order of returned values.

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

LIMIT

The LIMIT clause can be used with GROUP_CONCAT.

Examples

SELECT student_name,
       GROUP_CONCAT(test_score)
       FROM student
       GROUP BY student_name;

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

SELECT GROUP_CONCAT(DISTINCT User ORDER BY User SEPARATOR '\n')
   FROM mysql.user;

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

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

SELECT User, GROUP_CONCAT(Host ORDER BY Host SEPARATOR ', ') 
   FROM mysql.user GROUP BY User ORDER BY User;

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

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

CREATE TABLE d (dd DATE, cc INT);

INSERT INTO d VALUES ('2017-01-01',1);
INSERT INTO d VALUES ('2017-01-02',2);
INSERT INTO d VALUES ('2017-01-04',3);

the following query:

SELECT SUBSTRING_INDEX(GROUP_CONCAT(CONCAT_WS(":",dd,cc) 
ORDER BY cc DESC),",",1) FROM d;
+----------------------------------------------------------------------------+
| SUBSTRING_INDEX(GROUP_CONCAT(CONCAT_WS(":",dd,cc) ORDER BY cc DESC),",",1) |
+----------------------------------------------------------------------------+
| 2017-01-04:3                                                               |
+----------------------------------------------------------------------------+

can be more simply rewritten as:

SELECT GROUP_CONCAT(CONCAT_WS(":",dd,cc) ORDER BY cc DESC LIMIT 1) FROM d;
+-------------------------------------------------------------+
| GROUP_CONCAT(CONCAT_WS(":",dd,cc) ORDER BY cc DESC LIMIT 1) |
+-------------------------------------------------------------+
| 2017-01-04:3                                                |
+-------------------------------------------------------------+

NULLS:

CREATE OR REPLACE TABLE t1 (a int, b char);

INSERT INTO t1 VALUES (1, 'a'), (2, NULL);

SELECT GROUP_CONCAT(a, b) FROM t1;
+--------------------+
| GROUP_CONCAT(a, b) |
+--------------------+
| 1a                 |
+--------------------+

See Also

  • CONCAT()

  • CONCAT_WS()

  • SELECT

  • ORDER BY

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

MAX

Syntax

MAX([DISTINCT] expr)

Description

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

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

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

MAX() can be used as a window function.

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

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

Only ascending indexes can be used to optimize MAX.

Examples

CREATE TABLE student (name CHAR(10), test CHAR(10), score TINYINT); 

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

SELECT name, MAX(score) FROM student GROUP BY name;
+---------+------------+
| name    | MAX(score) |
+---------+------------+
| Chun    |         75 |
| Esben   |         43 |
| Kaolin  |         88 |
| Tatiana |         87 |
+---------+------------+

MAX string:

SELECT MAX(name) FROM student;
+-----------+
| MAX(name) |
+-----------+
| Tatiana   |
+-----------+

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

SELECT name,test,MAX(SCORE) FROM student;
+------+------+------------+
| name | test | MAX(SCORE) |
+------+------+------------+
| Chun | SQL  |         88 |
+------+------+------------+

Difference between ORDER BY DESC and MAX():

CREATE TABLE student2(name CHAR(10),grade ENUM('b','c','a'));

INSERT INTO student2 VALUES('Chun','b'),('Esben','c'),('Kaolin','a');

SELECT MAX(grade) FROM student2;
+------------+
| MAX(grade) |
+------------+
| c          |
+------------+

SELECT grade FROM student2 ORDER BY grade DESC LIMIT 1;
+-------+
| grade |
+-------+
| a     |
+-------+

As a window function:

CREATE OR REPLACE TABLE student_test (name CHAR(10), test CHAR(10), score TINYINT);
INSERT INTO student_test VALUES 
    ('Chun', 'SQL', 75), ('Chun', 'Tuning', 73), 
    ('Esben', 'SQL', 43), ('Esben', 'Tuning', 31), 
    ('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88), 
    ('Tatiana', 'SQL', 87);

SELECT name, test, score, MAX(score) 
  OVER (PARTITION BY name) AS highest_score FROM student_test;
+---------+--------+-------+---------------+
| name    | test   | score | highest_score |
+---------+--------+-------+---------------+
| Chun    | SQL    |    75 |            75 |
| Chun    | Tuning |    73 |            75 |
| Esben   | SQL    |    43 |            43 |
| Esben   | Tuning |    31 |            43 |
| Kaolin  | SQL    |    56 |            88 |
| Kaolin  | Tuning |    88 |            88 |
| Tatiana | SQL    |    87 |            87 |
+---------+--------+-------+---------------+

See Also

  • AVG (average)

  • MIN (minimum)

  • SUM (sum total)

  • GREATEST() returns the largest value from a list

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

MIN

Syntax

MIN([DISTINCT] expr)

Description

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

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

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

MIN() can be used as a window function.

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

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

Only ascending indexes can be used to optimize MIN.

Examples

CREATE TABLE student (name CHAR(10), test CHAR(10), score TINYINT); 

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

SELECT name, MIN(score) FROM student GROUP BY name;
+---------+------------+
| name    | MIN(score) |
+---------+------------+
| Chun    |         73 |
| Esben   |         31 |
| Kaolin  |         56 |
| Tatiana |         83 |
+---------+------------+

MIN() with a string:

SELECT MIN(name) FROM student;
+-----------+
| MIN(name) |
+-----------+
| Chun      |
+-----------+

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

SELECT name,test,MIN(score) FROM student;
+------+------+------------+
| name | test | MIN(score) |
+------+------+------------+
| Chun | SQL  |         31 |
+------+------+------------+

Difference between ORDER BY ASC and MIN():

CREATE TABLE student2(name CHAR(10),grade ENUM('b','c','a'));

INSERT INTO student2 VALUES('Chun','b'),('Esben','c'),('Kaolin','a');

SELECT MIN(grade) FROM student2;
+------------+
| MIN(grade) |
+------------+
| a          |
+------------+

SELECT grade FROM student2 ORDER BY grade ASC LIMIT 1;
+-------+
| grade |
+-------+
| b     |
+-------+

As a window function:

CREATE OR REPLACE TABLE student_test (name CHAR(10), test CHAR(10), score TINYINT);
INSERT INTO student_test VALUES 
    ('Chun', 'SQL', 75), ('Chun', 'Tuning', 73), 
    ('Esben', 'SQL', 43), ('Esben', 'Tuning', 31), 
    ('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88), 
    ('Tatiana', 'SQL', 87);


SELECT name, test, score, MIN(score) 
  OVER (PARTITION BY name) AS lowest_score FROM student_test;
+---------+--------+-------+--------------+
| name    | test   | score | lowest_score |
+---------+--------+-------+--------------+
| Chun    | SQL    |    75 |           73 |
| Chun    | Tuning |    73 |           73 |
| Esben   | SQL    |    43 |           31 |
| Esben   | Tuning |    31 |           31 |
| Kaolin  | SQL    |    56 |           56 |
| Kaolin  | Tuning |    88 |           56 |
| Tatiana | SQL    |    87 |           87 |
+---------+--------+-------+--------------+

See Also

  • AVG (average)

  • MAX (maximum)

  • SUM (sum total)

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

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

STD

Syntax

STD(expr)

Description

Returns the population standard deviation of expr. This is an extension to standard SQL. The standard SQL function STDDEV_POP() can be used instead.

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

STD() can be used as a window function.

This function returns NULL if there were no matching rows.

Examples

As an aggregate function:

CREATE OR REPLACE TABLE stats (category VARCHAR(2), x INT);

INSERT INTO stats VALUES 
  ('a',1),('a',2),('a',3),
  ('b',11),('b',12),('b',20),('b',30),('b',60);

SELECT category, STDDEV_POP(x), STDDEV_SAMP(x), VAR_POP(x) 
  FROM stats GROUP BY category;
+----------+---------------+----------------+------------+
| category | STDDEV_POP(x) | STDDEV_SAMP(x) | VAR_POP(x) |
+----------+---------------+----------------+------------+
| a        |        0.8165 |         1.0000 |     0.6667 |
| b        |       18.0400 |        20.1693 |   325.4400 |
+----------+---------------+----------------+------------+

As a window function:

CREATE OR REPLACE TABLE student_test (name CHAR(10), test CHAR(10), score TINYINT);

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

SELECT name, test, score, STDDEV_POP(score) 
  OVER (PARTITION BY test) AS stddev_results FROM student_test;
+---------+--------+-------+----------------+
| name    | test   | score | stddev_results |
+---------+--------+-------+----------------+
| Chun    | SQL    |    75 |        16.9466 |
| Chun    | Tuning |    73 |        24.1247 |
| Esben   | SQL    |    43 |        16.9466 |
| Esben   | Tuning |    31 |        24.1247 |
| Kaolin  | SQL    |    56 |        16.9466 |
| Kaolin  | Tuning |    88 |        24.1247 |
| Tatiana | SQL    |    87 |        16.9466 |
+---------+--------+-------+----------------+

See Also

  • STDDEV_POP (equivalent, standard SQL)

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

  • VAR_POP (variance)

  • STDDEV_SAMP (sample standard deviation)

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

STDDEV

Syntax

STDDEV(expr)

Description

Returns the population standard deviation of expr. This function is provided for compatibility with Oracle. The standard SQL functionSTDDEV_POP() can be used instead.

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

STDDEV() can be used as a window function.

This function returns NULL if there were no matching rows.

Examples

As an aggregate function:

CREATE OR REPLACE TABLE stats (category VARCHAR(2), x INT);

INSERT INTO stats VALUES 
  ('a',1),('a',2),('a',3),
  ('b',11),('b',12),('b',20),('b',30),('b',60);

SELECT category, STDDEV_POP(x), STDDEV_SAMP(x), VAR_POP(x) 
  FROM stats GROUP BY category;
+----------+---------------+----------------+------------+
| category | STDDEV_POP(x) | STDDEV_SAMP(x) | VAR_POP(x) |
+----------+---------------+----------------+------------+
| a        |        0.8165 |         1.0000 |     0.6667 |
| b        |       18.0400 |        20.1693 |   325.4400 |
+----------+---------------+----------------+------------+

As a window function:

CREATE OR REPLACE TABLE student_test (name CHAR(10), test CHAR(10), score TINYINT);

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

SELECT name, test, score, STDDEV_POP(score) 
  OVER (PARTITION BY test) AS stddev_results FROM student_test;
+---------+--------+-------+----------------+
| name    | test   | score | stddev_results |
+---------+--------+-------+----------------+
| Chun    | SQL    |    75 |        16.9466 |
| Chun    | Tuning |    73 |        24.1247 |
| Esben   | SQL    |    43 |        16.9466 |
| Esben   | Tuning |    31 |        24.1247 |
| Kaolin  | SQL    |    56 |        16.9466 |
| Kaolin  | Tuning |    88 |        24.1247 |
| Tatiana | SQL    |    87 |        16.9466 |
+---------+--------+-------+----------------+

See Also

  • STDDEV_POP (equivalent, standard SQL)

  • STD (equivalent, non-standard SQL)

  • VAR_POP (variance)

  • STDDEV_SAMP (sample standard deviation)

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

STDDEV_POP

Syntax

STDDEV_POP(expr)

Description

Returns the population standard deviation of expr (the square root of VAR_POP()). You can also use STD() or STDDEV(), which are equivalent but not standard SQL.

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

STDDEV_POP() can be used as a window function.

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

Examples

As an aggregate function:

CREATE OR REPLACE TABLE stats (category VARCHAR(2), x INT);

INSERT INTO stats VALUES 
  ('a',1),('a',2),('a',3),
  ('b',11),('b',12),('b',20),('b',30),('b',60);

SELECT category, STDDEV_POP(x), STDDEV_SAMP(x), VAR_POP(x) 
  FROM stats GROUP BY category;
+----------+---------------+----------------+------------+
| category | STDDEV_POP(x) | STDDEV_SAMP(x) | VAR_POP(x) |
+----------+---------------+----------------+------------+
| a        |        0.8165 |         1.0000 |     0.6667 |
| b        |       18.0400 |        20.1693 |   325.4400 |
+----------+---------------+----------------+------------+

As a window function:

CREATE OR REPLACE TABLE student_test (name CHAR(10), test CHAR(10), score TINYINT);

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

SELECT name, test, score, STDDEV_POP(score) 
  OVER (PARTITION BY test) AS stddev_results FROM student_test;
+---------+--------+-------+----------------+
| name    | test   | score | stddev_results |
+---------+--------+-------+----------------+
| Chun    | SQL    |    75 |        16.9466 |
| Chun    | Tuning |    73 |        24.1247 |
| Esben   | SQL    |    43 |        16.9466 |
| Esben   | Tuning |    31 |        24.1247 |
| Kaolin  | SQL    |    56 |        16.9466 |
| Kaolin  | Tuning |    88 |        24.1247 |
| Tatiana | SQL    |    87 |        16.9466 |
+---------+--------+-------+----------------+

See Also

  • STD (equivalent, non-standard SQL)

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

  • VAR_POP (variance)

  • STDDEV_SAMP (sample standard deviation)

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

STDDEV_SAMP

Syntax

STDDEV_SAMP(expr)

Description

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

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

STDDEV_SAMP() can be used as a window function.

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

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

SUM

Syntax

SUM([DISTINCT] expr)

Description

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

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

Examples

CREATE TABLE sales (sales_value INT);
INSERT INTO sales VALUES(10),(20),(20),(40);

SELECT SUM(sales_value) FROM sales;
+------------------+
| SUM(sales_value) |
+------------------+
|               90 |
+------------------+

SELECT SUM(DISTINCT(sales_value)) FROM sales;
+----------------------------+
| SUM(DISTINCT(sales_value)) |
+----------------------------+
|                         70 |
+----------------------------+

Commonly, SUM is used with a GROUP BY clause:

CREATE TABLE sales (name CHAR(10), month CHAR(10), units INT);

INSERT INTO sales VALUES 
  ('Chun', 'Jan', 75), ('Chun', 'Feb', 73),
  ('Esben', 'Jan', 43), ('Esben', 'Feb', 31),
  ('Kaolin', 'Jan', 56), ('Kaolin', 'Feb', 88),
  ('Tatiana', 'Jan', 87), ('Tatiana', 'Feb', 83);

SELECT name, SUM(units) FROM sales GROUP BY name;
+---------+------------+
| name    | SUM(units) |
+---------+------------+
| Chun    |        148 |
| Esben   |         74 |
| Kaolin  |        144 |
| Tatiana |        170 |
+---------+------------+

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

SELECT name,SUM(units) FROM sales
;+------+------------+
| name | SUM(units) |
+------+------------+
| Chun |        536 |
+------+------------+

As a window function:

CREATE OR REPLACE TABLE student_test (name CHAR(10), test CHAR(10), score TINYINT);
INSERT INTO student_test VALUES 
    ('Chun', 'SQL', 75), ('Chun', 'Tuning', 73), 
    ('Esben', 'SQL', 43), ('Esben', 'Tuning', 31), 
    ('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88), 
    ('Tatiana', 'SQL', 87);

SELECT name, test, score, SUM(score) OVER (PARTITION BY name) AS total_score FROM student_test;
+---------+--------+-------+-------------+
| name    | test   | score | total_score |
+---------+--------+-------+-------------+
| Chun    | SQL    |    75 |         148 |
| Chun    | Tuning |    73 |         148 |
| Esben   | SQL    |    43 |          74 |
| Esben   | Tuning |    31 |          74 |
| Kaolin  | SQL    |    56 |         144 |
| Kaolin  | Tuning |    88 |         144 |
| Tatiana | SQL    |    87 |          87 |
+---------+--------+-------+-------------+

See Also

  • AVG (average)

  • MAX (maximum)

  • MIN (minimum)

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

VAR_POP

Syntax

VAR_POP(expr)

Description

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

Variance is calculated by

  • working out the mean for the set;

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

  • calculating the average of the resulting differences.

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

VAR_POP() can be used as a window function.

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

Examples

CREATE TABLE v(i tinyint);

INSERT INTO v VALUES(101),(99);

SELECT VAR_POP(i) FROM v;
+------------+
| VAR_POP(i) |
+------------+
|     1.0000 |
+------------+

INSERT INTO v VALUES(120),(80);

SELECT VAR_POP(i) FROM v;
+------------+
| VAR_POP(i) |
+------------+
|   200.5000 |
+------------+

As an aggregate function:

CREATE OR REPLACE TABLE stats (category VARCHAR(2), x INT);

INSERT INTO stats VALUES 
  ('a',1),('a',2),('a',3),
  ('b',11),('b',12),('b',20),('b',30),('b',60);

SELECT category, STDDEV_POP(x), STDDEV_SAMP(x), VAR_POP(x) 
  FROM stats GROUP BY category;
+----------+---------------+----------------+------------+
| category | STDDEV_POP(x) | STDDEV_SAMP(x) | VAR_POP(x) |
+----------+---------------+----------------+------------+
| a        |        0.8165 |         1.0000 |     0.6667 |
| b        |       18.0400 |        20.1693 |   325.4400 |
+----------+---------------+----------------+------------+

As a window function:

CREATE OR REPLACE TABLE student_test (name CHAR(10), test CHAR(10), score TINYINT);

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

SELECT name, test, score, VAR_POP(score) 
  OVER (PARTITION BY test) AS variance_results FROM student_test;
+---------+--------+-------+------------------+
| name    | test   | score | variance_results |
+---------+--------+-------+------------------+
| Chun    | SQL    |    75 |         287.1875 |
| Esben   | SQL    |    43 |         287.1875 |
| Kaolin  | SQL    |    56 |         287.1875 |
| Tatiana | SQL    |    87 |         287.1875 |
| Chun    | Tuning |    73 |         582.0000 |
| Esben   | Tuning |    31 |         582.0000 |
| Kaolin  | Tuning |    88 |         582.0000 |
+---------+--------+-------+------------------+

See Also

  • VARIANCE (equivalent, non-standard SQL)

  • STDDEV_POP (population standard deviation)

  • STDDEV_SAMP (sample standard deviation)

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

VAR_SAMP

Syntax

VAR_SAMP(expr)

Description

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

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

VAR_SAMP() can be used as a window function.

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

Examples

As an aggregate function:

CREATE OR REPLACE TABLE stats (category VARCHAR(2), x INT);

INSERT INTO stats VALUES 
  ('a',1),('a',2),('a',3),
  ('b',11),('b',12),('b',20),('b',30),('b',60);

SELECT category, STDDEV_POP(x), STDDEV_SAMP(x), VAR_POP(x) 
  FROM stats GROUP BY category;
+----------+---------------+----------------+------------+
| category | STDDEV_POP(x) | STDDEV_SAMP(x) | VAR_POP(x) |
+----------+---------------+----------------+------------+
| a        |        0.8165 |         1.0000 |     0.6667 |
| b        |       18.0400 |        20.1693 |   325.4400 |
+----------+---------------+----------------+------------+

As a window function:

CREATE OR REPLACE TABLE student_test (name CHAR(10), test CHAR(10), score TINYINT);

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

SELECT name, test, score, VAR_SAMP(score) 
  OVER (PARTITION BY test) AS variance_results FROM student_test;
+---------+--------+-------+------------------+
| name    | test   | score | variance_results |
+---------+--------+-------+------------------+
| Chun    | SQL    |    75 |         382.9167 |
| Chun    | Tuning |    73 |         873.0000 |
| Esben   | SQL    |    43 |         382.9167 |
| Esben   | Tuning |    31 |         873.0000 |
| Kaolin  | SQL    |    56 |         382.9167 |
| Kaolin  | Tuning |    88 |         873.0000 |
| Tatiana | SQL    |    87 |         382.9167 |
+---------+--------+-------+------------------+

See Also

  • VAR_POP (variance)

  • STDDEV_POP (population standard deviation)

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

VARIANCE

Syntax

VARIANCE(expr)

Description

Returns the population standard variance of expr. This is an extension to standard SQL. The standard SQL function VAR_POP() can be used instead.

Variance is calculated by

  • working out the mean for the set;

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

  • calculating the average of the resulting differences.

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

VARIANCE() can be used as a window function.

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

Examples

CREATE TABLE v(i tinyint);

INSERT INTO v VALUES(101),(99);

SELECT VARIANCE(i) FROM v;
+-------------+
| VARIANCE(i) |
+-------------+
|      1.0000 |
+-------------+

INSERT INTO v VALUES(120),(80);

SELECT VARIANCE(i) FROM v;
+-------------+
| VARIANCE(i) |
+-------------+
|    200.5000 |
+-------------+

As an aggregate function:

CREATE OR REPLACE TABLE stats (category VARCHAR(2), x INT);

INSERT INTO stats VALUES 
  ('a',1),('a',2),('a',3),
  ('b',11),('b',12),('b',20),('b',30),('b',60);

SELECT category, STDDEV_POP(x), STDDEV_SAMP(x), VAR_POP(x) 
  FROM stats GROUP BY category;
+----------+---------------+----------------+------------+
| category | STDDEV_POP(x) | STDDEV_SAMP(x) | VAR_POP(x) |
+----------+---------------+----------------+------------+
| a        |        0.8165 |         1.0000 |     0.6667 |
| b        |       18.0400 |        20.1693 |   325.4400 |
+----------+---------------+----------------+------------+

As a window function:

CREATE OR REPLACE TABLE student_test (name CHAR(10), test CHAR(10), score TINYINT);

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

SELECT name, test, score, VAR_POP(score) 
  OVER (PARTITION BY test) AS variance_results FROM student_test;
+---------+--------+-------+------------------+
| name    | test   | score | variance_results |
+---------+--------+-------+------------------+
| Chun    | SQL    |    75 |         287.1875 |
| Chun    | Tuning |    73 |         582.0000 |
| Esben   | SQL    |    43 |         287.1875 |
| Esben   | Tuning |    31 |         582.0000 |
| Kaolin  | SQL    |    56 |         287.1875 |
| Kaolin  | Tuning |    88 |         582.0000 |
| Tatiana | SQL    |    87 |         287.1875 |
+---------+--------+-------+------------------+

See Also

  • VAR_POP (equivalent, standard SQL)

  • STDDEV_POP (population standard deviation)

  • STDDEV_SAMP (sample standard deviation)

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

Control Flow Functions

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

CASE OPERATOR

Syntax

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

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

Description

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

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

Examples

SELECT CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END;
+------------------------------------------------------------+
| CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END |
+------------------------------------------------------------+
| one                                                        |
+------------------------------------------------------------+

SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;
+--------------------------------------------+
| CASE WHEN 1>0 THEN 'true' ELSE 'false' END |
+--------------------------------------------+
| true                                       |
+--------------------------------------------+


SELECT CASE BINARY 'B' WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;
+-----------------------------------------------------+
| CASE BINARY 'B' WHEN 'a' THEN 1 WHEN 'b' THEN 2 END |
+-----------------------------------------------------+
|                                                NULL |
+-----------------------------------------------------+

Only the first matching condition is processed:

SELECT 
  CASE true 
     WHEN (1=1) THEN '1=1' -- result is returned 
     WHEN (1=1 OR 2=2) THEN '1=1 OR 2=2' -- condition not processed
     ELSE 'else'
  END 
;
+-------------------------------------------------------------------------------------+
| CASE true WHEN (1=1) THEN '1=1' WHEN (1=1 OR 2=2) THEN '1=1 OR 2=2' ELSE 'else' END |
+-------------------------------------------------------------------------------------+
+ 1=1                                                                                 +
+-------------------------------------------------------------------------------------+

See Also

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

  • The IF() function.

  • Operator Precedence

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

DECODE_ORACLE

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

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

IF Function

Syntax

IF(expr1,expr2,expr3)

Description

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

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

Examples

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

See Also

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

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

IFNULL

Syntax

IFNULL(expr1,expr2)
NVL(expr1,expr2)

Description

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

NVL() is an alias for IFNULL().

Examples

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

See Also

  • NULL values

  • IS NULL operator

  • IS NOT NULL operator

  • COALESCE function

  • NULLIF function

  • CONNECT data types

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

NULLIF

Syntax

NULLIF(expr1,expr2)

Description

Returns NULL if expr1 = expr2 is true, otherwise returns expr1. This is the same as CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END.

Examples

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

SELECT NULLIF(1,2);
+-------------+
| NULLIF(1,2) |
+-------------+
|           1 |
+-------------+

See Also

  • NULL values

  • IS NULL operator

  • IS NOT NULL operator

  • COALESCE function

  • IFNULL function

  • CONNECT data types

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

NVL

NVL is a synonym for IFNULL.

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

NVL2

Syntax

NVL2(expr1,expr2,expr3)

Description

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

Examples

SELECT NVL2(NULL,1,2);
+----------------+
| NVL2(NULL,1,2) |
+----------------+
|              2 |
+----------------+

SELECT NVL2('x',1,2);
+---------------+
| NVL2('x',1,2) |
+---------------+
|             1 |
+---------------+

See Also

  • IFNULL (or NVL)

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

Date & Time Functions

Learn about date and time functions in MariaDB Server. This section details SQL functions for manipulating, formatting, and calculating with date and time values for various applications.

Date and Time Units

The INTERVAL keyword can be used to add or subtract a time interval of time to a DATETIME, DATE or TIME value.

The syntax is:

INTERVAL time_quantity time_unit

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

SELECT '2008-12-31 23:59:59' + INTERVAL 1 SECOND;
+-------------------------------------------+
| '2008-12-31 23:59:59' + INTERVAL 1 SECOND |
+-------------------------------------------+
| 2009-01-01 00:00:00                       |
+-------------------------------------------+

The following units are valid:

Unit
Description

MICROSECOND

Microseconds

SECOND

Seconds

MINUTE

Minutes

HOUR

Hours

DAY

Days

WEEK

Weeks

MONTH

Months

QUARTER

Quarters

YEAR

Years

SECOND_MICROSECOND

Seconds.Microseconds

MINUTE_MICROSECOND

Minutes.Seconds.Microseconds

MINUTE_SECOND

Minutes.Seconds

HOUR_MICROSECOND

Hours.Minutes.Seconds.Microseconds

HOUR_SECOND

Hours.Minutes.Seconds

HOUR_MINUTE

Hours.Minutes

DAY_MICROSECOND

Days Hours.Minutes.Seconds.Microseconds

DAY_SECOND

Days Hours.Minutes.Seconds

DAY_MINUTE

Days Hours.Minutes

DAY_HOUR

Days Hours

YEAR_MONTH

Years-Months

The time units containing an underscore are composite; that is, they consist of multiple base time units. For base time units, time_quantity is an integer number. For composite units, the quantity must be expressed as a string with multiple integer numbers separated by any punctuation character.

Example of composite units:

INTERVAL '2:2' YEAR_MONTH
INTERVAL '1:30:30' HOUR_SECOND
INTERVAL '1!30!30' HOUR_SECOND -- same as above

Time units can be used in the following contexts:

  • after a + or a - operator;

  • with the following DATE or TIME functions: ADDDATE(), SUBDATE(), DATE_ADD(), DATE_SUB(), TIMESTAMPADD(), TIMESTAMPDIFF(), EXTRACT();

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

  • when defining a partitioning BY SYSTEM_TIME .

See Also

  • Date and time literals

  • Operator Precedence

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

ADD_MONTHS

ADD_MONTHS is available from 10.6.1.

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

Syntax

ADD_MONTHS(date, months)

Description

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.

Examples

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)

See Also

  • SQL_MODE=ORACLE

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

ADDDATE

Syntax

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

Description

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

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

Examples

SELECT DATE_ADD('2008-01-02', INTERVAL 31 DAY);
+-----------------------------------------+
| DATE_ADD('2008-01-02', INTERVAL 31 DAY) |
+-----------------------------------------+
| 2008-02-02                              |
+-----------------------------------------+

SELECT ADDDATE('2008-01-02', INTERVAL 31 DAY);
+----------------------------------------+
| ADDDATE('2008-01-02', INTERVAL 31 DAY) |
+----------------------------------------+
| 2008-02-02                             |
+----------------------------------------+
SELECT ADDDATE('2008-01-02', 31);
+---------------------------+
| ADDDATE('2008-01-02', 31) |
+---------------------------+
| 2008-02-02                |
+---------------------------+
CREATE TABLE t1 (d DATETIME);
INSERT INTO t1 VALUES
    ("2007-01-30 21:31:07"),
    ("1983-10-15 06:42:51"),
    ("2011-04-21 12:34:56"),
    ("2011-10-30 06:31:41"),
    ("2011-01-30 14:03:25"),
    ("2004-10-07 11:19:34");
SELECT d, ADDDATE(d, 10) from t1;
+---------------------+---------------------+
| d                   | ADDDATE(d, 10)      |
+---------------------+---------------------+
| 2007-01-30 21:31:07 | 2007-02-09 21:31:07 |
| 1983-10-15 06:42:51 | 1983-10-25 06:42:51 |
| 2011-04-21 12:34:56 | 2011-05-01 12:34:56 |
| 2011-10-30 06:31:41 | 2011-11-09 06:31:41 |
| 2011-01-30 14:03:25 | 2011-02-09 14:03:25 |
| 2004-10-07 11:19:34 | 2004-10-17 11:19:34 |
+---------------------+---------------------+

SELECT d, ADDDATE(d, INTERVAL 10 HOUR) from t1;
+---------------------+------------------------------+
| d                   | ADDDATE(d, INTERVAL 10 HOUR) |
+---------------------+------------------------------+
| 2007-01-30 21:31:07 | 2007-01-31 07:31:07          |
| 1983-10-15 06:42:51 | 1983-10-15 16:42:51          |
| 2011-04-21 12:34:56 | 2011-04-21 22:34:56          |
| 2011-10-30 06:31:41 | 2011-10-30 16:31:41          |
| 2011-01-30 14:03:25 | 2011-01-31 00:03:25          |
| 2004-10-07 11:19:34 | 2004-10-07 21:19:34          |
+---------------------+------------------------------+

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

ADDTIME

Syntax

ADDTIME(expr1,expr2)

Description

ADDTIME() adds expr2 to expr1 and returns the result. expr1 is a time or datetime expression, and expr2 is a time expression.

Examples

SELECT ADDTIME('2007-12-31 23:59:59.999999', '1 1:1:1.000002');
+---------------------------------------------------------+
| ADDTIME('2007-12-31 23:59:59.999999', '1 1:1:1.000002') |
+---------------------------------------------------------+
| 2008-01-02 01:01:01.000001                              |
+---------------------------------------------------------+

SELECT ADDTIME('01:00:00.999999', '02:00:00.999998');
+-----------------------------------------------+
| ADDTIME('01:00:00.999999', '02:00:00.999998') |
+-----------------------------------------------+
| 03:00:01.999997                               |
+-----------------------------------------------+

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

CONVERT_TZ

Syntax

CONVERT_TZ(dt,from_tz,to_tz)

Description

CONVERT_TZ() converts a datetime value dt from the time zone given by from_tz to the time zone given by to_tz and returns the resulting value.

In order to use named time zones, such as GMT, MET or Africa/Johannesburg, the time_zone tables must be loaded (see mysql_tzinfo_to_sql).

No conversion will take place if the value falls outside of the supported TIMESTAMP range ('1970-01-01 00:00:01' to '2038-01-19 05:14:07' UTC) when converted from from_tz to UTC.

This function returns NULL if the arguments are invalid (or named time zones have not been loaded).

See time zones for more information.

Examples

SELECT CONVERT_TZ('2016-01-01 12:00:00','+00:00','+10:00');
+-----------------------------------------------------+
| CONVERT_TZ('2016-01-01 12:00:00','+00:00','+10:00') |
+-----------------------------------------------------+
| 2016-01-01 22:00:00                                 |
+-----------------------------------------------------+

Using named time zones (with the time zone tables loaded):

SELECT CONVERT_TZ('2016-01-01 12:00:00','GMT','Africa/Johannesburg');
+---------------------------------------------------------------+
| CONVERT_TZ('2016-01-01 12:00:00','GMT','Africa/Johannesburg') |
+---------------------------------------------------------------+
| 2016-01-01 14:00:00                                           |
+---------------------------------------------------------------+

The value is out of the TIMESTAMP range, so no conversion takes place:

SELECT CONVERT_TZ('1969-12-31 22:00:00','+00:00','+10:00');
+-----------------------------------------------------+
| CONVERT_TZ('1969-12-31 22:00:00','+00:00','+10:00') |
+-----------------------------------------------------+
| 1969-12-31 22:00:00                                 |
+-----------------------------------------------------+

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

CURDATE

Syntax

CURDATE()
CURRENT_DATE
CURRENT_DATE()

Description

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

CURRENT_DATE and CURRENT_DATE() are synonyms.

Examples

SELECT CURDATE();
+------------+
| CURDATE()  |
+------------+
| 2019-03-05 |
+------------+

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

SELECT CURDATE() +0;
+--------------+
| CURDATE() +0 |
+--------------+
|     20190305 |
+--------------+

Date calculation:

SELECT CURDATE() - INTERVAL 5 DAY;
+----------------------------+
| CURDATE() - INTERVAL 5 DAY |
+----------------------------+
| 2019-02-28                 |
+----------------------------+

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

CURRENT_DATE

Syntax

CURRENT_DATE, CURRENT_DATE()

Description

CURRENT_DATE and CURRENT_DATE() are synonyms for CURDATE().

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

CURRENT_TIME

Syntax

CURRENT_TIME
CURRENT_TIME([precision])

Description

CURRENT_TIME and CURRENT_TIME() are synonyms for CURTIME().

See Also

  • Microseconds in MariaDB

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

CURRENT_TIMESTAMP

Syntax

CURRENT_TIMESTAMP
CURRENT_TIMESTAMP([precision])

Description

CURRENT_TIMESTAMP and CURRENT_TIMESTAMP() are synonyms for NOW().

See Also

  • Microseconds in MariaDB

  • The TIMESTAMP data type

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

CURTIME

Syntax

CURTIME([precision])

Description

Returns the current time as a value in HH:MM:SS or HHMMSS.uuuuuu format, depending on whether the function is used in a string or numeric context. The value is expressed in the current time zone.

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

Examples

SELECT CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 12:45:39  |
+-----------+

SELECT CURTIME() + 0;
+---------------+
| CURTIME() + 0 |
+---------------+
| 124545.000000 |
+---------------+

With precision:

SELECT CURTIME(2);
+-------------+
| CURTIME(2)  |
+-------------+
| 09:49:08.09 |
+-------------+

See Also

  • Microseconds in MariaDB

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

DATE FUNCTION

Syntax

DATE(expr)

Description

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

Examples

SELECT DATE('2013-07-18 12:21:32');
+-----------------------------+
| DATE('2013-07-18 12:21:32') |
+-----------------------------+
| 2013-07-18                  |
+-----------------------------+

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

DATE_ADD

Syntax

DATE_ADD(date,INTERVAL expr unit)

Description

Performs date arithmetic. The date argument specifies the starting date or datetime value. expr is an expression specifying the interval value to be added to the starting date. expr is a string; it may start with a "-" for negative intervals. unit is a keyword indicating the units in which the expression should be interpreted. See Date and Time Units for a complete list of permitted units.

The result type of DATE_ADD() is determined as follows:

  • if the first argument is of the type DATETIME, the function returns DATETIME ;

  • if the first argument is DATE and the interval uses HOUR or smaller units, the function returns DATETIME ;

  • if the first argument is DATE and the interval uses DAY or larger units, the function returns DATE ;

  • similarly, if the first argument is TIME and the interval uses DAY or smaller units the function returns TIME, if the interval uses anything larger, the function returns DATETIME ;

  • if the first argument isn't a temporal type, the function returns a string.

Examples

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

See Also

  • DATE_SUB

  • ADD_MONTHS

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

DATE_FORMAT

Syntax

DATE_FORMAT(date, format[, locale])

Description

Formats the date value according to the format string.

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

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

Option
Description

%a

Short weekday name in current locale (Variable lc_time_names).

%b

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

%c

Month with 1 or 2 digits.

%D

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

%d

Day with 2 digits.

%e

Day with 1 or 2 digits.

%f

Microseconds 6 digits.

%H

Hour with 2 digits between 00-23.

%h

Hour with 2 digits between 01-12.

%I

Hour with 2 digits between 01-12.

%i

Minute with 2 digits.

%j

Day of the year (001-366)

%k

Hour with 1 digits between 0-23.

%l

Hour with 1 digits between 1-12.

%M

Full month name in current locale (Variable lc_time_names).

%m

Month with 2 digits.

%p

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

%r

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

%S

Seconds with 2 digits.

%s

Seconds with 2 digits.

%T

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

%U

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

%u

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

%V

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

%v

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

%W

Full weekday name in current locale (Variable lc_time_names).

%w

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

%X

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

%x

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

%Y

Year with 4 digits.

%y

Year with 2 digits.

%Z

Timezone abbreviation. From MariaDB 11.3.0.

%z

Numeric timezone +hhmm or -hhmm presenting the hour and minute offset from UTC. From MariaDB 11.3.0.

%#

For str_to_date(), skip all numbers.

%.

For str_to_date(), skip all punctation characters.

%@

For str_to_date(), skip all alpha characters.

%%

A literal % character.

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

Examples

SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
+------------------------------------------------+
| DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y') |
+------------------------------------------------+
| Sunday October 2009                            |
+------------------------------------------------+

SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
+------------------------------------------------+
| DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s') |
+------------------------------------------------+
| 22:23:00                                       |
+------------------------------------------------+

SELECT DATE_FORMAT('1900-10-04 22:23:00', '%D %y %a %d %m %b %j');
+------------------------------------------------------------+
| DATE_FORMAT('1900-10-04 22:23:00', '%D %y %a %d %m %b %j') |
+------------------------------------------------------------+
| 4th 00 Thu 04 10 Oct 277                                   |
+------------------------------------------------------------+

SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H %k %I %r %T %S %w');
+------------------------------------------------------------+
| DATE_FORMAT('1997-10-04 22:23:00', '%H %k %I %r %T %S %w') |
+------------------------------------------------------------+
| 22 22 10 10:23:00 PM 22:23:00 00 6                         |
+------------------------------------------------------------+

SELECT DATE_FORMAT('1999-01-01', '%X %V');
+------------------------------------+
| DATE_FORMAT('1999-01-01', '%X %V') |
+------------------------------------+
| 1998 52                            |
+------------------------------------+

SELECT DATE_FORMAT('2006-06-00', '%d');
+---------------------------------+
| DATE_FORMAT('2006-06-00', '%d') |
+---------------------------------+
| 00                              |
+---------------------------------+

Optionally, the locale can be explicitly specified as the third DATE_FORMAT() argument. Doing so makes the function independent from the session settings, and the three argument version of DATE_FORMAT() can be used in virtual indexed and persistent generated-columns:

SELECT DATE_FORMAT('2006-01-01', '%W', 'el_GR');
+------------------------------------------+
| DATE_FORMAT('2006-01-01', '%W', 'el_GR') |
+------------------------------------------+
| Κυριακή                                  |
+------------------------------------------+

Timezone information from MariaDB 11.3:

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

See Also

  • STR_TO_DATE()

  • FROM_UNIXTIME()

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

DATE_SUB

Syntax

DATE_SUB(date,INTERVAL expr unit)

Description

Performs date arithmetic. The date argument specifies the starting date or datetime value. expr is an expression specifying the interval value to be subtracted from the starting date. expr is a string; it may start with a "-" for negative intervals. unit is a keyword indicating the units in which the expression should be interpreted. See Date and Time Units for a complete list of permitted units.

See also DATE_ADD().

Examples

SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
+-----------------------------------------+
| DATE_SUB('1998-01-02', INTERVAL 31 DAY) |
+-----------------------------------------+
| 1997-12-02                              |
+-----------------------------------------+
SELECT DATE_SUB('2005-01-01 00:00:00', INTERVAL '1 1:1:1' DAY_SECOND);
+----------------------------------------------------------------+
| DATE_SUB('2005-01-01 00:00:00', INTERVAL '1 1:1:1' DAY_SECOND) |
+----------------------------------------------------------------+
| 2004-12-30 22:58:59                                            |
+----------------------------------------------------------------+

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

DATEDIFF

Syntax

DATEDIFF(expr1,expr2)

Description

DATEDIFF() returns (expr1 – expr2) expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation.

Examples

SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
+----------------------------------------------+
| DATEDIFF('2007-12-31 23:59:59','2007-12-30') |
+----------------------------------------------+
|                                            1 |
+----------------------------------------------+

SELECT DATEDIFF('2010-11-30 23:59:59','2010-12-31');
+----------------------------------------------+
| DATEDIFF('2010-11-30 23:59:59','2010-12-31') |
+----------------------------------------------+
|                                          -31 |
+----------------------------------------------+
CREATE TABLE t1 (d DATETIME);
INSERT INTO t1 VALUES
    ("2007-01-30 21:31:07"),
    ("1983-10-15 06:42:51"),
    ("2011-04-21 12:34:56"),
    ("2011-10-30 06:31:41"),
    ("2011-01-30 14:03:25"),
    ("2004-10-07 11:19:34");
SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2011-05-23 10:56:05 |
+---------------------+

SELECT d, DATEDIFF(NOW(),d) FROM t1;
+---------------------+-------------------+
| d                   | DATEDIFF(NOW(),d) |
+---------------------+-------------------+
| 2007-01-30 21:31:07 |              1574 |
| 1983-10-15 06:42:51 |             10082 |
| 2011-04-21 12:34:56 |                32 |
| 2011-10-30 06:31:41 |              -160 |
| 2011-01-30 14:03:25 |               113 |
| 2004-10-07 11:19:34 |              2419 |
+---------------------+-------------------+

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

DAY

Syntax

DAY(date)

Description

DAY() is a synonym for DAYOFMONTH().

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

DAYNAME

Syntax

DAYNAME(date)

Description

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

Examples

SELECT DAYNAME('2007-02-03');
+-----------------------+
| DAYNAME('2007-02-03') |
+-----------------------+
| Saturday              |
+-----------------------+
CREATE TABLE t1 (d DATETIME);
INSERT INTO t1 VALUES
    ("2007-01-30 21:31:07"),
    ("1983-10-15 06:42:51"),
    ("2011-04-21 12:34:56"),
    ("2011-10-30 06:31:41"),
    ("2011-01-30 14:03:25"),
    ("2004-10-07 11:19:34");
SELECT d, DAYNAME(d) FROM t1;
+---------------------+------------+
| d                   | DAYNAME(d) |
+---------------------+------------+
| 2007-01-30 21:31:07 | Tuesday    |
| 1983-10-15 06:42:51 | Saturday   |
| 2011-04-21 12:34:56 | Thursday   |
| 2011-10-30 06:31:41 | Sunday     |
| 2011-01-30 14:03:25 | Sunday     |
| 2004-10-07 11:19:34 | Thursday   |
+---------------------+------------+

Changing the locale:

SET lc_time_names = 'fr_CA';

SELECT DAYNAME('2013-04-01');
+-----------------------+
| DAYNAME('2013-04-01') |
+-----------------------+
| lundi                 |
+-----------------------+

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

DAYOFMONTH

Syntax

DAYOFMONTH(date)

Description

Returns the day of the month for date, in the range 1 to 31, or 0 for dates such as '0000-00-00' or '2008-00-00' which have a zero day part.

DAY() is a synonym.

Examples

SELECT DAYOFMONTH('2007-02-03');
+--------------------------+
| DAYOFMONTH('2007-02-03') |
+--------------------------+
|                        3 |
+--------------------------+
CREATE TABLE t1 (d DATETIME);
INSERT INTO t1 VALUES
    ("2007-01-30 21:31:07"),
    ("1983-10-15 06:42:51"),
    ("2011-04-21 12:34:56"),
    ("2011-10-30 06:31:41"),
    ("2011-01-30 14:03:25"),
    ("2004-10-07 11:19:34");
SELECT d FROM t1 where DAYOFMONTH(d) = 30;
+---------------------+
| d                   |
+---------------------+
| 2007-01-30 21:31:07 |
| 2011-10-30 06:31:41 |
| 2011-01-30 14:03:25 |
+---------------------+

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

DAYOFWEEK

Syntax

DAYOFWEEK(date)

Description

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

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

Examples

SELECT DAYOFWEEK('2007-02-03');
+-------------------------+
| DAYOFWEEK('2007-02-03') |
+-------------------------+
|                       7 |
+-------------------------+
CREATE TABLE t1 (d DATETIME);
INSERT INTO t1 VALUES
    ("2007-01-30 21:31:07"),
    ("1983-10-15 06:42:51"),
    ("2011-04-21 12:34:56"),
    ("2011-10-30 06:31:41"),
    ("2011-01-30 14:03:25"),
    ("2004-10-07 11:19:34");
SELECT d, DAYNAME(d), DAYOFWEEK(d), WEEKDAY(d) from t1;
+---------------------+------------+--------------+------------+
| d                   | DAYNAME(d) | DAYOFWEEK(d) | WEEKDAY(d) |
+---------------------+------------+--------------+------------+
| 2007-01-30 21:31:07 | Tuesday    |            3 |          1 |
| 1983-10-15 06:42:51 | Saturday   |            7 |          5 |
| 2011-04-21 12:34:56 | Thursday   |            5 |          3 |
| 2011-10-30 06:31:41 | Sunday     |            1 |          6 |
| 2011-01-30 14:03:25 | Sunday     |            1 |          6 |
| 2004-10-07 11:19:34 | Thursday   |            5 |          3 |
+---------------------+------------+--------------+------------+

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

DAYOFYEAR

Syntax

DAYOFYEAR(date)

Description

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

Examples

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

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

EXTRACT

Syntax

EXTRACT(unit FROM date)

Description

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

HOUR() is not a standard SQL function, so continues to adhere to the old behavior inherited from MySQL.

Examples

SELECT EXTRACT(YEAR FROM '2009-07-02');
+---------------------------------+
| EXTRACT(YEAR FROM '2009-07-02') |
+---------------------------------+
|                            2009 |
+---------------------------------+

SELECT EXTRACT(YEAR_MONTH FROM '2009-07-02 01:02:03');
+------------------------------------------------+
| EXTRACT(YEAR_MONTH FROM '2009-07-02 01:02:03') |
+------------------------------------------------+
|                                         200907 |
+------------------------------------------------+

SELECT EXTRACT(DAY_MINUTE FROM '2009-07-02 01:02:03');
+------------------------------------------------+
| EXTRACT(DAY_MINUTE FROM '2009-07-02 01:02:03') |
+------------------------------------------------+
|                                          20102 |
+------------------------------------------------+

SELECT EXTRACT(MICROSECOND FROM '2003-01-02 10:30:00.000123');
+--------------------------------------------------------+
| EXTRACT(MICROSECOND FROM '2003-01-02 10:30:00.000123') |
+--------------------------------------------------------+
|                                                    123 |
+--------------------------------------------------------+

EXTRACT (HOUR FROM...) returns a value from 0 to 23, as per the SQL standard. HOUR is not a standard function, so continues to adhere to the old behaviour inherited from MySQL.

SELECT EXTRACT(HOUR FROM '26:30:00'), HOUR('26:30:00');
+-------------------------------+------------------+
| EXTRACT(HOUR FROM '26:30:00') | HOUR('26:30:00') |
+-------------------------------+------------------+
|                             2 |               26 |
+-------------------------------+------------------+

See Also

  • Date and Time Units

  • Date and Time Literals

  • HOUR()

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

FORMAT_PICO_TIME

FORMAT_PICO_TIME is available from MariaDB 11.0.2.

Syntax

FORMAT_PICO_TIME(time_val)

Description

Given a time in picoseconds, returns a human-readable time value and unit indicator. Resulting unit is dependent on the length of the argument, and can be:

  • ps - picoseconds

  • ns - nanoseconds

  • us - microseconds

  • ms - milliseconds

  • s - seconds

  • min - minutes

  • h - hours

  • d - days

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

Returns NULL if the argument is NULL.

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

  • Represents minutes as min rather than m.

  • Does not represent weeks.

Examples

SELECT
    FORMAT_PICO_TIME(43) AS ps,
    FORMAT_PICO_TIME(4321) AS ns, 
    FORMAT_PICO_TIME(43211234) AS us,
    FORMAT_PICO_TIME(432112344321) AS ms,
    FORMAT_PICO_TIME(43211234432123) AS s,
    FORMAT_PICO_TIME(432112344321234) AS m,
    FORMAT_PICO_TIME(4321123443212345) AS h,
    FORMAT_PICO_TIME(432112344321234545) AS d;
+--------+---------+----------+-----------+---------+----------+--------+--------+
| ps     | ns      | us       | ms        | s       | m        | h      | d      |
+--------+---------+----------+-----------+---------+----------+--------+--------+
|  43 ps | 4.32 ns | 43.21 us | 432.11 ms | 43.21 s | 7.20 min | 1.20 h | 5.00 d |
+--------+---------+----------+-----------+---------+----------+--------+--------+

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

FROM_DAYS

Syntax

FROM_DAYS(N)

Description

Given a day number N, returns a DATE value. The day count is based on the number of days from the start of the standard calendar (0000-00-00).

The function is not designed for use with dates before the advent of the Gregorian calendar in October 1582. Results will not be reliable since it doesn't account for the lost days when the calendar changed from the Julian calendar.

This is the converse of the TO_DAYS() function.

Examples

SELECT FROM_DAYS(730669);
+-------------------+
| FROM_DAYS(730669) |
+-------------------+
| 2000-07-03        |
+-------------------+

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

FROM_UNIXTIME

Syntax

FROM_UNIXTIME(unix_timestamp)
FROM_UNIXTIME(unix_timestamp,format)

Description

Converts the number of seconds from the epoch (1970-01-01 00:00:00 UTC) to aTIMESTAMP value, the opposite of what UNIX_TIMESTAMP() is doing. Returns NULL if the result would be outside of the valid range of TIMESTAMP values.

If format is given, the result is exactly equivalent to

DATE_FORMAT(FROM_UNIXTIME(unix_timestamp), format)

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

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

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

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

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

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

Option
Description

%a

Short weekday name in current locale (Variable lc_time_names).

%b

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

%c

Month with 1 or 2 digits.

%D

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

%d

Day with 2 digits.

%e

Day with 1 or 2 digits.

%f

Microseconds 6 digits.

%H

Hour with 2 digits between 00-23.

%h

Hour with 2 digits between 01-12.

%I

Hour with 2 digits between 01-12.

%i

Minute with 2 digits.

%j

Day of the year (001-366)

%k

Hour with 1 digits between 0-23.

%l

Hour with 1 digits between 1-12.

%M

Full month name in current locale (Variable lc_time_names).

%m

Month with 2 digits.

%p

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

%r

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

%S

Seconds with 2 digits.

%s

Seconds with 2 digits.

%T

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

%U

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

%u

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

%V

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

%v

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

%W

Full weekday name in current locale (Variable lc_time_names).

%w

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

%X

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

%x

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

%Y

Year with 4 digits.

%y

Year with 2 digits.

%#

For str_to_date(), skip all numbers.

%.

For str_to_date(), skip all punctation characters.

%@

For str_to_date(), skip all alpha characters.

%%

A literal % character.

Performance Considerations

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

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

Examples

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

See Also

  • UNIX_TIMESTAMP()

  • DATE_FORMAT()

  • STR_TO_DATE()

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

GET_FORMAT

Syntax

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

Description

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

Possible result formats are:

Function Call
Result Format

GET_FORMAT(DATE,'EUR')

'%d.%m.%Y'

GET_FORMAT(DATE,'USA')

'%m.%d.%Y'

GET_FORMAT(DATE,'JIS')

'%Y-%m-%d'

GET_FORMAT(DATE,'ISO')

'%Y-%m-%d'

GET_FORMAT(DATE,'INTERNAL')

'%Y%m%d'

GET_FORMAT(DATETIME,'EUR')

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

GET_FORMAT(DATETIME,'USA')

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

GET_FORMAT(DATETIME,'JIS')

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

GET_FORMAT(DATETIME,'ISO')

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

GET_FORMAT(DATETIME,'INTERNAL')

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

GET_FORMAT(TIME,'EUR')

'%H.%i.%s'

GET_FORMAT(TIME,'USA')

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

GET_FORMAT(TIME,'JIS')

'%H:%i:%s'

GET_FORMAT(TIME,'ISO')

'%H:%i:%s'

GET_FORMAT(TIME,'INTERNAL')

'%H%i%s'

Examples

Obtaining the string matching to the standard European date format:

SELECT GET_FORMAT(DATE, 'EUR');
+-------------------------+
| GET_FORMAT(DATE, 'EUR') |
+-------------------------+
| %d.%m.%Y                |
+-------------------------+

Using the same string to format a date:

SELECT DATE_FORMAT('2003-10-03',GET_FORMAT(DATE,'EUR'));
+--------------------------------------------------+
| DATE_FORMAT('2003-10-03',GET_FORMAT(DATE,'EUR')) |
+--------------------------------------------------+
| 03.10.2003                                       |
+--------------------------------------------------+

SELECT STR_TO_DATE('10.31.2003',GET_FORMAT(DATE,'USA'));
+--------------------------------------------------+
| STR_TO_DATE('10.31.2003',GET_FORMAT(DATE,'USA')) |
+--------------------------------------------------+
| 2003-10-31                                       |
+--------------------------------------------------+

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

HOUR

Syntax

HOUR(time)

Description

Returns the hour for time. The range of the return value is 0 to 23 for time-of-day values. However, the range of TIME values actually is much larger, so HOUR can return values greater than 23.

The return value is always positive, even if a negative TIME value is provided.

Examples

SELECT HOUR('10:05:03');
+------------------+
| HOUR('10:05:03') |
+------------------+
|               10 |
+------------------+

SELECT HOUR('272:59:59');
+-------------------+
| HOUR('272:59:59') |
+-------------------+
|               272 |
+-------------------+

See Also

  • Date and Time Units

  • Date and Time Literals

  • EXTRACT()

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

LAST_DAY

Syntax

LAST_DAY(date)

Description

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

Examples

SELECT LAST_DAY('2003-02-05');
+------------------------+
| LAST_DAY('2003-02-05') |
+------------------------+
| 2003-02-28             |
+------------------------+

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

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

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

Warning (Code 1292): Incorrect datetime value: '2003-03-32'

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

LOCALTIME

Syntax

LOCALTIME
LOCALTIME([precision])

Description

LOCALTIME and LOCALTIME() are synonyms for NOW().

See Also

  • Microseconds in MariaDB

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

LOCALTIMESTAMP

Syntax

LOCALTIMESTAMP
LOCALTIMESTAMP([precision])

Description

LOCALTIMESTAMP and LOCALTIMESTAMP() are synonyms for NOW().

See Also

  • Microseconds in MariaDB

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

MAKEDATE

Syntax

MAKEDATE(year,dayofyear)

Description

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

Examples

SELECT MAKEDATE(2011,31), MAKEDATE(2011,32);
+-------------------+-------------------+
| MAKEDATE(2011,31) | MAKEDATE(2011,32) |
+-------------------+-------------------+
| 2011-01-31        | 2011-02-01        |
+-------------------+-------------------+

2012 is a leap year:

SELECT MAKEDATE(2011,365), MAKEDATE(2012,365);
+--------------------+--------------------+
| MAKEDATE(2011,365) | MAKEDATE(2012,365) |
+--------------------+--------------------+
| 2011-12-31         | 2012-12-30         |
+--------------------+--------------------+

SELECT MAKEDATE(2011,366), MAKEDATE(2012,366);
+--------------------+--------------------+
| MAKEDATE(2011,366) | MAKEDATE(2012,366) |
+--------------------+--------------------+
| 2012-01-01         | 2012-12-31         |
+--------------------+--------------------+

SELECT MAKEDATE(2011,0);
+------------------+
| MAKEDATE(2011,0) |
+------------------+
| NULL             |
+------------------+

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

MAKETIME

Syntax

MAKETIME(hour,minute,second)

Description

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

If minute or second are out of the range 0 to 60, NULL is returned. The hour can be in the range -838 to 838, outside of which the value is truncated with a warning.

Examples

SELECT MAKETIME(13,57,33);
+--------------------+
| MAKETIME(13,57,33) |
+--------------------+
| 13:57:33           |
+--------------------+

SELECT MAKETIME(-13,57,33);
+---------------------+
| MAKETIME(-13,57,33) |
+---------------------+
| -13:57:33           |
+---------------------+

SELECT MAKETIME(13,67,33);
+--------------------+
| MAKETIME(13,67,33) |
+--------------------+
| NULL               |
+--------------------+

SELECT MAKETIME(-1000,57,33);
+-----------------------+
| MAKETIME(-1000,57,33) |
+-----------------------+
| -838:59:59            |
+-----------------------+
1 row in set, 1 warning (0.00 sec)

SHOW WARNINGS;
+---------+------+-----------------------------------------------+
| Level   | Code | Message                                       |
+---------+------+-----------------------------------------------+
| Warning | 1292 | Truncated incorrect time value: '-1000:57:33' |
+---------+------+-----------------------------------------------+

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

MICROSECOND

Syntax

MICROSECOND(expr)

Description

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

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

Examples

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

See Also

  • Microseconds in MariaDB

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

Microseconds in MariaDB

The TIME, DATETIME, and TIMESTAMP types, along with the temporal functions, CAST and dynamic columns, support microseconds. The datetime precision of a column can be specified when creating the table with CREATE TABLE, for example:

CREATE TABLE example(
  col_microsec DATETIME(6),
  col_millisec TIME(3)
);

Generally, the precision can be specified for any TIME, DATETIME, or TIMESTAMP column, in parentheses, after the type name. The datetime precision specifies number of digits after the decimal dot and can be any integer number from 0 to 6. If no precision is specified it is assumed to be 0, for backward compatibility reasons.

A datetime precision can be specified wherever a type name is used. For example:

  • when declaring arguments of stored routines;

  • when specifying a return type of a stored function;

  • when declaring variables;

  • in a CAST function.

CREATE FUNCTION example(x DATETIME(5)) RETURNS TIME(4)
BEGIN
 DECLARE y TIMESTAMP(6);
 RETURN CAST(x AS time(2));
END;

%f is used as the formatting option for microseconds in the STR_TO_DATE, DATE_FORMAT and FROM_UNIXTIME functions, for example:

SELECT STR_TO_DATE('20200809 020917076','%Y%m%d %H%i%s%f');
+-----------------------------------------------------+
| STR_TO_DATE('20200809 020917076','%Y%m%d %H%i%s%f') |
+-----------------------------------------------------+
| 2020-08-09 02:09:17.076000                          |
+-----------------------------------------------------+

Additional Information

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

  • The INFORMATION_SCHEMA.COLUMNS table has a new column DATETIME_PRECISION

  • NOW(), CURTIME(), UTC_TIMESTAMP(), UTC_TIME(), CURRENT_TIME(), CURRENT_TIMESTAMP(), LOCALTIME() and LOCALTIMESTAMP() accept datetime precision as an optional argument. For example:

SELECT CURTIME(4);
--> 10:11:12.3456
  • TIME_TO_SEC() and UNIX_TIMESTAMP() preserve microseconds of the argument. These functions will return a decimal number if the result non-zero datetime precision and an integer otherwise (for backward compatibility).

SELECT TIME_TO_SEC('10:10:10.12345');
--> 36610.12345
  • Current versions of this patch fix a bug in the following optimization: In certain queries with DISTINCT MariaDB can ignore this clause if it can prove that all result rows are unique anyway, for example, when a primary key is compared with a constant. Sometimes this optimization was applied incorrectly, though — for example, when comparing a string with a date constant. This is now fixed.

  • DATE_ADD() and DATE_SUB() functions can now take a TIME expression as an argument (not just DATETIME as before).

SELECT TIME('10:10:10') + INTERVAL 100 MICROSECOND;
--> 10:10:10.000100
  • The event_time field in the mysql.general_log table and the start_time, query_time, and lock_time fields in the mysql.slow_log table now store values with microsecond precision.

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

  • When a DATETIME value is compared to a TIME value, the latter is treated as a full datetime with a zero date part, similar to comparing DATE to a DATETIME, or to comparing DECIMAL numbers. Earlier versions of MariaDB used to compare only the time part of both operands in such a case.

  • In MariaDB, an extra column TIME_MS has been added to the INFORMATION_SCHEMA.PROCESSLIST table, as well as to the output of SHOW FULL PROCESSLIST.

Note: When you convert a temporal value to a value with a smaller precision, it will be truncated, not rounded. This is done to guarantee that the date part is not changed. For example:

SELECT CAST('2009-12-31 23:59:59.998877' AS DATETIME(3));
-> 2009-12-31 23:59:59.998

See Also

  • Data Type Storage Requirements

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

MINUTE

Syntax

MINUTE(time)

Description

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

Examples

SELECT MINUTE('2013-08-03 11:04:03');
+-------------------------------+
| MINUTE('2013-08-03 11:04:03') |
+-------------------------------+
|                             4 |
+-------------------------------+

 SELECT MINUTE ('23:12:50');
+---------------------+
| MINUTE ('23:12:50') |
+---------------------+
|                  12 |
+---------------------+

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

MONTH

Syntax

MONTH(date)

Description

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

Examples

SELECT MONTH('2019-01-03');
+---------------------+
| MONTH('2019-01-03') |
+---------------------+
|                   1 |
+---------------------+

SELECT MONTH('2019-00-03');
+---------------------+
| MONTH('2019-00-03') |
+---------------------+
|                   0 |
+---------------------+

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

MONTHNAME

Syntax

MONTHNAME(date)

Description

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

Examples

SELECT MONTHNAME('2019-02-03');
+-------------------------+
| MONTHNAME('2019-02-03') |
+-------------------------+
| February                |
+-------------------------+

Changing the locale:

SET lc_time_names = 'fr_CA';

SELECT MONTHNAME('2019-05-21');
+-------------------------+
| MONTHNAME('2019-05-21') |
+-------------------------+
| mai                     |
+-------------------------+

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

NOW

Syntax

NOW([precision])
CURRENT_TIMESTAMP
CURRENT_TIMESTAMP([precision])
LOCALTIME, LOCALTIME([precision])
LOCALTIMESTAMP
LOCALTIMESTAMP([precision])

Description

Returns the current date and time as a value in YYYY-MM-DD HH:MM:SS or YYYYMMDDHHMMSS.uuuuuu format, depending on whether the function is used in a string or numeric context. The value is expressed in the current time zone.

MariaDB starting with 11.7

These functions return SQL standard compliant types:

  • NOW() and CURRENT_TIMESTAMP() return a TIMESTAMP value (analogous to the standard type TIMESTAMP WITH LOCAL TIME ZONE) which corresponds to the current point in time and is unambiguous around DST changes.

  • LOCALTIMESTAMP returns a DATETIME value (analogous to the standard type TIMESTAMP WITHOUT TIME ZONE). Storing its result in a TIMESTAMP column can result in a data loss around DST changes.

These functions do not return SQL standard compliant types:

  • NOW()

  • CURRENT_TIMESTAMP()

  • LOCALTIMESTAMP

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

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

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

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

Examples

SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2010-03-27 13:13:25 |
+---------------------+

SELECT NOW() + 0;
+-----------------------+
| NOW() + 0             |
+-----------------------+
| 20100327131329.000000 |
+-----------------------+

With precision:

SELECT CURRENT_TIMESTAMP(2);
+------------------------+
| CURRENT_TIMESTAMP(2)   |
+------------------------+
| 2018-07-10 09:47:26.24 |
+------------------------+

Used as a default TIMESTAMP:

CREATE TABLE t (createdTS TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP);
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='test'
  AND COLUMN_NAME LIKE '%ts%'\G
*************************** 1. row ***************************
           TABLE_CATALOG: def
            TABLE_SCHEMA: test
              TABLE_NAME: t
             COLUMN_NAME: ts
        ORDINAL_POSITION: 1
          COLUMN_DEFAULT: current_timestamp()
...

See Also

  • Microseconds in MariaDB

  • timestamp server system variable

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

PERIOD_ADD

Syntax

PERIOD_ADD(P,N)

Description

Adds N months to period P. P is in the format YYMM or YYYYMM, and is not a date value. If P contains a two-digit year, values from 00 to 69 are converted to from 2000 to 2069, while values from 70 are converted to 1970 upwards.

Returns a value in the format YYYYMM.

Examples

SELECT PERIOD_ADD(200801,2);
+----------------------+
| PERIOD_ADD(200801,2) |
+----------------------+
|               200803 |
+----------------------+

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

SELECT PERIOD_ADD(7010,2);
+--------------------+
| PERIOD_ADD(7010,2) |
+--------------------+
|             197012 |
+--------------------+

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

PERIOD_DIFF

Syntax

PERIOD_DIFF(P1,P2)

Description

Returns the number of months between periods P1 and P2. P1 and P2 can be in the format YYMM or YYYYMM, and are not date values.

If P1 or P2 contains a two-digit year, values from 00 to 69 are converted to from 2000 to 2069, while values from 70 are converted to 1970 upwards.

Examples

SELECT PERIOD_DIFF(200802,200703);
+----------------------------+
| PERIOD_DIFF(200802,200703) |
+----------------------------+
|                         11 |
+----------------------------+

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

SELECT PERIOD_DIFF(7002,6803);
+------------------------+
| PERIOD_DIFF(7002,6803) |
+------------------------+
|                  -1177 |
+------------------------+

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

QUARTER

Syntax

QUARTER(date)

Description

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

Examples

SELECT QUARTER('2008-04-01');
+-----------------------+
| QUARTER('2008-04-01') |
+-----------------------+
|                     2 |
+-----------------------+

SELECT QUARTER('2019-00-01');
+-----------------------+
| QUARTER('2019-00-01') |
+-----------------------+
|                     0 |
+-----------------------+

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

SEC_TO_TIME

Syntax

SEC_TO_TIME(seconds)

Description

Returns the seconds argument, converted to hours, minutes, and seconds, as a TIME value. The range of the result is constrained to that of the 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.

Examples

SELECT SEC_TO_TIME(12414);
+--------------------+
| SEC_TO_TIME(12414) |
+--------------------+
| 03:26:54           |
+--------------------+

SELECT SEC_TO_TIME(12414)+0;
+----------------------+
| SEC_TO_TIME(12414)+0 |
+----------------------+
|                32654 |
+----------------------+

SELECT SEC_TO_TIME(9999999);
+----------------------+
| SEC_TO_TIME(9999999) |
+----------------------+
| 838:59:59            |
+----------------------+
1 row in set, 1 warning (0.00 sec)

SHOW WARNINGS;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1292 | Truncated incorrect time value: '9999999' |
+---------+------+-------------------------------------------+

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

SECOND

Syntax

SECOND(time)

Description

Returns the second for a given time (which can include microseconds), in the range 0 to 59, or NULL if not given a valid time value.

Examples

SELECT SECOND('10:05:03');
+--------------------+
| SECOND('10:05:03') |
+--------------------+
|                  3 |
+--------------------+

SELECT SECOND('10:05:01.999999');
+---------------------------+
| SECOND('10:05:01.999999') |
+---------------------------+
|                         1 |
+---------------------------+

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

STR_TO_DATE

Syntax

STR_TO_DATE(str,format)

Description

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

The date, time, or datetime values contained in str should be given in the format indicated by format. If str contains an illegal date, time, or datetime value, STR_TO_DATE() returns NULL. An illegal value also produces a warning.

Under specific SQL_MODE settings an error may also be generated if the str isn't a valid date:

  • ALLOW_INVALID_DATES

  • NO_ZERO_DATE

  • NO_ZERO_IN_DATE

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

Option
Description

%a

Short weekday name in current locale (Variable lc_time_names).

%b

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

%c

Month with 1 or 2 digits.

%D

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

%d

Day with 2 digits.

%e

Day with 1 or 2 digits.

%f

Microseconds 6 digits.

%H

Hour with 2 digits between 00-23.

%h

Hour with 2 digits between 01-12.

%I

Hour with 2 digits between 01-12.

%i

Minute with 2 digits.

%j

Day of the year (001-366)

%k

Hour with 1 digits between 0-23.

%l

Hour with 1 digits between 1-12.

%M

Full month name in current locale (Variable lc_time_names).

%m

Month with 2 digits.

%p

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

%r

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

%S

Seconds with 2 digits.

%s

Seconds with 2 digits.

%T

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

%U

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

%u

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

%V

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

%v

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

%W

Full weekday name in current locale (Variable lc_time_names).

%w

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

%X

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

%x

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

%Y

Year with 4 digits.

%y

Year with 2 digits.

%#

For str_to_date(), skip all numbers.

%.

For str_to_date(), skip all punctation characters.

%@

For str_to_date(), skip all alpha characters.

%%

A literal % character.

Examples

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

See Also

  • DATE_FORMAT()

  • FROM_UNIXTIME()

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

SUBDATE

Syntax

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

Description

When invoked with the INTERVAL form of the second argument, SUBDATE() is a synonym for DATE_SUB(). See Date and Time Units for a complete list of permitted units.

The second form allows the use of an integer value for days. In such cases, it is interpreted as the number of days to be subtracted from the date or datetime expression expr.

Examples

SELECT DATE_SUB('2008-01-02', INTERVAL 31 DAY);
+-----------------------------------------+
| DATE_SUB('2008-01-02', INTERVAL 31 DAY) |
+-----------------------------------------+
| 2007-12-02                              |
+-----------------------------------------+

SELECT SUBDATE('2008-01-02', INTERVAL 31 DAY);
+----------------------------------------+
| SUBDATE('2008-01-02', INTERVAL 31 DAY) |
+----------------------------------------+
| 2007-12-02                             |
+----------------------------------------+
SELECT SUBDATE('2008-01-02 12:00:00', 31);
+------------------------------------+
| SUBDATE('2008-01-02 12:00:00', 31) |
+------------------------------------+
| 2007-12-02 12:00:00                |
+------------------------------------+
CREATE TABLE t1 (d DATETIME);
INSERT INTO t1 VALUES
    ("2007-01-30 21:31:07"),
    ("1983-10-15 06:42:51"),
    ("2011-04-21 12:34:56"),
    ("2011-10-30 06:31:41"),
    ("2011-01-30 14:03:25"),
    ("2004-10-07 11:19:34");
SELECT d, SUBDATE(d, 10) from t1;
+---------------------+---------------------+
| d                   | SUBDATE(d, 10)      |
+---------------------+---------------------+
| 2007-01-30 21:31:07 | 2007-01-20 21:31:07 |
| 1983-10-15 06:42:51 | 1983-10-05 06:42:51 |
| 2011-04-21 12:34:56 | 2011-04-11 12:34:56 |
| 2011-10-30 06:31:41 | 2011-10-20 06:31:41 |
| 2011-01-30 14:03:25 | 2011-01-20 14:03:25 |
| 2004-10-07 11:19:34 | 2004-09-27 11:19:34 |
+---------------------+---------------------+

SELECT d, SUBDATE(d, INTERVAL 10 MINUTE) from t1;
+---------------------+--------------------------------+
| d                   | SUBDATE(d, INTERVAL 10 MINUTE) |
+---------------------+--------------------------------+
| 2007-01-30 21:31:07 | 2007-01-30 21:21:07            |
| 1983-10-15 06:42:51 | 1983-10-15 06:32:51            |
| 2011-04-21 12:34:56 | 2011-04-21 12:24:56            |
| 2011-10-30 06:31:41 | 2011-10-30 06:21:41            |
| 2011-01-30 14:03:25 | 2011-01-30 13:53:25            |
| 2004-10-07 11:19:34 | 2004-10-07 11:09:34            |
+---------------------+--------------------------------+

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

SUBTIME

Syntax

SUBTIME(expr1,expr2)

Description

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

Examples

SELECT SUBTIME('2007-12-31 23:59:59.999999','1 1:1:1.000002');
+--------------------------------------------------------+
| SUBTIME('2007-12-31 23:59:59.999999','1 1:1:1.000002') |
+--------------------------------------------------------+
| 2007-12-30 22:58:58.999997                             |
+--------------------------------------------------------+

SELECT SUBTIME('01:00:00.999999', '02:00:00.999998');
+-----------------------------------------------+
| SUBTIME('01:00:00.999999', '02:00:00.999998') |
+-----------------------------------------------+
| -00:59:59.999999                              |
+-----------------------------------------------+

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

SYSDATE

Syntax

SYSDATE([precision])

Description

Returns the current date and time as a value in YYYY-MM-DD HH:MM:SS or YYYYMMDDHHMMSS.uuuuuu format, depending on whether the function is used in a string or numeric context.

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

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

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

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

Examples

Difference between NOW() and SYSDATE():

SELECT NOW(), SLEEP(2), NOW();
+---------------------+----------+---------------------+
| NOW()               | SLEEP(2) | NOW()               |
+---------------------+----------+---------------------+
| 2010-03-27 13:23:40 |        0 | 2010-03-27 13:23:40 |
+---------------------+----------+---------------------+

SELECT SYSDATE(), SLEEP(2), SYSDATE();
+---------------------+----------+---------------------+
| SYSDATE()           | SLEEP(2) | SYSDATE()           |
+---------------------+----------+---------------------+
| 2010-03-27 13:23:52 |        0 | 2010-03-27 13:23:54 |
+---------------------+----------+---------------------+

With precision:

SELECT SYSDATE(4);
+--------------------------+
| SYSDATE(4)               |
+--------------------------+
| 2018-07-10 10:17:13.1689 |
+--------------------------+

See Also

  • Microseconds in MariaDB

  • timestamp server system variable

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

TIME

Syntax

TIME(expr)

Description

Extracts the time part of the time or datetime expression expr and returns it as a string.

Examples

SELECT TIME('2003-12-31 01:02:03');
+-----------------------------+
| TIME('2003-12-31 01:02:03') |
+-----------------------------+
| 01:02:03                    |
+-----------------------------+

SELECT TIME('2003-12-31 01:02:03.000123');
+------------------------------------+
| TIME('2003-12-31 01:02:03.000123') |
+------------------------------------+
| 01:02:03.000123                    |
+------------------------------------+

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

TIME_FORMAT

Syntax

TIME_FORMAT(time,format)

Description

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

Examples

SELECT TIME_FORMAT('100:00:00', '%H %k %h %I %l');
+--------------------------------------------+
| TIME_FORMAT('100:00:00', '%H %k %h %I %l') |
+--------------------------------------------+
| 100 100 04 04 4                            |
+--------------------------------------------+

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

TIME_TO_SEC

Syntax

TIME_TO_SEC(time)

Description

Returns the time argument, converted to seconds.

The value returned by TIME_TO_SEC is of type DOUBLE. The returned value preserves microseconds of the argument. See also Microseconds in MariaDB.

Examples

SELECT TIME_TO_SEC('22:23:00');
+-------------------------+
| TIME_TO_SEC('22:23:00') |
+-------------------------+
|                   80580 |
+-------------------------+
SELECT TIME_TO_SEC('00:39:38');
+-------------------------+
| TIME_TO_SEC('00:39:38') |
+-------------------------+
|                    2378 |
+-------------------------+
SELECT TIME_TO_SEC('09:12:55.2355');
+------------------------------+
| TIME_TO_SEC('09:12:55.2355') |
+------------------------------+
|                   33175.2355 |
+------------------------------+
1 row in set (0.000 sec)

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

TIMEDIFF

Syntax

TIMEDIFF(expr1,expr2)

Description

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

Examples

SELECT TIMEDIFF('2000:01:01 00:00:00', '2000:01:01 00:00:00.000001');
+---------------------------------------------------------------+
| TIMEDIFF('2000:01:01 00:00:00', '2000:01:01 00:00:00.000001') |
+---------------------------------------------------------------+
| -00:00:00.000001                                              |
+---------------------------------------------------------------+

SELECT TIMEDIFF('2008-12-31 23:59:59.000001', '2008-12-30 01:01:01.000002');
+----------------------------------------------------------------------+
| TIMEDIFF('2008-12-31 23:59:59.000001', '2008-12-30 01:01:01.000002') |
+----------------------------------------------------------------------+
| 46:58:57.999999                                                      |
+----------------------------------------------------------------------+

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

TIMESTAMP

For the timestamp data type, see TIMESTAMP.

Syntax

TIMESTAMP(expr), TIMESTAMP(expr1,expr2)

Description

With a single argument, this function returns the date or datetime expression expr as a datetime value. With two arguments, it adds the time expression expr2 to the date or datetime expression expr1 and returns the result as a datetime value.

Examples

SELECT TIMESTAMP('2003-12-31');
+-------------------------+
| TIMESTAMP('2003-12-31') |
+-------------------------+
| 2003-12-31 00:00:00     |
+-------------------------+

SELECT TIMESTAMP('2003-12-31 12:00:00','6:30:00');
+--------------------------------------------+
| TIMESTAMP('2003-12-31 12:00:00','6:30:00') |
+--------------------------------------------+
| 2003-12-31 18:30:00                        |
+--------------------------------------------+

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

TIMESTAMPADD

Syntax

TIMESTAMPADD(unit,interval,datetime_expr)

Description

Adds the integer expression interval to the date or datetime expression datetime_expr. The unit for interval is given by the unit argument, which should be one of the following values: MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.

The unit value may be specified using one of keywords as shown, or with a prefix of SQL_TSI_. For example, DAY and SQL_TSI_DAY both are allowed.

Examples

SELECT TIMESTAMPADD(MINUTE,1,'2003-01-02');
+-------------------------------------+
| TIMESTAMPADD(MINUTE,1,'2003-01-02') |
+-------------------------------------+
| 2003-01-02 00:01:00                 |
+-------------------------------------+

SELECT TIMESTAMPADD(WEEK,1,'2003-01-02');
+-----------------------------------+
| TIMESTAMPADD(WEEK,1,'2003-01-02') |
+-----------------------------------+
| 2003-01-09                        |
+-----------------------------------+

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

TIMESTAMPDIFF

Syntax

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

Description

Returns datetime_expr2 - datetime_expr1, where datetime_expr1 anddatetime_expr2 are date or datetime expressions. One expression may be a date and the other a datetime; a date value is treated as a datetime having the time part '00:00:00' where necessary. The unit for the result (an integer) is given by the unit argument. The legal values for unit are the same as those listed in the description of the TIMESTAMPADD() function, that is, MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.

TIMESTAMPDIFF can also be used to calculate age.

Examples

SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');
+------------------------------------------------+
| TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01') |
+------------------------------------------------+
|                                              3 |
+------------------------------------------------+

SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01');
+-----------------------------------------------+
| TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01') |
+-----------------------------------------------+
|                                            -1 |
+-----------------------------------------------+

SELECT TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55');
+----------------------------------------------------------+
| TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55') |
+----------------------------------------------------------+
|                                                   128885 |
+----------------------------------------------------------+

Calculating age:

SELECT CURDATE();
+------------+
| CURDATE()  |
+------------+
| 2019-05-27 |
+------------+

SELECT TIMESTAMPDIFF(YEAR, '1971-06-06', CURDATE()) AS age;
+------+
| age  |
+------+
|   47 |
+------+

SELECT TIMESTAMPDIFF(YEAR, '1971-05-06', CURDATE()) AS age;
+------+
| age  |
+------+
|   48 |
+------+

Age as of 2014-08-02:

SELECT name, date_of_birth, TIMESTAMPDIFF(YEAR,date_of_birth,'2014-08-02') AS age 
  FROM student_details;
+---------+---------------+------+
| name    | date_of_birth | age  |
+---------+---------------+------+
| Chun    | 1993-12-31    |   20 |
| Esben   | 1946-01-01    |   68 |
| Kaolin  | 1996-07-16    |   18 |
| Tatiana | 1988-04-13    |   26 |
+---------+---------------+------+

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

TO_DAYS

Syntax

TO_DAYS(date)

Description

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

The function is not designed for use with dates before the advent of the Gregorian calendar in October 1582. Results will not be reliable since it doesn't account for the lost days when the calendar changed from the Julian calendar.

This is the converse of the FROM_DAYS() function.

Examples

SELECT TO_DAYS('2007-10-07');
+-----------------------+
| TO_DAYS('2007-10-07') |
+-----------------------+
|                733321 |
+-----------------------+

SELECT TO_DAYS('0000-01-01');
+-----------------------+
| TO_DAYS('0000-01-01') |
+-----------------------+
|                     1 |
+-----------------------+

SELECT TO_DAYS(950501);
+-----------------+
| TO_DAYS(950501) |
+-----------------+
|          728779 |
+-----------------+

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

TO_SECONDS

Syntax

TO_SECONDS(expr)

Description

Returns the number of seconds from year 0 till expr, or NULL if expr is not a valid date or datetime.

Examples

SELECT TO_SECONDS('2013-06-13');
+--------------------------+
| TO_SECONDS('2013-06-13') |
+--------------------------+
|              63538300800 |
+--------------------------+

SELECT TO_SECONDS('2013-06-13 21:45:13');
+-----------------------------------+
| TO_SECONDS('2013-06-13 21:45:13') |
+-----------------------------------+
|                       63538379113 |
+-----------------------------------+

SELECT TO_SECONDS(NOW());
+-------------------+
| TO_SECONDS(NOW()) |
+-------------------+
|       63543530875 |
+-------------------+

SELECT TO_SECONDS(20130513);
+----------------------+
| TO_SECONDS(20130513) |
+----------------------+
|          63535622400 |
+----------------------+
1 row in set (0.00 sec)

SELECT TO_SECONDS(130513);
+--------------------+
| TO_SECONDS(130513) |
+--------------------+
|        63535622400 |
+--------------------+

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

UNIX_TIMESTAMP

Syntax

UNIX_TIMESTAMP()
UNIX_TIMESTAMP(date)

Description

If called with no argument, returns a Unix timestamp (seconds since 1970-01-01 00:00:00 UTC) as an unsigned integer. If UNIX_TIMESTAMP() is called with a date argument, it returns the value of the argument as seconds since 1970-01-01 00:00:00 UTC. date may be a DATE string, aDATETIME string, a TIMESTAMP, or a number in the format YYMMDD or YYYYMMDD. The server interprets date as a value in the current time zone and converts it to an internal value in UTC. Clients can set their time zone as described in time zones.

The inverse function of UNIX_TIMESTAMP() is FROM_UNIXTIME()

UNIX_TIMESTAMP() supports microseconds.

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

Timestamps in MariaDB have a maximum value of 2147483647, equivalent to 2038-01-19 05:14:07. This is due to the underlying 32-bit limitation. Using the function on a timestamp beyond this will result in NULL being returned. Use DATETIME as a storage type if you require dates beyond this.

Error Handling

Returns NULL for wrong arguments to UNIX_TIMESTAMP().

Examples

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

See Also

  • FROM_UNIXTIME()

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

UTC_DATE

Syntax

UTC_DATE, UTC_DATE()

Description

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.

Examples

SELECT UTC_DATE(), UTC_DATE() + 0;
+------------+----------------+
| UTC_DATE() | UTC_DATE() + 0 |
+------------+----------------+
| 2010-03-27 |       20100327 |
+------------+----------------+

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

UTC_TIME

Syntax

UTC_TIME
UTC_TIME([precision])

Description

Returns the current UTC time as a value in HH:MM:SS or HHMMSS.uuuuuu format, depending on whether the function is used in a string or numeric context.

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

Examples

SELECT UTC_TIME(), UTC_TIME() + 0;
+------------+----------------+
| UTC_TIME() | UTC_TIME() + 0 |
+------------+----------------+
| 17:32:34   |  173234.000000 |
+------------+----------------+

With precision:

SELECT UTC_TIME(5);
+----------------+
| UTC_TIME(5)    |
+----------------+
| 07:52:50.78369 |
+----------------+

See Also

  • Microseconds in MariaDB

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

UTC_TIMESTAMP

Syntax

UTC_TIMESTAMP
UTC_TIMESTAMP([precision])

Description

Returns the current UTC date and time as a value in YYYY-MM-DD HH:MM:SS or YYYYMMDDHHMMSS.uuuuuu format, depending on whether the function is used in a string or numeric context.

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

Examples

SELECT UTC_TIMESTAMP(), UTC_TIMESTAMP() + 0;
+---------------------+-----------------------+
| UTC_TIMESTAMP()     | UTC_TIMESTAMP() + 0   |
+---------------------+-----------------------+
| 2010-03-27 17:33:16 | 20100327173316.000000 |
+---------------------+-----------------------+

With precision:

SELECT UTC_TIMESTAMP(4);
+--------------------------+
| UTC_TIMESTAMP(4)         |
+--------------------------+
| 2018-07-10 07:51:09.1019 |
+--------------------------+

See Also

  • Time Zones

  • Microseconds in MariaDB

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

WEEK

Syntax

WEEK(date[,mode])

Description

This function returns the week number for date. The two-argument form ofWEEK() allows you to specify whether the week starts on Sunday or Monday and whether the return value should be in the range from 0 to 53 or from 1 to 53. If the mode argument is omitted, the value of the default_week_format system variable is used.

Modes

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

0

Sunday

0-53

a Sunday in this year

1

Monday

0-53

more than 3 days this year

2

Sunday

1-53

a Sunday in this year

3

Monday

1-53

more than 3 days this year

4

Sunday

0-53

more than 3 days this year

5

Monday

0-53

a Monday in this year

6

Sunday

1-53

more than 3 days this year

7

Monday

1-53

a Monday in this year

With the mode value of 3, which means 'more than 3 days this year', weeks are numbered according to ISO 8601:1988.

Examples

SELECT WEEK('2008-02-20');
+--------------------+
| WEEK('2008-02-20') |
+--------------------+
|                  7 |
+--------------------+

SELECT WEEK('2008-02-20',0);
+----------------------+
| WEEK('2008-02-20',0) |
+----------------------+
|                    7 |
+----------------------+

SELECT WEEK('2008-02-20',1);
+----------------------+
| WEEK('2008-02-20',1) |
+----------------------+
|                    8 |
+----------------------+

SELECT WEEK('2008-12-31',0);
+----------------------+
| WEEK('2008-12-31',0) |
+----------------------+
|                   52 |
+----------------------+

SELECT WEEK('2008-12-31',1);
+----------------------+
| WEEK('2008-12-31',1) |
+----------------------+
|                   53 |
+----------------------+

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

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

WEEKDAY

Syntax

WEEKDAY(date)

Description

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

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

Examples

SELECT WEEKDAY('2008-02-03 22:23:00');
+--------------------------------+
| WEEKDAY('2008-02-03 22:23:00') |
+--------------------------------+
|                              6 |
+--------------------------------+

SELECT WEEKDAY('2007-11-06');
+-----------------------+
| WEEKDAY('2007-11-06') |
+-----------------------+
|                     1 |
+-----------------------+
CREATE TABLE t1 (d DATETIME);
INSERT INTO t1 VALUES
    ("2007-01-30 21:31:07"),
    ("1983-10-15 06:42:51"),
    ("2011-04-21 12:34:56"),
    ("2011-10-30 06:31:41"),
    ("2011-01-30 14:03:25"),
    ("2004-10-07 11:19:34");
SELECT d FROM t1 where WEEKDAY(d) = 6;
+---------------------+
| d                   |
+---------------------+
| 2011-10-30 06:31:41 |
| 2011-01-30 14:03:25 |
+---------------------+

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

WEEKOFYEAR

Syntax

WEEKOFYEAR(date)

Description

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

Examples

SELECT WEEKOFYEAR('2008-02-20');
+--------------------------+
| WEEKOFYEAR('2008-02-20') |
+--------------------------+
|                        8 |
+--------------------------+
CREATE TABLE t1 (d DATETIME);
INSERT INTO t1 VALUES
    ("2007-01-30 21:31:07"),
    ("1983-10-15 06:42:51"),
    ("2011-04-21 12:34:56"),
    ("2011-10-30 06:31:41"),
    ("2011-01-30 14:03:25"),
    ("2004-10-07 11:19:34");
SELECT * FROM t1;
+---------------------+
| d                   |
+---------------------+
| 2007-01-30 21:31:07 |
| 1983-10-15 06:42:51 |
| 2011-04-21 12:34:56 |
| 2011-10-30 06:31:41 |
| 2011-01-30 14:03:25 |
| 2004-10-07 11:19:34 |
+---------------------+
SELECT d, WEEKOFYEAR(d), WEEK(d,3) from t1;
+---------------------+---------------+-----------+
| d                   | WEEKOFYEAR(d) | WEEK(d,3) |
+---------------------+---------------+-----------+
| 2007-01-30 21:31:07 |             5 |         5 |
| 1983-10-15 06:42:51 |            41 |        41 |
| 2011-04-21 12:34:56 |            16 |        16 |
| 2011-10-30 06:31:41 |            43 |        43 |
| 2011-01-30 14:03:25 |             4 |         4 |
| 2004-10-07 11:19:34 |            41 |        41 |
+---------------------+---------------+-----------+

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

YEAR

Syntax

YEAR(date)

Description

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

SQL_TSI_YEAR is a synonym for YEAR:

Examples

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

YEAR Format

CREATE TABLE year_format_example (
  description VARCHAR(30),
  example YEAR
);
INSERT INTO year_format_example VALUES
  ('4-digit numeric year', 1966),
  ('2-digit numeric year', 66),
  ('4-digit string year', '1966'),
  ('2-digit string year', '66');

The resulting output would look like this:

SELECT * FROM year_format_example;

+----------------------+---------+
| description          | example |
+----------------------+---------+
| 4-digit numeric year |    1966 |
| 2-digit numeric year |    2066 |
| 4-digit string year  |    1966 |
| 2-digit string year  |    2066 |
+----------------------+---------+

YEAR Range

CREATE TABLE year_range_example (
  description VARCHAR(30),
  example YEAR
);
INSERT INTO year_range_example VALUES
  ('minimum', 1901),
  ('maximum', 2155),
  ('below minimum', 1900),
  ('above maximum', 2156);

If SQL_MODE is strict (the default), the example above generates the following error and no values are inserted:

ERROR 1264 (22003): Out of range value for column 'example' at row 3

If SQL_MODE is not strict, the example above generates a warning and (possibly modified) values are inserted:

Warning (sql 1264): Out of range value for column 'example' at row 3
Warning (sql 1264): Out of range value for column 'example' at row 4
The resulting data would look like this:
SELECT * FROM year_range_example;

+---------------+---------+
| description   | example |
+---------------+---------+
| minimum       |    1901 |
| maximum       |    2155 |
| below minimum |    0000 |
| above maximum |    0000 |
+---------------+---------+

Zero YEAR

CREATE TABLE year_zero_example (
  description VARCHAR(30),
  example YEAR
);
INSERT INTO year_zero_example VALUES
  ('4-digit numeric zero', 0000),
  ('3-digit numeric zero', 000),
  ('2-digit numeric zero', 00),
  ('1-digit numeric zero', 0),
  ('4-digit string zero', '0000'),
  ('3-digit string zero', '000'),
  ('2-digit string zero', '00'),
  ('1-digit string zero', '0');

The resulting data would look like this:

SELECT * FROM year_zero_example;

+----------------------+---------+
| description          | example |
+----------------------+---------+
| 4-digit numeric zero |    0000 |
| 3-digit numeric zero |    0000 |
| 2-digit numeric zero |    0000 |
| 1-digit numeric zero |    0000 |
| 4-digit string zero  |    0000 |
| 3-digit string zero  |    2000 |
| 2-digit string zero  |    2000 |
| 1-digit string zero  |    2000 |
+----------------------+---------+

See Also

  • YEAR data type

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

YEARWEEK

Syntax

YEARWEEK(date), YEARWEEK(date,mode)

Description

Returns year and week for a date. The mode argument works exactly like the mode argument to WEEK(). The year in the result may be different from the year in the date argument for the first and the last week of the year.

Examples

SELECT YEARWEEK('1987-01-01');
+------------------------+
| YEARWEEK('1987-01-01') |
+------------------------+
|                 198652 |
+------------------------+
CREATE TABLE t1 (d DATETIME);
INSERT INTO t1 VALUES
    ("2007-01-30 21:31:07"),
    ("1983-10-15 06:42:51"),
    ("2011-04-21 12:34:56"),
    ("2011-10-30 06:31:41"),
    ("2011-01-30 14:03:25"),
    ("2004-10-07 11:19:34");
SELECT * FROM t1;
+---------------------+
| d                   |
+---------------------+
| 2007-01-30 21:31:07 |
| 1983-10-15 06:42:51 |
| 2011-04-21 12:34:56 |
| 2011-10-30 06:31:41 |
| 2011-01-30 14:03:25 |
| 2004-10-07 11:19:34 |
+---------------------+
6 rows in set (0.02 sec)
SELECT YEARWEEK(d) FROM t1 WHERE YEAR(d) = 2011;
+-------------+
| YEARWEEK(d) |
+-------------+
|      201116 |
|      201144 |
|      201105 |
+-------------+
3 rows in set (0.03 sec)

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

Numeric Functions

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

ABS

Syntax

ABS(X)

Description

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

Examples

SELECT ABS(42);
+---------+
| ABS(42) |
+---------+
|      42 |
+---------+

SELECT ABS(-42);
+----------+
| ABS(-42) |
+----------+
|       42 |
+----------+

SELECT ABS(DATE '1994-01-01');
+------------------------+
| ABS(DATE '1994-01-01') |
+------------------------+
|               19940101 |
+------------------------+

See Also

  • SIGN()

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

ACOS

Syntax

ACOS(X)

Description

Returns the arc cosine of X, that is, the value whose cosine is X. Returns NULL if X is not in the range -1 to 1.

Examples

SELECT ACOS(1);
+---------+
| ACOS(1) |
+---------+
|       0 |
+---------+

SELECT ACOS(1.0001);
+--------------+
| ACOS(1.0001) |
+--------------+
|         NULL |
+--------------+

SELECT ACOS(0);
+-----------------+
| ACOS(0)         |
+-----------------+
| 1.5707963267949 |
+-----------------+

SELECT ACOS(0.234);
+------------------+
| ACOS(0.234)      |
+------------------+
| 1.33460644244679 |
+------------------+

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

Addition Operator (+)

Syntax

+

Description

Addition.

If both operands are integers, the result is calculated with BIGINT precision. If either integer is unsigned, the result is also an unsigned integer.

For real or string operands, the operand with the highest precision determines the result precision.

Examples

SELECT 3+5;
+-----+
| 3+5 |
+-----+
|   8 |
+-----+

See Also

  • Type Conversion

  • Subtraction Operator (-)

  • Multiplication Operator (*)

  • Division Operator (/)

  • Operator Precedence

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

ASIN

Syntax

ASIN(X)

Description

Returns the arc sine of X, that is, the value whose sine is X. Returns NULL if X is not in the range -1 to 1.

Examples

SELECT ASIN(0.2);
+--------------------+
| ASIN(0.2)          |
+--------------------+
| 0.2013579207903308 |
+--------------------+

SELECT ASIN('foo');
+-------------+
| ASIN('foo') |
+-------------+
|           0 |
+-------------+

SHOW WARNINGS;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'foo' |
+---------+------+-----------------------------------------+

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

ATAN

Syntax

ATAN(X)

Description

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

Examples

SELECT ATAN(2);
+--------------------+
| ATAN(2)            |
+--------------------+
| 1.1071487177940904 |
+--------------------+

SELECT ATAN(-2);
+---------------------+
| ATAN(-2)            |
+---------------------+
| -1.1071487177940904 |
+---------------------+

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

ATAN2

Syntax

ATAN(Y,X), ATAN2(Y,X)

Description

Returns the arc tangent of the two variables X and Y. It is similar to calculating the arc tangent of Y / X, except that the signs of both arguments are used to determine the quadrant of the result.

Examples

SELECT ATAN(-2,2);
+---------------------+
| ATAN(-2,2)          |
+---------------------+
| -0.7853981633974483 |
+---------------------+

SELECT ATAN2(PI(),0);
+--------------------+
| ATAN2(PI(),0)      |
+--------------------+
| 1.5707963267948966 |
+--------------------+

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