Date and time functions

current_date

The current_date function returns the current date in the YYYY-MM-DD format.

Syntax

current_date

Return value type

Date type.

Example

Queries yesterday's logs.

• Query and analysis statement (debug)

  * |
  SELECT
    *
  FROM  log
  WHERE
    __time__ < to_unixtime(current_date)
    AND __time__ > to_unixtime(date_add('day', -1, current_date))

• The query and analysis results are shown in the table below. The returned data is from yesterday's logs and includes fields such as body_bytes_sent, client_ip, host, and http_user_agent.

current_time

The current_time function returns the current time and time zone in the HH:MM:SS.Ms Time_zone format.

Syntax

current_time

Return value type

Time type.

Example

Queries the current time and time zone.

• Query and analysis statement (debug)

  * | select current_time

• Query and analysis result: The _col0 column returns 22:34:24.034 Asia/Shanghai.

current_timestamp

The current_timestamp function returns the current date, time, and time zone in the YYYY-MM-DD HH:MM:SS.Ms Time_zone format.

Syntax

current_timestamp

Return value type

Timestamp type.

Example

Queries yesterday's logs.

• Query and analysis statement (debug)

  * |
  SELECT
    *
  FROM  log
  WHERE
    __time__ < to_unixtime(current_timestamp)
    AND __time__ > to_unixtime(date_add('day', -1, current_timestamp))

• The query and analysis results are shown in the following table and include yesterday's log data with fields such as body_bytes_sent, client_ip, host, and http_user_agent.

current_timezone

The current_timezone function returns the current time zone.

Syntax

current_timezone()

Return value type

Varchar type.

Example

Queries the current time zone.

• Query and analysis statement (debug)

  * | select current_timezone()

• Query and analysis result: The _col0 column returns Asia/Shanghai.

date

The date function is used to extract the date part from a datetime expression. The date function is equivalent to cast(x as date). For more information, see Type conversion functions.

Syntax

date(x)

Parameter

Return value type

Date type.

Example

This example extracts the date from the current timestamp.

• Query and analysis statement (debug)

  * | SELECT current_timestamp, date(current_timestamp)

• Query and analysis result: The _col0 column returns the full timestamp, and the _col1 column returns the date part, such as 2021-09-07.

date_format

The date_format function converts a timestamp expression to a string in the specified format.

Syntax

date_format(x, format)

Parameter

Return value type

Varchar type.

Example

Calculates the Nginx request statuses and counts, and displays them in chronological order. It first aligns log times to the minute by using date_trunc, converts the time to the %H:%i format by using date_format, calculates the number of requests for each status code per minute, and displays the query and analysis results as a streamgraph.

• Query and analysis statement (debug)

  * |
  SELECT
    date_format(date_trunc('minute', __time__), '%H:%i') AS time,
    COUNT(1) AS count,
    status
  GROUP BY
    time,
    status
  ORDER BY
    time

• Query and analysis result

date_parse

The date_parse function converts a string in a specified format to a timestamp value.

Syntax

date_parse(x, format)

Parameter

Return value type

Timestamp type.

Example

Convert the StartTime field value and the EndTime field value to the timestamp type, and calculate the time difference between them.

• Query and analysis statement

  *|
  SELECT
      date_parse(StartTime, '%Y-%m-%d %H:%i') AS "StartTime",
      date_parse(EndTime, '%Y-%m-%d %H:%i') AS "EndTime",
      date_diff('hour', StartTime, EndTime) AS "Time Difference (hours)"

• Query and analysis result: The query returns a StartTime of 2021-07-21 20:00:00.000, an EndTime of 2021-07-21 21:00:00.000, and a time difference of 1 hour.

from_iso8601_date

The from_iso8601_date function converts an ISO 8601-formatted date string to a date value.

Syntax

from_iso8601_date(x)

Parameter

Return value type

Date type.

Example

Convert the value of the time field to a date expression of the date type.

• Field sample

  time:2020-05-03

• Query and analysis statement

  * | select from_iso8601_date(time)

• Query and analysis result: The _col0 column returns 2020-05-03, which is a date value.

from_iso8601_timestamp

The from_iso8601_timestamp function converts an ISO 8601-formatted timestamp string to a timestamp value.

Syntax

from_iso8601_timestamp(x)

Parameter

Return value type

Timestamp type.

Example

This example converts the time field to a timestamp value.

• Field sample

  time:2020-05-03T17:30:08

• Query and analysis statement

  * | select from_iso8601_timestamp(time)

• Query and analysis result: The return value in the _col0 column is 2020-05-03 17:30:08.000 Asia/Shanghai, a timestamp type with a time zone.

from_unixtime

The from_unixtime function converts a Unix timestamp to a timestamp value.

Syntax

• Convert to a timestamp value without time zone information.

  from_unixtime(x)

• Convert to a timestamp value with time zone information.

  from_unixtime(x,time zone)

• Convert to a timestamp value with time zone information, where hours and minutes specify the time zone offset.

  from_unixtime(x, hours, minutes)

Parameter

Return value type

Timestamp type.

Example

Convert the value of the time field to a datetime expression of the timestamp type with a time zone.

• Field sample

  time:1626774758

• Query and analysis statement

  * | select from_unixtime(time,'Asia/Shanghai')

• Query and analysis result: The _col0 column returns 2021-07-20 17:52:38.000 Asia/Shanghai.

localtime

The localtime function returns the local time in the HH:MM:SS.Ms format.

Syntax

localtime

Return value type

Time type.

Example

Queries the local time.

• Query and analysis statement (debug)

  * | select localtime

• Query and analysis result: The _col0 column returns 02:09:46.213.

localtimestamp

The localtimestamp function returns the local date and time in the YYYY-MM-DD HH:MM:SS.Ms Time_zone format.

Syntax

localtimestamp

Return value type

Timestamp type.

Example

Queries yesterday's logs.

• Query and analysis statement (debug)

  * |
  SELECT
    *
  FROM  log
  WHERE
    __time__ < to_unixtime(localtimestamp)
    AND __time__ > to_unixtime(date_add('day', -1, localtimestamp))

• The query and analysis results are shown in the table below. The results consist of log data from yesterday and include fields such as body_bytes_sent, client_ip, host, and http_user_agent.

now

The now function returns the current date and time in the YYYY-MM-DD HH:MM:SS.Ms Time_zone format. The now function is an alias for the current_timestamp function.

Syntax

now()

Return value type

Timestamp type.

Example

Queries yesterday's logs.

• Query and analysis statement (debug)

  * |
  SELECT
    *
  FROM  log
  WHERE
    __time__ < to_unixtime(now())
    AND __time__ > to_unixtime(date_add('day', -1, now()))

• The query and analysis results are shown in the table below. The results are yesterday's log data, which includes fields such as body_bytes_sent, client_ip, host, and http_user_agent.

to_iso8601

The to_iso8601 function converts a date or timestamp expression to an ISO 8601-formatted string.

Syntax

to_iso8601(x)

Parameter

Return value type

Varchar type.

Example

This example uses to_iso8601 to convert the value from current_timestamp into an ISO 8601-formatted string.

• Query and analysis statement (debug)

  * | select to_iso8601(current_timestamp) AS ISO8601

• Query and analysis result: The ISO8601 column returns 2021-09-02T16:57:56.964+08:00.

to_unixtime

The to_unixtime function converts a timestamp expression to a Unix timestamp.

Syntax

to_unixtime(x)

Parameter

Return value type

Double type.

Example

Queries yesterday's logs.

• Query and analysis statement (debug)

  * |
  SELECT
    *
  FROM  log
  WHERE
    __time__ < to_unixtime(now())
    AND __time__ > to_unixtime(date_add('day', -1, now()))

• The query and analysis results are shown in the table below. The query returned log data from yesterday that includes fields such as body_bytes_sent, client_ip, host, and http_user_agent.

current_unixtimestamp

This function returns the system's current Unix timestamp. A Unix timestamp is the total number of seconds that have elapsed since 00:00:00 Coordinated Universal Time (UTC) on January 1, 1970.

Syntax

current_unixtimestamp()

Return value

• Return value type: BIGINT

• Return value description: Returns an integer that represents the number of seconds since the Unix epoch.

• Precision: The default precision is to the second.

Example

• Example 1: Get the current timestamp.

  This basic usage is common for recording data insertion times.

  * | extend now_ts =current_unixtimestamp()
  -- Sample output: 1734913028

• Example 2: Format conversion

  It is often used with from_unixtime to convert a timestamp back to a human-readable date format.

  * | extend readable_time = 	date_format(from_unixtime(cast(current_unixtimestamp() as double)), '%Y-%m-%d %H:%i:%s')
  -- Sample output: 2025-12-23 00:17:08

day function

The day function returns the day of the month from a datetime expression and is equivalent to the day_of_month function.

Syntax

day(x)

Parameters

Return value type

bigint

Example

This example uses the current_date function to get the current date, and then the day function to return the day of the month.

• Query and analysis statement (Debug)

  * | SELECT current_date, day(current_date)

• Query and analysis results: The _col0 column returns the current date, and the _col1 column returns 7, indicating the seventh day of the month.

day_of_month function

The day_of_month function returns the day of the month from a datetime expression and is equivalent to the day function.

Syntax

day_of_month(x)

Parameters

Return value type

bigint

Example

This example uses the current_date function to get the current date and the day_of_month function to return the day of the month.

• Query statement (Debug)

  * | SELECT current_date, day_of_month(current_date)

• Query and analysis results: The _col0 column returns the current date, and the _col1 column returns 7, the day of the month.

day_of_week function

The day_of_week function returns an integer representing the day of the week from a date or datetime expression.

Syntax

day_of_week(x)

Parameters

Return type

The return type is bigint.

Example

This example retrieves the current date using the current_date function, and then extracts the day of the week using the day_of_week function.

• Query and analysis statement (Debug)

  * | SELECT current_date, day_of_week(current_date)

• Query and analysis results: The _col0 column returns the current date, and the _col1 column returns 2, which represents the second day of the week (Tuesday).

day_of_year function

The day_of_year function returns the day of the year from a datetime expression.

Syntax

day_of_year(x)

Parameters

Return type

bigint

Example

This example uses the current_date function to get the current date and the day_of_year function to return the day of the year.

• Query statement (Debug)

  * | SELECT current_date, day_of_year(current_date)

• Query and analysis results: The _col0 column returns the current date, and the _col1 column returns 250, meaning the current date is the 250th day of the year.

dow function

The dow function returns the day of the week from a datetime expression. This function is equivalent to the day_of_week function.

Syntax

dow(x)

Parameters

Return value type

bigint

Example

This example uses the dow function to extract the day of the week from the current date.

• Query and analysis statement (Debug)

  * | SELECT current_date, dow(current_date)

• Query and analysis results: The _col0 column returns the current date, and the _col1 column returns 2, which represents the second day of the week (Tuesday).

doy function

The doy function returns the day of the year from a datetime expression. This function is equivalent to the day_of_year function.

Syntax

doy(x)

Parameters

Return value type

bigint

Example

This example uses the current_date function to get the current date, and the doy function to return the day of the year.

• Query statement (Debug)

  * | SELECT current_date, doy(current_date)

• Query and analysis results: The _col0 column returns the current date, and the _col1 column returns 250, indicating the current date is the 250th day of the year.

Extract function

The extract function extracts a specific part, such as the year or hour, from a date or time expression based on the specified field.

Syntax

extract(field from x)

Parameters

Return value type

Returns a bigint value.

Example

This example extracts the year from the date returned by the current_date function.

• Query statement (Debug)

  * | SELECT extract(year from current_date)

• Query and analysis results: The _col0 column returns the extracted year: 2021.

Hour function

The hour function extracts the hour (0-23) from a datetime expression.

Syntax

hour(x)

Parameters

Return value type

The function returns a value of the bigint type.

Example

This example uses the current_timestamp function to get the current timestamp and the hour function to extract the hour.

• Query statement (Debug)

  * | SELECT current_timestamp, hour(current_timestamp)

• Query and analysis result: The _col0 column returns the current timestamp, while the _col1 column returns the current hour: 11.

Minute function

The minute function returns the minute component (from 0 to 59) of a timestamp.

Syntax

minute(x)

Parameters

Return value type

Returns a bigint value.

Example

This example retrieves the current timestamp with the current_timestamp function and then extracts the minute value.

• Query and analysis statement (Debug)

  * | SELECT current_timestamp, minute(current_timestamp)

• Query and analysis results: The _col0 column contains the current timestamp, and the _col1 column contains the minute component, such as 17.

Month function

The month function extracts the month from a datetime expression.

Syntax

month(x)

Parameters

Return value type

bigint

Example

This example retrieves the current timestamp using the current_timestamp function and then extracts the month using the month function.

• Query statement (Debug)

  * | SELECT current_timestamp, month(current_timestamp)

• Query and analysis results: The _col0 column returns the current timestamp, and the _col1 column returns the current month (9).

Quarter function

The quarter function returns the quarter of a date.

Syntax

quarter(x)

Parameters

Return value type

bigint

Example

This example calculates the quarter of the current date by using the current_timestamp and quarter functions.

• Query statement (Debug)

  * | SELECT current_timestamp,quarter(current_timestamp)

• Query and analysis results: The _col0 column returns the current timestamp, and the _col1 column returns 3, the current quarter.

Second function

The second function returns the second component of a timestamp expression.

Syntax

second(x)

Parameters

Return value type

A bigint value.

Example

This example uses the second function to extract the seconds from the value returned by the current_timestamp function.

• Query statement (debug)

  * | SELECT current_timestamp,second(current_timestamp)

• Query and analysis results: The _col0 column returns the current timestamp, and the _col1 column returns 41, the current second.

timezone_hour function

This function returns the time zone hour offset.

Syntax

timezone_hour(x)

Parameters

Return type

A bigint value.

Example

This example retrieves the time zone hour offset of the current time.

• Query and analysis statement (Debug)

  * | SELECT current_timestamp, timezone_hour(current_timestamp)

• Query and analysis results: The _col0 column returns the current timestamp, and the _col1 column returns 8, the time zone hour offset (UTC+8).

Timezone_minute function

The timezone_minute function calculates the minute offset of a time zone.

Syntax

timezone_minute(x)

Parameters

Return type

A value of the bigint type.

Example

Use current_timestamp to get the current timestamp, and then use timezone_minute to calculate the minute offset of the time zone.

• Query and analysis statement (Debug)

  * | SELECT current_timestamp,timezone_minute(current_timestamp)

• Query and analysis results: The _col0 column returns the current timestamp, and the _col1 column returns 0, which is the time zone minute offset.

Week function

The week function returns the week of the year for a specified date and is equivalent to the week_of_year function.

Syntax

week(x)

Parameters

Return value type

bigint

Example

Use the current_timestamp function to get the current date and time. Then, use the week function to calculate the current week of the year.

• Query statement (Debug)

  * | SELECT current_timestamp, week(current_timestamp)

• Query and analysis results: The _col0 column returns the current timestamp, and the _col1 column returns 36, the current week of the year.

week_of_year function

The week_of_year function, an alias for the week function, returns the week of the year for a date.

Syntax

week_of_year(x)

Parameters

Return value type

bigint

Example

This example retrieves the week of the year from the current timestamp.

• Query and analysis statement (debug)

  * | SELECT current_timestamp, week_of_year(current_timestamp)

• Query and analysis results: The _col0 column returns the current timestamp, and the _col1 column returns 36, indicating the current date is in the 36th week of the year.

Year function

The year function extracts the year from a date or timestamp.

Syntax

year(x)

Parameters

Return value type

Returns a bigint value.

Example

This example uses the current_timestamp function to get the current date and time, and the year function to extract the year.

• Query and analysis statement (debug)

  * | SELECT current_timestamp,year(current_timestamp)

• Query and analysis results: The _col0 column returns the current timestamp, and the _col1 column returns the current year.

year_of_week function

The year_of_week function returns the year of a given date in the ISO week date system. This function is equivalent to the yow function.

Syntax

year_of_week(x)

Parameters

Return value type

bigint.

Examples

This example uses the year_of_week function with the current_timestamp function to return the year of the current date in the ISO week date system.

• Query and analysis statement (debug)

  * | SELECT current_timestamp,year_of_week(current_timestamp)

• Query and analysis results: The _col0 column returns the current timestamp, and the _col1 column returns the year in the ISO week date system, for example, 2021.

yow function

The yow function returns the year for a date according to the ISO week date system. This function is an alias for the year_of_week function.

Syntax

yow(x)

Parameters

Return value type

The function returns a value of the bigint type.

Example

This example retrieves the current date and time using the current_timestamp function. It then uses the yow function to return the year according to the ISO week date system.

• Query statement (Debug)

  * | SELECT current_timestamp, yow(current_timestamp)

• Query and analysis results: The _col0 column returns the current timestamp, and the _col1 column returns the current year based on the ISO week date system.

date_trunc function

The date_trunc function truncates a date and time expression to a specified time unit. This function is commonly used for time-based aggregation and analysis.

Syntax

date_trunc(unit, x)

Parameters

* | SELECT count(1) AS pv,  __time__ - __time__ %300 AS time GROUP BY time LIMIT 100

Return type

The return type is the same as the type of the x parameter.

Example

Calculates the average request time per minute, then groups and sorts the results by time.

• Query and analysis statement (Debug)

  * |
  SELECT
    date_trunc('minute', __time__) AS time,
    truncate (avg(request_time)) AS avg_time,
    current_date AS date
  GROUP BY
    time
  ORDER BY
    time DESC
  LIMIT
    100

• The query and analysis result includes the time column (aligned by the minute), the avg_time column (the average request time), and the date column (the current date).

date_add function

The date_add function adds or subtracts a specified time interval from a date or time value.

Syntax

date_add(unit, n, x)

Parameters

Return type

A timestamp value.

Example

Query the logs from yesterday.

• Query and analysis statement (Debug)

  * |
  SELECT
    *
  FROM  log
  WHERE
    __time__ < to_unixtime(current_timestamp)
    AND __time__ > to_unixtime(date_add('day', -1, current_timestamp))

• The query and analysis result contains yesterday's log data, including fields such as body_bytes_sent, client_ip, host, and http_user_agent.

date_diff function

The date_diff function returns the difference between two date or time expressions.

Syntax

date_diff(unit, x, y)

Parameters

Return type

A bigint value.

Example

Calculates the server usage duration based on the UsageStartTime and UsageEndTime fields.

• Query and analysis statement

  * | SELECT  date_diff('hour', UsageStartTime, UsageEndTime) AS "时间差(小时)"

• The query returns a time difference of 24 hours.

time_series function

Syntax

time_series(x, window_time, format, padding_data)

Parameters

Return type

A value of the varchar type.

Example

Pad the data at a 2-hour time granularity and fill the missing values with 0.

• Query and analysis statement (Debug)

  * | select * from (select time_series(__time__, '2h', '%Y-%m-%d %H:%i:%s', '0') as time, count(*) as num from log group by time order by time) a limit 100

• The following table shows the query and analysis results. The query returns the time column at a 2-hour time granularity and the corresponding num column.