tencent cloud

PrevNext

Feedback

search by keyword

Recent Pages

Documentation
Cloud Log Service
    Documentation
    Download PDF

    Date and Time Functions

    Last updated: 2024-03-20 11:47:49
    Download PDF
      CLS provides time grouping, time truncation, time interval, and time sequence completion functions, and supports format conversion, grouping and aggregation, and other processing of date and time values in logs.
      Note:
      Among date and time functions, except the histogram and time_series functions that adopt the UTC+8 time zone, other Unix timestamp (unixtime) conversion functions adopt the UTC+0 time zone. To use another time zone, you need to use a function with the specified time zone feature, for example, such as from_unixtime(__TIMESTAMP__/1000, 'Asia/Shanghai'), or manually add the time zone offset for unixtime, for example, date_trunc('second', cast(__TIMESTAMP__+8*60*60*1000 as timestamp)).

      Basic Functions

      Function
      Description
      Example
      current_date
      Returns the current date.
      Return value format: YYYY-MM-DD, such as 2021-05-21
      Return value type: DATE
      * | select current_date
      current_time
      Returns the current time.
      Return value format: HH:MM:SS.Ms Time zone, such as 17:07:52.143+08:00
      Return value type: TIME
      * | select current_time
      current_timestamp
      Returns the current timestamp.
      Return value format: YYYY-MM-DDTHH:MM:SS.Ms Time zone, such as 2021-07-15T17:10:56.735+08:00[Asia/Shanghai]
      Return value type: TIMESTAMP
      * | select current_timestamp
      current_timezone()
      Returns the time zone defined by IANA (America/Los_Angeles) or the offset from UTC (+08:35).
      Return value type: VARCHAR, such as Asia/Shanghai
      * | select current_timezone()
      localtime
      Returns the local time.
      Return value format: HH:MM:SS.Ms, such as 19:56:36
      Return value type: TIME
      * | select localtime
      localtimestamp
      Returns the local date and time.
      Return value format: YYYY-MM-DD HH:MM:SS.Ms, such as 2021-07-15 19:56:26.908
      Return value type: TIMESTAMP
      * | select localtimestamp
      now()
      Returns the current date and time. This function is used in the same way as the current_timestamp function.
      Return value format: YYYY-MM-DDTHH:MM:SS.Ms Time zone, such as 2021-07-15T17:10:56.735+08:00[Asia/Shanghai]
      Return value type: TIMESTAMP
      * | select now()
      last_day_of_month(x)
      Returns the last day of a month.
      Return value format: YYYY-MM-DD, such as 2021-05-31
      Return value type: DATE
      * | select last_day_of_month(cast(__TIMESTAMP__ as timestamp))
      from_iso8601_date(string)
      Parses an ISO 8601 formatted string into a date.
      Return value format: YYYY-MM-DD, such as 2021-05-31
      Return value type: DATE
      * | select from_iso8601_date('2021-03-21')
      from_iso8601_timestamp(string)
      Parses an ISO 8601 formatted string into a timestamp with a time zone.
      Return value format: HH:MM:SS.Ms Time zone, such as 17:07:52.143+08:00
      Return value type: TIMESTAMP
      * | select from_iso8601_timestamp('2020-05-13')
      from_unixtime(unixtime)
      Parses a Unix formatted string into a timestamp.
      Return value format: YYYY-MM-DD HH:MM:SS.Ms, such as 2017-05-17 01:41:15.000
      Return value type: TIMESTAMP
      Example 1: * | select from_unixtime(1494985275)
      Example 2: * | select from_unixtime(__TIMESTAMP__/1000)
      from_unixtime(unixtime, zone)
      Parses a Unix formatted string into a timestamp with a time zone.
      Return value format: YYYY-MM-DD HH:MM:SS.Ms Time zone, such as 2017-05-17T09:41:15+08:00[Asia/Shanghai]
      Return value type: TIMESTAMP
      Example 1: * | select from_unixtime(1494985275, 'Asia/Shanghai')
      Example 2: * | select from_unixtime(__TIMESTAMP__/1000, 'Asia/Shanghai')
      to_unixtime(timestamp)
      Parses a timestamp formatted string into a Unix timestamp.
      Return value type: LONG, such as 1626347592.037
      * | select to_unixtime(cast(__TIMESTAMP__ as timestamp))
      to_milliseconds(interval)
      Returns a time interval in milliseconds.
      Return value type: BIGINT, such as 300000
      * | select to_milliseconds(INTERVAL 5 MINUTE)
      to_iso8601(x)
      Parses a date and time expression of the DATE or TIMESTAMP type into a date and time expression in the ISO8601 format.
      * | select to_iso8601(current_timestamp)
      timezone_hour(timestamp)
      Returns the hour offset of the timestamp's time zone.
      * | SELECT current_timestamp, timezone_hour(current_timestamp)
      timezone_minute(timestamp)
      Returns the minute offset of the timestamp's time zone.
      * | SELECT current_timestamp, timezone_minute(current_timestamp)

      Time Grouping Function

      The time grouping function can be used to group and aggregate the log data at a given interval. For example, you can use it to count page views (PV) every 5 minutes.
      Function format
      histogram(time_column, interval)
      Parameter description
      Parameter
      Description
      time_column
      Time column (KEY), such as \\_\\_TIMESTAMP\\_\\_. The value in this column must be a UNIX timestamp of the LONG type or a date and time expression of the TIMESTAMP type in milliseconds.
      If a value does not meet the requirement, use the cast function to convert the ISO 8601 formatted time string into the TIMESTAMP type, for example, cast('2020-08-19T03:18:29.000Z' as timestamp), or use the [date_parse](#date_parse) function to convert a time string of another custom type.
      If the time column adopts the TIMESTAMP type, the corresponding date and time expression must be in the UTC+0 time zone. If the date and time expression itself is in a different time zone, adjust it to UTC+0 by calculation. For example, if the time zone of the original time is UTC+8, use cast('2020-08-19T03:18:29.000Z' as timestamp) - interval 8 hour to adjust the time zone.
      interval
      Time interval. The following time units are supported: SECOND, MINUTE, HOUR, and DAY. For example, INTERVAL 5 MINUTE indicates an interval of 5 minutes.
      Sample
      Count the PV value every 5 minutes:
      * | select histogram(__TIMESTAMP__, INTERVAL 5 MINUTE) AS dt, count(*) as PV group by dt order by dt limit 1000

      Time Completion Function

      The time_series() function can be used to group and aggregate the log data at a given interval. Its main difference from the histogram() function is that it can complete missing data in your query time window.
      Note:
      The time_series() function must be used with the GROUP BY and ORDER BY syntax, and ORDER BY syntax does not support desc sorting.
      Function format
      time_series(time_column, interval, format, padding)
      Parameter description
      Parameter
      Description
      time_column
      Time column (KEY), such as \\_\\_TIMESTAMP\\_\\_. The value in this column must be a UNIX timestamp of the LONG type or a date and time expression of the TIMESTAMP type in milliseconds.
      If a value does not meet the requirement, use the cast function to convert the ISO 8601 formatted time string into the TIMESTAMP type, for example, cast('2020-08-19T03:18:29.000Z' as timestamp), or use the [date_parse](#date_parse) function to convert a time string of another custom type.
      If the time column adopts the TIMESTAMP type, the corresponding date and time expression must be in the UTC+0 time zone. If the date and time expression itself is in a different time zone, adjust it to UTC+0 by calculation. For example, if the time zone of the original time is UTC+8, use cast('2020-08-19T03:18:29.000Z' as timestamp) - interval 8 hour to adjust the time zone.
      interval
      Time interval. Valid values are s (second), m (minute), h (hour), and d (day). For example, 5m indicates 5 minutes.
      format
      Time format of the return result.
      padding
      Value used to complete missing data. Valid values include:
      0: Complete a missing value with 0
      null: Complete a missing value with null
      last: Complete a missing value with the value of the previous point in time
      next: Complete a missing value with the value of the next point in time
      avg: Complete a missing value with the average value of the previous and next points in time
      Sample
      Complete data with a time unit of 2 minutes:
      * | select time_series(__TIMESTAMP__, '2m', '%Y-%m-%dT%H:%i:%s+08:00', '0')  as time, count(*) as count group by time order by time limit 1000

      Time Truncation Function

      The date_trunc() function truncates a date and time expression based on the date part you specify, supporting alignment by second, minute, hour, day, month, and year. This function is often used in scenarios that require statistical analysis based on time.
      Function
      Description
      Example
      date_trunc(unit,x)
      Truncates x to unit. x is of the TIMESTAMP type.
      * | SELECT date_trunc('second', cast(__TIMESTAMP__ as timestamp))
      The date_trunc() function supports the following units:
      Unit
      Example Truncated Value
      Description
      second
      2021-05-21 05:20:01.000
      -
      minute
      2021-05-21 05:20:00.000
      -
      hour
      2021-05-21 05:00:00.000
      -
      day
      2021-05-21 00:00:00.000
      Returns the zero o'clock of a specified date.
      week
      2021-05-19 00:00:00.000
      Returns the zero o'clock on Monday of a specified week.
      month
      2021-05-01 00:00:00.000
      Returns the zero o'clock on the first day of a specified month.
      quarter
      2021-04-01 00:00:00.000
      Returns the zero o'clock on the first day of a specified quarter.
      year
      2021-01-01 00:00:00.000
      Returns the zero o'clock on the first day of a specified year.

      Time Extraction Functions

      Time extraction functions are used to extract the specified time fields, such as the year and month, from date and time expressions.
      Function
      Description
      Example
      extract(field FROM x)
      Extracts the specified fields from the date and time expression (x).
      * |select extract(hour from cast('2021-05-21 05:20:01.100' as timestamp))
      field supports the following values: year, quarter, month, week, day, day_of_month, day_of_week, dow, day_of_year, doy, year_of_week, yow, hour, minute, second.
      extract(field FROM x) can be simplified to field(); for example, extract(hour from cast('2021-05-21 05:20:01.100' as timestamp)) can be simplified to hour(cast('2021-05-21 05:20:01.100' as timestamp)).
      Field
      Extraction Result
      Description
      Simplified Format
      year
      2021
      Extracts the year from the target date.
      year(x)
      quarter
      2
      Extracts the quarter from the target date.
      quarter(x)
      month
      5
      Extracts the month from the target date.
      month(x)
      week
      20
      Calculates the week of the year the target date is in.
      week(x)
      day
      21
      Extracts the day from the target date by month, which is equivalent to day_of_month.
      day(x)
      day_of_month
      21
      Equivalent to day.
      day(x)
      day_of_week[]
      5
      Calculates the day of the week for the target date, which is equivalent to dow.
      day_of_week(x)
      dow[]
      5
      Equivalent to day_of_week.
      day_of_week(x)
      day_of_year
      141
      Calculates the day of the year for the target date, which is equivalent to doy.
      day_of_year(x)
      doy
      141
      Equivalent to day_of_year.
      day_of_year(x)
      year_of_week
      2021
      Returns the year for the target date in ISO week date, which is equivalent to yow.
      year_of_week(x)
      yow
      2021
      Equivalent to year_of_week.
      year_of_week(x)
      hour
      5
      Extracts the hour from the target date.
      hour(x)
      minute
      20
      Extracts the minute from the target date.
      minute(x)
      second
      1
      Extracts the second from the target date.
      second(x)

      Time Interval Functions

      Time interval functions perform time period-related operations, such as adding or subtracting a specified interval from a date or counting the time between two dates.
      Function
      Description
      Example
      date_add(unit,value,timestamp)
      Adds N time units (unit) to timestamp. If value is a negative value, subtraction is performed.
      * | SELECT date_add('day', -1, TIMESTAMP '2020-03-03 03:01:00')
      The return value is the date and time one day earlier than 2020-03-03 03:01:00, i.e., 2020-03-02 03:01:00.
      date_diff(unit, timestamp1, timestamp2)
      Returns the time difference between two time expressions, for example, calculates the number of time units (unit) between timestamp1 and timestamp2.
      * |SELECT date_diff('hour', TIMESTAMP '2020-03-01 00:00:00', TIMESTAMP '2020-03-02 00:00:00')
      The return value is the time difference between 2020-03-01 and 2020-03-02, i.e., one day.
      The following units (unit) are supported:
      unit
      Description
      millisecond
      Millisecond
      second
      Second
      minute
      Minute
      hour
      Hour
      day
      Day
      week
      Week
      month
      Month
      quarter
      Quarter of a year
      year
      Year
      Sample
      Return the interval value in seconds between '2020-03-01 00:00:00' and '2020-03-02 00:00:00':
      * | SELECT date_diff('second', TIMESTAMP '2020-03-01 00:00:00', TIMESTAMP '2020-03-02 00:00:00')

      Duration Functions

      Function
      Description
      Example
      parse_duration(string)
      Parses a unit value string into a duration expression.
      Return value type: INTERVAL, such as 0 00:00:00.043 (D HH:MM:SS.Ms)
      * | SELECT parse_duration('3.81 d')
      human_readable_seconds(double)
      Parses a unit value string into a duration expression.
      Return value type: VARCHAR, such as 1 minutes and 36 seconds
      * | SELECT human_readable_seconds(96)
      The following units are supported:
      Unit
      Description
      ns
      Nanosecond
      us
      Microsecond
      ms
      Millisecond
      s
      Second
      m
      Minute
      h
      Hour
      d
      Day
      Sample
      Parse the unit value string '3.81 d' into a duration string:
      * | SELECT parse_duration('3.81 d')

      Time Formatting Function

      Function
      Description
      Example
      date_format(timestamp, format)
      Parses a date and time string of the timestamp type into a string in the format format.
      * | select date_format(cast(__TIMESTAMP__ as timestamp), '%Y-%m-%d')
      date_parse(string, format)
      Parses a date and time string in the format format into the timestamp type.
      * | select date_parse('2017-05-17 09:45:00','%Y-%m-%d %H:%i:%s')
      The following formats (format) are supported:
      Format
      Description
      %a
      Abbreviated names of the days of the week, such as Sun and Sat
      %b
      Abbreviated month name, such as Jan and Dec
      %c
      Month, numeric. Value range: 1-12
      %d
      Day of the month, decimal. Value range: 01-31
      %e
      Day of the month, decimal. Value range: 1-31
      %f
      Millisecond. Value range: 0-000000
      %H
      Hour, in the 24-hour time system
      %h
      Hour, in the 12-hour time system
      %I
      Hour, in the 12-hour time system
      %i
      Minute, numeric. Value range: 00-59
      %j
      Day of the year. Value range: 001-366
      %k
      Hour. Value range: 0-23
      %l
      Hour. Value range: 1-12
      %M
      Month name in English, such as January and December
      %m
      Month name in digits, such as 01 and 02
      %p
      AM or PM
      %r
      Time, in the 12-hour time system. Format: hh:mm:ss AM/PM
      %S
      Second. Value range: 00-59
      %s
      Second. Value range: 00-59
      %T
      Time, in the 24-hour time system. Format: hh:mm:ss
      %v
      Week of the year, where Monday is the first day of the week. Value range: 01-53
      %W
      Names of the days of the week, such as Sunday and Saturday
      %Y
      Year (4-digit), such as 2020
      %y
      Year (2-digit), such as 20
      %%
      Escape character of %
      Sample
      Parse the time string '2017-05-17 09:45:00' in format into a date and time expression of the TIMESTAMP type, i.e., '2017-05-17 09:45:00.0':
      * | SELECT date_parse('2017-05-17 09:45:00','%Y-%m-%d %H:%i:%s')
      
      Contact Us

      Contact our sales team or business advisors to help your business.

      Contact Us
      Technical Support

      Open a ticket if you're looking for further assistance. Our Ticket is 7x24 avaliable.

      Submit a Ticket
      7x24 Phone Support
      Copyright © 2013-2024 Tencent Cloud. All Rights Reserved.
      Privacy PolicyLegalCookie Policy