MariaDB Date Functions: Complete Date and Time List
Updated on
MariaDB date and time functions cover parsing and formatting values, extracting date parts, doing date arithmetic, and converting time zones. Use this index to find syntax and examples for functions such as DATE_FORMAT(), STR_TO_DATE(), and DATE_ADD().
Function availability can depend on your MariaDB version. This list includes newer functions such as FORMAT_PICO_TIME() (11.0.2), TRUNC() (12.2), and TO_DATE() (12.3); MONTHS_BETWEEN() is also in MariaDB’s current function reference.
| Function | Description |
|---|---|
ADDDATE() |
Adds a time value (interval) to a date value. |
ADD_MONTHS() |
Adds the given number of months to the given date. |
ADDTIME() |
Add time to time value. |
CONVERT_TZ() |
Convert from one time zone to another. |
CURDATE() |
Returns the current date. |
CURRENT_DATE(), CURRENT_DATE |
Synonym for CURDATE(). |
CURTIME() |
Returns the current time. |
CURRENT_TIME(), CURRENT_TIME |
Synonym for CURTIME(). |
CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP |
synonym for NOW(). |
DATE() |
Extracts the date portion of a date or datetime expression. |
DATE_ADD() |
Adds a time value (interval) to a date value. |
DATE_FORMAT() |
Formats the given date according to the specified format. |
DATE_SUB() |
Subtracts a time value (interval) from a date. |
DATEDIFF() |
Returns the difference between two dates (one date is subtracted from the other). |
DAY() |
Synonym for DAYOFMONTH(). |
DAYNAME() |
Returns the weekday name for a given date. |
DAYOFMONTH() |
Returns the day of the month (0-31) for the given date. |
DAYOFWEEK() |
Returns the weekday index for a given date. |
DAYOFYEAR() |
Returns the day of the year (1-366) for the given date. |
EXTRACT() |
Extract the date part from the given date. |
FORMAT_PICO_TIME() |
Formats a duration in picoseconds using a human-readable time unit. Available from MariaDB 11.0.2. |
FROM_DAYS() |
Returns a date based on the given number of days from the standard calendar (0000-00-00). |
FROM_UNIXTIME() |
Convert the format of a Unix timestamp to a date. |
GET_FORMAT() |
Returns a date format string based on the input parameters. |
HOUR() |
Extracts the hour from a given time or datetime expression. |
LAST_DAY() |
Returns the last day of the month from its date argument. |
LOCALTIME(), LOCALTIME |
Synonym for NOW(). |
LOCALTIMESTAMP, LOCALTIMESTAMP() |
Synonym for NOW(). |
MAKEDATE() |
Create a date from a year and date. |
MAKETIME() |
Create a time from hours, minutes, seconds. |
MICROSECOND() |
Extracts microseconds from a given time or datetime expression. |
MINUTE() |
Extracts minutes from a given time or datetime expression. |
MONTH() |
Extracts the month from a given date. |
MONTHNAME() |
Extract the name of the month from the given date. |
MONTHS_BETWEEN() |
Returns the number of months between two dates, including a fractional result when needed. |
NOW() |
Returns the current date and time. |
PERIOD_ADD() |
Add period to year-month value. |
PERIOD_DIFF() |
Returns the number of months between periods. |
QUARTER() |
Returns the quarter from a given date expression. |
SEC_TO_TIME() |
Converts seconds to ‘hh:mm:ss’ format. |
SECOND() |
Returns the seconds (0-59) from a given time or datetime expression. |
STR_TO_DATE() |
Converts a string to a date, datetime, or time value according to the given format string. |
SUBDATE() |
Subtracts an amount from a given date. Synonym for DATE_SUB() when called with three arguments. |
SUBTIME() |
Subtracts a time value from another time value. |
SYSDATE() |
Returns the time the function was executed. |
TIME() |
Extracts the time portion of a time or datetime expression and returns it as a string. |
TIME_FORMAT() |
Formats a time value according to the given format string. |
TIME_TO_SEC() |
Returns the time argument converted to seconds. |
TIMEDIFF() |
Returns the difference between two time or datetime values. It subtracts the second from the first. |
TIMESTAMP() |
For a single argument, this function returns a date or datetime expression as a datetime value. Taking two arguments, it adds the time argument to a date or datetime argument, then returns the resulting datetime value. |
TIMESTAMPADD() |
Add intervals to datetime expressions. |
TIMESTAMPDIFF() |
Returns the difference between two datetime expressions, based on the given units. |
TO_DATE() |
Converts text to a DATETIME value using an Oracle-style format model. Available from MariaDB 12.3. |
TO_DAYS() |
Returns the date argument converted to days. |
TO_SECONDS() |
Returns the date or datetime argument converted to seconds since the year 0. |
TRUNC() |
Truncates a date or datetime to the specified unit in Oracle mode. Available from MariaDB 12.2. |
UNIX_TIMESTAMP() |
Returns a Unix timestamp. |
UTC_DATE() |
Returns the current UTC date. |
UTC_TIME() |
Returns the current UTC time. |
UTC_TIMESTAMP() |
Returns the current UTC date and time. |
WEEK() |
Returns the week number in a given date or datetime expression. |
WEEKDAY() |
Returns the weekday index from a given date or datetime expression. |
WEEKOFYEAR() |
Returns the calendar week of a date (1-53) from a given date or datetime expression. |
YEAR() |
Returns the year in a given date or datetime expression. |
YEARWEEK() |
Returns the year and week of a given date or datetime expression. |