Log Service supports time functions and date functions. You can use the date and time functions introduced in this document in the analysis syntax.

Date and time type

  1. unixtime: Indicates the number of seconds since January 1, 1970 in the type of int. For example, 1512374067 indicates 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.
  2. timestamp type: Indicates the time in the format of string. For example, 2017-11-01 13:30:00.

Date Functions

The common date functions supported by Log Service are as follows.

Function name Meaning Example
current_date Returns the current date. latency>100| select current_date
current_time hour:minute; second, millisecond time zone latency>100| select current_time
current_timestamp Returns the result combined by current_date and current_time. latency>100| select current_timestamp
current_timezone() Returns the time zone. latency>100| select current_timezone()
from_iso8601_timestamp(string) Converts an iso8601 time to a date. latency>100| select from_iso8601_timestamp(iso8601)
from_iso8601_date(string) Converts an iso8601 time to a date. latency>100| select from_iso8601_date(iso8601)
from_unixtime(unixtime) Converts a UNIX time to a timestamp. Latency> 100 | select (1494985275)
Unixtime (string) Converts a UNIX time to a timestamp using the string as the time zone. latency>100| select from_unixtime (1494985275,'Asia/Shanghai')
localtime Returns the current time. latency>100| select localtime
localtimestamp Returns the current timestamp. latency>100| select localtimestamp
now() Equivalent to current_timestamp . -
to_unixtime(timestamp) Timestamp is converted into unixtime. *| select to_unixtime('2017-05-17 09:45:00.848 Asia/Shanghai')

Time Function

MySQL time format

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

Function name Meaning Example
date_format(timestamp, format) Converts the timestamp into a format representation. latency>100| select date_format (date_parse('2017-05-17 09:45:00','%Y-%m-%d %H:%i:%S'), '%Y-%m-%d') group by method
date_parse(string, format) Parses a string into a timestamp using the format. latency>100|select date_parse('2017-05-17 09:45:00','%Y-%m-%d %H:%i:%S') group by method
Table 1. Description
Format Description
%a The abbreviation of a day in a week (Sun  .. Sat).
%b Month abbreviations (Jan ..  .. Sat).
%c The month in the type of number (1 ..  12) [4].
%D The day of the month with the suffix (0th, 1st, 2nd, 3rd, …).
%d The day of the month (01 ..  31) [4].
%e The day of the month (1 ..  31) [4].
%H The hour (00 .. 23).
%h Hours (01 .. 12).
%I The hour in 12-hour format (01 .. 12).
%i The minute (00 ..  59).
%j The day of the year (001 ..  366).
%k The hour (0 ..  23).
%l The hour (1  .. 12).
%M The month in English (January ..  Dece mber).
%m The month in number (01 ..  12) [4].
%p AM or PM.
%r The time in 12-hour format. The format is hh:mm:ss followed by AM or PM.
%S The second (00 ..  59).
%s The second (00 ..  59).
%T The time in 24-hour format (hh:mm:ss).
%U The week of the year (00 ..  53).
%u The week of the year (00 ..  53).
%V The week of the year (01 ..  53).
%v The week of the year (01 ..  53), where Monday is the first day of the week; used with %x.
%W The name of a day in a week (Sunday ..  Saturday)。
%w The day of the week (0 ..  6). Sunday is the day 0.
%Y The year.
%y The year. Double digit.
%% %escape character
Time period alignment functions

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.

  Function syntax:

date_trunc(unit, x)

 Parameters:

The optional values for Unit are as follows (x is 2001-08-22 03:04:05.000）：

Unit Converted 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

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

The %300 indicates to make the modulus and alignment every five minutes.

Date function example

The following is a comprehensive example using the time format: 
*|select date_trunc('minute' , __time__) as t,
       truncate (avg(latency) ) ,
       current_date  
       group by t
       order by t desc 
       limit 60