Log Service supports time functions, date functions, interval functions, and a time series padding function. You can use the functions described in this topic in query statements.

Date and time data types

  • UNIX time: the number of seconds that have elapsed since 00:00:00 (UTC) on January 1, 1970. The value is of the integer type. For example, 1512374067 indicates the time Mon Dec 4 15:54:27 CST 2017. Values of the __time__ field in each log entry of Log Service use UNIX time.
  • timestamp: the time in the format of string. Example: 2017-11-01 13:30:00.

Date functions

The following table describes the common date functions supported by Log Service.

Function Description Example
current_date Returns the current date. latency>100| select current_date
current_time Returns the current time. latency>100| select current_time
current_timestamp Returns the current timestamp by combining the results of current_date and current_time. latency>100| select current_timestamp
current_timezone() Returns the current time zone. latency>100| select current_timezone()
from_iso8601_timestamp(string) Parses an ISO 8601-formatted time into a timestamp that contains the time zone. latency>100| select from_iso8601_timestamp(iso8601)
from_iso8601_date(string) Parses an ISO 8601-formatted time into a date. latency>100| select from_iso8601_date(iso8601)
from_unixtime(unixtime) Parses a UNIX time into a timestamp. latency>100| select from_unixtime(1494985275)
from_unixtime(unixtime,string) Parses a Unix time into a timestamp that uses the specified string as the time zone. latency>100| select from_unixtime (1494985275,'Asia/Shanghai')
localtime Returns the current local time. latency>100| select localtime
localtimestamp Returns the current local timestamp. latency>100| select localtimestamp
now() Functions the same as current_timestamp. N/A
to_unixtime(timestamp) Parses a timestamp into a Unix time. *| select to_unixtime('2017-05-17 09:45:00.848 Asia/Shanghai')

Time functions

Log Service supports MySQL time formats such as %a, %b, and %y.

Function Description Example
date_format(timestamp, format) Converts a timestamp to a string in the specified 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) Parses a string into a timestamp in the specified format. latency>100|select date_format (date_parse(time,'%Y-%m-%d %H:%i:%S'), '%Y-%m-%d')
Table 1. Format description
Format Description
%a The abbreviation of the day of the week, such as Sun or Sat.
%b The abbreviation of the month, such as Jan or Dec.
%c The month, as a numeric value. Valid values: [1, 12].
%D The day in the month with a suffix, such as 0th, 1st, 2nd, or 3rd.
%d The day in a month in decimal format. Valid values: [01,31].
%e The day in a month in decimal format. Valid values: [1, 31].
%H The hour in 24-hour format.
%h The hour in 12-hour format.
%I The hour in 12-hour format.
%i The minute, as a numeric value. Valid values: [00, 59].
%j The day of the year. Valid values: [001, 366].
%k The hour. Valid values: [0, 23].
%l The hour. Valid values: [1, 12].
%M The month. Valid values: January, February, March, April, May, June, July, August, September, October, November, and December.
%m The month of the numeric type. Valid values: [01, 12].
%p The abbreviation of a period in 12-hour format. Valid values: AM and PM.
%r The time in 12-hour format: hh:mm:ss AM/PM.
%S The seconds. Valid values: [00, 59].
%s The seconds. Valid values: [00, 59].
%T The time in 24-hour format: hh:mm:ss.
%U The week of the year, where Sunday is the first day of the week. Valid values: [00, 53].
%u The week of the year, where Monday is the first day of the week. Valid values: [00, 53].
%V The week of the year, where Sunday is the first day of the week. Valid values: [01, 53]. %V is used together with %X.
%v The week of the year, where Monday is the first day of the week. Valid values: [01, 53]. %v is used together with %x.
%W The day of the week. Valid values: Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, and Saturday.
%w The day of the week, where Sunday is the first day of the week. Valid values: [0, 6].
%Y The year in four-digit format.
%y The year in two-digit format.
%% The escape character %.

Truncation function

Log Service supports a truncation function, which can return a time truncated to the second, minute, hour, day, month, and year. The truncation function is applicable to time-based statistics.
  • Function syntax: 
    date_trunc(unit, x)
  • Parameters:

    The value of the x parameter can be a timestamp or UNIX time.

    The following table lists the values of the unit parameter and the output results when the x parameter is set to 2001-08-22 03:04:05.000.
    Value Output result
    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
  • Example:

    The date_trunc function is applicable only to statistics at a fixed time interval. For statistics based on flexible time dimensions, you must use a GROUP BY clause according to the mathematical modulus method. 

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

    In the preceding statement, %300 indicates that modulus and truncation are performed every 5 minutes.

    The following statement provides an example on how to use the date_trunc function: 
    *|select  date_trunc('minute' ,  __time__)  as t,
       truncate (avg(latency) ) ,
       current_date  
       group by   t
       order by t  desc 
       limit 60

Interval functions

Interval functions are used to perform interval-related calculation. For example, you can add or subtract intervals based on a date, or calculate the interval between two dates.
Function Description Example
date_add(unit, value, timestamp) Adds an interval value of the unit type to a timestamp. To subtract an interval, use a negative value. date_add('day', -7, '2018-08-09 00:00:00'): indicates seven days before August 9.
date_diff(unit, timestamp1, timestamp2) Returns the time difference between timestamp1 and timestamp2 expressed in terms of unit. date_diff('day', '2018-08-02 00:00:00', '2018-08-09 00:00:00') = 7
The following table lists the values of the unit parameter supported by the interval functions.
Value Description
millisecond The millisecond.
second The second.
minute The minute.
hour The hour.
day The day.
week The week.
month The month.
quarter The three-month quarter.
year The year.

Time series padding function

The time series padding function time_series is used to pad the missing points in time series data.
Note This function must be used together with group by time order by time. In addition, order by does not support the desc sorting method.
  • Function format: 
    time_series(time_column, window, format, padding_data)
  • The following table describes the parameters.
    Parameter Description
    time_column The sequence of time, such as the default time field __time__ provided by Log Service. The value is of the long or timestamp type.
    window The size of the time window, which is composed of a number and a unit. Unit: s (seconds), m (minutes), h (hours), or d (days). Example: 2h, 5m, or 3d.
    format The MySQL time format, which is the final output format.
    padding_data The content to be added. Valid values:
    • 0: adds 0.
    • null: adds null.
    • last: adds the last value.
    • next: adds the next value.
    • avg: adds the average value of the last and next values.
  • Example:

    The following statement is used to format data every 2 hours:

    * | 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
    The following figure shows the output result.