Reactor

Date and Time Functions

Mappings: Functions and Expressions · Updated June 9, 2025

Date and Time Functions

Convert Timezone

Description

Converts the timezone of a date/time/stamp. Both input_timezone and output_timezone accept an IANA time zone name.

Syntax

CONVERT_TIMEZONE(input_timezone,output_timezone,date/datetime/timestamp)

ℹ️ Because Reactor stores data in the JSON format and JSON does not have any date or time types, all dates, datetimes, and timestamps are stored as strings.

When using any of the date/time/stamp_add and date/time/stamp_diff functions, be sure to wrap any date/time/stamps in one of these functions to prevent any type errors: 

For example:

CONVERT_TIMEZONE("UTC", "America/Los_Angeles", "2024-01-01")

Returns an error, while,

CONVERT_TIMEZONE("UTC", "America/Los_Angeles", PARSE_DATETIME("2024-01-01"), )

Does not return an error.

⚠️ If you want to convert the timezone of a date/time/stamp that has an offset (e.g. 2023-12-31T16:00:00-08:00), the offset must match the timezone of input_timezone parameter.

If the input’s offset does not match the timezone of the parameter, use one of the functions below to remove the input’s offset:

  • Strip the offset using a Replace or Regex Replace function (e.g. REPLACE({input string},"{offset}",""))

  • Use a Split function to split the timestamp at the + and select the first string returned (e.g. SPLIT({input string},"+")[1])

For example:

CONVERT_TIMEZONE("UTC","America/Los_Angeles",TIMESTAMP("2024-01-01T00:00:00+01:00"))
CONVERT_TIMEZONE("UTC","America/Los_Angeles",TIMESTAMP("2024-01-01T00:00:00+01:00"))
 
Returns an error, while,
CONVERT_TIMEZONE("UTC","America/Los_Angeles",TIMESTAMP("2024-01-01T00:00:00"))
CONVERT_TIMEZONE("UTC","America/Los_Angeles",TIMESTAMP("2024-01-01T00:00:00"))

and

CONVERT_TIMEZONE("UTC","America/Los_Angeles",PARSE_TIMESTAMP(SPLIT("2024-01-01T00:00:00+01:00","+")[1]))

Does not return an error.

Other examples:

CONVERT_TIMEZONE("UTC", "America/Los_Angeles",PARSE_DATETIME("2024-01-01")

returns

2023-12-31T16:00:00:00-08:00

Current Date

Description

Returns the current date with an optional timezone offset.

Syntax

CURRENT_DATE([timezone])

Timezone is defined here.

Examples

CURRENT_DATE()

will return today’s date in UTC at the time of this writing:

2023-11-01

and

CURRENT_DATE("America/Los_Angeles")

Will return today’s date in Pacific Time at the time of this writing:

2023-11-01

Current Datetime

Description

Returns the current datetime with an optional timezone offset.

Syntax

CURRENT_DATETIME([timezone])

Timezone is defined here.

Examples

CURRENT_DATETIME()

Will return today’s date in UTC at the time of this writing:

2023-11-01 20:52:06.873253+00:00 
CURRENT_DATETIME("America/Los_Angeles")

Will return today’s date in Pacific Time at the time of this writing:

2023-11-01 13:52:23.009127-07:00 

Date

Description

Returns a date from the given argument(s). All string values must be in the form of ISO-8601 dates/datetimes.

Syntax

DATE(year, month, day)

DATE(timestamp, [timezone])

DATE(datetime)
  • datetime, day, month, timestamp, timezone, and year are defined here.

⚠️ This function does not convert the timezone of an input date, datetime, or timestamp. Please use the Convert Timezone function for timezone conversion. 

Examples

DATE(2002,10,2) 
returns: 2022-10-02
DATE("2022-10-02 12:00:00") 
returns: 2022-10-02
DATE("2022-10-02T12:00:00Z") 
returns: 2022-10-02
DATE("2022-10-02T22:00:00","America/Los_Angeles")
returns: 2022-10-02
DATE("2022-10-02","America/Los_Angeles") 
and 
DATE("2022-10-02",-08) 
both return: 2022-10-02

Date Add

Description 

Adds a specified integer to a date at a particular time granularity. Granularity is case-insensitive but must be surrounded by double quotes (i.e., "DAY" or "day", not DAY or day). Supported granularity values for this function include:

  • day
  • week
  • month
  • year

Syntax

DATE_ADD(date_expression,integer,granularity)

ℹ️ Because Reactor stores data in the JSON format and JSON does not have any date or time types, all dates, datetimes, and timestamps are stored as strings.

When using any of the date/time/stamp_add and date/time/stamp_diff functions, be sure to wrap any date/time/stamps in one of these functions to prevent any type errors: 

For example:

DATE_ADD("2024-01-01", 1, "DAY")

Returns an error, while,

DATETIME_ADD(PARSE_DATE("2024-01-01"), 1, "DAY")

Does not return an error.

Examples

DATE_ADD(PARSE_DATE("2024-01-01T00:00:00"), 1, "DAY") 
returns: "2024-01-02"
DATE_ADD(PARSE_DATE("2024-01-01T00:00:00"), 1, "MONTH") 
returns: "2024-02-01"

Nest this function to add two or more intervals to a datetime, for instance:

DATE_ADD(DATE_ADD(PARSE_DATE("2024-01-01T00:00:00"), 1, "DAY"), 1 "MONTH") 
returns: "2024-02-02"

Date Diff

Description

Returns the number of intervals between two date values (end_date - start_date) at a particular time granularity. Granularity is case-insensitive but must be surrounded by double quotes (i.e., "DAY" or "day", not DAY or day). Supported granularity values for this function include:

  • day
  • week
  • month
  • year

Syntax

DATE_DIFF(end_date,start_date,granularity)

ℹ️ Because Reactor stores data in the JSON format and JSON does not have any date or time types, all dates, datetimes, and timestamps are stored as strings.

When using any of the date/time/stamp_add and date/time/stamp_diff functions, be sure to wrap any date/time/stamps in one of these functions to prevent any type errors: 

For example:

DATE_DIFF("2024-01-01", "2024-01-02", "DAY")

Returns an error, while,

DATE_DIFF(PARSE_DATE("2024-01-01"), PARSE_DATE("2024-01-02"), "DAY")

Does not return an error.

Examples

DATE_DIFF(PARSE_DATE("2024-01-02"), PARSE_DATE("2024-01-01"), "DAY") 
returns: 1
DATE_DIFF(PARSE_DATE("2024-01-02"), PARSE_DATE("2024-01-01"), "MONTH") 
returns: 0
DATE_DIFF(PARSE_DATE("2024-01-02"), PARSE_DATE("2023-01-01"), "MONTH")
returns: 12
DATE_DIFF(PARSE_DATE("2024-01-02"), PARSE_DATE("2024-01-01"), "YEAR") 
returns: 0

Datetime

Description

  • Returns a datetime from the given argument(s). All string values must be in the form of ISO-8601 dates/datetimes.

    ⚠️ This function does not convert the timezone of an input date, datetime, or timestamp. Please use the Convert Timezone function for timezone conversion. 

Syntax

DATETIME(year, month, day, [hour, [minute, [second, [microsecond]]]])

DATETIME(date, [timezone])
DATETIME(timestamp, [timezone])
  • datetime, day, hour, microsecond, minute, month, second, timestamp, timezone, and year are defined here.

Examples

DATETIME(2002,10,2)
returns: 2002-10-02 00:00:00
DATETIME(2002,10,2,1)
returns: 2002-10-02 01:00:00
DATETIME(2002,10,2,1,26)
returns: 2002-10-02 01:26:00
DATETIME(2002,10,2,1,26,34)
returns: 2002-10-02 01:26:34
DATETIME(2002,10,2,1,26,34,20)
returns: 2002-10-02 01:26:34.000020
DATETIME("2022-10-02 12:00:00")
returns: 2022-10-02 12:00:00
DATETIME("2022-10-02T12:00:00Z")
returns: 2022-10-02 12:00:00+00:00
DATETIME("2022-10-02T22:00:00","America/Los_Angeles")
returns: 2022-10-02 22:00:00-08:00
DATETIME("2022-10-02","America/Los_Angeles") and DATETIME("2022-10-02",-08)
both return:  2022-10-02 00:00:00-08:00

Datetime Add

Description 

Adds a specified integer to a datetime at a particular time granularity. Granularity is case-insensitive but must be surrounded by double quotes (i.e., "DAY" or "day", not DAY or day). Supported granularity values for this function include:

  • second
  • minute
  • hour
  • day
  • week
  • month
  • year

Syntax

DATETIME_ADD(datetime_expression,integer,granularity)

ℹ️ Because Reactor stores data in the JSON format and JSON does not have any date or time types, all dates, datetimes, and timestamps are stored as strings.

When using any of the date/time/stamp_add and date/time/stamp_diff functions, be sure to wrap any date/time/stamps in one of these functions to prevent any type errors: 

For example:

DATETIME_ADD(datetime_expression,integer,granularity)

Returns an error, while,

DATETIME_ADD(PARSE_DATETIME("2024-01-01T00:00:00"), 1, "DAY")

Does not return an error.

Examples

DATETIME_ADD(PARSE_DATETIME("2024-01-01T00:00:00"), 1, "DAY") 
returns: "2024-01-02T00:00:00"
DATETIME_ADD(PARSE_DATETIME("2024-01-01T00:00:00"), 1, "MONTH") 
returns: "2024-02-01T00:00:00"

Nest this function to add two or more intervals to a datetime, for instance:

DATETIME_ADD(DATETIME_ADD(PARSE_DATETIME("2024-01-01T00:00:00"), 1, "DAY"), 1 "HOUR")
returns: 2024-01-02T01:00:00"

Datetime Diff

Description 

Returns the number of intervals between two date values (end_datetime - start_datetime) at a particular time granularity. Granularity is case-insensitive but must be surrounded by double quotes (i.e., "DAY" or "day", not DAY or day). Supported granularity values for this function include:

  • second
  • minute
  • hour
  • day
  • week
  • month
  • year

Syntax

DATETIME_DIFF(end_datetime,start_datetime,granularity)

ℹ️ Because Reactor stores data in the JSON format and JSON does not have any date or time types, all dates, datetimes, and timestamps are stored as strings.

When using any of the date/time/stamp_add and date/time/stamp_diff functions, be sure to wrap any date/time/stamps in one of these functions to prevent any type errors: 

For example:

DATETIME_DIFF("2024-01-01", "2024-01-02", "DAY")

Returns an error, while,

DATETIME_DIFF(PARSE_DATETIME("2024-01-01"), PARSE_DATETIME("2024-01-02"), "DAY")

Does not return an error.

Examples

DATETIME_DIFF(PARSE_DATE("2024-01-02"), PARSE_DATE("2024-01-01"), "DAY") 
returns: 1
DATETIME_DIFF(PARSE_DATE("2024-01-02"), PARSE_DATE("2024-01-01"), "WEEK") 
returns: 0
DATETIME_DIFF(PARSE_DATE("2024-01-02"), PARSE_DATE("2023-01-01"), "MONTH") 
returns: 12
DATETIME_DIFF(PARSE_DATE("2024-01-02"), PARSE_DATE("2024-01-01"), "YEAR") 
returns: 0

Parse Date

Description

Attempts to convert a string value representing a date into a date, with an optional format string.

Syntax

PARSE_DATE(date_string[, format_str])

A list of accepted format_str values can be found here (en_US locale only):

Examples

PARSE_DATE("2022-10-02 12:00:00") 
returns: 2022-10-02

Parse Datetime

Description

Attempts to convert a string value representing a datetime into a datetime, with an optional format string.

Syntax

PARSE_DATETIME(datetime_string[, format_string])

A list of accepted format_str values can be found here (en_US locale only):

Examples

PARSE_DATETIME("2022-10-02 12:00:00") 
returns: 2022-10-02T12:00:00
PARSE_DATETIME("2022-10-02") 
returns: 2022-10-02T00:00:00

Parse Timestamp

Description

Attempts to convert a string value representing a timestamp into a timestamp, with an optional format string.

Syntax

PARSE_TIMESTAMP(timestamp_str[, format_str])

A list of accepted format_str values can be found here (en_US locale only):

Examples

PARSE_TIMESTAMP("2022-10-02 12:00:00") 
returns: 2022-10-02T12:00:00
PARSE_TIMESTAMP("2022-10-02") 
returns: 2022-10-02T00:00:00

Timestamp

Description

  1. Returns a timestamp from the given argument(s).
  2. All string values must be in the form of ISO-8601 dates/datetimes.

Syntax

TIMESTAMP(datetime_string, [timezone]) 
TIMESTAMP(datetime, [timezone]) 
TIMESTAMP(date, [timezone])
  • datetime, day, hour, microsecond, minute, month, second, timestamp, timezone, and year are defined here.

⚠️ This function does not convert the timezone of an input date, datetime, or timestamp. Please use the Convert Timezone function for timezone conversion. 

Examples

TIMESTAMP("2022-10-02") 
returns: 2022-10-02 00:00:00
TIMESTAMP("2022-10-02 12:00:00") 
returns: 2022-10-02 12:00:00
TIMESTAMP("2022-10-02T22:00:00","America/Los_Angeles") 
returns 2022-10-02 22:00:00-07:00
TIMESTAMP("2022-10-02","America/Los_Angeles") 
returns: 2022-10-02 00:00:00-07:00

Timestamp Add

Description 

Adds a specified integer to a timestamp at a particular time granularity. Granularity is case-insensitive but must be surrounded by double quotes (i.e., "DAY" or "day", not DAY or day). Supported granularity values for this function include:

  • second
  • minute
  • hour
  • day
  • week
  • month
  • year

Syntax

TIMESTAMP_ADD(timestamp_expression,integer,granularity)

ℹ️ Because Reactor stores data in the JSON format and JSON does not have any date or time types, all dates, datetimes, and timestamps are stored as strings.

When using the timestamp_add function, be sure to wrap any timestamps in a Parse Timestamp function to prevent any type errors.

For example:

TIMESTAMP_ADD("2024-01-01T00:00:00", 1, "DAY")

Returns an error, while,

 TIMESTAMP_ADD(PARSE_TIMESTAMP("2024-01-01T00:00:00"), 1, "DAY")

Does not return an error.

Examples

TIMESTAMP_ADD(PARSE_TIMESTAMP("2024-01-01T00:00:00"), 1, "DAY") 
returns: "2024-01-02T00:00:00"
TIMESTAMP_ADD(PARSE_TIMESTAMP("2024-01-01T00:00:00"), 1, "MONTH") 
returns: "2024-02-01T00:00:00"

Nest this function to add two or more intervals to a datetime, for instance:

TIMESTAMP_ADD(TIMESTAMP_ADD(PARSE_TIMESTAMP("2024-01-01T00:00:00"), 1, "DAY"), 1 "HOUR") 
returns: "2024-01-02T01:00:00"

Timestamp Diff

Description 

Returns the number of intervals between two date values (end_datetime - start_datetime) at a particular time granularity. Granularity is case-insensitive but must be surrounded by double quotes (i.e., "DAY" or "day", not DAY or day). Supported granularity values for this function include:

  • second
  • minute
  • hour
  • day
  • week
  • month
  • year

Syntax

TIMESTAMP_DIFF(end_timestamp,start_timestamp,granularity)

ℹ️ Because Reactor stores data in the JSON format and JSON does not have any date or time types, all dates, datetimes, and timestamps are stored as strings.

When using the timestamp_diff function, be sure to wrap any timestamps in a Parse Timestamp function to prevent any type errors.

For example:

TIMESTAMP_DIFF("2024-01-01", "2024-01-02", "DAY")

Returns an error, while,

TIMESTAMP_DIFF(PARSE_TIMESTAMP("2024-01-01"), 
PARSE_TIMESTAMP("2024-01-02"), "DAY")

Does not return an error.

Examples

TIMESTAMP_DIFF(PARSE_TIMESTAMP("2024-01-02"), PARSE_TIMESTAMP("2024-01-01"), "DAY") 
returns: 1
TIMESTAMP_DIFF(PARSE_TIMESTAMP("2024-01-02"), PARSE_TIMESTAMP("2024-01-01"), "WEEK") 
returns: 0
TIMESTAMP_DIFF(PARSE_TIMESTAMP("2024-01-02"), PARSE_TIMESTAMP("2023-01-01"), "MONTH") 
returns: 12
TIMESTAMP_DIFF(PARSE_TIMESTAMP("2024-01-02"), PARSE_TIMESTAMP("2024-01-01"), "YEAR") 
returns: 0

Timestamp Microseconds

Description

Interprets an integer as the number of microseconds since 1970-01-01 00:00:00 UTC and returns a timestamp.

Syntax

TIMESTAMP_MICROS(integer)

Examples

TIMESTAMP_MICROS(101295) returns 1970-01-01 00:00:00.101295
TIMESTAMP_MICROS(1701234311000000) returns 2023-11-29 05:05:11

Timestamp Milliseconds

Description

Interprets an integer as the number of milliseconds since 1970-01-01 00:00:00 UTC and returns a timestamp.

Syntax

TIMESTAMP_MILLIS(integer)

Examples

TIMESTAMP_MILLIS(101295) 
returns: 1970-01-01 00:01:41.295000
TIMESTAMP_MILLIS(1701234311000) 
returns: 2023-11-29 05:05:11

Timestamp Seconds

Description

Interprets an integer as the number of seconds since 1970-01-01 00:00:00 UTC and returns a timestamp.

Syntax

TIMESTAMP_SECONDS(integer)

Examples

TIMESTAMP_SECONDS(101295) returns 1970-01-02 04:08:15
TIMESTAMP_SECONDS(1701234311) returns 2023-11-29 05:05:11

Truncate Date

Description

Truncates a date value to the specified granularity. Granularity is case-insensitive but must be surrounded by double quotes (i.e., "DAY" or "day", not DAY or day). Supported granularity values for this function include:

  • day
  • month
  • year

Syntax

TRUNCATE_DATE(date,granularity)

ℹ️ Because Reactor stores data in the JSON format and JSON does not have any date or time types, all dates, datetimes, and timestamps are stored as strings.

When using the truncate_date function, be sure to wrap any timestamps in a Parse Date function to prevent any type errors.

For example:

TRUNCATE_DATE("2024-01-01", "DAY")

Returns an error, while,

TRUNCATE_DATE(PARSE_DATE("2024-01-01") "DAY")

Does not return an error.

Examples

TRUNCATE_DATE(PARSE_DATE("2024-01-02T12:34:00"), "DAY") 
returns: "2024-01-02"
TRUNCATE_DATE(PARSE_DATE("2024-01-02T12:34:00"), "MONTH") 
returns: "2024-01-01"

Truncate Datetime

Description

Truncates a datetime value to the specified granularity. Granularity is case-insensitive but must be surrounded by double quotes (i.e., "DAY" or "day", not DAY or day). Supported granularity values for this function include:

  • second
  • minute
  • hour
  • day
  • month
  • year

Syntax

TRUNCATE_DATE(date,granularity)

ℹ️ Because Reactor stores data in the JSON format and JSON does not have any date or time types, all dates, datetimes, and timestamps are stored as strings.

When using the truncate_date function, be sure to wrap any timestamps in a Parse Datetime function to prevent any type errors.

For example:

TRUNCATE_DATETIME("2024-01-01", "DAY")

Returns an error, while,

TRUNCATE_DATETIME(PARSE_DATETIME("2024-01-01") "DAY")

Does not return an error.

Examples

TRUNCATE_DATETIME(PARSE_DATETIME("2024-01-02T12:34:00"), "DAY") 
returns: "2024-01-02T00:00:00"
TRUNCATE_DATETIME(PARSE_DATETIME("2024-01-02T12:34:00"), "HOUR") 
returns: "2024-01-02T12:00:00"
TRUNCATE_DATETIME(PARSE_DATETIME("2024-01-02T12:34:00"), "MONTH") 
returns: "2024-01-01T00:00:00"