Date and Time Functions

Date functions provide mechanisms for processing and transforming dates and times.

The examples for each function use the following notation:

• Square brackets ([]) indicate arrays.
• Curly braces ({}) indicate groups.
• Arrows (==>) separate inputs and outputs. Inputs are shown on the left side of the arrow. Outputs are shown on the right side of the arrow.

Date Add

The Date Add function adds the given amount of time to the provided date and returns a new date. The timezone is not affected.

Usage Information

Category	Details
Number of Arguments	2
Mandatory Argument Names and Datatypes	timestamp: A datetime (with or without timezone) to use as the starting point. increment: An integer indicating the amount of time to add to timestamp. Scalar Support: True Array Support: True Group of Scalars Support: True Group of Arrays Support: True
Optional Argument Names and Datatypes	N/A
Modifiers	N/A
Output Datatype(s)	The output datatype matches the input datatype, either: Datetime with Timezone Datetime without Timezone

Examples

dateAdd(["2022-09-23T21:35:37.000Z","2022-09-23T21:35:38.000Z","2022-09-23T21:35:39.000Z"],increment=5000) ==> ["2022-09-23T21:35:42.000Z","2022-09-23T21:35:43.000Z","2022-09-23T21:35:44.000Z"]
dateAdd(["2022-09-23T21:35:37.000Z","2022-09-23T21:35:38.000Z","2022-09-23T21:35:39.000Z"],increment={5000,4000,3000}) ==> {"2022-09-23T21:35:42.000Z","2022-09-23T21:35:42.000Z","2022-09-23T21:35:42.000Z"}

dateAdd({"2022-09-23T21:35:37.000Z","2022-09-23T21:35:38.000Z","2022-09-23T21:35:39.000Z"},increment={5000,4000,3000}) ==> {"2022-09-23T21:35:42.000Z","2022-09-23T21:35:42.000Z","2022-09-23T21:35:42.000Z"}
dateAdd({["2022-09-23T21:35:37.000Z","2022-09-23T21:35:38.000Z","2022-09-23T21:35:39.000Z"]},increment=5000) ==> {["2022-09-23T21:35:42.000Z","2022-09-23T21:35:43.000Z","2022-09-23T21:35:44.000Z"]}

Date Diff

The Date Diff function computes the absolute difference between two dates in milliseconds.

Usage Information

Category	Details
Number of Arguments	2
Mandatory Argument Names and Datatypes	arg1: A datetime with timezone or datetime without timezone. arg2: The same datatype as arg1. Scalar Support: True Array Support: True Group of Scalars Support: True Group of Arrays Support: True
Optional Argument Names and Datatypes	N/A
Modifiers	N/A
Output Datatype(s)	Integer

Examples

dateDiff("2022-09-23T05:35:37.000Z", "2022-09-22T21:35:37.000+0800", useTimezone=True) ==> 0

dateDiff([],"2022-09-23T21:35:37.000Z") ==> []
dateDiff("2022-09-23T21:35:37.000Z",[]) ==> []

dateDiff({},{}) ==> {}
dateDiff({},"2022-09-23T21:35:37.000Z") ==> {}

dateDiff({[],[],[]},"2022-09-23T21:35:37.000Z") ==> {[],[],[]}
dateDiff({[],[],[]},{"2022-09-23T21:35:37.000Z","",""}) ==> {[],[],[]}

Date Part

The Date Part function returns an integer representing the specified part of the input date.

Usage Information

Category	Details
Number of Arguments	1
Mandatory Argument Names and Datatypes	arg: A datetime with timezone or datetime without timezone. Scalar Support: True Array Support: True Group of Scalars Support: True Group of Arrays Support: True
Optional Argument Names and Datatypes	N/A
Modifiers	date part (Default: y): The part of the datetime to return. If the specified part does not exist, it returns null. The available options are: y: Four-digit year. M: Numeric month of the year, from 1 to 12. w: The week number of the year. W: The week number of the month. D: The day of the year. d: The day of the month. u: The day of the week. H: The hour of the day. m: The minute of the hour. s: The second of the minute. S: The millisecond of the second. O: The timezone offset in seconds (from GMT). It returns 0 for Zulu formats.
Output Datatype(s)	Integer

Examples

datePart("2022-09-23T21:35:37.000Z", date_part='H') ==> 21
datePart({}, date_part='H') ==> {}
datePart([], date_part='H') ==> []
datePart({[],[],[]},date_part="")

Date Truncate

The Date Truncate function returns the date after truncating up to and including the given granularity.

Usage Information

Category	Details
Number of Arguments	1
Mandatory Argument Names and Datatypes	arg: A datetime (with or without timezone) to truncate. Scalar Support: True Array Support: True Group of Scalars Support: True Group of Arrays Support: True
Optional Argument Names and Datatypes	N/A
Modifiers	granularity (Default: second): The level of specificity for the truncation operation. The available options are: millisecond second minute hour day month
Output Datatype(s)	The output datatype matches the input datatype, either: Datetime with Timezone Datetime without Timezone

Examples

dateTruncate("2022-09-23T21:35:37.000Z", granularity='day') ==> "2022-09-23T00:00:00.000Z"
dateTruncate({}, granularity='month') ==> {}
dateTruncate([], granularity='month') ==> []
dateTruncate({[],[],[]}, granularity='month') ==> {}

Drop Timezone

The Drop Timezone function removes the timezone information from date/time inputs.

Usage Information

Category	Details
Number of Arguments	1
Mandatory Argument Names and Datatypes	arg: A datetime with timezone information to remove. Scalar Support: True Array Support: True Group of Scalars Support: True Group of Arrays Support: True
Optional Argument Names and Datatypes	N/A
Modifiers	N/A
Output Datatype(s)	Datetime without Timezone

Examples

dropTimezone("2025-02-28T09:40:00.000Z") ==> "2025-02-28 09:40:00.000"
dropTimezone("2025-02-28T01:40:00.000-8000") ==> "2025-02-28 01:40:00.000"

Now

The Now function returns the current GMT date and time with millisecond precision.

Usage Information

Category	Details
Number of Arguments	0
Mandatory Argument Names and Datatypes	N/A
Optional Argument Names and Datatypes	N/A
Modifiers	N/A
Output Datatype(s)	Datetime with Timezone

Examples

now() ==> "2022-09-23T21:35:37.000Z"

To Time Zone

The To Time Zone function converts the input date and time to the date and time in the specified timezone.

Usage Information

Category	Details
Number of Arguments	2
Mandatory Argument Names and Datatypes	arg: A datetime (with or without timezone) or integer to convert. timezone: A string indicating the target timezone. It must be a value from the Time Zone Database columns TZ identifier, UTC offsets, and Timezone abbreviation. (See the Wikipedia list of tz database time zones for quick reference.) Scalar Support: True Array Support: True Group of Scalars Support: True Group of Arrays Support: True
Optional Argument Names and Datatypes	N/A
Modifiers	N/A
Output Datatype(s)	Datetime with Timezone

Examples

toTimeZone("2022-09-23T21:35:37.000Z","-0800") ==> "2022-09-23T13:35:37.000-08:00"
toTimeZone("2022-09-23T21:35:37.000Z","America/Los_Angeles") ==> "2022-09-23T13:35:37.000-08:00"
toTimeZone("2022-09-23T13:35:37.000-08:00", "UTC") ==> "2022-09-23T21:35:37.000Z"

toTimeZone([], "") ==> []
toTimeZone({}, "") ==> {}

toTimeZone({["","",""],["","",""],["","",""]},"")