Time functions

Time functions return the decimal number for a particular time value, calculate the number of time units (days, months, years) between two dates, and allow to determine timestamps and timeframes, among others.

duration

Creates a duration from the given amount and time unit.

Syntax

duration(value, unit)

Parameters

Returns

The data type of the returned value is duration.

Examples

Example 1

data record(value = 1000, unit = "ns"),
     record(value = 60, unit = "s"),
     record(value = 1000 * 60 * 60 * 24, unit = "ms"),
     record(value = 24, unit = "h")
| fieldsAdd duration(value, unit)

Query result:

formatTimestamp

Formats the timestamp according to a format string (using the defined interval).

Timestamps according to the ISO 8601 standard can be parsed and converted to the timestamp datatype.

All letters A to Z and a to z are reserved as pattern letters. Any non-letter characters, other than [, ], {, }, # and the single quote will be output directly. However, it is recommended to use single quotes around all characters that you want to output directly to ensure that future changes do not affect your query. Unrecognized pattern letters result in an error. See Java DateTime Formatter for the list of supported patterns and symbols.

Syntax

formatTimestamp(timestamp [, interval] [, format] [, timezone] [, locale])

Parameters

Returns

The data type of the returned value is string.

Examples

Example 1

data record(timestamp = toTimestamp("2019-08-01T09:30:00.000-0400"))
| fieldsAdd formatted = formatTimestamp(timestamp, format:"MM-dd-yyyy"),
            year      = formatTimestamp(timestamp, format:"y"),
            month     = formatTimestamp(timestamp, format:"M"),
            week      = formatTimestamp(timestamp, format:"w"),
            dayofWeek = formatTimestamp(timestamp, format:"E", locale:"en-US"),
            hour      = formatTimestamp(timestamp, format:"H"),
            time      = formatTimestamp(timestamp, format:"HH:mm, VV"),
            timeET    = formatTimestamp(timestamp, format:"HH:mm, VV", timezone:"US/Eastern")

Query result:

getDayOfMonth

Extracts the day of the month from a timestamp.

Syntax

getDayOfMonth(timestamp [, timezone])

Parameters

Returns

The data type of the returned value is long.

Examples

Example 1

data record(timestamp = toTimestamp("2019-08-01T09:30:00.000-0400"))
| fieldsAdd getDayOfMonth(timestamp)

Query result:

getDayOfWeek

Extracts the day of the week from a timestamp. The getDayOfWeek function always uses UTC as its timezone. The week starts on Monday and ends on Sunday.
The numeric values represent the following days:

Syntax

getDayOfWeek(timestamp [, timezone])

Parameters

Returns

The data type of the returned value is long.

Examples

Example 1

data record(timestamp = toTimestamp("2019-08-01T09:30:00.000-0400"))
| fieldsAdd getDayOfWeek(timestamp)

Query result:

getDayOfYear

Extracts the day of the year from a timestamp.

Syntax

getDayOfYear(timestamp [, timezone])

Parameters

Returns

The data type of the returned value is long.

Examples

Example 1

data record(timestamp = toTimestamp("2019-08-01T09:30:00.000-0400"))
| fieldsAdd getDayOfYear(timestamp)

Query result:

getEnd

Extracts the end timestamp from a timeframe.

Syntax

getEnd(timeframe)

Parameters

Returns

The data type of the returned value is timestamp.

Examples

Example 1

data record(timeframe = timeframe(from:"2019-08-01T09:30:00.000-0400", to:"2019-08-01T09:35:00.000-0400"))
| fieldsAdd getEnd(timeframe)

Query result:

getHour

Extracts the hour from a timestamp.

Syntax

getHour(timestamp [, timezone])

Parameters

Returns

The data type of the returned value is long.

Examples

Example 1

data record(timestamp = toTimestamp("2019-08-01T09:30:00.000-0400"))
| fieldsAdd getHour(timestamp)

Query result:

getMinute

Extracts the minute from a timestamp.

Syntax

getMinute(timestamp [, timezone])

Parameters

Returns

The data type of the returned value is long.

Examples

Example 1

data record(timestamp = toTimestamp("2019-08-01T09:30:00.000-0400"))
| fieldsAdd getMinute(timestamp)

Query result:

getStart

Extracts the start timestamp from a timeframe.

Syntax

getStart(timeframe)

Parameters

Returns

The data type of the returned value is timestamp.

Examples

Example 1

data record(timeframe = timeframe(from:"2019-08-01T09:30:00.000-0400", to:"2019-08-01T09:35:00.000-0400"))
| fieldsAdd getStart(timeframe)

Query result:

getSecond

Extracts the second from a timestamp.

Syntax

getSecond(timestamp [, timezone])

Parameters

Returns

The data type of the returned value is long.

Examples

Example 1

data record(timestamp = toTimestamp("2019-08-01T09:30:00.000-0400"))
| fieldsAdd getSecond(timestamp)

Query result:

getYear

Extracts the year from a timestamp.

Syntax

getYear(timestamp [, timezone])

Parameters

Returns

The data type of the returned value is long.

Examples

Example 1

data record(timestamp = toTimestamp("2019-08-01T09:30:00.000-0400"))
| fieldsAdd getYear(timestamp)

Query result:

getWeekOfYear

Extracts the week of the year from a timestamp.

Syntax

getWeekOfYear(timestamp [, timezone])

Parameters

Returns

The data type of the returned value is long.

Examples

Example 1

data record(timestamp = toTimestamp("2019-08-01T09:30:00.000-0400"))
| fieldsAdd getWeekOfYear(timestamp)

Query result:

now

Returns the current time as a fixed timestamp of the query start.

Syntax

now()

Returns

The data type of the returned value is timestamp.

Examples

Example 1

data record()
| fieldsAdd now()

Query result:

timeframe

Creates a timeframe structure from the given start and end timestamps.

Syntax

timeframe(from [, to])

Parameters

Returns

The data type of the returned value is timeframe.

Examples

Example 1

data record(timestamp = toTimestamp("2019-08-01T09:30:00.000-0400"))
| fieldsAdd timeframe(from:timestamp - 5m, to: timestamp)

Query result:

timestamp

Creates a timestamp using provided values in mandatory parameters.

Syntax

timestamp(year, month, day, hour, minute, second [, millis] [, micros] [, nanos] [, timezone])

Parameters

Returns

The data type of the returned value is timestamp.

Examples

Example 1

data record()
| fieldsAdd timestamp(year:2019, month:8, day:1, hour:13, minute:30, second:0, timezone:"UTC")

Query result:

timestampFromUnixMillis

Creates a timestamp from the given milliseconds since Unix epoch.

Syntax

timestampFromUnixMillis(millis)

Parameters

Returns

The data type of the returned value is timestamp.

Examples

Example 1

data record(millis = 1564666200000)
| fieldsAdd timestampFromUnixMillis(millis)

Query result:

timestampFromUnixNanos

Creates a timestamp from the given nanoseconds since Unix epoch.

Syntax

timestampFromUnixNanos(nanos)

Parameters

Returns

The data type of the returned value is timestamp.

Examples

Example 1

data record(nanos = 1564666200000000000)
| fieldsAdd timestampFromUnixNanos(nanos)

Query result:

timestampFromUnixSeconds

Creates a timestamp from the given seconds since Unix epoch.

Syntax

timestampFromUnixSeconds(seconds)

Parameters

Returns

The data type of the returned value is timestamp.

Examples

Example 1

data record(seconds = 1564666200)
| fieldsAdd timestampFromUnixSeconds(seconds)

Query result:

unixMillisFromTimestamp

Converts a timestamp into milliseconds.

Syntax

unixMillisFromTimestamp(timestamp)

Parameters

Returns

The data type of the returned value is long.

Examples

Example 1

data record(timestamp = toTimestamp("2019-08-01T09:30:00.000-0400"))
| fieldsAdd unixMillisFromTimestamp(timestamp)

Query result:

unixNanosFromTimestamp

Converts a timestamp into nanoseconds.

Syntax

unixNanosFromTimestamp(timestamp)

Parameters

Returns

The data type of the returned value is long.

Examples

Example 1

data record(timestamp = toTimestamp("2019-08-01T09:30:00.000-0400"))
| fieldsAdd unixNanosFromTimestamp(timestamp)

Query result:

unixSecondsFromTimestamp

Converts a timestamp into seconds.

Syntax

unixSecondsFromTimestamp(timestamp)

Parameters

Returns

The data type of the returned value is long.

Examples

Example 1

data record(timestamp = toTimestamp("2019-08-01T09:30:00.000-0400"))
| fieldsAdd unixSecondsFromTimestamp(timestamp)

Query result: