The read-only analysis instance supports most MySQL date functions, but it still has limitations and incompatibilities in some scenarios. This document lists the date functions supported by the read-only analysis instance and specifies the supported data types for each function's parameters. Parameter types and functions not listed in this document are not supported.
Supported Date Functions
DATEDIFF Function
Returns the number of days between two dates.
Syntax: DATEDIFF(date1, date2)
The data types supported by date1: date, datetime.
The data types supported by date2: date, datetime.
YEAR Function
Returns the year of a date.
Syntax: YEAR(date1)
The data types supported by date1: date, datetime, char, varchar.
MONTH Function
Returns the month of a date.
Syntax: MONTH(date1)
The data types supported by date1: date, datetime, char, varchar.
DAY Function
Returns the day of the month for a date.
Syntax: DAY(date1)
The data types supported by date1: date, datetime, char, varchar.
HOUR Function
Returns the hour part of a time.
Syntax: HOUR(date1)
The data types supported by date1: date, datetime.
MINUTE Function
Returns the minute part of a time.
Syntax: MINUTE(date1)
The data types supported by date1: date, datetime.
SECOND Function
Returns the second part of a time.
Syntax: SECOND(date1)
The data types supported by date1: date, datetime.
MICROSECOND Function
Returns the microsecond part of a time or datetime.
Syntax: MICROSECOND(date1)
The data types supported by date1: date, datetime.
QUARTER Function
Returns the quarter (a number from 1 to 4) in which a given date falls.
Syntax: QUARTER(date1)
The data types supported by date1: date, datetime, char, varchar.
DATE Function
Extract the date part from a date or datetime expression.
Syntax: DATE(date1)
The data types supported by date1: date, datetime, char, varchar.
SYSDATE Function
Returns the current date and time of the system.
Syntax: SYSDATE()
Input parameters are not required.
DATE_ADD Function
Adds a specified time interval to a date.
Syntax: DATE_ADD(date1, INTERVAL expr type)
The data types supported by date1: date, datetime.
expr supports the following data types: int, bigint, double, decimal.
type supports the following value: DAY (keyword).
DATE_SUB Function
Subtracts a specified time interval from a date.
Syntax: DATE_SUB(date1, INTERVAL expr type)
The data types supported by date1: date, datetime.
expr supports the following data types: int, bigint, double, decimal.
type supports the following value: DAY (keyword).
DAYOFYEAR Function
Returns the day of the year for a given date.
Syntax: DAYOFYEAR(date1)
The data types supported by date1: date, datetime, char, varchar.
DAYOFMONTH Function
Returns the day of the month for a given date.
Syntax: DAYOFMONTH(date1)
The data types supported by date1: date, datetime, char, varchar.
DAYOFWEEK Function
Returns the day of the week for a given date.
Syntax: DAYOFWEEK(date1)
The data types supported by date1: date, datetime, char, varchar.
DAYNAME Function
Returns the name of the weekday corresponding to the specified date.
Syntax: DAYNAME(date1)
The data types supported by date1: date, datetime, char, varchar.
MONTHNAME Function
Returns the name of the month corresponding to the specified date.
Syntax: MONTHNAME(date1)
The data types supported by date1: date, datetime, char, varchar.
TO_SECONDS Function
Returns the number of seconds from the year 0 AD to the specified date or datetime.
Syntax: TO_SECONDS(date1)
The data types supported by date1: date, datetime, char, varchar.
LAST_DAY Function
Returns the last day of the month for a given date.
Syntax: LAST_DAY(date1)
The data types supported by date1: date, datetime, char, varchar.
SLEEP Function
Pauses execution for the specified number of seconds.
Syntax: SLEEP(N)
The data types supported by N: int.
NOW Function
Returns the current date and time.
Syntax: NOW()
Input parameters are not required.
CURRENT_TIMESTAMP Function
Returns the current date and time, which is equivalent to NOW().
Syntax: CURRENT_TIMESTAMP()
Input parameters are not required.
CURDATE Function
Returns the current date, which is equivalent to CURRENT_DATE().
Syntax: CURDATE()
Input parameters are not required.
CURRENT_DATE Function
Returns the current date, which is equivalent to CURDATE().
Syntax: CURRENT_DATE()
Input parameters are not required.
CURRENT_TIME Function
Returns the current time.
Syntax: CURRENT_TIME()
Input parameters are not required.
CURTIME Function
Returns the current time, which is equivalent to CURRENT_TIME().
Syntax: CURTIME()
Input parameters are not required.
FROM_UNIXTIME Function
Converts a Unix timestamp to a datetime string in a specified format.
Syntax: FROM_UNIXTIME(unix_timestamp [, format])
The data types supported by unix_timestamp: int.
The format parameter is optional. For supported formats, refer to the format table of the DATE_FORMAT Function. If format is empty, the %Y-%m-%d %H:%i:%s format is used by default. UNIX_TIMESTAMP Function
Converts a datetime to a Unix timestamp.
Syntax: UNIX_TIMESTAMP([date])
The data types supported by date: the standard DATE type.
WEEKDAY Function
Returns the weekday number corresponding to a given date.
Syntax: WEEKDAY(date)
The data types supported for input parameters: date, datetime.
The returned result ranges from 0 - 6, corresponding to Monday through Sunday respectively.
If the date is NULL or 0000-00-00, the function returns NULL.
TIMEDIFF Function
Returns the difference between two times.
Syntax: TIMEDIFF(time1, time2)
Returns the time difference of time1 - time2.
TO_DAYS Function
Returns the number of days from December 31, year 0 AD to the given date.
Syntax: TO_DAYS(date)
The data types supported by date: date or string.
The return value type is a number.
YEARWEEK Function
Returns the year and week number for a specified date.
Syntax: YEARWEEK(date [, mode])
date: The date to be calculated, for example '2025-06-15'.
mode: An optional parameter that specifies the start day of the week. The default value is 0, which indicates that the week starts on Sunday.
The return value type is a number.
TIME Function
Extracts the time part from a datetime expression and returns it as a string.
Syntax: TIME(expr)
expr supports the following data types: time or datetime.
The return value type is a string. If expr is NULL, the function returns NULL.
DATE_FORMAT Function
Formats a date according to a specified format.
Syntax: DATE_FORMAT(date1, format)
The data types supported by date1: date, datetime, char, varchar. Scenarios where date1 is NULL are not supported.
format: The output format for date/time. The supported format specifiers are listed in the table below.
|
%a | Abbreviate the names of the days of the week. |
%b | Abbreviate the names of the months. |
%c | Month, numeric value. |
%D | Day in the mid-month with English prefix. |
%d | Day of the month, numeric value (00 - 31). |
%e | Day of the month, numeric value (0 - 31). |
%f | Microsecond. |
%H | Hour (00 - 23). |
%h | Hour (01 - 12). |
%I | Hour (01 - 12). |
%i | Minute, numeric value (00 - 59). |
%j | Day of the year (001 - 366). |
%k | Hour (0 - 23). |
%l | Hour (1 - 12). |
%M | Month name. |
%m | Month, numeric value (00 - 12). |
%p | AM or PM. |
%r | Time, 12-hour format (hh:mm:ss AM or PM). |
%S | Second (00 - 59). |
%s | Second (00 - 59). |
%T | Time, 24-hour format (hh:mm:ss). |
%U | Week (00 - 53), Sunday is the first day of the week. |
%u | Week (00 - 53), Monday is the first day of the week. |
%V | Week (01 - 53), Sunday is the first day of the week, used in conjunction with %X. |
%v | Week (01 - 53), Monday is the first day of the week, used in conjunction with %x. |
%W | Name of the day of the week. |
%w | Specific day of the week (0 = Sunday, 6 = Saturday). |
%X | Year, where Sunday is the first day of the week, 4-digit, used in conjunction with %V. |
%x | Year, where Monday is the first day of the week, 4-digit, used in conjunction with %v. |
%Y | 4-digit year. |
%y | 2-digit year. |