CLS provides time grouping, time truncation, time interval, and time sequence completion functions, and supports format conversion, grouping and aggregation, and other processing of date and time values in logs.
Note:
Among date and time functions, except the histogram and time_series functions that adopt the UTC+8 time zone, other Unix timestamp (unixtime) conversion functions adopt the UTC+0 time zone. To use another time zone, you need to use a function with the specified time zone feature, for example, such as from_unixtime(__TIMESTAMP__/1000, 'Asia/Shanghai'), or manually add the time zone offset for unixtime, for example, date_trunc('second', cast(__TIMESTAMP__+8*60*60*1000 as timestamp)).
Basic Functions
|
current_date | Returns the current date. Return value format: YYYY-MM-DD, such as 2021-05-21 Return value type: DATE | * | select current_date
|
current_time | Returns the current time. Return value format: HH:MM:SS.Ms Time zone, such as 17:07:52.143+08:00 Return value type: TIME | * | select current_time
|
current_timestamp | Returns the current timestamp. Return value format: YYYY-MM-DDTHH:MM:SS.Ms Time zone, such as 2021-07-15T17:10:56.735+08:00[Asia/Shanghai] Return value type: TIMESTAMP | * | select current_timestamp
|
current_timezone() | Returns the time zone defined by IANA (America/Los_Angeles) or the offset from UTC (+08:35). Return value type: VARCHAR, such as Asia/Shanghai | * | select current_timezone()
|
localtime | Returns the local time. Return value format: HH:MM:SS.Ms, such as 19:56:36 Return value type: TIME | * | select localtime
|
localtimestamp | Returns the local date and time. Return value format: YYYY-MM-DD HH:MM:SS.Ms, such as 2021-07-15 19:56:26.908 Return value type: TIMESTAMP | * | select localtimestamp
|
now() | Returns the current date and time. This function is used in the same way as the current_timestamp function. Return value format: YYYY-MM-DDTHH:MM:SS.Ms Time zone, such as 2021-07-15T17:10:56.735+08:00[Asia/Shanghai] Return value type: TIMESTAMP | * | select now()
|
last_day_of_month(x) | Returns the last day of a month. Return value format: YYYY-MM-DD, such as 2021-05-31 Return value type: DATE | * | select last_day_of_month(cast(__TIMESTAMP__ as timestamp))
|
from_iso8601_date(string) | Parses an ISO 8601 formatted string into a date. Return value format: YYYY-MM-DD, such as 2021-05-31 Return value type: DATE | * | select from_iso8601_date('2021-03-21')
|
from_iso8601_timestamp(string) | Parses an ISO 8601 formatted string into a timestamp with a time zone. Return value format: HH:MM:SS.Ms Time zone, such as 17:07:52.143+08:00 Return value type: TIMESTAMP | * | select from_iso8601_timestamp('2020-05-13')
|
from_unixtime(unixtime) | Parses a Unix formatted string into a timestamp. Return value format: YYYY-MM-DD HH:MM:SS.Ms, such as 2017-05-17 01:41:15.000 Return value type: TIMESTAMP | Example 1: * | select from_unixtime(1494985275) Example 2: * | select from_unixtime(__TIMESTAMP__/1000) |
from_unixtime(unixtime, zone) | Parses a Unix formatted string into a timestamp with a time zone. Return value format: YYYY-MM-DD HH:MM:SS.Ms Time zone, such as 2017-05-17T09:41:15+08:00[Asia/Shanghai] Return value type: TIMESTAMP | Example 1: * | select from_unixtime(1494985275, 'Asia/Shanghai') Example 2: * | select from_unixtime(__TIMESTAMP__/1000, 'Asia/Shanghai') |
to_unixtime(timestamp) | Parses a timestamp formatted string into a Unix timestamp. Return value type: LONG, such as 1626347592.037 | * | select to_unixtime(cast(__TIMESTAMP__ as timestamp))
|
to_milliseconds(interval) | Returns a time interval in milliseconds. Return value type: BIGINT, such as 300000 | * | select to_milliseconds(INTERVAL 5 MINUTE)
|
to_iso8601(x) | Parses a date and time expression of the DATE or TIMESTAMP type into a date and time expression in the ISO8601 format. | * | select to_iso8601(current_timestamp)
|
timezone_hour(timestamp) | Returns the hour offset of the timestamp's time zone. | * | SELECT current_timestamp, timezone_hour(current_timestamp)
|
timezone_minute(timestamp) | Returns the minute offset of the timestamp's time zone. | * | SELECT current_timestamp, timezone_minute(current_timestamp)
|
Time Grouping Function
The time grouping function can be used to group and aggregate the log data at a given interval. For example, you can use it to count page views (PV) every 5 minutes.
Function format
histogram(time_column, interval)
Parameter description
|
| Time column (KEY), such as \_\_TIMESTAMP\_\_. The value in this column must be a UNIX timestamp of the LONG type or a date and time expression of the TIMESTAMP type in milliseconds. If a value does not meet the requirement, use the cast function to convert the ISO 8601 formatted time string into the TIMESTAMP type, for example, cast('2020-08-19T03:18:29.000Z' as timestamp), or use the [date_parse](#date_parse) function to convert a time string of another custom type. If the time column adopts the TIMESTAMP type, the corresponding date and time expression must be in the UTC+0 time zone. If the date and time expression itself is in a different time zone, adjust it to UTC+0 by calculation. For example, if the time zone of the original time is UTC+8, use cast('2020-08-19T03:18:29.000Z' as timestamp) - interval 8 hour to adjust the time zone. |
| Time interval. The following time units are supported: SECOND, MINUTE, HOUR, and DAY. For example, INTERVAL 5 MINUTE indicates an interval of 5 minutes. |
Sample
Count the PV value every 5 minutes:
* | select histogram(__TIMESTAMP__, INTERVAL 5 MINUTE) AS dt, count(*) as PV group by dt order by dt limit 1000
Time Completion Function
The time_series() function can be used to group and aggregate the log data at a given interval. Its main difference from the histogram() function is that it can complete missing data in your query time window.
Note:
The time_series() function must be used with the GROUP BY and ORDER BY syntax, and ORDER BY syntax does not support desc sorting.
Function format
time_series(time_column, interval, format, padding)
Parameter description
|
| Time column (KEY), such as \_\_TIMESTAMP\_\_. The value in this column must be a UNIX timestamp of the LONG type or a date and time expression of the TIMESTAMP type in milliseconds. If a value does not meet the requirement, use the cast function to convert the ISO 8601 formatted time string into the TIMESTAMP type, for example, cast('2020-08-19T03:18:29.000Z' as timestamp), or use the [date_parse](#date_parse) function to convert a time string of another custom type. If the time column adopts the TIMESTAMP type, the corresponding date and time expression must be in the UTC+0 time zone. If the date and time expression itself is in a different time zone, adjust it to UTC+0 by calculation. For example, if the time zone of the original time is UTC+8, use cast('2020-08-19T03:18:29.000Z' as timestamp) - interval 8 hour to adjust the time zone. |
| Time interval. Valid values are s (second), m (minute), h (hour), and d (day). For example, 5m indicates 5 minutes. |
| Time format of the return result. |
| Value used to complete missing data. Valid values include: 0: Complete a missing value with 0 null: Complete a missing value with null last: Complete a missing value with the value of the previous point in time next: Complete a missing value with the value of the next point in time avg: Complete a missing value with the average value of the previous and next points in time |
Sample
Complete data with a time unit of 2 minutes:
* | select time_series(__TIMESTAMP__, '2m', '%Y-%m-%dT%H:%i:%s+08:00', '0') as time, count(*) as count group by time order by time limit 1000
Time Truncation Function
The date_trunc() function truncates a date and time expression based on the date part you specify, supporting alignment by second, minute, hour, day, month, and year. This function is often used in scenarios that require statistical analysis based on time.
|
| Truncates x to unit. x is of the TIMESTAMP type. | * | SELECT date_trunc('second', cast(__TIMESTAMP__ as timestamp))
|
The date_trunc() function supports the following units:
|
| | |
| | |
| | |
| | Returns the zero o'clock of a specified date. |
| | Returns the zero o'clock on Monday of a specified week. |
| | Returns the zero o'clock on the first day of a specified month. |
| | Returns the zero o'clock on the first day of a specified quarter. |
| | Returns the zero o'clock on the first day of a specified year. |
Time extraction functions are used to extract the specified time fields, such as the year and month, from date and time expressions.
|
| Extracts the specified fields from the date and time expression (x). | * |select extract(hour from cast('2021-05-21 05:20:01.100' as timestamp))
|
field supports the following values: year, quarter, month, week, day, day_of_month, day_of_week, dow, day_of_year, doy, year_of_week, yow, hour, minute, second.
extract(field FROM x) can be simplified to field(); for example, extract(hour from cast('2021-05-21 05:20:01.100' as timestamp)) can be simplified to hour(cast('2021-05-21 05:20:01.100' as timestamp)).
|
| | Extracts the year from the target date. | |
| | Extracts the quarter from the target date. | |
| | Extracts the month from the target date. | |
| | Calculates the week of the year the target date is in. | |
| | Extracts the day from the target date by month, which is equivalent to day_of_month. | |
| | | |
| | Calculates the day of the week for the target date, which is equivalent to dow. | |
| | Equivalent to day_of_week. | |
| | Calculates the day of the year for the target date, which is equivalent to doy. | |
| | Equivalent to day_of_year. | |
| | Returns the year for the target date in ISO week date, which is equivalent to yow. | |
| | Equivalent to year_of_week. | |
| | Extracts the hour from the target date. | |
| | Extracts the minute from the target date. | |
| | Extracts the second from the target date. | |
Time Interval Functions
Time interval functions perform time period-related operations, such as adding or subtracting a specified interval from a date or counting the time between two dates.
|
date_add(unit,value,timestamp) | Adds N time units (unit) to timestamp. If value is a negative value, subtraction is performed. | * | SELECT date_add('day', -1, TIMESTAMP '2020-03-03 03:01:00')
The return value is the date and time one day earlier than 2020-03-03 03:01:00, i.e., 2020-03-02 03:01:00. |
date_diff(unit, timestamp1, timestamp2) | Returns the time difference between two time expressions, for example, calculates the number of time units (unit) between timestamp1 and timestamp2. | * |SELECT date_diff('hour', TIMESTAMP '2020-03-01 00:00:00', TIMESTAMP '2020-03-02 00:00:00')
The return value is the time difference between 2020-03-01 and 2020-03-02, i.e., one day. |
The following units (unit) are supported:
Sample
Return the interval value in seconds between '2020-03-01 00:00:00' and '2020-03-02 00:00:00':
* | SELECT date_diff('second', TIMESTAMP '2020-03-01 00:00:00', TIMESTAMP '2020-03-02 00:00:00')
Duration Functions
|
| Parses a unit value string into a duration expression. Return value type: INTERVAL, such as 0 00:00:00.043 (D HH:MM:SS.Ms) | * | SELECT parse_duration('3.81 d')
|
human_readable_seconds(double) | Parses a unit value string into a duration expression. Return value type: VARCHAR, such as 1 minutes and 36 seconds | * | SELECT human_readable_seconds(96)
|
The following units are supported:
Sample
Parse the unit value string '3.81 d' into a duration string:
* | SELECT parse_duration('3.81 d')
Time Formatting Function
|
date_format(timestamp, format) | Parses a date and time string of the timestamp type into a string in the format format. | * | select date_format(cast(__TIMESTAMP__ as timestamp), '%Y-%m-%d')
|
date_parse(string, format) | Parses a date and time string in the format format into the timestamp type. | * | select date_parse('2017-05-17 09:45:00','%Y-%m-%d %H:%i:%s')
|
The following formats (format) are supported:
|
| Abbreviated names of the days of the week, such as Sun and Sat |
| Abbreviated month name, such as Jan and Dec |
| Month, numeric. Value range: 1-12 |
| Day of the month, decimal. Value range: 01-31 |
| Day of the month, decimal. Value range: 1-31 |
| Millisecond. Value range: 0-000000 |
| Hour, in the 24-hour time system |
| Hour, in the 12-hour time system |
| Hour, in the 12-hour time system |
| Minute, numeric. Value range: 00-59 |
| Day of the year. Value range: 001-366 |
| |
| |
| Month name in English, such as January and December |
| Month name in digits, such as 01 and 02 |
| |
| Time, in the 12-hour time system. Format: hh:mm:ss AM/PM |
| Second. Value range: 00-59 |
| Second. Value range: 00-59 |
| Time, in the 24-hour time system. Format: hh:mm:ss |
| Week of the year, where Monday is the first day of the week. Value range: 01-53 |
| Names of the days of the week, such as Sunday and Saturday |
| Year (4-digit), such as 2020 |
| Year (2-digit), such as 20 |
| |
Sample
Parse the time string '2017-05-17 09:45:00' in format into a date and time expression of the TIMESTAMP type, i.e., '2017-05-17 09:45:00.0':
* | SELECT date_parse('2017-05-17 09:45:00','%Y-%m-%d %H:%i:%s')
