During query and analysis, the system may need to process time fields in logs, such as converting a timestamp to a specific format. This topic provides examples on how to convert time fields.
Time fields
Time fields
__time__: This is a reserved field of Simple Log Service. The field records the log time that you specify when you use an API or SDK to write logs. You can use the field to ship, query, and analyze logs.
Original time field in raw logs: This field records the time when a log is generated.
Methods to convert time fields
Convert __time__ to a timestamp
You can use the from_unixtime function to convert the value of the __time__ field from a UNIX timestamp to a datetime expression that can return a timestamp value.
* | select from_unixtime(__time__) Display __time__ in a specific format
You can use the date_format function to convert the value of the __time__ field from a datetime expression that can return a timestamp value to a specific format.
* | select date_format(__time__, '%Y-%m-%d %H:%i:%S') Convert the original time field in a log to a specific format
You can perform the following steps to convert the original time field in a log from a string to a specific format.
Use the date_parse function to convert the time field from a string to the
Year-Month-Day Hour:Minute:Secondformat.Use the date_format function to extract the
Year-Month-Daypart.Use
group byto group data.
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:00Sample 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