This topic describes the date and time functions that you can use in Log Service to analyze log data. These functions include the time functions, date functions, interval functions, and a time series padding function.
Date and time data types
- Unix timestamp: an integer that indicates the number of seconds that have elapsed
since 00:00:00 Thursday, January 1, 1970. For example,
Mon Dec 4 15:54:27 CST 2017. The
__time__field in each log entry is of this type.
- TimeStamp: a string that specifies the date and time, for example,
The following table lists the date functions that are commonly used in Log Service.
||Returns the current date.||
||Returns the current time.||
||Returns the current TimeStamp by combining the results of current_date and current_time functions.||
||Returns the current time zone.||
||Converts an ISO 8601-formatted time into a TimeStamp that contains the time zone.||
||Converts an ISO 8601-formatted time into a date.||
||Converts a UNIX timestamp into a TimeStamp.||
||Converts a Unix timestamp into a TimeStamp based on the time zone that is specified by the string parameter.||
||Returns the current local time.||
||Returns the current local TimeStamp.||
||Equivalent to the
||Converts a TimeStamp into a Unix timestamp.||
The following table lists the time functions that you can use in Log Service to convert time in the formats supported in MySQL, such as %a, %b, and %y.
||Converts a TimeStamp into a string in the specified format.||
||Converts a string into a TimeStamp in the specified format.||
|%a||Abbreviated day name, such as Sun or Sat.|
|%b||Abbreviated month name, such as Jan or Dec.|
|%c||Month number. Valid values: 1 to 12.|
|%D||Day of month with a suffix, such as 0th, 1st, 2nd, and 3rd.|
|%d||Day of month. Valid values: 01 to 31.|
|%e||Day of month. Valid values: 1 to 31.|
|%H||The hour in the 24-hour clock.|
|%h||The hour in the 12-hour clock.|
|%I||The hour in the 12-hour clock.|
|%i||Minutes. Valid values: 00 to 59.|
|%j||Day of year. Valid values: 001 to 366.|
|%k||Hours. Valid values: 0 to 23.|
|%l||Hours. Valid values: 1 to 12.|
|%M||Month name. Valid values: January, February, March, April, May, June, July, August, September, October, November, and December.|
|%m||Month number. Valid values: 01 to 12.|
|%p||Meridian indicators. Valid values: AM and PM.|
|%r||The time in the 12-hour clock. The time is in the
|%S||Seconds. Valid values: 00 to 59.|
|%s||Seconds. Valid values: 00 to 59.|
|%T||The time in the 24-hour clock. The time is in the
|%V||Week number of year. Sunday is the first day of the week. This format is used together with %X. Valid values: 01 to 53.|
|%v||Week number of year. Monday is the first day of the week. This format is used together with %x. Valid values: 01 to 53.|
|%W||Day name. Valid values: Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, and Saturday.|
|%w||Day of week. Valid values: 0 to 6. The value 0 indicates Sunday.|
|%Y||4-digit year number.|
|%y||2-digit year number.|
|%%||Escapes the second percent sign (%).|
- Function syntax:
The value of the x parameter can be a TimeStamp or Unix timestamp.Assume that you set the x parameter to
2001-08-22 03:04:05.000. The following table lists the valid values of the unit parameter and the execution result.
Unit 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
You can use the date_trunc function to truncate a time only by the second, minute, hour, day, month, or year. To truncate a time by specified intervals such as five minutes, you must use a group by clause based on the modulus method.
* | SELECT count(1) as pv, __time__ - __time__% 300 as minute5 group by minute5 limit 100
In the preceding statement,
%300indicates that modulo 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
||Adds an interval
||Calculates the time difference between
|quarter||Unit: quarters, namely, three months.|
Time series padding function
group by time order by timeclause. In this case, the
order byclause does not support the
- Function syntax:
time_series(time_column, window, format, padding_data)
Parameter Description time_column The sequence of time, for example, the default time field
__time__that is provided by Log Service. The value is of the long or timestamp type.
window The size of the time window. The size is composed of a number and a unit. Valid units: s (seconds), m (minutes), h (hours), and d (days). For example, you can set the window to 2h, 5m, or 3d. format The MySQL time format. It is the format of the output. 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.