Time Functions
Description
Time functions can be used with __time. Any column stored as a millisecond timestamp can use the MILLIS_TO_TIMESTAMP function, or any column stored as a string timestamp can use the TIME_PARSE function. By default, the UTC time is used for time operations. You can change the time zone by setting the timezone parameter to the name of another time zone (for example, Asia/Shanghai) or to an offset (for example, +08:00).
Syntax
Statement |
Description |
Example |
---|---|---|
CURRENT_DATE |
Current date in the connected time zone. |
SELECT CURRENT_DATE |
CURRENT_TIMESTAMP |
Current timestamp in the connected time zone. |
SELECT CURRENT_TIMESTAMP |
DATE_TRUNC(<unit>,<expr>) |
Truncates the timestamp and returns it as a new timestamp. |
SELECT DATE_TRUNC('minute',__time) |
TIME_FORMAT(<expr>,<patten>,<timezone>)) |
Parses a character string into a timestamp using the specified pattern or ISO 8601 (for example, 2000-01-02T03:04:05Z). timezone (if specified) should be a time zone name (such as America/Los_Angeles) or an offset (such as +08:00), and will be used as the time zone of the string that does not include a time zone offset. The mode and time zone must be literal. If a character string cannot be parsed as a timestamp, null is returned. |
SELECT TIME_FORMAT(__time,'yyyy-MM-dd HH:mm:ss','+08:00') |
TIME_PARSE(<expr>,<pattern>,<timezone>) |
Parses a character string into a timestamp using the specified pattern or ISO 8601 (for example, 2000-01-02T03:04:05Z). timezone (if specified) should be a time zone name (such as America/Los_Angeles) or an offset (such as +08:00), and will be used as the time zone of the string that does not include a time zone offset. The mode and time zone must be literal. If a character string cannot be parsed as a timestamp, null is returned. |
SELECT TIME_PARSE("timestamp",'yyyy-MM-dd HH:mm:ss','+08:00') |
MILLIS_TO_TIMESTAMP(expr) |
Converts the timestamp to the time format. |
SELECT MILLIS_TO_TIMESTAMP(expr) |
TIMESTAMP_TO_MILLIS(expr) |
Converts the time to the timestamp format. |
SELECT TIMESTAMP_TO_MILLIS (expr) |
EXTRACT(<extract_unit>FROM expr) |
Extracts the time part from expr and returns it as a number. |
SELECT EXTRACT(MINUTE FROM __time) |
TIMESTAMPDIFF(<unit>,<expr1>,<expr2>) |
Returns the unit between expr1 and expr2. |
SELECT TIMESTAMPDIFF (minute, expr1, expr2) |
TIME_SERIES |
Completes the missing data in the query time window. |
TIME_SERIES(__time, period, time_format, [padding_value],<timezone>) |
TIME_SERIES
![](https://support.huaweicloud.com/intl/en-us/usermanual-lts/public_sys-resources/note_3.0-en-us.png)
- This function must be used together with ORDER BY and must be the first parameter of ORDER BY.
- The OFFSET statement cannot be used in the query statement.
- The TIME_SERIES function cannot be used as a subquery.
Syntax
time_series(__time, period, time_format, [padding_value], <timezone>)
Parameter |
Description |
---|---|
__time |
Time sequence. |
period |
Time window size (ISO 8601 standard). For example, P1M (1 month), P1D (1 day), PT1H (1 hour), PT1M (1 minute), and PT1S (1 second). |
time_format |
Time format of the returned result. (For details, see Joda DateTimeFormat.) |
padding_value |
Supplemented content. Options:
|
timezone |
Time zone, for example, UTC+08:00 (Beijing). |
Returned Data Type
The value is of the bigint type.
Example
Supplement data based on the time granularity of a day, set the missing value to 0, and add a time zone.
- Query and analysis statement
select time_series(__time, 'P1D', 'yyyy-MM-dd HH:mm:ss', '0', '+08:00') as t_time, count(*) as num group by t_time order by t_time
- Query and analysis results
t_time
num
2021-10-01 08:00:00
5
2021-10-02 08:00:00
0
2021-10-03 08:00:00
0
2021-10-04 08:00:00
21
2021-10-05 08:00:00
17
2021-10-06 08:00:00
0
2021-10-07 08:00:00
34
CURRENT_DATE/CURRENT_TIMESTAMP
CURRENT_DATE returns the ISO 8601 time (UTC time) of 00:00 on the current day. This function can be used to calculate timestamps.
CURRENT_TIMESTAMP returns the ISO 8601 time (UTC time) of the current time. This function can be used to calculate timestamps.
- Example field
- Query and analysis statement
select __time,CURRENT_DATE, CURRENT_TIMESTAMP,CURRENT_TIMESTAMP
- Query and analysis result
Table 2 Query and analysis result __time
CURRENT_DATE
CURRENT_TIMESTAMP
2023-02-14T02:35:56.706Z
2023-02-14T00:00:00.000Z
2023-02-14T14:35:57.000Z
DATE_TRUNC (<unit>, <timestamp_expr>)
Truncates <timestamp_expr> to the precision specified in <unit>, fills in 0, and returns the value as a new timestamp. The unit is case-insensitive, and can be milliseconds, second, minute, hour, day, week, month, quarter, year, decade, century, or millennium.
- Example field
- Query and analysis statement
SELECT __time,DATE_TRUNC('minute', __time),DATE_TRUNC('day', __time),DATE_TRUNC('year', __time)
- Query and analysis result
Table 3 Query and analysis result __time
EXPR$1
EXPR$2
EXPR$3
2023-02-14T02:35:56.706Z
2023-02-15T08:50:00.000Z
2023-02-15T00:00:00.000Z
2023-01-01T00:00:00.000Z
TIME_PARSE(<string_expr>, [<pattern>, [<timezone>]])/TIME_FORMAT(<timestamp_expr>, [<pattern>, [<timezone>]])
Parses the given character string <timestamp_expr> into a timestamp according to the custom <pattern> parameter and Joda DateTimeFormat mode. If <pattern> is not specified, the character string is parsed according to ISO 8601 by default. <timezone> is optional.
Parses the given timestamp <timestamp_expr> into a character string according to the custom <pattern> parameter and Joda DateTimeFormat mode. If <pattern> is not specified, the timestamp is parsed according to ISO 8601 by default. <timezone> is optional.
- Example field
__time: 2023-02-16T07:38:25.306Z
start_time:2023-02-14 02:35:56
- Query and analysis statement
SELECT __time,TIME_PARSE(start_time,'yyyy-MM-dd HH:mm:ss'),TIME_FORMAT(__time,'yyyy-MM-dd HH:mm:ss')
- Query and analysis result
Table 4 Query and analysis result __time
EXPR$1
EXPR$2
2023-02-16T07:38:25.306Z
2023-02-14T02:35:56.000Z
2023-02-16 07:38:25
MILLIS_TO_TIMESTAMP(millis_expr)/TIMESTAMP_TO_MILLIS(timestamp_expr)
MILLIS_TO_TIMESTAMP converts a number of milliseconds into a timestamp in ISO 8601 format. The converted parameter can be used to calculate timestamps. TIMESTAMP_TO_MILLIS converts a timestamp to a number of milliseconds.
- Example field
- Query and analysis statement
SELECT __time,MILLIS_TO_TIMESTAMP(start_time),TIMESTAMP_TO_MILLIS(__time)
- Query and analysis result
Table 5 Query and analysis result __time
EXPR$1
EXPR$2
2023-02-16T07:54:15.106Z
2023-02-16T07:54:05.000Z
1676534055106
TIME_EXTRACT(<timestamp_expr>,[<unit>,[<timezone>]])/EXTRACT(<unit> FROM timestamp_expr)
TIME_EXTRACT extracts time from timestamp_expr and returns it as a number. The unit can be EPOCH (Unix time in seconds since the epoch), SECOND (second of the current minute), MINUTE (minute of the current hour), HOUR (hour of the current day), DAY (day of the current month), DOW (day of the current week), DOY (day of the current year), WEEK (week of the current year), MONTH (month of the current year), QUARTER (quarter of the current year), or YEAR (current year). <timezone> is optional. EXTRACT is short for TIME_EXTRACT.
- Example field
- Query and analysis statement
SELECT __time,MILLIS_TO_TIMESTAMP(start_time),TIMESTAMP_TO_MILLIS(__time)
- Query and analysis result
Table 6 Query and analysis result __time
EXPR$1
EXPR$2
2023-02-16T07:54:15.106Z
2023-02-16T07:54:05.000Z
1676534055106
Reference Information
- Unit
unit
Description
second
Second
minute
Minute
hour
Hour
day
Day
week
Week
month
Month
quarter
Quarter
year
Year
- extract_unit
extract_unit
Description
SECOND
Second
MINUTE
Minute
HOUR
Hour
DAY
Day of a month
DOW
Day of a week
DOY
Day of a year
WEEK
Week of a year
MONTH
Month
QUARTER
Quarter
YEAR
Year
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot