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,
1512374067indicates 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:
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.||
||Parses a UNIX time into a timestamp.||
||Parses a Unix time into a timestamp that uses the specified string as the time zone.||
||Returns the current local time.||
||Returns the current local timestamp.||
||Functions the same as
||Parses a timestamp into a Unix time.||
Log Service supports MySQL time formats such as %a, %b, and %y.
||Converts a timestamp to a string in the specified format.||
||Parses a string into a timestamp in the specified format.||
|%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:
|%S||The seconds. Valid values: [00, 59].|
|%s||The seconds. Valid values: [00, 59].|
|%T||The time in 24-hour format:
|%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 %.|
- Function syntax:
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
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, 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,
%300indicates 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
||Adds an interval
||Returns the time difference between
|quarter||The three-month quarter.|
Time series padding function
group by time order by time. In addition,
order bydoes not support the
- 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.
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.