OpenObserve Docs
ReferenceSql functions

Date and Time Functions

Updated View MarkdownOpen in ChatGPTOpen in Claude

OpenObserve stores _timestamp as an integer number of microseconds since the Unix epoch. The functions on this page work with such integer timestamps. Standard DataFusion functions such as to_timestamp, date_trunc, and date_bin also work; see the DataFusion documentation.

Function index

FunctionPurpose
time_rangeKeep records whose timestamp falls between two times
date_formatFormat an integer timestamp as text in a given time zone
cast_to_timestampParse text into a microsecond timestamp

time_range

Syntax: time_range(field, 'start', 'end')

Description:

  • Returns true when field is at or after start and before end.
  • field is an integer timestamp in microseconds, such as _timestamp.
  • start and end are strings. Each can be an RFC 3339 time (2026-10-29T10:00:00Z), a YYYY-MM-DD HH:MM:SS time (read as UTC), or an epoch number in seconds, milliseconds, microseconds, or nanoseconds.

Example:

SELECT _timestamp, k8s_pod_name, log FROM "docs_sql_examples" WHERE time_range(_timestamp, '2026-10-29T10:00:00Z', '2026-10-29T10:15:00Z')

This query returns the records from the 15 minutes between 10

and 10
UTC, within the time range selected in the UI.

Gotchas

  • time_range filters inside the query time range. It does not widen it: records outside the time picker range are never read.
  • If start or end cannot be parsed, the function returns NULL and no record matches.
  • The value '0' means the current time, not "no limit".

date_format

Syntax: date_format(field, 'format', 'timezone')

Description:

  • Returns field as a string, formatted with format in timezone.
  • field is an integer epoch timestamp. Seconds, milliseconds, microseconds, and nanoseconds are detected from the size of the number.
  • format uses chrono format specifiers, for example %Y-%m-%d %H:%M:%S.
  • timezone is UTC, a fixed offset such as +05:30 or -07:00, or an IANA zone name such as Europe/Berlin. IANA zones apply daylight saving time for each timestamp.

Example:

SELECT date_format(_timestamp, '%Y-%m-%d %H:00', 'Asia/Kolkata') AS hour_ist, COUNT(*) AS events FROM "docs_sql_examples" GROUP BY hour_ist ORDER BY hour_ist

This query counts records per hour, with hours shown in India Standard Time.

Gotchas

  • CST is read as China Standard Time (+08:00), not US Central Time. Use America/Chicago for US Central.
  • An unknown time zone or invalid format returns NULL.
  • A timestamp of 0 is formatted as the current time.

cast_to_timestamp

Syntax: cast_to_timestamp(expression[, 'format', ...])

Description:

  • Parses expression and returns an integer timestamp in microseconds, the same unit as _timestamp, so you can compare the result with _timestamp directly.
  • Works like the DataFusion to_timestamp_micros function: with no format, RFC 3339 text and numbers are accepted; with one or more chrono formats, each format is tried in order.

Example:

SELECT _timestamp, log FROM "docs_sql_examples" WHERE _timestamp >= cast_to_timestamp('2026-10-29T10:00:00Z')

This query returns records from 10

UTC onwards.

To parse a time written in a custom format:

SELECT _timestamp, log FROM "docs_sql_examples" WHERE _timestamp < cast_to_timestamp('29/10/2026 10:15:00', '%d/%m/%Y %H:%M:%S')

Gotchas

  • The result is an integer, not a TIMESTAMP value. Wrap it in to_timestamp_micros() if you need a timestamp type.
  • Text that matches none of the formats fails the query.

Next steps

Need some help?

Was this page helpful?

String and Regex Functions

OpenObserve SQL functions that extract values with regular expressions and split strings into arrays: re_matches, regexp_match_to_fields, string_to_array_v2.

Aggregate Functions

OpenObserve aggregate SQL functions: histogram time buckets, weighted summary_percentile, and sequence functions for ordered funnels and step timing.

On this page

Function indextime_rangedate_formatcast_to_timestampNext steps