Simple Log Service (SLS) logs contain two types of time fields. Each type requires a different conversion approach for querying and analysis.
Time fields
-
Time fields
__time__: A reserved field that records the log time you specify when writing logs via the API or SDK. Use it for log shipping, querying, and analysis.
Original time field in raw logs: Records the time when a log is generated. Stored as a string in the raw log entry.
-
The following conversion scenarios are covered:
Convert __time__ to a timestamp
Use the from_unixtime function to convert __time__ from a UNIX timestamp integer to a datetime value.
* | select from_unixtime(__time__) Display __time__ in a specific format
Use the date_format function to format __time__ as a readable date-time string. The following example formats the output as YYYY-MM-DD HH:mm:SS.
* | select date_format(__time__, '%Y-%m-%d %H:%i:%S') Convert the original time field in a log to a specific format
When a raw log stores time as a string, use date_parse and date_format together to convert and reformat it. The following example extracts the date portion and counts the number of log entries per day.
Use the date_parse function to parse the time field string into the
Year-Month-Day Hour:Minute:Secondformat.Use the date_format function to extract the
Year-Month-Daypart.Use
group byto group data by day.
-
Sample log:
__topic__: body_byte_sent: 307 hostname: example.com http_user_agent: Mozilla/5.0 (iPhone; CPU iPhone OS 10_3_3 like Mac OS X) AppleWebKit/603.3.8 (KHTML, like Gecko) Mobile/14G60 QQ/192.0.2.1 V1_IPH_SQ_7.1.8_1_APP_A Pixel/750 Core/UIWebView NetType/WIFI QBWebViewType/1 method: GET referer: www.example.com remote_addr: 192.0.2.0 request_length: 111 request_time: 2.705 status: 200 upstream_response_time: 0.225582883754 url: /?k0=v9& time:2017-05-17 09:45:00 -
Sample SQL statement:
* | select date_format (date_parse(time,'%Y-%m-%d %H:%i:%S'), '%Y-%m-%d') as day, count(1) as uv group by day order by day asc