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
- Time field types
- Reserved field __time of LTS: log time specified when a log is written to LTS via an API or SDK. This field is used for log delivery, querying, and analysis.
- Original time field in logs: a field in raw logs. It records the time when a log event occurs.
- Time field format conversion modes
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:
- Use the date_parse function to convert the time from a string into YYYY-MM-DD hh:mm:ss format.
- Use the date_format function to extract the year, month, and day.
- 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
- Log example:
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot