日志服务支持时间函数、日期函数、区间函数和时序补全函数,本文介绍各个函数的使用及案例。

日期时间类型

  • UNIX时间戳类型:从1970年1月1日开始所经过的秒数,例如1512374067代表的时间是Mon Dec 4 15:54:27 CST 2017。日志服务每条日志中内置的时间字段__time__即为这种类型。
  • TimeStamp类型:以字符串形式表示时间,例如2017-11-01 13:30:00。

日期函数

函数名 含义 样例
current_date 当天日期。 latency>100| select current_date
current_time 当前时间。 latency>100| select current_time
current_timestamp 结合current_date和current_time的结果。 latency>100| select current_timestamp
current_timezone() 返回时区。 latency>100| select current_timezone()
from_iso8601_timestamp(string) 把ISO8601时间转化成带时区的时间。 latency>100| select from_iso8601_timestamp(iso8601)
from_iso8601_date(string) 把ISO8601转化成天。 latency>100| select from_iso8601_date(iso8601)
from_unixtime(unixtime) 把Unix时间戳转化为日期时间格式。 latency>100| select from_unixtime(1494985275)
from_unixtime(unixtime,string) 以String为时区,把Unix时间戳转化为日期时间。 latency>100| select from_unixtime (1494985275,'Asia/Shanghai')
localtime 本地时间。 latency>100| select localtime
localtimestamp 本地日期时间。 latency>100| select localtimestamp
now() 结合current_date和current_time的结果,等同于current_timestamp函数。
to_unixtime(timestamp) 把TimeStamp类型转化成Unix时间戳类型。 *| select to_unixtime('2017-05-17 09:45:00.848 Asia/Shanghai')

时间函数

函数名 含义 样例
date_format(timestamp, format) 把TimeStamp类型转化为format形式。 latency>100| select date_format (date_parse('2017-05-17 09:45:00','%Y-%m-%d %H:%i:%S'), '%Y-%m-%d')
date_parse(string, format) 把字符串以format形式表示,转化成TimeStamp类型。 latency>100|select date_format (date_parse(time,'%Y-%m-%d %H:%i:%S'), '%Y-%m-%d')
表 1. format说明
format 说明
%a 星期的缩写,即Sun、Sat等。
%b 月份的缩写,即Jan、Dec等。
%c 月份,数值类型,即1~12。
%D 每月的第几天,带后缀,即0th、1st、2nd、3rd等。
%d 每月第几天,十进制格式,范围为01~31。
%e 每月第几天,十进制格式,范围为1~31。
%H 小时,24小时制。
%h 小时,12小时制。
%I 小时,12小时制。
%i 分钟,数值类型,范围为00~59。
%j 每年的第几天,范围为001~366。
%k 小时,范围为0~23。
%l 小时,范围为1~12。
%M 月份的英文表达,范围为January~December。
%m 月份,数值格式,范围为01~12。
%p AM或PM。
%r 时间,12小时制,格式为hh:mm:ss AM/PM
%S 秒,范围为00~59。
%s 秒,范围为00~59。
%T 时间,24时制,格式为hh:mm:ss
%V 每年的第几周,星期日是一周的第一天。范围为01~53,与%X同时使用。
%v 每年的第几周,星期一是一周的第一天。范围为01~53,与%x同时使用。
%W 星期几的名称,范围为Sunday到Saturday。
%w 一周的第几天,星期日为第0天。
%Y 4位数的年份。
%y 2位数的年份。
%% %转义字符。

时间段对齐函数

日志服务支持时间段对齐函数,可以按照秒、分钟,小时、日、月、年等对齐。这个函数常用于一些按照时间进行统计的场景。
  • 函数语法
    date_trunc(unit, x)
  • 参数说明
    x可以是TimeStamp类型,也可以是UNIX时间戳类型。例如x为2001-08-22 03:04:05.000,Unit的取值包括以下类型:
    Unit 转化后结果
    second 2001-08-22 03:04:05.000
    minute 2001-08-22 03:04:00.000
    hour 2001-08-22 03:00:00.000
    day 2001-08-22 00:00:00.000
    week 2001-08-20 00:00:00.000
    month 2001-08-01 00:00:00.000
    quarter 2001-07-01 00:00:00.000
    year 2001-01-01 00:00:00.000
  • 示例

    date_trunc只能按照固定时间间隔统计,如果需要按照灵活的时间维度统计(例如统计每5分钟的数据),请使用数学取模方法进行分组。

    * | SELECT count(1) as pv,  __time__ - __time__% 300 as minute5 group by minute5 limit 100

    上述语句中的%300表示按照5分钟进行取模对齐。

    以下为使用时间格式的一个综合样例。
    *|select  date_trunc('minute' ,  __time__)  as t,
           truncate (avg(latency) ) ,
           current_date  
           group by   t
           order by t  desc 
           limit 60

时间间隔函数

时间间隔函数用来执行时间段相关的运算,如在日期中添加或减去指定的时间间隔、计算两个日期之间的时间。
函数名 含义 样例
date_add(unit, value, timestamp) timestamp上加上valueunit。如果要执行减法,value使用负值。 date_add('day', -7, '2018-08-09 00:00:00') 表示8月9号之前7天
date_diff(unit, timestamp1, timestamp2) 表示timestamp1timestamp2之间相差几个unit date_diff('day', '2018-08-02 00:00:00', '2018-08-09 00:00:00') = 7
该函数支持以下区间单位:
单位 说明
millisecond 毫秒
second
minute 分钟
hour 小时
day
week
month
quarter 季度,即三个月
year

时序补全函数

时序补全函数time_series用于处理某些时间缺少的情况。
说明 该函数必须和group by time order by time一起使用,且order by不支持desc排序方式。
  • 函数格式
    time_series(time_column, window, format, padding_data)
  • 参数说明
    参数 说明
    time_column 时间列,例如日志服务提供的默认时间字段__time__。格式为long类型或timestamp类型。
    window 窗口大小,由一个数字和单位组成。单位为s(秒)、m(分)、H(小时)、或d(天)。例如2h、5m、3d。
    format MySQL时间格式,表示最终输出的格式。
    padding_data 表示补全的内容,包括:
    • 0:补零
    • null:补null
    • last:补上一个值
    • next:补下一个值
    • avg:补前后的平均值
  • 示例:

    按照每两个小时进行格式化,查询分析语句如下所示:

    * | select time_series(__time__, '2h', '%Y-%m-%d %H:%i:%s', '0')  as stamp, count(*) as num from log group by stamp order by stamp
                            
    时间补全函数示例图