Date & Time Functions
Complete MariaDB date and time functions guide. Complete reference for formatting, calculations, conversions, time zones, and operations for production use.
Reference keywords for date arithmetic. These units, such as DAY, HOUR, and MINUTE, specify the interval type used in functions like DATE_ADD and EXTRACT.
Add a specific number of months to a date. This Oracle-compatible function simplifies date calculations involving monthly intervals.
Add a time interval to a date. This function performs date arithmetic, adding a specified value like days or hours to a starting date.
Add a time value to a date or time expression. This function sums two time arguments, returning a new time or datetime result.
Convert a datetime value between time zones. This function shifts a timestamp from a source time zone to a target time zone.
Return the current date. This function outputs today's date as a value in 'YYYY-MM-DD' or YYYYMMDD format, depending on the context.
Synonym for CURDATE(). Returns the current date as a value in 'YYYY-MM-DD' or YYYYMMDD format.
Synonym for CURTIME(). Returns the current time as a value in 'HH:MM:SS' or HHMMSS format.
Synonym for NOW(). Returns the current date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format.
Return the current time. This function outputs the current time of day as a value in 'HH:MM:SS' or HHMMSS format.
Extract the date part from a datetime expression. This function returns the year, month, and day portions, discarding the time component.
Complete DATE_ADD() reference: DATE_ADD(date, INTERVAL expr unit) syntax, negative interval support, unit keywords (DAY/MONTH/YEAR), and return types.
Complete DATE_FORMAT reference for MariaDB. Complete function guide with syntax, parameters, return values, and usage examples with comprehensive examples.
Subtract a time interval from a date. This function calculates a past date by subtracting a specified unit, such as days, from a starting value.
Complete DATEDIFF() reference: DATEDIFF(expr1,expr2) syntax, date vs datetime expression handling, time component ignore, and positive/negative results.
Synonym for DAYOFMONTH(). Returns the day of the month (1-31) for a given date.
Return the name of the weekday. This function returns the full name of the day, such as 'Monday' or 'Sunday', for a given date.
Return the day of the month. This function extracts the day portion of a date, returning a number from 1 to 31.
Return the weekday index. This function returns a number from 1 (Sunday) to 7 (Saturday) representing the day of the week.
Return the day of the year. This function returns a number from 1 to 366 indicating the day's position within the year.
Extract a specific part of a date. This function retrieves components like YEAR, MONTH, DAY, or HOUR from a date or datetime expression.
Format a time in picoseconds. This function converts a numeric picosecond value into a human-readable string with units like ps, ns, us, ms, s, m, h, or d.
Convert a day number to a date. This function returns a DATE value corresponding to the number of days since year 0.
Convert a Unix timestamp to a datetime. This function formats a Unix timestamp as a date string or number in the current time zone.
Return a format string. This function provides standard format strings for DATE_FORMAT and STR_TO_DATE based on regions like 'USA' or 'EUR'.
Extract the hour. This function returns the hour portion of a time or datetime value as a number from 0 to 23.
Return the last day of the month. This function calculates the date of the final day for the month containing the given date.
Synonym for NOW(). Returns the current date and time in the session time zone.
Synonym for NOW(). Returns the current date and time in the session time zone as a datetime value.
Create a date from a year and day of year. This function constructs a DATE value given a year and the day number within that year.
Create a time from hour, minute, and second. This function constructs a TIME value from three numeric arguments.
Extract microseconds. This function returns the microsecond part of a time or datetime expression as a number from 0 to 999999.
Understand microsecond precision. This concept page explains how MariaDB stores and handles fractional seconds in time data types.
Extract the minute. This function returns the minute portion of a time or datetime value as a number from 0 to 59.
Extract the month. This function returns the month portion of a date as a number from 1 (January) to 12 (December).
Return the name of the month. This function returns the full name of the month, such as 'January' or 'December', for a given date.
Calculate the difference between two months.
Complete NOW() function reference: NOW([precision]) and CURRENT_TIMESTAMP synonyms, TIMESTAMP vs DATETIME types, timezone/DST handling, and fractional seconds.
Add months to a period. This function adds a specified number of months to a period formatted as YYMM or YYYYMM.
Calculate the difference between periods. This function returns the number of months between two periods formatted as YYMM or YYYYMM.
Return the quarter of the year. This function returns a number from 1 to 4 indicating the quarter for a given date.
Convert seconds to time. This function returns a TIME value corresponding to the number of seconds elapsed from the start of the day.
Extract the second. This function returns the second portion of a time or datetime value as a number from 0 to 59.
Complete STR_TO_DATE() reference: parse strings to DATE/TIME/DATETIME, format specifiers (%Y %m %d %H %i %s), invalid input handling, and SQL_MODE errors.
Subtract a time interval from a date. This synonym for DATE_SUB calculates a past date by subtracting a specified unit from a starting value.
Subtract a time value. This function subtracts one time or datetime expression from another and returns the result.
Return the time of execution. Unlike NOW(), which returns the start time of the statement, SYSDATE() returns the time it executes.
Extract the time portion. This function returns the time part of a time or datetime expression.
Format a time. This function formats a time value according to a format string, similar to DATE_FORMAT but for time values.
TIME_TO_SEC() converts a time value to the number of seconds, returning a DOUBLE that preserves microseconds.
Subtract two time values. This function calculates the difference between two time or datetime expressions.
Convert to datetime or add time. With one argument, it returns a datetime; with two, it adds a time expression to a date or datetime.
Add an interval to a timestamp. This function adds a specified integer number of units (like MONTH or SECOND) to a datetime expression.
Calculate the difference between timestamps. This function returns the difference between two datetime expressions in the specified unit.
TO_DATE() converts a string to a date using a specified format, with optional handling for conversion errors.
Convert a date to a day number. This function returns the number of days between year 0 and the given date.
Convert a date to seconds. This function returns the number of seconds from year 0 to the given date or datetime.
Truncate a date. In Oracle mode, this function truncates a date value to a specified unit of measure.
Return a Unix timestamp. This function returns the number of seconds since the Unix Epoch ('1970-01-01 00:00:00' UTC).
Return the current UTC date. This function returns the current Coordinated Universal Time date in 'YYYY-MM-DD' or YYYYMMDD format.
Return the current UTC time. This function returns the current Coordinated Universal Time in 'HH:MM:SS' or HHMMSS format.
Return the current UTC timestamp. This function returns the current Coordinated Universal Time date and time.
Return the week number. This function returns the week number for a date, with an optional mode to define the start of the week.
Return the weekday index. This function returns the index of the day of the week (0=Monday, 6=Sunday).
Return the calendar week. This function returns the week number of the date (1-53), equivalent to WEEK(date, 3).
Extract the year. This function returns the year portion of a date as a number from 1000 to 9999.
Return the year and week. This function returns the year and week number for a date, useful for grouping results by week.
This page is licensed: CC BY-SA / Gnu FDL
Last updated
Was this helpful?