Log Service supports time functions, date functions, and interval functions. You can use the date, time, and interval functions introduced in this document in the analysis syntax.
Date and time type
- unixtime: Indicates the number of seconds since January 1, 1970 in the type of int. 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 type: Indicates the time in the format of string. For example,
The common date functions supported by Log Service are as follows.
||Returns the current date.||
||Returns the current time.||
||Returns the result combined by current_date and current_time.||
||Returns the time zone.||
||Converts an iso8601 time to a date with time zone.||
||Converts an iso8601 time to a date.||
||Converts a UNIX time to a timestamp.||
||Converts a UNIX time to a timestamp by using the string as the time zone.||
||Returns the current time.||
||Returns the current timestamp.||
||Timestamp is converted into unixtime.||
Time FunctionMySQL time format
Log Service supports the MySQL time format such as %a, %b, and %y.
||Converts the timestamp into a format representation.||
||Parses a string into a timestamp by using the 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||Month, in the numeric type: 1 to 12.|
|%D||The day of each month with a suffix, such as 0th, 1st, 2nd, and 3nd.|
|%d||The day of each month, which is in decimal format and in the range of 01 to 31.|
|%e||The day of each month, which is in decimal format and in the range of 1 to 31.|
|%H||The hour in 24-hour format.|
|%h||The hour in 12-hour format.|
|%I||The hour in 12-hour format.|
|%i||Minutes, which is in the type of number and in the range of 00 to 59.|
|%j||The day of each year, which is in the range of 001 to 366.|
|%k||Hour, which is in the range of 0 to 23.|
|%l||Hour, which is in the range of 1 to 12.|
|%M||The English expression of a month, which is in the range of January to December.|
|%m||Month, which is in the numeric format and in the range 01 to 12.|
|%p||AM or PM.|
|%r||Time in 12-hour format:
|% S||Seconds, in the range of 00 to 59.|
|%s||Seconds, in the range of 00 to 59.|
|%T||Time, in the 24-hour format:
|%U||The week number of each year. Sunday is the first day of each week. The value range is from 00 to 53.|
|%u||The week number of each year. Monday is the first day of each week. The value range is 00 to 53.|
|%V||The week number of each year. Sunday is the first day of each week. The value range is 01 to 53. Use this format in conjunction with %X.|
|%v||The week number of each year. Monday is the first day of each week. The value range is 01 to 53. Use this format in conjunction with %x.|
|%W||The name of each day of a week, in the range of Sunday to Saturday.|
|%w||The day of the week, in the range of 0 to 6. Sunday is the day 0.|
|%Y||The year in the 4-digit format.|
|%y||The year in the 2-digit format.|
Log Service supports time period alignment functions, which can be aligned according to seconds, minutes, hours, days, months, and years. Time period alignment functions are usually used when statistics are made according to time.
The optional values for Unit are as follows (x is
x can be of the timestamp type or UNIX time type.
date_trunc can only make statistics every fixed time period. If you need to make statistics according to flexible time dimension, for example, make the statistics every five minutes, perform GROUP BY according to the mathematical modulus method.
* | SELECT count(1) as pv, __time__ - __time__% 300 as minute5groupby minute5 limit 100
%300 indicates to make the modulus and alignment every five minutes.
Date function example
*|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 delete an interval in the date, or calculate the time between two dates.
||The number of
|quarter||A quarter, namely, three months.|