How to convert date and time data types using SPL statements-Simple Log Service(SLS)-阿里云帮助中心

Concepts

Date and time processing in Simple Log Service (SLS) Structured Process Language (SPL) syntax involves three main data types: date and time strings, date and time objects, and UNIX timestamps.

Date and time string
A date and time string is a human-readable representation of a point in time. SPL syntax supports two forms:
- A date and time string with time zone information, such as 2019-06-02 18:41:26+08:00.
- A date and time string without time zone information, such as 2019-06-02 10:41:26.
A date and time string with time zone information includes a time zone offset appended to the date and time:
- 2019-06-02 18:41:26+08:00 indicates that the time is 2019-06-02 18:41:26 in the UTC+8 time zone.
- 2019-06-02 18:41:26-07:00 indicates that the time is 2019-06-02 18:41:26 in the UTC-7 time zone.
Date and time object

A date and time object is an instantiated value of the Datetime data type, used for display and readability.
UNIX timestamp
The number of seconds that have elapsed since 00:00:00 on January 1, 1970 (UTC). Common use cases include:
- To represent system time.
  In a log event, the __time__ meta field indicates when the log was generated, and the __receive_time__ field indicates when the log was received. Both fields store UNIX timestamps representing system time.
```
__source__:  192.0.2.1
__tag__:__receive_time__:  1562741899
__topic__: 
__time__: 1562731122
```
- For time-related calculations.
  Because UNIX timestamps are numerical values in seconds, they are well-suited for date and time calculations. For example:
  - Raw log
    time1: 1562741899 time2: 1562731122
  - SPL statement
    * | extend time_diff = cast(time1 as BIGINT) - cast(time2 as BIGINT)
  - Query and analysis results
    time1: 1562741899 time2: 1562731122 time_diff: 10777

Data type conversions and transform functions

Use case		Transform function
Convert between date and time objects and UNIX timestamps	Convert a date and time object to a UNIX timestamp.	to_unixtime: Converts a date and time object or string to a UNIX timestamp.
Convert between date and time objects and UNIX timestamps	Convert a UNIX timestamp to a date and time object.	from_unixtime: Converts a UNIX timestamp to a date and time object without a time zone.
Convert between date and time objects and date and time strings	Convert a date and time object to a date and time string.	date_format: Converts a date and time object of the timestamp type to a date and time string in a specified format.
	Convert a date and time string to a date and time object.	date_parse: Converts a date and time string to a date and time object of the timestamp type in a specified format.

Convert between date and time objects and UNIX timestamps

Functions
- to_unixtime: Converts a date and time object or string to a UNIX timestamp.
- from_unixtime: Converts a UNIX timestamp to a date and time object.
Use case 1: Use the to_unixtime function to convert a date and time object or string without a time zone to a UNIX timestamp.
- Raw log
```
time: 2023-09-21 10:59:37.055
```
- SPL statement
```
* | extend time=cast(time as TIMESTAMP) | extend new_time=to_unixtime(time)
```
- Query and analysis results
```
time: 2023-09-21 10:59:37.055
new_time:1695293977.055
```

Use case 2: Use the to_unixtime function to convert a date and time object or string with a time zone to a UNIX timestamp.

Raw log
```
time: 2024-03-13_16:44:58.800+0800
```

SPL statement

* | extend new_time=replace(replace(time, '+0800', ''), '_', ' ') | extend new_time = cast(to_unixtime(cast(new_time as TIMESTAMP)) as bigint) - 28800

Query and analysis results

time:2024-03-13_16:44:58.800+0800
new_time:1710319499

Use case 3: Use the from_unixtime function to convert a UNIX timestamp to a date and time object without a time zone.
- Raw log
```
time:1695191402
```
- SPL statement
```
* | extend new_time=from_unixtime(cast(time as DOUBLE))
```
- Query and analysis results
```
time: 1695191402
new_time:2023-09-20 06:30:02.000
```
- Use case 4: Use the from_unixtime function to convert a UNIX timestamp to a date and time object with a time offset.
  - Raw log
```
time:1695191402
```
  - SPL statement
```
* | extend new_time=from_unixtime(cast(time as DOUBLE) + 28800)
```
  - Query and analysis results
```
time:1695191402
new_time:2023-09-20 14:30:02.000
```

Convert between date and time objects and date and time strings

Functions
- date_format: Converts a date and time object of the timestamp type to a date and time string in a specified format.
- date_parse: Converts a date and time string to a date and time object of the timestamp type in a specified format.
Use case 1: Use the date_format function to convert a date and time object of the timestamp type to a date and time string in a specified format.
- Raw log
```
time: 2023-09-21 10:59:37.055
```
- SPL statement
```
* | extend time=cast(time as TIMESTAMP) | extend new_time=date_format(time, '%H:%i:%s')
```
- Query and analysis results
```
time: 2023-09-21 10:59:37.055
new_time: 10:59:37
```
Use case 2: Use the date_parse function to convert a date and time string to a date and time object of the timestamp type in a specified format.
- Raw log
```
time: 2022-10-19 15:46:05
```
- SPL statement
```
* | extend time=cast(time as varchar) | extend new_time=date_parse(time, '%Y-%m-%d %H:%i:%s')
```
- Query and analysis results
```
time: 2022-10-19 15:46:05
new_time: 2022-10-19 15:46:05.000
```