Help Center/ Log Tank Service/ Best Practices/ Log Search and Analysis/ Using Time Functions to Convert Log Time Fields to a Specified Format
Updated on 2025-09-26 GMT+08:00
View PDF
Share

Using Time Functions to Convert Log Time Fields to a Specified Format

During log search and analysis, the time fields in logs must be processed, for example, by converting the timestamp to a specified format. This section describes common conversion examples.

This function is available only to whitelisted users in regions CN North-Beijing4, CN East-Shanghai1, CN South-Guangzhou, AP-Singapore, and LA-Mexico City2. To use it, submit a service ticket.

Time Field

Converting __time to a Timestamp

Use the from_unixtime function to convert the UNIX timestamp in the __time field to a date and time expression of the timestamp type.

* | select from_unixtime(__time) 
* | select from_unixtime(__time,'+08:00')

Printing __time in a Specified Format

Use the date_format function to convert the __time field from a date and time expression of the timestamp type to a specified format.

* | select time_format( from_unixtime(__time), 'yyyy-MM-dd HH:mm:ss','+08:00')

Use the time_format function to convert the __time field from a date and time expression of the timestamp type to a specified format.

* | select date_format(from_unixtime(__time,'+08:00'),'%Y-%m-%d %H:%i:%s')

Converting the Time in Logs to a Specified Format

Convert a time field in a log from a character string to a specified format as follows:

  1. Use the date_parse function to convert the time from a string into YYYY-MM-DD hh:mm:ss format.
  2. Use the date_format function to extract the year, month, and day.
  3. Use the group by statement to group the log data by date.
    • Log example: 
      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
Parent topic: Log Search and Analysis