日期和时间函数

最近更新时间:2024-02-29 20:25:51

我的收藏
日志服务(Cloud Log Service,CLS)提供时间分组函数、时间截断函数、时间间隔函数和时序补全函数,支持对日志中的日期和时间进行格式转换,分组聚合等处理。
注意
转换 UNIX 时间戳时,除 histogram 函数和 time_series 函数采用 UTC+8 时区外,其它函数均采用 UTC+0 时区。如需转换时区,可使用具备指定时区功能的函数(例如 from_unixtime(__TIMESTAMP__/1000, 'Asia/Shanghai'))或手动添加时区偏移(例如 cast(__TIMESTAMP__+8*60*60*1000 as timestamp)cast(__TIMESTAMP__ as timestamp)+ INTERVAL 8 HOUR)。

基本函数

函数名称
说明
示例

current_date

返回当前日期。
返回值格式:YYYY-MM-DD,例如:2021-05-21。
返回值类型:DATE 。
* | select current_date

current_time

返回当前时间。
返回值格式:HH:MM:SS.Ms Time zone,例如:17:07:52.143+08:00。
返回值类型:TIME。
* | select current_time

current_timestamp

返回当前时间戳。
返回值格式:YYYY-MM-DDTHH:MM:SS.Ms Time zone,例如:2021-07-15T17:10:56.735+08:00[Asia/Shanghai]。
返回值类型:TIMESTAMP。
* | select current_timestamp

current_timezone
()
返回 IANA 定义的时区(America/Los_Angeles)或相对于 UTC 的偏移时差(+08:35)。
返回值类型:VARCHAR,例如: Asia/Shanghai。
* | select current_timezone()

localtime

返回本地时间。
返回值格式:HH:MM:SS.Ms,例如:19:56:36。
返回值类型:TIME。
* | select localtime

localtimestamp

返回本地的日期和时间。
返回值格式:YYYY-MM-DD HH:MM:SS.Ms,例如:2021-07-15 19:56:26.908。
返回值类型:TIMESTAMP。
* | select localtimestamp

now
()
返回当前日期和时间,与 current_timestamp 函数同等用法。
返回值格式:YYYY-MM-DDTHH:MM:SS.Ms Time zone,例如:2021-07-15T17:10:56.735+08:00[Asia/Shanghai]。
返回值类型:TIMESTAMP。
* | select now()

last_day_of_month
(x)
返回月份最后一天。
返回值格式:YYYY-MM-DD,例如:2021-05-31。
返回值类型:DATE。
* | select last_day_of_month(cast(__TIMESTAMP__ as timestamp))

from_iso8601_date
(string)
把 ISO8601 格式的日期表达式转化为 DATE 类型的日期表达式。
返回值格式:YYYY-MM-DD,例如:2021-05-31。
返回值类型:DATE。
* | select from_iso8601_date('2021-03-21')

from_iso8601_timestamp
(string)
把 ISO8601 格式的日期时间表达式转化为具有时区的 Timestamp 类型的日期时间表达式。
返回值格式:HH:MM:SS.Ms Time zone,例如:2021-07-15T17:10:56.735+08:00[Asia/Shanghai]。
返回值类型:TIMESTAMP。
* | select from_iso8601_timestamp('2020-05-13')

from_unixtime
(unixtime)
把 Unix 时间戳(秒级)转化为 TIMESTAMP 类型的日期时间表达式。
返回值格式:YYYY-MM-DD HH:MM:SS.Ms,例如:2017-05-17 01:41:15.000。
返回值类型:TIMESTAMP 。
注意:该函数采用UTC+0时区,如需指定时区请使用from_unixtime(unixtime, zone)
示例1: * | select from_unixtime(1494985275) 示例2: * | select from_unixtime(__TIMESTAMP__/1000)

from_unixtime
(unixtime, zone)
把 Unix 时间戳(秒级)转化为具有时区的 TIMESTAMP 类型的日期时间表达式。
返回值格式:YYYY-MM-DD HH:MM:SS.Ms Time zone,例如:2017-05-17T09:41:15+08:00[Asia/Shanghai]。
返回值类型:TIMESTAMP。
示例1: * | select from_unixtime(1494985275, 'Asia/Shanghai') 示例2: * | select from_unixtime(__TIMESTAMP__/1000, 'Asia/Shanghai')

to_unixtime
(timestamp)
把 TIMESTAMP 类型的日期时间表达式转化为 Unixtime 时间戳。
返回值类型:LONG。例如:1626347592。
* | select to_unixtime(cast(__TIMESTAMP__ as timestamp))

to_milliseconds
(interval)
以毫秒为单位返回间隔的时间值。
返回值类型:BIGINT。例如:300000。
* | select to_milliseconds(INTERVAL 5 MINUTE)

to_iso8601
(x)
将 DATE 类型或 TIMESTAMP 类型的日期和时间表达式转换为 ISO8601 格式的日期和时间表达式。
* | select to_iso8601(current_timestamp)

timezone_hour
(timestamp)
返回 TIMESTAMP 所属时区的小时偏移量。
* | SELECT current_timestamp, timezone_hour(current_timestamp)

timezone_minute
(timestamp)
返回 TIMESTAMP 所属时区的分钟偏移量
* | SELECT current_timestamp, timezone_minute(current_timestamp)

时间分组函数

支持按固定时间间隔对日志数据进行分组聚合统计,例如统计每5分钟的访问次数等场景。
函数格式
histogram(time_column, interval)
参数说明
参数
说明
time_column
时间列(KEY),例如 __TIMESTAMP__。该列的值必须为毫秒的 LONG 类型 UNIX 时间戳或 TIMESTAMP 类型的日期时间表达式。
如果时间列不符合上述要求,可以使用 cast 函数将 ISO8601 格式的时间字符串转换为 TIMESTAMP 类型,例如cast('2020-08-19T03:18:29.000Z' as timestamp),或使用 date_parse 函数转换其他自定义类型时间字符串。

注意:时间列使用 TIMESTAMP 时,其对应的日期时间表达式需要为 UTC+0 时区。如果日期时间表达式本身为其他时区,需通过计算调整为 UTC+0 时区。例如原始时间为北京时间(UTC+8)时,使用cast('2020-08-19T03:18:29.000Z' as timestamp) - interval 8 hour进行调整。
interval
固定时间间隔,支持单位为 SECOND(秒)、MINUTE(分)、HOUR(小时)、DAY(天)。例如时间间隔5分钟,即 INTERVAL 5 MINUTE。
动态时间间隔,格式为:${__interval}。${__interval}取值会依据查询的时间范围自动变化。例如,当查询最近1小时的数据,时间间隔是1分钟。查询最近1天的数据,时间间隔是15分钟。(注意:${__interval} 暂不支持在API中使用)
示例
1. 统计每5分钟访问次数 PV 值:
* | select histogram(__TIMESTAMP__, INTERVAL 5 MINUTE) AS dt, count(*) as PV group by dt order by dt limit 10000
2. 统计动态时间间隔的访问次数 PV 值:
* | select histogram(__TIMESTAMP__, INTERVAL ${__interval}) AS dt, count(*) as PV group by dt order by dt limit 10000

时间补全函数

time_series() 支持按固定时间间隔对日志数据进行分组聚合统计,与 histogram() 函数的主要差异在于支持补全查询时间窗口内缺失的数据。
注意
time_series() 函数必须搭配 GROUP BY 语法和 ORDER BY 语法使用,且 ORDER BY 语法不支持 desc 排序方式。
函数格式
time_series(time_column, interval, format, padding)
参数说明
参数
说明
time_column
时间列(KEY),例如 __TIMESTAMP__。该列的值必须为毫秒的 LONG 类型 UNIX 时间戳或 TIMESTAMP 类型的日期时间表达式。
如果时间列不符合上述要求,可以使用 cast 函数将 ISO8601 格式的时间字符串转换为 TIMESTAMP 类型,例如cast('2020-08-19T03:18:29.000Z' as timestamp),或使用 date_parse 函数转换其他自定义类型时间字符串。

注意:时间列使用 TIMESTAMP 时,其对应的日期时间表达式需要为 UTC+0 时区。如果日期时间表达式本身为其它时区,需通过计算调整为 UTC+0 时区。例如原始时间为北京时间(UTC+8)时,使用cast('2020-08-19T03:18:29.000Z' as timestamp) - interval 8 hour进行调整。
interval
固定时间间隔,支持单位为s(秒)、m(分)、h(小时)、d(天)。例如5分钟,即5m。
动态时间间隔,格式为:${__interval}。${__interval}取值会依据查询的时间范围自动变化。例如,当查询最近1小时的数据,时间间隔是1分钟。查询最近1天的数据,时间间隔是15分钟。(注意:${__interval} 暂不支持在API中使用)
format
返回结果的时间格式,使用 format 格式
padding
补全的内容,包括:
0:将缺失的值补全为0。
null:将缺失的值补全为 null。
ast:将缺失的值补全为上一个时间点的值。
next:将缺失的值补全为下一个时间点的值。
avg:将缺失的值补全为前后两个时间点的平均值。
示例
1. 按照2分钟的时间粒度进行数据补全,查询和分析语句如下:
* | 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
2. 按照动态时间粒度进行数据补全,查询和分析语句如下:
* | 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

时间截断函数

date_trunc() 函数根据您指定的日期时间部分截断日期时间表达式,支持按照秒、分钟,小时、日、月、年对齐。该函数常用于需要按照时间进行统计分析的场景。
函数名
说明
示例

date_trunc
(unit,x)
将 x 截断至 unit 单位。x 为 timestamp 类型。
* | SELECT date_trunc('second', cast(__TIMESTAMP__ as timestamp))
截断支持如下粒度:
unit
转化结果
说明
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
返回指定日期的零点。
week
2021-05-19 00:00:00.000
返回指定周的周一零点
month
2021-05-01 00:00:00.000
返回指定月份的第一天零点。
quarter
2021-04-01 00:00:00.000
返回指定季度的第一天零点
year
2021-01-01 00:00:00.000
返回本年度第一天零点。

时间提取函数

时间提取函数用于日期和时间表达式中提取指定的时间部分,例如提取年份、月份等。
函数名
说明
示例

extract
(field FROM x)
从日期和时间表达式(x)中提取指定的时间部分(field)。
* |select extract(hour from cast('2021-05-21 05:20:01.100' as timestamp))
field 支持如下取值: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) 也可以简化为field()的形式,例如extract(hour from cast('2021-05-21 05:20:01.100' as timestamp))可简化为hour(cast('2021-05-21 05:20:01.100' as timestamp))
field
提取结果
说明
简化形式
year
2021
提取目标日期中的年份。
year(x)
quarter
2
提取目标日期所属的季度。
quarter(x)
month
5
提取目标日期中的月份。
month(x)
week
20
计算目标日期是在一年中的第几周。
week(x)
day
21
提取目标日期中的天数,按月分别计算,等同于 day_of_month。
day(x)
day_of_month
21
等同于 day。
day(x)
day_of_week[]
5
计算目标日期是一周中的第几天,等同于 dow。
day_of_week(x)
dow[]
5
等同于 day_of_week。
day_of_week(x)
day_of_year
141
计算目标日期是一年中的第几天,等同于 doy。
day_of_year(x)
doy
141
等同于 day_of_year。
day_of_year(x)
year_of_week
2021
获取目标日期在 ISO week date 中的年份,等同于 yow。
year_of_week(x)
yow
2021
等同于 year_of_week。
year_of_week(x)
hour
5
提取目标日期中的小时。
hour(x)
minute
20
提取目标日期中的分钟。
minute(x)
second
1
提取目标日期中的秒。
second(x)

时间间隔函数

时间间隔函数用来执行时间段相关的运算,例如在日期中添加或减去指定的时间间隔、计算两个日期之间的时间。
函数名
说明
示例

date_add
(unit,value,timestamp)
在 timestamp 上加上 N 个时间单位(unit)。value 为负数则为减法。
* | SELECT date_add('day', -1, TIMESTAMP '2020-03-03 03:01:00') 返回2020年3月3日1天前的日期和时间,即2020-03-02 03:01:00。

date_diff
(unit, timestamp1, timestamp2)
返回两个时间表达式之间的时间差值,例如计算 timestamp1 和 timestamp2 之间相差几个时间单位(unit)。
* |SELECT date_diff('hour', TIMESTAMP '2020-03-01 00:00:00', TIMESTAMP '2020-03-02 00:00:00') 返回2020年3月1日和3月2日之间相差的时间单位值,即相差1天。
unit 取值如下:
unit
说明
millisecond
毫秒
second
minute
分钟
hour
小时
day
week
month
quarter
季度
year
示例
返回 '2020-03-01 00:00:00' 和 '2020-03-02 00:00:00' 在second单位下的间隔数值,查询分析语句如下:
* | SELECT date_diff('second', TIMESTAMP '2020-03-01 00:00:00', TIMESTAMP '2020-03-02 00:00:00')

时间持续函数

函数名
说明
示例

parse_duration
(string)
将单元值字符串转换为时间段表达式。
返回值类型:INTERVAL。例如:0 00:00:00.043(D HH:MM:SS.Ms)
* | SELECT parse_duration('3.81 d')

human_readable_seconds
(double)
将单元值字符串转换为时间段表达式。
返回值类型:VARCHAR。例如:1 minutes, 36 seconds。
* | SELECT human_readable_seconds(96)
支持粒度如下:
Unit
说明
ns
纳秒
us
微秒
ms
毫秒
s
m
分钟
h
小时
d
示例
将单元值'3.81 d' 单元字符串的值转换为间隔字符串的值,查询分析语句如下:
* | SELECT parse_duration('3.81 d')

时间格式化函数

函数名
说明
示例

date_format
(timestamp, format)
把 TIMESTAMP 类型的日期时间转化为 Format 格式的字符串。
* | select date_format(cast(__TIMESTAMP__ as timestamp), '%Y-%m-%d')

date_parse
(string, format)
把 Format 格式的日期时间字符串转化为 TIMESTAMP 类型。
* | select date_parse('2017-05-17 09:45:00','%Y-%m-%d %H:%i:%s')

Format 说明:

Format
说明
%a
星期的缩写。例如 Sun、Sat。
%b
月份的缩写。例如 Jan、Dec。
%c
月份。数值类型,取值范围为1 - 12。
%d
每月的第几天。十进制格式,取值范围为01 - 31。
%e
每月的第几天。十进制格式,取值范围为1 - 31。
%f
秒的小数部分,date_format展示数据时采用6位数字(000000~999000),date_parse转换数据时支持1~9位数字(0~999999999)。
%H
小时,24小时制。
%h
小时,12小时制。
%I
小时,12小时制。
%i
分钟。数值类型,取值范围为00 - 59。
%j
每年的第几天。取值范围为001 - 366。
%k
小时。取值范围为0 - 23。
%l
小时。取值范围为1 - 12。
%M
月份的英文表达,例如 January、December。
%m
月份的数字表达,例如 01、02。
%p
AM、PM。
%r
时间。12小时制,格式为hh:mm:ss AM/PM
%S
秒。取值范围为00 - 59。
注意:此处不支持毫秒,如需使用毫秒可使用%f并结合其它函数进行替代,例如 substr(date_format(current_timestamp,'%Y-%m-%d %T.%f'),1,23)
%s
秒。取值范围为00 - 59。
%T
时间。24小时制,格式为hh:mm:ss
%v
每年的第几周,星期一是一周的第一天。取值范围为01 - 53。
%W
星期几的名称。例如 Sunday、Saturday。
%Y
4位数的年份。例如2020。
%y
2位数的年份。例如20。
%%
%的转义字符。
示例
将 fomat 格式的时间字符串 '2017-05-17 09:45:00' 转换为 TIMESTAMP 类型的日期时间表达式,即 '2017-05-17 09:45:00.0' 。查询分析语句如下:
* | SELECT date_parse('2017-05-17 09:45:00','%Y-%m-%d %H:%i:%s')