更新时间:2024-07-25 GMT+08:00
分享

SQL时间函数

功能描述

时间函数可以与__time一起使用,任何存储为毫秒时间戳的列都可以使用 MILLIS_TO_TIMESTAMP 函数,或者任何存储为字符串时间戳的列都可以使用 TIME_PARSE 函数。默认情况下,时间操作使用UTC时区,您可以通过参数"timezone"设置为另一个时区的名称(如"Asia/Shanghai")或设置为偏移量(如"+08:00")来更改时区。

语法格式

语句

说明

示例

CURRENT_DATE

在连接时区的当期日期。

SELECT CURRENT_DATE

CURRENT_TIMESTAMP

在连接时区的当前时间戳。

SELECT CURRENT_TIMESTAMP

DATE_TRUNC(<unit>,<expr>)

截断时间戳,将其作为新时间戳返回。

SELECT DATE_TRUNC('minute',__time)

TIME_FORMAT(<expr>,<patten>,<timezone>))

使用给定的 pattern 或ISO8601(例如2000-01-02T03:04:05Z)将字符串解析为时间戳。timezone(如果提供)应为时区名称,如"America/Los_Angeles"或偏移量,如"+08:00",并将用作不包括时区偏移量的字符串的时区。模式和时区必须是字面量。无法解析为时间戳的字符串将返回空值。

SELECT TIME_FORMAT(__time,'yyyy-MM-dd HH:mm:ss','+08:00')

TIME_PARSE(<expr>,<pattern>,<timezone>)

使用给定的 pattern 或ISO8601(例如2000-01-02T03:04:05Z)将字符串解析为时间戳。时区(如果提供)应为时区名称,如"America/Los_Angeles"或偏移量,如"+08:00",并将用作不包括时区偏移量的字符串的时区。模式和时区必须是字面量。无法解析为时间戳的字符串将返回空值。

SELECT TIME_PARSE("timestamp",'yyyy-MM-dd HH:mm:ss','+08:00')

MILLIS_TO_TIMESTAMP(expr)

将时间戳转换为时间格式。

SELECT MILLIS_TO_TIMESTAMP(expr)

TIMESTAMP_TO_MILLIS(expr)

将时间转换为时间戳格式

SELECT TIMESTAMP_TO_MILLIS (expr)

EXTRACT(<extract_unit>FROM expr)

从expr中提取时间部分,并将其作为数字返回。

SELECT

EXTRACT(MINUTE FROM __time)

TIMESTAMPDIFF(<unit>,<expr1>,<expr2>)

返回expr1和expr2之间的unit

SELECT

TIMESTAMPDIFF (minute, expr1, expr2)

TIME_SERIES

补全您查询时间窗口内缺失的数据。

TIME_SERIES(__time, period, time_format, [padding_value],<timezone>)

TIME_SERIES函数

time_series函数用于补全您查询时间窗口内缺失的数据。
  • 必须搭配ORDER BY语法使用,并且time_series函数是ORDER BY的第一个参数
  • 查询语句中不能使用OFFSET语句
  • time_series函数不支持作为子查询使用

语法格式

time_series(__time, period, time_format, [padding_value], <timezone>)
参数说明
表1

参数

说明

__time

时间序列。

period

ISO 8601标准的时间窗口大小。例如P1M(1月)、P1D(1天)、PT1H(1小时)、PT1M(1分钟)、PT1S(1秒钟)。

time_format

返回结果的时间格式。(参考Joda DateTimeFormat模式)。

padding_value

补全的内容。包括:

  • 0 或 zero:将缺失的值设置为0(默认值)。
  • null:将缺失的值设置为null。
  • last:将缺失的值设置了上一个时间点对应的值。
  • next:将缺失的值设置了下一个时间点对应的值。
  • avg:将缺失的值设置为前后两个时间点的平均值。

timezone

时区。例如:北京时区:+08:00

返回类型

bigint类型。

示例说明

按照一天的时间粒度进行数据补全,将缺失的值设置为0,并添加时区。

  • 查询和分析语句
    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
  • 查询和分析结果

    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返回查询当天的凌晨零点的ISO8601时间,返回的为UTC时间,该函数可直接参与时间戳之间的运算

CURRENT_TIMESTAMP返回查询当前的ISO8601时间,返回的为UTC时间,该函数可直接参与时间戳之间的运算

  1. 字段样例

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

  2. 查询和分析语句
    select __time,CURRENT_DATE, CURRENT_TIMESTAMP,CURRENT_TIMESTAMP
  3. 查询和分析结果
    表2 查询和分析结果

    __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>)函数

舍去时间戳<timestamp_expr>中精度大于所选单位<unit>的值,将其置为零,并作为新时间戳返回。单位可以是'milliseconds'(毫秒), 'second'(秒), 'minute'(分), 'hour'(时), 'day'(日), 'week'(周), 'month'(月), 'quarter'(季), 'year'(年), 'decade'(十年), 'century'(千年),'millennium'(世纪), unit不区分大小写。

  1. 字段样例

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

  2. 查询和分析语句
    SELECT __time,DATE_TRUNC('minute', __time),DATE_TRUNC('day', __time),DATE_TRUNC('year', __time)
  3. 查询和分析结果
    表3 查询和分析结果

    __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>]])函数

TIME_PARSE将给定的字符串<timestamp_expr>,依据用户自定义的<pattern>参数以 Joda DateTimeFormat模式解析为时间戳。若<pattern>未填写则以默认的ISO8601将字符串解析。<timezone>为时区,可省略。

TIME_FORMAT将给定的时间戳< timestamp_expr>,依据用户自定义的<pattern>参数以 Joda DateTimeFormat模式解析为字符串。若<pattern>未填写则以默认的ISO8601将时间戳解析。<timezone>为时区,可省略。

  1. 字段样例

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

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

  2. 查询和分析语句
    SELECT __time,TIME_PARSE(start_time,'yyyy-MM-dd HH:mm:ss'),TIME_FORMAT(__time,'yyyy-MM-dd HH:mm:ss')
  3. 查询和分析结果
    表4 查询和分析结果

    __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函数将毫秒值转化为ISO8601格式的时间戳,转化后的参数可进行时间戳之间的运算。TIMESTAMP_TO_MILLIS将时间戳转化为毫秒值。

  1. 字段样例

    __time: 2023-02-16T07:54:15.106Zstart_time: 1676534055106

  2. 查询和分析语句
    SELECT __time,MILLIS_TO_TIMESTAMP(start_time),TIMESTAMP_TO_MILLIS(__time)
  3. 查询和分析结果
    表5 查询和分析结果

    __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函数,从timestamp_expr中提取时间部分,并将其作为数字返回。单位可以是EPOCH(返回纪元以来的秒值unix)、SECOND(当前分钟的秒值)、MINUTE(当前小时中的分钟值)、HOUR(当天的小时值)、DAY(当月的天数)、DOW(当前周的天数)、DOY(当前年的天数)、WEEK(当前年的周数)、MONTH(当前的月数)、QUARTER(当年的季度)或YEAR(返回当前年份)。<timezone>为时区,可省略。EXTRACT函数为TIME_EXTRACT的简写形式。

  1. 字段样例

    __time: 2023-02-16T07:54:15.106Zstart_time: 1676534055106

  2. 查询和分析语句
    SELECT __time,MILLIS_TO_TIMESTAMP(start_time),TIMESTAMP_TO_MILLIS(__time)
  3. 查询和分析结果
    表6 查询和分析结果

    __time

    EXPR$1

    EXPR$2

    2023-02-16T07:54:15.106Z

    2023-02-16T07:54:05.000Z

    1676534055106

参考信息

  • unit说明

    unit

    说明

    second

    minute

    hour

    day

    week

    month

    quarter

    year

  • extract_unit说明

    extract_unit

    说明

    SECOND

    MINUTE

    HOUR

    DAY

    每月的第几天

    DOW

    每周的第几天

    DOY

    每年的第几天

    WEEK

    每年的第几周

    MONTH

    QUARTER

    YEAR

相关文档