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

TIME_SERIES

The TIME_SERIES function is used to supplement the missing data in the query time window.

• 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>)

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.

1. Example field

   __time: 2023-02-14T02:35:56.706Z

2. Query and analysis statement

   select __time,CURRENT_DATE, CURRENT_TIMESTAMP,CURRENT_TIMESTAMP

3. 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.

1. Example field

   __time: 2023-02-14T02:35:56.706Z

2. Query and analysis statement

   SELECT __time,DATE_TRUNC('minute', __time),DATE_TRUNC('day', __time),DATE_TRUNC('year', __time)

3. 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.

1. Example field

   __time: 2023-02-16T07:38:25.306Z

   start_time:2023-02-14 02:35:56

2. 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')

3. 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.

1. Example field

   __time: 2023-02-16T07:54:15.106Z,start_time: 1676534055106

2. Query and analysis statement

   SELECT __time,MILLIS_TO_TIMESTAMP(start_time),TIMESTAMP_TO_MILLIS(__time)

3. 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.

1. Example field

   __time: 2023-02-16T07:54:15.106Z,start_time: 1676534055106

2. Query and analysis statement

   SELECT __time,MILLIS_TO_TIMESTAMP(start_time),TIMESTAMP_TO_MILLIS(__time)

3. 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