Description of Supported Date Functions

Recent Pages

Description of Supported Date Functions

Last updated: 2025-01-09 14:23:18

Currently, the read-only analysis engine supports most of MySQL's date functions, but there are still limitations and incompatibilities in certain scenarios. This document introduces the list of supported date functions in the read-only analysis engine, and enumerates type restrictions on input parameters. Input parameters and functions not mentioned in the document are not supported.
Supported Date Function Types
datediff Function
A function that returns the number of specified time intervals between two dates.
Syntax: datediff(date1, date2)
Supported value types for date1: date and datetime.
Supported value types for date2: date and datetime.
year Function
A function that calculates the year.
Syntax: year(date1)
Supported value types for date1: date, datetime, char, and varchar.
month Function
A function that calculates the month.
Syntax: month(date1)
Supported value types for date1: date, datetime, char, and varchar.
day Function
A function that calculates the number of the day.
Syntax: day(date1)
Supported value types for date1: date, datetime, char, and varchar.
hour Function
A function that calculates the hour.
Syntax: hour(date1)
Supported value types for date1: date and datetime.
minute Function
A function that calculates the minute.
Syntax: minute(date1)
Supported value types for date1: date and datetime.
second Function
A function that calculates the second.
Syntax: second(date1)
Supported value types for date1: date and datetime.
microsecond Function
It returns the microsecond part of the time/datetime.
Syntax: microsecond(date1)
Supported value types for date1: date and datetime.
quarter Function
It returns the quarter of the year for a given date value (a number between 1 and 4).
Syntax: quarter(date1)
Supported value types for date1: date, datetime, varchar, and char.
date Function
Date function.
Syntax: date(date1)
Supported value types for date1: date, datetime, char, and varchar.
sysdate Function
A function that returns the system time.
Syntax: sysdate()
date_add Function
Date addition function.
Syntax: date_add(date1, interval expr type)
Supported value types for date1: date and datetime.
Supported value types for expr: int, long, double, and decimal.
Supported value types for type: DAY, which is the keyword.
date_sub Function
Date subtraction function.
Syntax: date_sub(date1, interval expr type)
Supported value types for date1: date and datetime.
Supported value types for expr: int, long, double, and decimal.
Supported value types for type: DAY, which is the keyword.
dayofyear Function
A function that returns the day number of a given date in the year.
Syntax: dayofyear(date1)
Supported value types for date1: date, datetime, char, and varchar.
dayofmonth Function
A function that returns the day number of a given date in the month.
Syntax: dayofmonth(date1)
Supported value types for date1: date, datetime, char, and varchar.
dayofweek Function
A functions that returns the day number of a given date in the week.
Syntax: dayofweek(date1)
Supported value types for date1: date, datetime, char, and varchar.
dayname Function
A function that returns the name of the week corresponding to a specified date.
Syntax: dayname(date1)
Supported value types for date1: date, datetime, char, and varchar.
monthname Function
A function that returns the name of the month corresponding to a specified date.
Syntax: monthname(date1)
Supported value types for date1: date, datetime, char, and varchar.
to_seconds Function
A function that returns the number of seconds since year 0 for a specified date/datetime.
Syntax: to_seconds(date1)
Supported value types for date1: date, datetime, char, and varchar.
last_day Function
A function that returns the last day of the month.
Syntax: last_day(date1)
Supported value types for date1: date, datetime, char, and varchar.
date_format Function
Date formatting function.
Syntax: date_format(date1, format)
Supported value types for date1: date, datetime, char, and varchar. Null is not supported. format specifies the output format of the date/time. Available formats are listed in the table below.
Format
Description
%a
Abbreviated weekday name.
%b
Abbreviated month name.
%c
Month, numeric.
%D
The day of the month with an English prefix.
%d
The day of the month, numeric (00 - 31).
%e
The day of the month, numeric (0 - 31).
%f
Microsecond.
%H
Hour (00 - 23).
%h
Hour (01 - 12).
%I
Hour (01 - 12).
%i
Minute, numeric (00 - 59).
%j
The day of the year (001 - 366).
%k
Hour (0 - 23).
%l
Hour (1 - 12).
%M
Month name.
%m
Month, numeric (00 - 12).
%p
AM or PM.
%r
Time, 12-hour (hh:mm:ss AM or PM).
%S
Second (00 - 59).
%s
Second (00 - 59).
%T
Time, 24-hour (hh:mm:ss).
%U
Week (00 - 53). Sunday is the first day of a week.
%u
Week (00 - 53). Monday is the first day of a week.
%V
Week (01 - 53). Sunday is the first day of a week. It is used with %X.
%v
Week (01 - 53). Monday is the first day of a week. It is used with %x.
%W
Weekday name.
%w
The day of the week (0 = Sunday, 6 = Saturday).
%X
Year. Sunday is the first day of a week. It contains 4 digits, and is used with %V.
%x
Year. Monday is the first day of a week. It contains 4 digits, and is used with %v.
%Y
Year, containing 4 digits.
%y
Year, containing 2 digits.

Contact Us

Contact our sales team or business advisors to help your business.

Technical Support

Open a ticket if you're looking for further assistance. Our Ticket is 7x24 avaliable.

7x24 Phone Support

tencent cloud

Recent Pages

Description of Supported Date Functions

Supported Date Function Types

datediff Function

year Function

month Function

day Function

hour Function

minute Function

second Function

microsecond Function

quarter Function

date Function

sysdate Function

date_add Function

date_sub Function

dayofyear Function

dayofmonth Function

dayofweek Function

dayname Function

monthname Function

to_seconds Function

last_day Function

date_format Function

Was this page helpful?

Was this page helpful?

Format	Description
%a	Abbreviated weekday name.
%b	Abbreviated month name.
%c	Month, numeric.
%D	The day of the month with an English prefix.
%d	The day of the month, numeric (00 - 31).
%e	The day of the month, numeric (0 - 31).
%f	Microsecond.
%H	Hour (00 - 23).
%h	Hour (01 - 12).
%I	Hour (01 - 12).
%i	Minute, numeric (00 - 59).
%j	The day of the year (001 - 366).
%k	Hour (0 - 23).
%l	Hour (1 - 12).
%M	Month name.
%m	Month, numeric (00 - 12).
%p	AM or PM.
%r	Time, 12-hour (hh:mm:ss AM or PM).
%S	Second (00 - 59).
%s	Second (00 - 59).
%T	Time, 24-hour (hh:mm:ss).
%U	Week (00 - 53). Sunday is the first day of a week.
%u	Week (00 - 53). Monday is the first day of a week.
%V	Week (01 - 53). Sunday is the first day of a week. It is used with %X.
%v	Week (01 - 53). Monday is the first day of a week. It is used with %x.
%W	Weekday name.
%w	The day of the week (0 = Sunday, 6 = Saturday).
%X	Year. Sunday is the first day of a week. It contains 4 digits, and is used with %V.
%x	Year. Monday is the first day of a week. It contains 4 digits, and is used with %v.
%Y	Year, containing 4 digits.
%y	Year, containing 2 digits.

tencent cloud

Sign Up

Log in

Recent Pages

Description of Supported Date Functions

Supported Date Function Types

datediff Function

year Function

month Function

day Function

hour Function

minute Function

second Function

microsecond Function

quarter Function

date Function

sysdate Function

date_add Function

date_sub Function

dayofyear Function

dayofmonth Function

dayofweek Function

dayname Function

monthname Function

to_seconds Function

last_day Function

date_format Function

Was this page helpful?

Was this page helpful?