Date Functions
add_day
| Syntax | add_day(T,I) |
|---|---|
| Parameters | 1. Time type parameter; 2. Number of days to add, optional type: number |
| Return Type | Time |
| Description | Returns the date after the specified number of days from the starting date. If the numeric field is negative, it returns the date before the specified number of days. |
| Example | add_day('2018-02-02', 3), returns: 2018-02-05 |
add_hour
| Syntax | add_hour(T,I) |
|---|---|
| Parameters | 1. Time type parameter; 2. Number of hours to add, optional type: numeric |
| Return Type | Time |
| Description | Returns the time after adding the specified number of hours to the starting time. If the numeric field is negative, it returns the time before the specified number of hours. |
| Example | add_hour('2018-02-02 08:30:00.000', 3), returns: 2018-02-02 11:30:00.000 |
add_minute
| Syntax | add_minute(T,I) |
|---|---|
| Parameters | 1. Time type parameter; 2. Minutes to add, optional type: number |
| Return Type | Time |
| Description | Returns the time after adding the specified number of minutes to the starting time. If the numeric field is negative, it returns the time before the specified number of minutes. |
| Example | add_minute('2018-02-02 08:30:00.000', 3), returns: 2018-02-02 08:33:00.000 |
add_month
| Syntax | add_month(T,I) |
|---|---|
| Parameters | 1. Time type parameter; 2. Number of months to add, optional type: number |
| Return Type | Time |
| Description | Returns the date after the specified number of months from the starting date. If the numeric field is negative, it returns the date before the specified number of months. |
| Example | add_month('2018-02-02', 3), returns: 2018-05-02 |
add_quarter
| Syntax | add_quarter(T,I) |
|---|---|
| Parameters | 1. Time type parameter; 2. Number of quarters to add, optional type: number |
| Return Type | Time |
| Description | Returns the date after the specified number of quarters from the start date. If the numeric field is negative, it returns the date before the specified number of quarters. |
| Example | add_quarter('2018-02-02', 3), returns: 2018-05-02 |
add_week
| Syntax | add_week(T,I) |
|---|---|
| Parameters | 1. Time type parameter; 2. Number of weeks to add, optional type: number |
| Return Type | Time |
| Description | Returns the date after the specified number of weeks from the start date. If the numeric field is negative, it returns the date before the specified number of weeks. |
| Example | add_week('2018-02-02', 3), returns: 2018-05-02 |
add_year
| Syntax | add_year(T,I) |
|---|---|
| Parameters | 1. Time type parameter; 2. Number of years to add, optional type: number |
| Return Type | Time |
| Description | Returns the date after the specified number of years from the starting date. If the numeric field is negative, it returns the date before the specified number of years. |
| Example | add_year('2018-02-02', 3), returns: 2021-02-02 |
add_year_keep_woy
| Syntax | add_year_keep_woy(T,I) |
|---|---|
| Parameter Description | 1. Time type parameter; 2. Number of years to add, optional type: number |
| Return Type | Time |
| Description | Similar to add_year, but adjusts the date to keep the week of the year unchanged |
| Example | add_year_keep_woy('2023-01-01', 1), returns: 2024-01-01 (example, specific result may vary depending on week calculation logic) |
| Unsupported Data Sources | mysql, tidb, sqlserver, redshift, spark, hive, impala, presto, db2, mongodb, maxcompute |
age_in_days
| Syntax | age_in_days(START, END) |
|---|---|
| Parameters | 1. Start time, time-type parameter; 2. End time, time-type parameter |
| Return Type | Number |
| Description | Compares the difference in days between two times, with all time components involved in the calculation. |
| Example | age_in_days('2021-05-31 00:00:50', '2022-05-31 00:00:00'), returns: 364 |
age_in_hours
| Syntax | age_in_hours(START, END) |
|---|---|
| Parameters | 1. Start time, time type parameter; 2. End time, time type parameter |
| Return Type | Number |
| Description | Compares the difference between two times in hours, with all time components included in the calculation. |
| Example | age_in_hours('2021-05-31 00:00:50', '2022-05-31 00:00:00'), returns: 8759 |
age_in_milliseconds
| Syntax | age_in_milliseconds(START, END) |
|---|---|
| Parameter Description | 1. Start time, time type parameter; 2. End time, time type parameter |
| Return Type | Number |
| Description | Compares the difference in milliseconds between two times, with all time components included in the calculation. |
| Example | age_in_milliseconds('2021-05-31 00:00:00.531', '2022-05-31 00:00:00.000'), returns: 31535999469 |
age_in_minutes
| Syntax | age_in_minutes(START, END) |
|---|---|
| Parameter Description | 1. Start time, time-type parameter; 2. End time, time-type parameter |
| Return Type | Number |
| Description | Compares the difference between two times in minutes, with all time components involved in the calculation. |
| Example | age_in_minutes('2021-05-31 00:00:50', '2022-05-31 00:00:00'), returns: 525599 |
age_in_months
| Syntax | age_in_months(START, END) |
|---|---|
| Parameter Description | 1. Start time, time type parameter; 2. End time, time type parameter |
| Return Type | Number |
| Description | Compares the difference in months between two times, with all time components participating in the calculation. |
| Example | age_in_months('2021-05-31 00:00:50', '2022-05-31 00:00:00'), returns: 11 |
age_in_seconds
| Syntax | age_in_seconds(START, END) |
|---|---|
| Parameter Description | 1. Start time, time type parameter; 2. End time, time type parameter |
| Return Type | Number |
| Description | Compares the difference between two times in seconds, with all time components included in the calculation. |
| Example | age_in_seconds('2021-05-31 00:00:00.531', '2022-05-31 00:00:00.000'), returns: 31535999 |
age_in_years
| Syntax | age_in_years(START, END) |
|---|---|
| Parameter Description | 1. Start time, time-type parameter; 2. End time, time-type parameter |
| Return Type | Number |
| Description | Compares the difference in years between two times, with all time components participating in the calculation. |
| Example | age_in_years('2021-05-31 00:00:50', '2022-05-31 00:00:00'), returns: 0 |
diff_in_day
| Syntax | diff_in_day(START,END) |
|---|---|
| Parameter Description | 1. Specify the start, optional type: time; 2. Specify the end, optional type: time |
| Return Value Type | Number |
| Description | Compares the difference in days between two dates. |
| Example | diff_in_day('2015-02-03', '2018-02-03'), returns: 1095 |
diff_in_hour
| Syntax | diff_in_hour(START,END) |
|---|---|
| Parameter Description | 1. Specify the start time, optional type: time; 2. Specify the end time, optional type: time |
| Return Type | Number |
| Description | Compares the difference between two times in hours. |
| Example | diff_in_hour('2015-03-24 16:32:32', '2018-02-02 12:34:43'), returns: 25100 |
diff_in_minute
| Syntax | diff_in_minute(START,END) |
|---|---|
| Parameter Description | 1. Specify the start time, optional type: time; 2. Specify the end time, optional type: time |
| Return Type | Number |
| Description | Compares the difference between two times in minutes. |
| Example | diff_in_minute('2015-03-24 16:32:32', '2018-02-02 12:34:43'), returns: 1506002 |
diff_in_month
| Syntax | diff_in_month(START,END) |
|---|---|
| Parameter Description | 1. Specify the start, optional type: time; 2. Specify the end, optional type: time |
| Return Type | Number |
| Description | Compares the difference in months between two dates. |
| Example | diff_in_month('2015-02-03', '2018-02-03'), returns: 36 |
diff_in_year
| Syntax | diff_in_year(START,END) |
|---|---|
| Parameter Description | 1. Specify the start, optional type: time; 2. Specify the end, optional type: time |
| Return Type | Number |
| Description | Compares the difference in years between two times. |
| Example | diff_in_year('2015-02-03', '2018-02-03'), returns: 3 |
end_of_month
| Syntax | end_of_month(T) |
|---|---|
| Parameter | Time type parameter |
| Return Type | Time |
| Description | Retrieves the start time of the last day of the month for the given time field |
| Example | end_of_month('2018-02-02'), returns: 2018-02-28 |
extract_day
| Syntax | extract_day(T) |
|---|---|
| Parameter | Time type parameter |
| Return Type | Number |
| Description | Retrieves the day part of the parameter, indicating which day of the month it is. |
| Example | extract_day('2018-02-02'), returns: 2 |
extract_dow
| Syntax | extract_dow(T) |
|---|---|
| Parameter Description | Time-type parameter |
| Return Value Type | Number |
| Description | Retrieves the day part of the parameter, indicating which day of the week it is. |
| Example | extract_dow('2018-02-02'), returns: 5 |
extract_doy
| Syntax | extract_doy(T) |
|---|---|
| Parameter | Time type parameter |
| Return Type | Number |
| Description | Retrieves the day part of the parameter, indicating which day of the year it is. |
| Example | extract_doy('2018-02-02'), returns: 33 |
extract_hour
| Syntax | extract_hour(T) |
|---|---|
| Parameter Description | Time-type parameter |
| Return Value Type | Number |
| Description | Retrieves the hour part from the parameter, representing the current hour. |
| Example | extract_hour('2018-02-03 13:23:21'), returns: 13 |
extract_minute
| Syntax | extract_minute(T) |
|---|---|
| Parameter | Time type parameter |
| Return Type | Number |
| Description | Retrieves the minute part of the parameter, representing the current minute. |
| Example | extract_minute('2018-02-03 13:23:21'), returns: 23 |
extract_month
| Syntax | extract_month(T) |
|---|---|
| Parameter | Time type parameter |
| Return Type | Number |
| Description | Retrieves the month part from the parameter. |
| Example | extract_month('2018-02-02'), returns: 2 |
extract_quarter
| Syntax | extract_quarter(T) |
|---|---|
| Parameter | Time type parameter |
| Return Type | Number |
| Description | Retrieves the quarter part from the parameter. |
| Example | extract_quarter('2018-02-02'), returns: 1 |
extract_second
| Syntax | extract_second(T) |
|---|---|
| Parameter | Time type parameter |
| Return Type | Number |
| Description | Retrieves the second part of the parameter, representing the current second. |
| Example | extract_second('2018-02-03 13:23:21'), returns: 21 |
extract_week
| Syntax | extract_week(T) |
|---|---|
| Parameter | Time type parameter |
| Return Type | Number |
| Description | Retrieves the week part of the parameter, indicating which week of the year the given day belongs to. |
| Example | extract_week('2018-02-02'), returns: 4 |
extract_year
| Syntax | extract_year(T) |
|---|---|
| Parameter | Time type parameter |
| Return Type | Number |
| Description | Retrieves the year part from the parameter. |
| Example | extract_year('2018-02-02'), returns: 2018 |
month_weekday_count
| Syntax | month_weekday_count(YEAR,MONTH) |
|---|---|
| Parameter Description | 1. Year, optional type: number; 2. Month, optional type: number |
| Return Value Type | Number |
| Description | Retrieves the number of weekdays in the specified month. |
| Example | month_weekday_count(2018, 2), returns: 20 |
| Unsupported Data Sources | mysql, tidb, sqlserver, redshift, oracle, impala, hive, spark, db2, mongodb, maxcompute |
now
| Syntax | now() |
|---|---|
| Parameters | None |
| Return Type | Time |
| Description | Returns the current time, including hour, minute, and second information. |
| Example | now(), returns: current system time (e.g., 2024-05-20 15:30:45) |
parse_time
| Syntax | parse_time(S,LITERAL_PATTERN) |
|---|---|
| Parameter Description | 1. Text type parameter; 2. Specified pattern, optional type: string |
| Return Value Type | Time |
| Description | Parses the s parameter into a time type using literal_pattern |
| Example | parse_time('2024-05-20', 'yyyy-MM-dd'), returns: 2024-05-20 |
| Unsupported Data Source | sqlserver |
time_in_range
| Syntax | time_in_range(T,S) |
|---|---|
| Parameter Description | 1. Time type parameter; 2. Text type parameter |
| Return Type | Boolean |
| Description | Determines whether the date is within the specified range. Available ranges include: 'Current Year', 'Current Year-to-Date', 'Current Quarter', 'Current Quarter-to-Date', 'Current Month', 'Current Month-to-Date', 'Current Week', 'Current Week-to-Date', 'Today', 'Current Hour', 'Yesterday', 'Recent 7 days', 'Recent 30 days', 'Recent 90 days', 'All Available Date', 'All Invalid Date', 'All' |
| Example | time_in_range('2024-05-20', 'Today'), returns: TRUE (if the current date is 2024-05-20) |
today
| Syntax | today() |
|---|---|
| Parameter | None |
| Return Type | Date |
| Description | Returns the current date, excluding hour, minute, and second information. |
| Example | today(), returns: the current system date (e.g., 2024-05-20) |
trunc_day
| Syntax | trunc_day(T) |
|---|---|
| Parameter | Time type parameter |
| Return Type | Time |
| Description | Truncates the time or date column to the day. |
| Example | trunc_day('2018-02-02 12:30:59'), returns: '2018-02-02' |
trunc_hour
| Syntax | trunc_hour(T) |
|---|---|
| Parameter | Time type parameter |
| Return Type | Time |
| Description | Truncates the time column to the hour, setting finer granularity to 0. |
| Example | trunc_hour('2018-02-02 12:23:32'), returns: '2018-02-02 12:00:00' |
trunc_minute
| Syntax | trunc_minute(T) |
|---|---|
| Parameter Description | Time type parameter |
| Return Value Type | Time |
| Description | Truncates the time column to minutes, setting all finer granularity to 0. |
| Example | trunc_minute('2018-02-02 12:23:32'), returns: '2018-02-02 12:23:00' |
trunc_month
| Syntax | trunc_month(T) |
|---|---|
| Parameter Description | Time type parameter |
| Return Value Type | Time |
| Description | Truncates the time or date column to the month, setting the date to the 1st day of the month. |
| Example | trunc_month('2018-02-02 12:23:32'), returns: '2018-02-01' |
trunc_millisecond
| Syntax | trunc_millisecond(T) |
|---|---|
| Parameter | Time type parameter |
| Return Type | Time |
| Description | Truncates the time column to milliseconds, setting all finer granularity to 0. |
| Example | trunc_millisecond('2018-02-02 12:23:32.123'), returns: '2018-02-02 12:23:32.123' |
trunc_quarter
| Syntax | trunc_quarter(T) |
|---|---|
| Parameter Description | Time type parameter |
| Return Value Type | Time |
| Description | Truncates the time or date column to the quarter, sets the month to the first month of the quarter, and the date to the 1st. |
| Example | trunc_quarter('2018-05-02 12:23:32'), returns: '2018-04-01' |
trunc_second
| Syntax | trunc_second(T) |
|---|---|
| Parameter Description | Time type parameter |
| Return Value Type | Time |
| Description | Truncates the time column to seconds, setting all finer granularity to 0. |
| Example | trunc_second('2018-02-02 12:23:32.123'), returns: '2018-02-02 12:23:32.000' |
trunc_week
| Syntax | trunc_week(T) |
|---|---|
| Parameter | Time type parameter |
| Return Type | Time |
| Description | Truncates the time or date column to the week, setting the month and date to the first day of the week. |
| Example | trunc_week('2018-02-02'), returns: '2018-01-29' |
trunc_year
| Syntax | trunc_year(T) |
|---|---|
| Parameter Description | Time type parameter |
| Return Value Type | Time |
| Description | Truncates the time or date column to the year, setting the month and day to January 1st. |
| Example | trunc_year('2018-02-02 12:23:32'), returns: '2018-01-01' |