DateTime Functions
Convert Epoch Milliseconds to other Time Granular
toEpochSeconds
Converts epoch milliseconds to epoch seconds.
Syntax
toEpochSeconds(millis)Parameters
millis (LONG): Epoch timestamp in milliseconds.
Returns
LONG: Epoch timestamp in seconds.
Example
SELECT toEpochSeconds(1700000000000) FROM myTable
-- Returns 1700000000toEpochSecondsMV
Converts an array of epoch milliseconds to epoch seconds.
Syntax
Parameters
millisArray (LONG[]): Array of epoch timestamps in milliseconds.
Returns
LONG[]: Array of epoch timestamps in seconds.
toEpochMinutes
Converts epoch milliseconds to epoch minutes.
Syntax
Parameters
millis (LONG): Epoch timestamp in milliseconds.
Returns
LONG: Epoch timestamp in minutes.
Example
toEpochMinutesMV
Converts an array of epoch milliseconds to epoch minutes.
Syntax
Parameters
millisArray (LONG[]): Array of epoch timestamps in milliseconds.
Returns
LONG[]: Array of epoch timestamps in minutes.
toEpochHours
Converts epoch milliseconds to epoch hours.
Syntax
Parameters
millis (LONG): Epoch timestamp in milliseconds.
Returns
LONG: Epoch timestamp in hours.
Example
toEpochHoursMV
Converts an array of epoch milliseconds to epoch hours.
Syntax
Parameters
millisArray (LONG[]): Array of epoch timestamps in milliseconds.
Returns
LONG[]: Array of epoch timestamps in hours.
toEpochDays
Converts epoch milliseconds to epoch days.
Syntax
Parameters
millis (LONG): Epoch timestamp in milliseconds.
Returns
LONG: Epoch timestamp in days.
Example
toEpochDaysMV
Converts an array of epoch milliseconds to epoch days.
Syntax
Parameters
millisArray (LONG[]): Array of epoch timestamps in milliseconds.
Returns
LONG[]: Array of epoch timestamps in days.
Round Epoch Milliseconds to other Time Granular
toEpochSecondsRounded
Converts epoch milliseconds to epoch seconds, then rounds down to the nearest specified bucket size.
Syntax
Parameters
millis (LONG): Epoch timestamp in milliseconds.
roundToNearest (LONG): Bucket size in seconds for rounding.
Returns
LONG: Rounded epoch time in seconds.
Example
toEpochSecondsRoundedMV
Converts an array of epoch milliseconds to epoch seconds, then rounds each to the nearest specified bucket size.
Syntax
Parameters
millisArray (LONG[]): Array of epoch timestamps in milliseconds.
roundToNearest (LONG): Bucket size in seconds for rounding.
Returns
LONG[]: Array of rounded epoch timestamps in seconds.
toEpochMinutesRounded
Converts epoch milliseconds to epoch minutes, then rounds down to the nearest specified bucket size.
Syntax
Parameters
millis (LONG): Epoch timestamp in milliseconds.
roundToNearest (LONG): Bucket size in minutes for rounding.
Returns
LONG: Rounded epoch time in minutes.
Example
toEpochMinutesRoundedMV
Converts an array of epoch milliseconds to epoch minutes, then rounds each to the nearest specified bucket size.
Syntax
Parameters
millisArray (LONG[]): Array of epoch timestamps in milliseconds.
roundToNearest (LONG): Bucket size in minutes for rounding.
Returns
LONG[]: Array of rounded epoch timestamps in minutes.
toEpochHoursRounded
Converts epoch milliseconds to epoch hours, then rounds down to the nearest specified bucket size.
Syntax
Parameters
millis (LONG): Epoch timestamp in milliseconds.
roundToNearest (LONG): Bucket size in hours for rounding.
Returns
LONG: Rounded epoch time in hours.
Example
toEpochHoursRoundedMV
Converts an array of epoch milliseconds to epoch hours, then rounds each to the nearest specified bucket size.
Syntax
Parameters
millisArray (LONG[]): Array of epoch timestamps in milliseconds.
roundToNearest (LONG): Bucket size in hours for rounding.
Returns
LONG[]: Array of rounded epoch timestamps in hours.
toEpochDaysRounded
Converts epoch milliseconds to epoch days, then rounds down to the nearest specified bucket size.
Syntax
Parameters
millis (LONG): Epoch timestamp in milliseconds.
roundToNearest (LONG): Bucket size in days for rounding.
Returns
LONG: Rounded epoch time in days.
Example
toEpochDaysRoundedMV
Converts an array of epoch milliseconds to epoch days, then rounds each to the nearest specified bucket size.
Syntax
Parameters
millisArray (LONG[]): Array of epoch timestamps in milliseconds.
roundToNearest (LONG): Bucket size in days for rounding.
Returns
LONG[]: Array of rounded epoch timestamps in days.
Convert Epoch Milliseconds to other Time Granular then into Buckets
toEpochSecondsBucket
Converts epoch milliseconds to epoch seconds, then divides by a bucket to compute the number of buckets since the epoch.
Syntax
Parameters
millis (LONG): Epoch timestamp in milliseconds.
bucket (LONG): Size of each time bucket in seconds.
Returns
LONG: Number of bucket-sized seconds since epoch.
Example
toEpochSecondsBucketMV
Converts an array of epoch milliseconds to epoch seconds, then divides each by a bucket.
Syntax
Parameters
millisArray (LONG[]): Array of epoch timestamps in milliseconds.
bucket (LONG): Size of each time bucket in seconds.
Returns
LONG[]: Array of bucket-sized seconds since epoch.
toEpochMinutesBucket
Converts epoch milliseconds to epoch minutes, then divides by a bucket to compute the number of buckets since the epoch.
Syntax
Parameters
millis (LONG): Epoch timestamp in milliseconds.
bucket (LONG): Size of each time bucket in minutes.
Returns
LONG: Number of bucket-sized minutes since epoch.
Example
toEpochMinutesBucketMV
Converts an array of epoch milliseconds to epoch minutes, then divides each by a bucket.
Syntax
Parameters
millisArray (LONG[]): Array of epoch timestamps in milliseconds.
bucket (LONG): Size of each time bucket in minutes.
Returns
LONG[]: Array of bucket-sized minutes since epoch.
toEpochHoursBucket
Converts epoch milliseconds to epoch hours, then divides by a bucket to compute the number of buckets since the epoch.
Syntax
Parameters
millis (LONG): Epoch timestamp in milliseconds.
bucket (LONG): Size of each time bucket in hours.
Returns
LONG: Number of bucket-sized hours since epoch.
Example
toEpochHoursBucketMV
Converts an array of epoch milliseconds to epoch hours, then divides each by a bucket.
Syntax
Parameters
millisArray (LONG[]): Array of epoch timestamps in milliseconds.
bucket (LONG): Size of each time bucket in hours.
Returns
LONG[]: Array of bucket-sized hours since epoch.
toEpochDaysBucket
Converts epoch milliseconds to epoch days, then divides by a bucket to compute the number of buckets since the epoch.
Syntax
Parameters
millis (LONG): Epoch timestamp in milliseconds.
bucket (LONG): Size of each time bucket in days.
Returns
LONG: Number of bucket-sized days since epoch.
Example
toEpochDaysBucketMV
Converts an array of epoch milliseconds to epoch days, then divides each by a bucket.
Syntax
Parameters
millisArray (LONG[]): Array of epoch timestamps in milliseconds.
bucket (LONG): Size of each time bucket in days.
Returns
LONG[]: Array of bucket-sized days since epoch.
Any Time Granular to Epoch Milliseconds
fromEpochSeconds
Converts epoch seconds to epoch milliseconds.
Syntax
Parameters
seconds (LONG): Epoch timestamp in seconds.
Returns
LONG: Epoch timestamp in milliseconds.
Example
fromEpochSecondsMV
Converts an array of epoch seconds to epoch milliseconds.
Syntax
Parameters
secondsArray (LONG[]): Array of epoch timestamps in seconds.
Returns
LONG[]: Array of epoch timestamps in milliseconds.
fromEpochMinutes
Converts epoch minutes to epoch milliseconds.
Syntax
Parameters
minutes (LONG): Epoch timestamp in minutes.
Returns
LONG: Epoch timestamp in milliseconds.
Example
fromEpochMinutesMV
Converts an array of epoch minutes to epoch milliseconds.
Syntax
Parameters
minutesArray (LONG[]): Array of epoch timestamps in minutes.
Returns
LONG[]: Array of epoch timestamps in milliseconds.
fromEpochHours
Converts epoch hours to epoch milliseconds.
Syntax
Parameters
hours (LONG): Epoch timestamp in hours
Returns
LONG: Epoch timestamp in milliseconds.
Example
fromEpochHoursMV
Converts an array of epoch hours to epoch milliseconds.
Syntax
Parameters
hoursArray (LONG[]): Array of epoch timestamps in hours.
Returns
LONG[]: Array of epoch timestamps in milliseconds.
fromEpochDays
Converts epoch days to epoch milliseconds.
Syntax
Parameters
days (LONG): Epoch timestamp in days.
Returns
LONG: Epoch timestamp in milliseconds.
Example
fromEpochDaysMV
Converts an array of epoch days to epoch milliseconds.
Syntax
Parameters
daysArray (LONG[]): Array of epoch timestamps in days.
Returns
LONG[]: Array of epoch timestamps in milliseconds.
Any Bucketed Time Granular to Epoch Milliseconds
Converts a bucketed count of epoch seconds back to epoch milliseconds.
Syntax
Parameters
nSecondsSinceEpoch (LONG): Seconds-since-epoch divided by a bucket.
bucket (LONG): Size of each time bucket in seconds.
Returns
LONG: Epoch timestamp in milliseconds.
Example
fromEpochSecondsBucketMV
Converts an array of bucketed epoch seconds back to epoch milliseconds.
Syntax
Parameters
nSecondsArray (LONG[]): Array of bucketed epoch seconds.
bucket (LONG): Size of each time bucket in seconds.
Returns
LONG[]: Array of epoch timestamps in milliseconds.
fromEpochMinutesBucket
Converts a bucketed count of epoch minutes back to epoch milliseconds.
Syntax
Parameters
nMinutesSinceEpoch (LONG): Minutes-since-epoch divided by a bucket.
bucket (LONG): Size of each time bucket in minutes.
Returns
LONG: Epoch timestamp in milliseconds.
Example
fromEpochMinutesBucketMV
Converts an array of bucketed epoch minutes back to epoch milliseconds.
Syntax
Parameters
nMinutesArray (LONG[]): Array of bucketed epoch minutes.
bucket (LONG): Size of each time bucket in minutes.
Returns
LONG[]: Array of epoch timestamps in milliseconds.
fromEpochHoursBucket
Converts a bucketed count of epoch hours back to epoch milliseconds.
Syntax
Parameters
nHoursSinceEpoch (LONG): Hours-since-epoch divided by a bucket.
bucket (LONG): Size of each time bucket in hours.
Returns
LONG: Epoch timestamp in milliseconds.
fromEpochHoursBucketMV
Converts an array of bucketed epoch hours back to epoch milliseconds.
Syntax
Parameters
nHoursArray (LONG[]): Array of bucketed epoch hours.
bucket (LONG): Size of each time bucket in hours.
Returns
LONG[]: Array of epoch timestamps in milliseconds.
fromEpochDaysBucket
Converts a bucketed count of epoch days back to epoch milliseconds.
Syntax
Parameters
nDaysSinceEpoch (LONG): Days-since-epoch divided by a bucket.
bucket (LONG): Size of each time bucket in days.
Returns
LONG: Epoch timestamp in milliseconds.
fromEpochDaysBucketMV
Converts an array of bucketed epoch days back to epoch milliseconds.
Syntax
Parameters
nDaysArray (LONG[]): Array of bucketed epoch days.
bucket (LONG): Size of each time bucket in days.
Returns
LONG[]: Array of epoch timestamps in milliseconds.
ISO 8601 Conversion Functions
These functions allow converting between ISO 8601 date-time strings and epoch timestamps in milliseconds.
fromIso8601
Converts an ISO 8601 date-time string to epoch milliseconds.
Syntax
Parameters
iso8601 (STRING): A timestamp in ISO 8601 format (e.g., '2023-08-20T13:00:00Z').
Returns
LONG: Epoch timestamp in milliseconds.
Example
fromIso8601MV
Converts an array of ISO 8601 date-time strings to epoch milliseconds.
Syntax
Parameters
iso8601Array (STRING[]): Array of ISO 8601 formatted date-time strings.
Returns
LONG[]: Array of epoch timestamps in milliseconds.
toIso8601
Converts epoch milliseconds to an ISO 8601 formatted string.
Syntax
Parameters
millis (LONG): Epoch timestamp in milliseconds.
Returns
STRING: ISO 8601 formatted date-time string.
Example
toIso8601MV
Converts an array of epoch milliseconds to ISO 8601 formatted strings.
Syntax
Parameters
millisArray (LONG[]): Array of epoch timestamps in milliseconds.
Returns
STRING[]: Array of ISO 8601 formatted date-time strings.
Timestamp Conversion Functions
These functions allow converting between Java Timestamp objects and epoch timestamps in milliseconds. They are primarily useful when interfacing with SQL-compatible UDFs or timestamp-typed columns in Pinot.
toTimestamp
Converts epoch milliseconds to a Java Timestamp object.
Syntax
Parameters
millis (LONG): Epoch timestamp in milliseconds.
Returns
TIMESTAMP: A Java Timestamp object representing the given epoch time.
Example
toTimestampMV
Converts an array of epoch milliseconds to Java Timestamp objects.
Syntax
Parameters
millisArray (LONG[]): Array of epoch timestamps in milliseconds.
Returns
TIMESTAMP[]: Array of Java Timestamp objects.
fromTimestamp
Converts a Java Timestamp to epoch milliseconds.
Syntax
Parameters
timestamp (TIMESTAMP): A Java Timestamp object.
Returns
LONG: Epoch timestamp in milliseconds.
Example
fromTimestampMV
Converts an array of Java Timestamp objects to epoch milliseconds.
Syntax
Parameters
timestampArray (TIMESTAMP[]): Array of Java Timestamp objects.
Returns
LONG[]: Array of epoch timestamps in milliseconds.
Pattern-Based DateTime Conversion Functions
These functions allow conversions between epoch timestamps and formatted DateTime strings using custom patterns and optional time zones. Patterns must follow Java DateTimeFormatter syntax.
toDateTime
Converts epoch milliseconds to a DateTime string using a specified pattern.
Syntax
Parameters
millis (LONG): Epoch timestamp in milliseconds.
pattern (STRING): DateTime format pattern.
Returns
STRING: Formatted DateTime string.
Example
toDateTimeMV
Converts an array of epoch milliseconds to formatted DateTime strings using a pattern.
Syntax
Parameters
millisArray (LONG[]): Array of epoch timestamps.
pattern (STRING): DateTime format pattern.
Returns
STRING[]: Array of formatted DateTime strings.
toDateTime (with Time Zone)
Converts epoch milliseconds to a DateTime string using a specified pattern and time zone.
Syntax
Parameters
millis (LONG): Epoch timestamp in milliseconds.
pattern (STRING): DateTime format pattern.
timezoneId (STRING): Time zone ID (e.g., 'UTC', 'America/Los_Angeles').
Returns
STRING: Formatted DateTime string in the specified time zone.
Example
toDateTimeMV (with Time Zone)
Converts an array of epoch milliseconds to formatted DateTime strings using a pattern and time zone.
Syntax
Parameters
millisArray (LONG[]): Array of epoch timestamps.
pattern (STRING): DateTime format pattern.
timezoneId (STRING): Time zone ID.
Returns
STRING[]: Array of formatted DateTime strings.
fromDateTime
Parses a DateTime string (formatted per pattern) to epoch milliseconds.
Syntax
Parameters
dateTimeString (STRING): Input DateTime string.
pattern (STRING): DateTime format pattern.
Returns
LONG: Epoch timestamp in milliseconds.
Example
fromDateTimeMV
Parses an array of formatted DateTime strings to epoch milliseconds.
Syntax
Parameters
dateTimeArray (STRING[]): Array of DateTime strings.
pattern (STRING): DateTime format pattern.
Returns
LONG[]: Array of epoch timestamps.
fromDateTime (with Time Zone)
Parses a DateTime string to epoch milliseconds using a specified time zone.
Syntax
Parameters
dateTimeString (STRING): Input DateTime string.
pattern (STRING): DateTime format pattern.
timeZoneId (STRING): Time zone ID.
Returns
LONG: Epoch timestamp in milliseconds.
fromDateTime (with Time Zone and Default Value)
Parses a DateTime string to epoch milliseconds using time zone and returns a default if parsing fails.
Syntax
Parameters
dateTimeString (STRING): Input DateTime string.
pattern (STRING): DateTime format pattern.
timeZoneId (STRING): Time zone ID.
defaultVal (LONG): Fallback value (in millis) if parsing fails.
Returns
LONG: Epoch milliseconds or defaultVal on failure.
fromDateTimeMV (with Time Zone)
Parses an array of formatted DateTime strings to epoch milliseconds using a time zone.
Syntax
Parameters
dateTimeArray (STRING[]): Array of DateTime strings.
pattern (STRING): DateTime format pattern.
timeZoneId (STRING): Time zone ID.
Returns
LONG[]: Array of epoch timestamps.
Generic Time Rounding Functions
These functions round time values to the nearest multiple of a given unit, useful for custom bucketing or alignment.
round
Rounds the given time value down to the nearest multiple of roundToNearest.
Syntax
Parameters
timeValue (LONG): A time value, typically in milliseconds, seconds, etc.
roundToNearest (LONG): Unit of rounding (e.g., 60, 1000, etc.).
Returns
LONG: The original value rounded down to the nearest multiple of roundToNearest.
Example
roundMV
Rounds each value in an array of time values to the nearest multiple of roundToNearest.
Syntax
Parameters
timeArray (LONG[]): Array of time values.
roundToNearest (LONG): Unit of rounding.
Returns
LONG[]: Array of rounded values.
Time Utility Functions for now() and ago()
These scalar functions return or manipulate timestamps based on the current system time or durations. Useful for debugging, time-based filters, and dynamic comparisons.
now
Returns the current system time in epoch milliseconds.
Syntax
Parameters
None
Returns
LONG: Current time in milliseconds since epoch.
Example
sleep
Sleeps the current thread for the given number of milliseconds, returning the same value.
This function only works when assertions are enabled and is mostly intended for debugging and testing.
Syntax
Parameters
millis (LONG): Duration to sleep (in milliseconds).
Returns
LONG: Returns the same millis value after sleep completes.
ago
Returns the epoch millis representing the time before now by the given ISO-8601 duration.
Syntax
Parameters
periodString (STRING): Duration string in ISO-8601 format (e.g., 'PT10H', 'P2DT3H4M').
Returns
LONG: Epoch time in milliseconds representing now minus the given duration.
Example
agoMV
Applies the ago() logic on an array of ISO-8601 duration strings.
Syntax
Parameters
periodArray (STRING[]): Array of duration strings in ISO-8601 format.
Returns
LONG[]: Epoch times in milliseconds for each duration subtracted from current time.
Time Zone Offset Functions
These functions return the hour or minute component of a time zone’s offset from UTC, based on a time zone ID. They support static (current offset) and dynamic (offset at a specific timestamp) evaluations, including daylight saving time adjustments.
ℹ️ The timezoneId must follow Joda-Time time zone IDs (e.g., 'America/Los_Angeles', 'UTC', 'Asia/Kolkata').
timezoneHour
Returns the hour offset of the specified time zone at the epoch (UTC).
Syntax
Parameters
timezoneId (STRING): Joda-Time-compatible time zone ID.
Returns
INT: Hour offset from UTC (e.g., -8, 0, 5).
Example
timezoneHourMV
Returns the hour offset from UTC for an array of time zone IDs.
Syntax
Parameters
timezoneIdArray (STRING[]): Array of time zone IDs.
Returns
INT[]: Hour offsets from UTC.
timezoneHour (at timestamp)
Returns the hour offset of the time zone at a specific UTC timestamp (in millis), respecting daylight saving time.
Syntax
Parameters
timezoneId (STRING): Joda-Time-compatible time zone ID.
millis (LONG): UTC timestamp in milliseconds.
Returns
INT: Hour offset from UTC at that moment.
timezoneHourMV (at timestamp)
Returns hour offsets for the specified time zone ID at each of the provided timestamps.
Syntax
Parameters
timezoneId (STRING): Joda-Time-compatible time zone ID.
millisArray (LONG[]): Array of UTC timestamps in milliseconds.
Returns
INT[]: Hour offsets at the corresponding timestamps.
timezoneMinute
Returns the minute offset of the specified time zone at the epoch (UTC).
Syntax
Parameters
timezoneId (STRING): Joda-Time-compatible time zone ID.
Returns
INT: Minute component of the time zone offset (e.g., 30 for IST, 0 for UTC).
timezoneMinuteMV
Returns minute offsets from UTC for an array of time zone IDs.
Syntax
Parameters
timezoneIdArray (STRING[]): Array of time zone IDs.
Returns
INT[]: Minute components of UTC offset.
timezoneMinute (at timestamp)
Returns the minute offset of the time zone at a specific UTC timestamp (in millis), accounting for DST.
Syntax
Parameters
timezoneId (STRING): Joda-Time-compatible time zone ID.
millis (LONG): UTC timestamp in milliseconds.
Returns
INT: Minute offset from UTC at the given timestamp.
timezoneMinuteMV (at timestamp)
Returns minute offsets for the specified time zone ID at each timestamp.
Syntax
Parameters
timezoneId (STRING): Joda-Time-compatible time zone ID.
millisArray (LONG[]): Array of UTC timestamps.
Returns
INT[]: Minute offsets from UTC.
Date and Time Component Functions
These functions extract specific components (year, month, day, hour, etc.) from an epoch timestamp in milliseconds. They support both UTC (default) and custom time zones using Joda-Time zone IDs. MV variants apply the same logic to arrays of timestamps.
year(millis)
Returns the calendar year in UTC.
Syntax
Parameters
millis (LONG): Epoch timestamp in milliseconds.
Returns
INT: Year (e.g., 2024)
year(millis, timezoneId)
Returns the calendar year in the specified time zone.
Syntax
Parameters
timezoneId (STRING): Joda-Time-compatible time zone ID.
Returns
INT: Year (e.g., 2024)
yearMV(millisArray) / yearMV(millisArray, timezoneId)
Returns years from an array of timestamps.
Returns
INT[]: Array of years
yearOfWeek(millis) / yow(millis)
Returns the ISO week-based year in UTC.
Returns
INT: Week-based year (e.g., 2024)
yearOfWeek(millis, timezoneId)
Returns ISO week-based year in a specified time zone.
yearOfWeekMV(...) / yowMV(...)
Multi-value variants.
quarter(millis[, timezoneId])
Returns the quarter of the year (1 to 4).
Returns
INT: 1 = Q1, 2 = Q2, 3 = Q3, 4 = Q4
month(millis[, timezoneId]) / monthOfYear(...)
Returns the month of the year (1 to 12).
week(millis[, timezoneId]) / weekOfYear(...)
Returns the ISO week of the year (1 to 53).
dayOfYear(millis[, timezoneId]) / doy(...)
Returns the day of the year (1 to 366).
dayOfMonth(millis[, timezoneId]) / day(...)
Returns the day of the month (1 to 31).
dayOfWeek(millis[, timezoneId]) / dow(...)
Returns the day of the week (1 = Monday to 7 = Sunday).
hour(millis[, timezoneId])
Returns the hour of the day (0 to 23).
minute(millis[, timezoneId])
Returns the minute of the hour (0 to 59).
second(millis[, timezoneId])
Returns the second of the minute (0 to 59).
millisecond(millis[, timezoneId])
Returns the millisecond of the second (0 to 999).
MV Variants
All of the above functions have MV counterparts that operate on arrays of epoch timestamps and return arrays of corresponding extracted values.
Example
Generalized Date/Time Extraction Function
This function extracts specific components (e.g., year, month, day, hour) from a timestamp using a flexible string-based interval specifier.
extract(interval, timestamp)
Returns the specified date/time component from the given epoch timestamp.
Syntax
Parameters
interval (STRING): The component to extract. Supported values depend on DateTimeUtils.ExtractFieldType and typically include:
YEAR, QUARTER, MONTH, WEEK, DAY, DOY, DOW,
HOUR, MINUTE, SECOND, MILLISECOND
timestamp (LONG): The epoch time in milliseconds.
Returns
INT: Extracted component as an integer.
Examples
ℹ️ This function provides a flexible alternative to the dedicated year(), month(), hour(), etc., functions when the field to extract is dynamically determined.
Timestamp Truncation(DateTrunc) Functions
These functions round down (truncate) a timestamp to the start of a specified time unit (e.g., to the start of the hour, day, or month). They support flexible input/output units and optional time zone handling.
dateTrunc(unit, timeValue)
Truncates a timestamp to the start of the specified unit in UTC, returning milliseconds since epoch.
Syntax
Parameters
unit (STRING): One of millisecond, second, minute, hour, day, week, month, quarter, year.
timeValue (LONG): Timestamp in milliseconds.
Returns
LONG: Truncated timestamp in milliseconds.
Example
dateTrunc(unit, timeValue, inputTimeUnit)
Truncates a timestamp using an input unit (e.g., seconds, hours). Output remains in the same unit.
Syntax
Parameters
inputTimeUnit (STRING): Time unit of timeValue. Values include MILLISECONDS, SECONDS, MINUTES, etc.
Example
dateTrunc(unit, timeValue, inputTimeUnit, timezone)
Truncates timestamp to unit, considering time zone offset and DST.
Syntax
Parameters
timezone (STRING): Time zone ID (e.g., America/Los_Angeles).
Example
dateTrunc(unit, timeValue, inputTimeUnit, timezone, outputTimeUnit)
Full-featured truncation including conversion of both input and output units and time zone.
Syntax
Example
dateTruncMV(...)
All above variants have corresponding multi-value (MV) forms:
dateTruncMV(unit, timeArray)
dateTruncMV(unit, timeArray, inputTimeUnit)
dateTruncMV(unit, timeArray, inputTimeUnit, timeZone)
dateTruncMV(unit, timeArray, inputTimeUnit, timeZone, outputTimeUnit)
Returns
LONG[]: Array of truncated timestamps.
Example
Date Binning Functions
These functions align a given timestamp to the nearest fixed-width time bin based on a specified duration and an origin timestamp. They are useful for windowed aggregations, histograms, or time-based bucketing.
dateBin(binWidthStr, sourceTimestamp, originTimestamp)
Aligns a sourceTimestamp to the nearest lower bin boundary of the given binWidthStr starting from the specified originTimestamp.
Syntax
Parameters
binWidthStr (STRING): The bin width as a duration string. Supported formats include:
ISO-8601 durations (e.g., 'PT15M' for 15 minutes, 'P1D' for 1 day)
Shortened forms (e.g., '15m', '2h', '1d') if supported by TimeUtils.convertPeriodToMillis.
sourceTimestamp (TIMESTAMP): The timestamp to align.
originTimestamp (TIMESTAMP): The origin (start) time for bin alignment.
Returns
TIMESTAMP: Aligned timestamp that falls at the start of the corresponding bin.
Examples
Timestamp Arithmetic Functions
These functions support performing arithmetic operations on timestamps in epoch milliseconds—such as adding durations to a timestamp or calculating the difference between two timestamps.
Multi-value (MV) variants operate over arrays of timestamps.
timestampAdd(unit, interval, timestamp)
Aliases: dateAdd
Adds a specified amount of time to a timestamp.
Syntax
Parameters
unit (STRING): The time unit to add. Supported values include:
milliseconds, seconds, minutes, hours, days, weeks, months, quarters, years.
interval (LONG): The amount of time to add. Can be negative to subtract.
timestamp (LONG): The input timestamp in epoch milliseconds.
Returns
LONG: The resulting timestamp, also in epoch milliseconds.
Examples
timestampAddMV(unit, interval, timestampArray)
Aliases: dateAddMV
Vectorized version of timestampAdd, applying the operation to an array of timestamps.
Parameters
timestampArray (LONG[]): Array of input timestamps.
Returns
LONG[]: Resulting timestamps.
Example
timestampDiff(unit, timestamp1, timestamp2)
Aliases: dateDiff
Computes the difference between timestamp2 and timestamp1, expressed in the specified unit.
Positive values indicate timestamp2 is later than timestamp1.
Syntax
Parameters
unit (STRING): Time unit for the result (days, hours, months, etc.).
timestamp1 (LONG): Starting timestamp (in epoch millis).
timestamp2 (LONG): Ending timestamp (in epoch millis).
Returns
LONG: Time difference in the given unit.
Example
timestampDiffMV(unit, timestampArray1, timestamp2)
Aliases: dateDiffMV
Computes the difference between each timestamp in the first array and a single second timestamp.
Returns
LONG[]: Differences in specified unit.
Example
timestampDiffMVReverse(unit, timestamp1, timestampArray2)
Aliases: dateDiffMVReverse
Computes the difference between a single first timestamp and each timestamp in the second array.
Returns
LONG[]: Differences in specified unit.
Example
Last updated
Was this helpful?