Ctrlk
For the complete documentation index, see llms.txt. This page is also available as Markdown.

Date and time

The e6 Ingestion Engine scalar functions are based on Apache DataFusion and these docs are derived from the DataFusion function reference.

Function
Description
Example
Result

date_bin(interval, ts, origin)

Bin timestamp into intervals

date_bin(interval '15 minutes', '2024-01-01 10:18:00'::timestamp, '2024-01-01 00:00:00'::timestamp)

2024-01-01 10:15:00

date_trunc(precision, ts)

Truncate to precision

date_trunc('hour', '2024-01-01 10:30:00'::timestamp)

2024-01-01 10:00:00

date_part(part, ts)

Extract part as integer

date_part('month', '2024-03-15'::date)

3

extract(field FROM ts)

Extract sub-field

extract(day FROM '2024-01-15'::date)

15

make_date(y, m, d)

Create date from parts

make_date(2024, 3, 15)

2024-03-15

to_char(ts, fmt)

Format as string

to_char('2024-03-15'::date, '%Y/%m/%d')

'2024/03/15'

to_timestamp(str)

Parse to timestamp

to_timestamp('2024-01-01T10:30:00')

2024-01-01T10:30:00

to_timestamp_millis(str)

Parse to millisecond precision

to_timestamp_millis('2024-01-01T10:30:00.123')

2024-01-01T10:30:00.123

to_timestamp_seconds(str)

Parse to second precision

to_timestamp_seconds('2024-01-01T10:30:00')

2024-01-01T10:30:00

from_unixtime(n)

Unix epoch to timestamp

from_unixtime(1704067200000000000)

2024-01-01T00:00:00

now()

Current UTC timestamp

now()

(pipeline start time)

row_time()

Event time of current row

row_time()

(event timestamp)

date_bin

Calculates time intervals and returns the start of the interval nearest to the specified timestamp. Use date_bin to downsample time series data by grouping rows into time-based "bins" or "windows" and applying an aggregate or selector function to each window.

For example, if you "bin" or "window" data into 15 minute intervals, an input timestamp of 2023-01-01T18:18:18Z will be updated to the start time of the 15 minute bin it is in: 2023-01-01T18:15:00Z.

date_bin(interval, expression, origin-timestamp)

Arguments

  • interval: Bin interval.

  • expression: Time expression to operate on. Can be a constant, column, or function.

  • origin-timestamp: Optional. Starting point used to determine bin boundaries. If not specified defaults 1970-01-01T00:00:00Z (the UNIX epoch in UTC).

The following intervals are supported:

  • nanoseconds

  • microseconds

  • milliseconds

  • seconds

  • minutes

  • hours

  • days

  • weeks

  • months

  • years

  • century

date_trunc

Truncates a timestamp value to a specified precision.

Arguments

  • precision: Time precision to truncate to. The following precisions are supported:

    • year / YEAR

    • quarter / QUARTER

    • month / MONTH

    • week / WEEK

    • day / DAY

    • hour / HOUR

    • minute / MINUTE

    • second / SECOND

  • expression: Time expression to operate on. Can be a constant, column, or function.

Aliases

  • datetrunc

datetrunc

Alias of date_trunc.

date_part

Returns the specified part of the date as an integer.

Arguments

  • part: Part of the date to return. The following date parts are supported:

    • year

    • quarter (emits value in inclusive range [1, 4] based on which quartile of the year the date is in)

    • month

    • week (week of the year)

    • day (day of the month)

    • hour

    • minute

    • second

    • millisecond

    • microsecond

    • nanosecond

    • dow (day of the week)

    • doy (day of the year)

    • epoch (seconds since Unix epoch)

  • expression: Time expression to operate on. Can be a constant, column, or function.

Aliases

  • datepart

datepart

Alias of date_part.

extract

Returns a sub-field from a time value as an integer.

Equivalent to calling date_part('field', source). For example, these are equivalent:

See date_part.

make_date

Make a date from year/month/day component parts.

Arguments

  • year: Year to use when making the date. Can be a constant, column or function, and any combination of arithmetic operators.

  • month: Month to use when making the date. Can be a constant, column or function, and any combination of arithmetic operators.

  • day: Day to use when making the date. Can be a constant, column or function, and any combination of arithmetic operators.

Example

to_char

Returns a string representation of a date, time, timestamp or duration based on a Chrono format. Unlike the PostgreSQL equivalent of this function numerical formatting is not supported.

Arguments

  • expression: Expression to operate on. Can be a constant, column, or function that results in a date, time, timestamp or duration.

  • format: A Chrono format string to use to convert the expression.

Example

Aliases

  • date_format

to_timestamp

Converts a value to a timestamp (YYYY-MM-DDT00:00:00Z). Supports strings, integer, unsigned integer, and double types as input. Strings are parsed as RFC3339 (e.g. '2023-07-20T05:44:00') if no [Chrono formats] are provided. Integers, unsigned integers, and doubles are interpreted as seconds since the unix epoch (1970-01-01T00:00:00Z). Returns the corresponding timestamp.

Note: to_timestamp returns Timestamp(Nanosecond). The supported range for integer input is between -9223372037 and 9223372036. Supported range for string input is between 1677-09-21T00:12:44.0 and 2262-04-11T23:47:16.0. Please use to_timestamp_seconds for the input outside of supported bounds.

Arguments

  • expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

  • format_n: Optional Chrono format strings to use to parse the expression. Formats will be tried in the order they appear with the first successful one being returned. If none of the formats successfully parse the expression an error will be returned.

Example

to_timestamp_millis

Converts a value to a timestamp (YYYY-MM-DDT00:00:00.000Z). Supports strings, integer, and unsigned integer types as input. Strings are parsed as RFC3339 (e.g. '2023-07-20T05:44:00') if no Chrono formats are provided. Integers and unsigned integers are interpreted as milliseconds since the unix epoch (1970-01-01T00:00:00Z). Returns the corresponding timestamp.

Arguments

  • expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

  • format_n: Optional Chrono format strings to use to parse the expression. Formats will be tried in the order they appear with the first successful one being returned. If none of the formats successfully parse the expression an error will be returned.

Example

to_timestamp_micros

Converts a value to a timestamp (YYYY-MM-DDT00:00:00.000000Z). Supports strings, integer, and unsigned integer types as input. Strings are parsed as RFC3339 (e.g. '2023-07-20T05:44:00') if no Chrono formats are provided. Integers and unsigned integers are interpreted as microseconds since the unix epoch (1970-01-01T00:00:00Z) Returns the corresponding timestamp.

Arguments

  • expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

  • format_n: Optional Chrono format strings to use to parse the expression. Formats will be tried in the order they appear with the first successful one being returned. If none of the formats successfully parse the expression an error will be returned.

Example

to_timestamp_nanos

Converts a value to a timestamp (YYYY-MM-DDT00:00:00.000000000Z). Supports strings, integer, and unsigned integer types as input. Strings are parsed as RFC3339 (e.g. '2023-07-20T05:44:00') if no Chrono formats are provided. Integers and unsigned integers are interpreted as nanoseconds since the unix epoch (1970-01-01T00:00:00Z). Returns the corresponding timestamp.

Arguments

  • expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

  • format_n: Optional Chrono format strings to use to parse the expression. Formats will be tried in the order they appear with the first successful one being returned. If none of the formats successfully parse the expression an error will be returned.

Example

to_timestamp_seconds

Converts a value to a timestamp (YYYY-MM-DDT00:00:00.000Z). Supports strings, integer, and unsigned integer types as input. Strings are parsed as RFC3339 (e.g. '2023-07-20T05:44:00') if no Chrono formats are provided. Integers and unsigned integers are interpreted as seconds since the unix epoch (1970-01-01T00:00:00Z). Returns the corresponding timestamp.

Arguments

  • expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

  • format_n: Optional Chrono format strings to use to parse the expression. Formats will be tried in the order they appear with the first successful one being returned. If none of the formats successfully parse the expression an error will be returned.

Example

from_unixtime

Converts an integer to RFC3339 timestamp format (YYYY-MM-DDT00:00:00.000000000Z). Integers and unsigned integers are interpreted as nanoseconds since the unix epoch (1970-01-01T00:00:00Z) return the corresponding timestamp.

Arguments

  • expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

now

Returns the UTC timestamp at pipeline start.

The now() return value is determined at query compilation time, and will be constant across the execution of the pipeline.

row_time

Returns the event timestamp for the current row.

This function returns the event time for the current row.

PreviousMath
NextJSON

Last updated

Was this helpful?