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 analysis statements.
Date and time data types
- Unix timestamp: 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,
1512374067indicates the time
Mon Dec 4 15:54:27 CST 2017. The built-in time
__time__in each log of Log Service is of this type.
- timestamp: the time in the format of string, for example,
The following table describes the common date functions supported by Log Service.
||Returns the current date.||
||Returns the current time.||
||Returns the current timestamp by combining the results of current_date and current_time.||
||Returns the current time zone.||
||Parses an ISO 8601-formatted time into a timestamp that contains the time zone.||
||Parses an ISO 8601-formatted time into a date.||
||Returns a Unix timestamp as a timestamp.||
||Returns a Unix timestamp as a timestamp that uses the specified string as the time zone.||
||Returns the current time.||
||Returns the current timestamp.||
||Functions the same as
||Returns a timestamp as a Unix timestamp.||
Time functionsMySQL time formats
Log Service supports the MySQL time formats, such as %a, %b, and %y.
||Formats a timestamp as a string by using the specified format.||
||Parses a string into a timestamp by using the specified format.||
|%a||The abbreviation of a day in a week, such as Sun and Sat.|
|%b||The abbreviation of a month, such as Jan and Dec.|
|%c||The month, of the numeric type. Valid values: [1, 12].|
|%D||The day in a month with a suffix, such as 0th, 1st, 2nd, and 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 minutes, of the numeric type. Valid values: [00, 59].|
|%j||The day in a 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:
|%S||The seconds. Valid values: [00, 59].|
|%s||The seconds. Valid values: [00, 59].|
|%T||The time in 24-hour format:
|%U||The week in a year, where Sunday is the first day of each week. Valid values: [00, 53].|
|%u||The week in a year, where Monday is the first day of each week. Valid values: [00, 53].|
|%V||The week in a year, where Sunday is the first day of each week. Valid values: [01, 53]. %V is used with %X.|
|%v||The week in a year, where Monday is the first day of each week. Valid values: [01, 53]. %v is used with %x.|
|%W||The day in a week. Valid values: Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, and Saturday.|
|%w||The day in a week, where Sunday is the first day of each week. Valid values: [0, 6].|
|%Y||The year in four-digit format.|
|%y||The year in two-digit format.|
|%%||The escape character %.|
Log Service supports a truncation function, which can return a time truncated by the second, minute, hour, day, month, and year. The truncation function is applicable to time-based statistics.
- Function syntax:
The value of the x parameter can be a timestamp or Unix timestamp.The following table lists the values of the unit parameter and the output results when the x parameter is set to
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
The date_trunc function is applicable only to statistics at a fixed time interval. For statistics based on flexible time dimensions, for example, every 5 minutes, you need to 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,
%300indicates that modulus and truncation are performed every 5 minutes.The following example shows 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 are used to perform interval-related calculation. For example, add or subtract an interval based on a date, or calculate the interval between two dates.
||Adds an interval
||Returns the time difference between
|quarter||The quarter, namely, three months.|
Time series padding function
The time series padding function time_series is used to pad time series data with the missing time.
- 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). For 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.
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 stampThe following figure shows the output result.