Skip to main content
SoundCommerce

Search

Date and Time Functions

  • Updated

Table of Contents

  1. Convert Timezone Function
  2. Current Date Function
  3. Current Datetime Function
  4. Date Function
  5. Date Add Function
  6. Date Diff Function
  7. Datetime Function
  8. Datetime Add Function
  9. Datetime Diff Function
  10. Parse Date Function
  11. Parse Datetime Function
  12. Parse Timestamp Function
  13. Timestamp Function
  14. Timestamp Add Function
  15. Timestamp Diff Function
  16. Timestamp Microseconds Function
  17. Timestamp Milliseconds Function
  18. Timestamp Seconds Function
  19. Truncate Date Function
  20. Truncate Datetime Function

Date and Time Functions

Convert Timezone

Description

  1. 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])) do not return an error.

Examples

  1. CONVERT_TIMEZONE("UTC", "America/Los_Angeles",PARSE_DATETIME("2024-01-01")_returns 2023-12-31T16:00:00:00-08:00

 

Current Date

Description

  1. Returns the current date with an optional timezone offset.

Syntax

CURRENT_DATE([timezone])
  1. Timezone is defined in the Glossary.

Examples

  1. CURRENT_DATE() will return today’s date in UTC (2023-11-01 at the time of this writing)

  2. CURRENT_DATE("America/Los_Angeles") will return today’s date in Pacific Time (2023-11-01 at the time of this writing)

 

Current Datetime

Description

  1. Returns the current datetime with an optional timezone offset.

Syntax

CURRENT_DATETIME([timezone])
  1. Timezone is defined in the Glossary.

Examples

  1. CURRENT_DATETIME() will return today’s date and in UTC (2023-11-01 20:52:06.873253+00:00 at the time of this writing)

  2. CURRENT_DATETIME("America/Los_Angeles") will return today’s date in Pacific Time (2023-11-01 13:52:23.009127-07:00 at the time of this writing)

 

Date

Description

  1. 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)
  1. datetime, day, month, timestamp, timezone, and year are defined in the Glossary.
    ⚠️ This function does not convert the timezone of an input date, datetime, or timestamp. Please use the Convert Timezone function for timezone conversion. 

Examples

  1. DATE(2002,10,2) returns 2022-10-02

  2. DATE("2022-10-02 12:00:00") returns 2022-10-02

  3. DATE("2022-10-02T12:00:00Z") returns 2022-10-02

  4. DATE("2022-10-02T22:00:00","America/Los_Angeles") returns 2022-10-02

  5. 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

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

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

  3. 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

  1. DATE_DIFF(PARSE_DATE("2024-01-02"), PARSE_DATE("2024-01-01"), "DAY") returns 1

  2. DATE_DIFF(PARSE_DATE("2024-01-02"), PARSE_DATE("2024-01-01"), "MONTH") returns0

  3. DATE_DIFF(PARSE_DATE("2024-01-02"), PARSE_DATE("2023-01-01"), "MONTH") returns 12

  4. DATE_DIFF(PARSE_DATE("2024-01-02"), PARSE_DATE("2024-01-01"), "YEAR") returns 0

 

Datetime

Description

  1. 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])
  1. datetime, day, hour, microsecond, minute, month, second, timestamp, timezone, and year are defined in the Glossary.

Examples

  1. DATETIME(2002,10,2) returns 2002-10-02 00:00:00

  2. DATETIME(2002,10,2,1) returns 2002-10-02 01:00:00

  3. DATETIME(2002,10,2,1,26) returns 2002-10-02 01:26:00

  4. DATETIME(2002,10,2,1,26,34) returns 2002-10-02 01:26:34

  5. DATETIME(2002,10,2,1,26,34,20) returns 2002-10-02 01:26:34.000020

  6. DATETIME("2022-10-02 12:00:00") returns 2022-10-02 12:00:00

  7. DATETIME("2022-10-02T12:00:00Z") returns 2022-10-02 12:00:00+00:00

  8. DATETIME("2022-10-02T22:00:00","America/Los_Angeles") returns 2022-10-02 22:00:00-08:00

  9. 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("2024-01-01T00:00:00", 1, "DAY")returns an error, while DATETIME_ADD(PARSE_DATETIME("2024-01-01T00:00:00"), 1, "DAY")does not return an error.

Examples

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

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

    3. 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

  1. DATETIME_DIFF(PARSE_DATE("2024-01-02"), PARSE_DATE("2024-01-01"), "DAY") returns 1

  2. DATETIME_DIFF(PARSE_DATE("2024-01-02"), PARSE_DATE("2024-01-01"), "WEEK") returns 0

  3. DATETIME_DIFF(PARSE_DATE("2024-01-02"), PARSE_DATE("2023-01-01"), "MONTH") returns 12

  4. DATETIME_DIFF(PARSE_DATE("2024-01-02"), PARSE_DATE("2024-01-01"), "YEAR") returns 0

 

Parse Date

Description

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

Syntax

PARSE_DATE(date_string[, format_str])
  1. A list of accepted format_str values can be found here (en_US locale only):

Examples

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

 

Parse Datetime

Description

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

Syntax

PARSE_DATETIME(datetime_string[, format_string])
  1. A list of accepted format_str values can be found here (en_US locale only):

Examples

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

  2. PARSE_DATETIME("2022-10-02") returns 2022-10-02T00:00:00

 

Parse Timestamp

Description

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

Syntax

PARSE_TIMESTAMP(timestamp_str[, format_str])
  1. A list of accepted format_str values can be found here (en_US locale only):

Examples

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

  2. 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])
  1. datetime, day, hour, microsecond, minute, month, second, timestamp, timezone, and year are defined in the Glossary.
    ⚠️ This function does not convert the timezone of an input date, datetime, or timestamp. Please use the Convert Timezone function for timezone conversion. 

Examples

  1. TIMESTAMP("2022-10-02") returns 2022-10-02 00:00:00

  2. TIMESTAMP("2022-10-02 12:00:00") returns 2022-10-02 12:00:00

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

  4. 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

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

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

    3. 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

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 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

  1. TIMESTAMP_DIFF(PARSE_TIMESTAMP("2024-01-02"), PARSE_TIMESTAMP("2024-01-01"), "DAY") returns 1

  2. TIMESTAMP_DIFF(PARSE_TIMESTAMP("2024-01-02"), PARSE_TIMESTAMP("2024-01-01"), "WEEK") returns 0

  3. TIMESTAMP_DIFF(PARSE_TIMESTAMP("2024-01-02"), PARSE_TIMESTAMP("2023-01-01"), "MONTH") returns 12

  4. TIMESTAMP_DIFF(PARSE_TIMESTAMP("2024-01-02"), PARSE_TIMESTAMP("2024-01-01"), "YEAR") returns 0

 

Timestamp Microseconds

Description

  1. 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

  1. TIMESTAMP_MICROS(101295) returns 1970-01-01 00:00:00.101295

  2. TIMESTAMP_MICROS(1701234311000000) returns 2023-11-29 05:05:11

 

Timestamp Milliseconds

Description

  1. 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

  1. TIMESTAMP_MILLIS(101295) returns 1970-01-01 00:01:41.295000

  2. TIMESTAMP_MILLIS(1701234311000) returns 2023-11-29 05:05:11

 

Timestamp Seconds

Description

  1. 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

  1. TIMESTAMP_SECONDS(101295) returns 1970-01-02 04:08:15

  2. 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

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

  2. 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

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

  2. TRUNCATE_DATETIME(PARSE_DATETIME("2024-01-02T12:34:00"), "HOUR") returns "2024-01-02T12:00:00"

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

Comments

0 comments

Please sign in to leave a comment.