During query and analysis, you often need to process time fields in logs, such as converting a timestamp to a specified format. This topic uses some examples to describe how to convert time fields.

A log may include multiple fields that record the time. For example:
  • __time__: the log time that you specify when you use the API or SDK to write log data. This field can be used for log data shipping, query, and analysis.
  • Original time field in the log: the field that records the log event occurrence time when the log is generated. This field is in the raw log.

Time fields in different formats are difficult to view and read. In this case, you can convert the time fields to the specified format in query and analysis. For example:

Convert __time__ to a timestamp

We recommend that you use the from_unixtime function to convert the __time__ field to a timestamp.
* | select from_unixtime(__time__) 

Display __time__ in a specified format

Display the __time__ field in the format of YYYY-MM-DD HH:MM:SS. We recommend that you use the date_format function to convert the field.
* | select date_format(__time__, '%Y-%m-%d %H:%i:%S') 

Convert the time in a log to a specified format

Convert the time field in a log to the specified format (YYYY-MM-DD HH:MM:SS), and perform the GROUP BY operation on the YYYY-MM-DD part. We recommend that you use the date_format function to convert the field.
  • Sample log:
    __topic__:  
    body_byte_sent:  307
    hostname:  www.host1.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/7.1.8.452 V1_IPH_SQ_7.1.8_1_APP_A Pixel/750 Core/UIWebView NetType/WIFI QBWebViewType/1
    method:  GET
    referer:  www.host0.com
    remote_addr:  36.63.1.23
    request_length:  111
    request_time:  2.705
    status:  200
    upstream_response_time:  0.225582883754
    url:  /? k0=v9&
    time:2017-05-17 09:45:00
  • SQL statement example:
    * | 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