from_unixtime(__TIMESTAMP__/1000, 'Asia/Shanghai')) or manually add a time zone offset (for example, cast(__TIMESTAMP__+8*60*60*1000 as timestamp) or cast(__TIMESTAMP__ as timestamp)+ INTERVAL 8 HOUR).Function | Description | Example |
current_date | Returns the current date. Return value format: YYYY-MM-DD, such as 2021-05-21Return 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:00Return 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 type: VARCHAR, for example, Asia/Shanghai. | * | select current_timezone() |
localtime | Returns the local time. Return value format: HH:MM:SS.Ms, such as 19:56:36Return 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.908Return 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-31Return 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. Format: YYYY-MM-DD, for example, 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 2021-07-15T17:10:56.735+08:00[Asia/Shanghai] 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.000Return value type: TIMESTAMP Note: This function uses the UTC+0 time zone. To specify another time zone, use the from_unixtime(unixtime, zone) function. | Example 1: * | select from_unixtime(1494985275) |
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') |
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) |
histogram(time_column, interval)
Parameter | Description |
time_column | Time column (KEY). For example, __TIMESTAMP__. The values in this column should be millisecond-level UNIX timestamps of the LONG type or date or time expressions of the TIMESTAMP type. If the time column does not meet the preceding requirements, use the cast function to convert an ISO8601-formatted time string into the TIMESTAMP type, for example, cast('2020-08-19T03:18:29.000Z' as timestamp), or use the date_parse function to convert time strings of other custom types. Note: 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. |
interval | Fixed time interval. Supported units are SECOND (s), MINUTE (min), HOUR (hr), and DAY (d). For example, INTERVAL 5 MINUTE indicates an interval of five minutes. Dynamic time interval, in the format: ${__interval}. The value of ${__interval} automatically changes based on the query time range. For example, when data is queried from the last 1 hour, the time interval is 1 minute. When data is queried from the last 1 day, the time interval is 15 minutes. (Note: ${__interval} is not currently supported in APIs.) |
* | select histogram(__TIMESTAMP__, INTERVAL 5 MINUTE) AS dt, count(*) as PV group by dt order by dt limit 10000
* | select histogram(__TIMESTAMP__, INTERVAL ${__interval}) AS dt, count(*) as PV group by dt order by dt limit 10000
time_series(time_column, interval, format, padding)
Parameter | Description |
time_column | Time column (KEY). For example, __TIMESTAMP__. The values in this column should be millisecond-level UNIX timestamps of the LONG type or date or time expressions of the TIMESTAMP type. If the time column does not meet the preceding requirements, use the cast function to convert an ISO8601-formatted time string into the TIMESTAMP type, for example, cast('2020-08-19T03:18:29.000Z' as timestamp), or use the date_parse function to convert time strings of other custom types. Note: If the TIMESTAMP type is used for the time column, the date or time expressions should use the UTC+0 time zone. If other time zones are already used, convert them into UTC+0 through calculation. For example, if the original time uses Beijing Time (UTC+8), adjust it using cast('2020-08-19T03:18:29.000Z' as timestamp) - interval 8 hour. |
interval | Fixed time interval. Supported units are s (second), m (minute), h (hour), and d (day). For example, 5m indicates 5 minutes. Dynamic time interval, in the format: ${__interval}. The value of ${__interval} automatically changes based on the query time range. For example, when data is queried from the last 1 hour, the time interval is 1 minute. When data is queried from the last 1 day, the time interval is 15 minutes. (Note: ${__interval} is not currently supported in APIs.) |
format | |
padding | Content for completion. 0: Fill with 0. null: Fill with null. last: Replicate the value from the previous valid time point. next: Replicate the value from the next valid time point. avg: Fill with the average of the previous and next valid values. |
* | 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 10000
* | select time_series(__TIMESTAMP__, '${__interval}', '%Y-%m-%dT%H:%i:%s+08:00', '0') as time, count(*) as count group by time order by time limit 10000
Function | Description | Example |
date_trunc(unit,x) | Truncates x to unit. x is of the TIMESTAMP type. | * | SELECT date_trunc('second', cast(__TIMESTAMP__ as timestamp)) |
unit | Example Truncated Value | Description |
second | 2021-05-21 05:20:01.000 | - |
minute | 2021-05-21 05:20:00.000 | - |
hour | 2021-05-21 05:00:00.000 | - |
day | 2021-05-21 00:00:00.000 | Returns the zero o'clock of a specified date. |
week | 2021-05-19 00:00:00.000 | Returns the zero o'clock on Monday of a specified week. |
month | 2021-05-01 00:00:00.000 | Returns the zero o'clock on the first day of a specified month. |
quarter | 2021-04-01 00:00:00.000 | Returns the zero o'clock on the first day of a specified quarter. |
year | 2021-01-01 00:00:00.000 | Returns the zero o'clock on the first day of a specified year. |
Function | Description | Example |
extract(field FROM x) | 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)).Field | Extraction Result | Description | Simplified Format |
year | 2021 | Extracts the year from the target date. | year(x) |
quarter | 2 | Extracts the quarter from the target date. | quarter(x) |
month | 5 | Extracts the month from the target date. | month(x) |
week | 20 | Calculates the week of the year the target date is in. | week(x) |
day | 21 | Extracts the day from the target date by month, which is equivalent to day_of_month. | day(x) |
day_of_month | 21 | Equivalent to day. | day(x) |
day_of_week[] | 5 | Calculates the day of the week for the target date, which is equivalent to dow. | day_of_week(x) |
dow[] | 5 | Equivalent to day_of_week. | day_of_week(x) |
day_of_year | 141 | Calculates the day of the year for the target date, which is equivalent to doy. | day_of_year(x) |
doy | 141 | Equivalent to day_of_year. | day_of_year(x) |
year_of_week | 2021 | Extracts the year of the target date in the ISO week date calendar, which is equivalent to yow. | year_of_week(x) |
yow | 2021 | Equivalent to year_of_week. | year_of_week(x) |
hour | 5 | Extracts the hour from the target date. | hour(x) |
minute | 20 | Extracts the minute from the target date. | minute(x) |
second | 1 | Extracts the second from the target date. | second(x) |
Function | Description | Example |
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') |
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') |
unit) are supported:unit | Description |
millisecond | Millisecond |
second | Second |
minute | Minute |
hour | Hour |
day | Day |
week | Week |
month | Month |
quarter | Quarter of a year |
year | Year |
* | SELECT date_diff('second', TIMESTAMP '2020-03-01 00:00:00', TIMESTAMP '2020-03-02 00:00:00')
Function | Description | Example |
parse_duration(string) | 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) |
Unit | Description |
ns | Nanosecond |
us | Microsecond |
ms | Millisecond |
s | Second |
m | Minute |
h | Hour |
d | Day |
* | SELECT parse_duration('3.81 d')
Function | Description | Example |
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') |
Format | Description |
%a | Abbreviated names of the days of the week, such as Sun and Sat |
%b | Abbreviated month name, such as Jan and Dec |
%c | Month, numeric. Value range: III-12 |
%d | Day of the month, decimal. Value range: 01-31 |
%e | Day of the month, decimal. Value range: 1-31 |
%f | Fractional part of a second. The date_format function presents data in a six-digit format (000000–999000), whereas the date_parse function presents data with one to nine digits (0–999999999). |
%H | Hour, in the 24-hour time system |
%h | Hour, in the 12-hour time system |
%I | Hour, in the 12-hour time system |
%i | Minute, numeric. Value range: 00-59 |
%j | Day of the year. Value range: 001-366 |
%k | Hour. Value range: 0-23 |
%l | Hour. Value range: 1-12 |
%M | Month name in English, such as January and December |
%m | Month name in digits, such as 01 and 02 |
%p | AM or PM |
%r | Time, in the 12-hour time system. Format: hh:mm:ss AM/PM |
%S | Second. Value range: 00-59 Note: Milliseconds are not supported here. For milliseconds, use %f in combination with other functions. For example, substr(date_format(current_timestamp,'%Y-%m-%d %T.%f'),1,23). |
%s | Second. Value range: 00-59 |
%T | Time, in the 24-hour time system. Format: hh:mm:ss |
%v | Week of the year, where Monday is the first day of the week. Value range: 01-53 |
%W | Names of the days of the week, such as Sunday and Saturday |
%Y | Year (4-digit), such as 2020 |
%y | Year (2-digit), such as 20 |
%% | Escape character of % |
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')
피드백