Overview¶
Table 1 lists the date functions supported by DLI.
Syntax | Value Type | Description |
|---|---|---|
add_months(string start_date, int num_months) | STRING | Returns the date that is num_months after start_date. |
current_date() | DATE | Returns the current date, for example, 2016-07-04. |
current_timestamp() | TIMESTAMP | Returns the current time, for example, 2016-07-04 11:18:11.685. |
date_add(string startdate, int days) | STRING or DATE | Adds a number of days to a date. |
dateadd(string date, bigint delta, string datepart) | STRING or DATE | Changes a date based on datepart and delta. date: This parameter is mandatory. Date value, which is of the STRING type. The time format is yyyy-mm-dd hh:mi:ss, for example, 2021-08-28 00:00:00. delta: This parameter is mandatory. Adjustment amplitude, which is of the BIGINT type. datepart: Adjustment unit, which is a constant of the STRING type. This parameter is mandatory. |
date_sub(string startdate, int days) | STRING | Subtracts a number of days from a date. |
date_format(string date, string format) | STRING | Converts a date into a string based on the format specified by format. |
datediff(string date1, string date2) | BIGINT | Calculates the difference between date1 and date2. |
datediff1(string date1, string date2, string datepart) | BIGINT | Calculates the difference between date1 and date2 and returns the difference in a specified datepart. |
datepart (string date, string datepart) | BIGINT | Returns the value of the field that meets a specified time unit in the date. |
datetrunc (string date, string datepart) | STRING | Calculates the date otained through the truncation of a specified date based on a specified datepart. date: The value is in the yyyy-mm-dd or yyyy-mm-dd hh:mi:ss format. This parameter is mandatory. datepart: Supported date format, which is a STRING constant. This parameter is mandatory. |
day(string date), dayofmonth(string date) | INT | Returns the date of a specified time. |
from_unixtime(bigint unixtime) | STRING | Converts a timestamp to a time, in yyyy-MM-dd HH:mm:ss or yyyyMMddHHmmss.uuuuuu format. For example, select FROM_UNIXTIME(1608135036,'yyyy-MM-dd HH:mm:ss'). |
from_utc_timestamp(string timestamp, string timezone) | TIMESTAMP | Converts a UTC timestamp to the corresponding timestamp in a specific time zone. |
getdate() | STRING | Obtains the current system time. |
hour(string date) | INT | Returns the hour (from 0 to 23) of a specified time. |
isdate(string date , string format) | BOOLEAN | date: Date, which is implicitly converted to the STRING type and then used for calculation. This parameter is mandatory. format: Unsupported date extension format, which is a STRING constant. This parameter is mandatory. If there are redundant format strings in format, only the date value corresponding to the first format string is used. Other format strings are used as separators. For example, isdate("1234-yyyy", "yyyy-yyyy") returns True. |
last_day(string date) | DATE | Returns the last day of the month a date belongs to, in the format of yyyy-MM-dd, for example, 2015-08-31. |
lastday(string date) | STRING | Returns the last day of the month a date belongs to. The value is of the STRING type and is in the yyyy-mm-dd hh:mi:ss format. |
minute(string date) | INT | Returns the minute (from 0 to 59) of a specified time. |
month(string date) | INT | Returns the month (from January to December) of a specified time. |
months_between(string date1, string date2) | DOUBLE | Returns the month difference between date1 and date2. |
next_day(string start_date, string day_of_week) | DATE | Returns the date closest to day_of_week after start_date, in the yyyy-MM-dd format. day_of_week indicates a day in a week, for example, Monday or Friday. |
quarter(string date) | INT | Returns the quarter of the date, timestamp, or string, for example, quarter('2015-04-01')=2. |
second(string date) | INT | Returns the second (from 0 to 59) of a specified time. |
to_char(string date, string format) | STRING | Converts a date into a string in a specified format. |
to_date(string timestamp) | DATE | Returns the date part of a time string, for example, to_date("1970-01-01 00:00:00") = "1970-01-01". |
to_date1(string date, string format) | STRING | The value is of the STRING type, in the yyyy-mm-dd hh:mi:ss format. If the value of date or format is NULL, NULL is returned. Converts a string in a specified format to a date value. |
to_utc_timestamp(string timestamp, string timezone) | TIMESTAMP | Converts a timestamp in a given time zone to a UTC timestamp. |
trunc(string date, string format) | DTAE | Resets the date in a specified format. Supported formats are MONTH/MON/MM and YEAR/YYYY/YY, for example, trunc('2015-03-17', 'MM') = 2015-03-01. |
unix_timestamp(string timestamp, string pattern) | BIGINT | Returns a Unix timestamp (the number of seconds since 1970-01-01 00:00:00) as an unsigned integer if the function is called without parameters. |
weekday (string date) | INT | Returns the day of the current week. |
weekofyear(string date) | INT | Returns the week number (from 0 to 53) of a specified date. |
year(string date) | INT | Returns the year of a specified date. |