You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@spark.apache.org by ur...@apache.org on 2026/09/11 10:08:40 UTC
(spark) branch branch-4.x updated: [SPARK-57820][SQL] Support nanosecond-precision timestamps in time_bucket
This is an automated email from the ASF dual-hosted git repository.
uros-b pushed a commit to branch branch-4.x
in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/branch-4.x by this push:
new 63d8f8bc69bd [SPARK-57820][SQL] Support nanosecond-precision timestamps in time_bucket
63d8f8bc69bd is described below
commit 63d8f8bc69bd145956fa5cfa4493598d7bd7a2ac
Author: Vinod KC <vi...@gmail.com>
AuthorDate: Fri Sep 11 10:08:18 2026 +0000
[SPARK-57820][SQL] Support nanosecond-precision timestamps in time_bucket
### What changes were proposed in this pull request?
This PR extends the time_bucket() function to support nanosecond-precision timestamps (TIMESTAMP_NTZ(p) and TIMESTAMP_LTZ(p) where p > 6).
### Why are the changes needed?
Users working with high-precision temporal data (nanosecond resolution) were unable to use time_bucket() for this precision level, even though Spark SQL supports nanosecond-precision timestamps. This PR enables full support for nanosecond-precision bucketing while maintaining complete backward compatibility with existing microsecond behavior.
### Does this PR introduce _any_ user-facing change?
Yes. The time_bucket() function now accepts nanosecond-precision timestamp arguments (TIMESTAMP_NTZ(p) and TIMESTAMP_LTZ(p) where p > 6) and returns bucket boundaries at nanosecond precision. It preserves nanosecond precision in the result
The change is backward compatible: existing queries using microsecond-precision timestamps continue to work unchanged.
```sql
SET spark.sql.timestampNanosTypes.enabled = true;
SET TIME ZONE 'UTC';
-- Two ticks that land on the SAME microsecond (10:00:04.000000) but differ
-- in their true sub-microsecond arrival time.
CREATE OR REPLACE TEMP VIEW ticks AS
SELECT * FROM VALUES
(CAST('2024-06-20 10:00:04.000000100' AS TIMESTAMP_NTZ(9)), 'tick_A'),
(CAST('2024-06-20 10:00:04.000000700' AS TIMESTAMP_NTZ(9)), 'tick_B')
AS ticks(event_time, tick_id);
-- The session-start origin, itself nanosecond-precision.
SELECT event_time, tick_id,
time_bucket(
INTERVAL '1' SECOND,
event_time,
CAST('1970-01-01 00:00:00.000000500' AS TIMESTAMP_NTZ(9))) AS session_window
FROM ticks
ORDER BY event_time;
```
**Output (with this fix):**
```
event_time tick_id session_window
2024-06-20 10:00:04.0000001 tick_A 2024-06-20 10:00:03.0000005
2024-06-20 10:00:04.0000007 tick_B 2024-06-20 10:00:04.0000005
```
### How was this patch tested?
1. Unit Tests:
- `DateExpressionsSuite`: Tests for nanosecond bucketing with various bucket intervals (days, hours, months, years)
- `DateTimeUtilsSuite`: Tests for edge cases in nanosecond bucketing, including the microsecond-boundary case
2. Golden File Tests: Extended r`esources/sql-tests/inputs/time-bucket.sql` with nanosecond test cases
### Was this patch authored or co-authored using generative AI tooling?
Yes, Generated-by: Claude Code
Closes #56968 from vinodkc/SPARK-57820.
Authored-by: Vinod KC <vi...@gmail.com>
Signed-off-by: Uros Bojanic <22...@users.noreply.github.com>
(cherry picked from commit 89001f2c8cdacf8edf289abb22cb618610b3ef93)
Signed-off-by: Uros Bojanic <22...@users.noreply.github.com>
---
python/pyspark/sql/functions/builtin.py | 3 +-
.../scala/org/apache/spark/sql/functions.scala | 6 +-
.../catalyst/expressions/datetimeExpressions.scala | 54 ++++--
.../spark/sql/catalyst/util/DateTimeUtils.scala | 42 +++++
.../expressions/DateExpressionsSuite.scala | 201 +++++++++++++++++++++
.../sql/catalyst/util/DateTimeUtilsSuite.scala | 72 ++++++++
.../sql-tests/analyzer-results/time-bucket.sql.out | 92 +++++++++-
.../resources/sql-tests/inputs/time-bucket.sql | 48 +++++
.../sql-tests/results/time-bucket.sql.out | 99 +++++++++-
9 files changed, 590 insertions(+), 27 deletions(-)
diff --git a/python/pyspark/sql/functions/builtin.py b/python/pyspark/sql/functions/builtin.py
index 250b92abdd75..75f31d4bc1c4 100644
--- a/python/pyspark/sql/functions/builtin.py
+++ b/python/pyspark/sql/functions/builtin.py
@@ -14339,7 +14339,8 @@ def time_bucket(
A day-time or year-month interval defining the bucket size. Must be positive
and foldable.
ts : :class:`~pyspark.sql.Column` or column name
- A TIMESTAMP or TIMESTAMP_NTZ value to bucket.
+ A TIMESTAMP, TIMESTAMP_NTZ, or nanosecond-precision
+ (TIMESTAMP_LTZ(p) / TIMESTAMP_NTZ(p), p in [7, 9]) value to bucket.
origin : :class:`~pyspark.sql.Column`, optional
Alignment anchor. Defaults to 1970-01-01 00:00:00. Must be the same type as
``ts`` and must be foldable.
diff --git a/sql/api/src/main/scala/org/apache/spark/sql/functions.scala b/sql/api/src/main/scala/org/apache/spark/sql/functions.scala
index fe74c8d7ff9c..fa6cca280d7e 100644
--- a/sql/api/src/main/scala/org/apache/spark/sql/functions.scala
+++ b/sql/api/src/main/scala/org/apache/spark/sql/functions.scala
@@ -13297,7 +13297,8 @@ object functions {
* @param bucketSize
* A day-time or year-month interval defining the bucket size. Must be positive and foldable.
* @param ts
- * A TIMESTAMP or TIMESTAMP_NTZ value to bucket.
+ * A TIMESTAMP, TIMESTAMP_NTZ, or nanosecond-precision (TIMESTAMP_LTZ(p) / TIMESTAMP_NTZ(p), p
+ * in [7, 9]) value to bucket.
* @group datetime_funcs
* @since 4.2.0
* @return
@@ -13319,7 +13320,8 @@ object functions {
* @param bucketSize
* A day-time or year-month interval defining the bucket size. Must be positive and foldable.
* @param ts
- * A TIMESTAMP or TIMESTAMP_NTZ value to bucket.
+ * A TIMESTAMP, TIMESTAMP_NTZ, or nanosecond-precision (TIMESTAMP_LTZ(p) / TIMESTAMP_NTZ(p), p
+ * in [7, 9]) value to bucket.
* @param origin
* Alignment anchor. Must be the same type as `ts` and must be foldable.
* @group datetime_funcs
diff --git a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/datetimeExpressions.scala b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/datetimeExpressions.scala
index e98a8dab4b7a..65eb1e19c5e2 100644
--- a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/datetimeExpressions.scala
+++ b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/datetimeExpressions.scala
@@ -5217,8 +5217,8 @@ case class TimeBucket(
override def inputTypes: Seq[AbstractDataType] = Seq(
TypeCollection(DayTimeIntervalType, YearMonthIntervalType),
- AnyTimestampType,
- AnyTimestampType)
+ TypeCollection(AnyTimestampType, AnyTimestampNanoType),
+ TypeCollection(AnyTimestampType, AnyTimestampNanoType))
override def dataType: DataType = ts.dataType
@@ -5276,16 +5276,24 @@ case class TimeBucket(
}
override def nullSafeEval(bucketSizeVal: Any, tsVal: Any, originVal: Any): Any = {
- first.dataType match {
- case _: DayTimeIntervalType =>
+ (first.dataType, ts.dataType) match {
+ case (_: DayTimeIntervalType, _: AnyTimestampNanoType) =>
+ DateTimeUtils.timeBucketDTIntervalNanos(
+ bucketSizeVal.asInstanceOf[Long], tsVal.asInstanceOf[TimestampNanosVal],
+ originVal.asInstanceOf[TimestampNanosVal], zoneIdInEval)
+ case (_: DayTimeIntervalType, _) =>
DateTimeUtils.timeBucketDTInterval(
bucketSizeVal.asInstanceOf[Long], tsVal.asInstanceOf[Long],
originVal.asInstanceOf[Long], zoneIdInEval)
- case _: YearMonthIntervalType =>
+ case (_: YearMonthIntervalType, _: AnyTimestampNanoType) =>
+ DateTimeUtils.timeBucketYMIntervalNanos(
+ bucketSizeVal.asInstanceOf[Int], tsVal.asInstanceOf[TimestampNanosVal],
+ originVal.asInstanceOf[TimestampNanosVal], zoneIdInEval)
+ case (_: YearMonthIntervalType, _) =>
DateTimeUtils.timeBucketYMInterval(
bucketSizeVal.asInstanceOf[Int], tsVal.asInstanceOf[Long],
originVal.asInstanceOf[Long], zoneIdInEval)
- case other => throw SparkException.internalError(
+ case (other, _) => throw SparkException.internalError(
s"Unexpected bucketSize type: $other")
}
}
@@ -5293,14 +5301,20 @@ case class TimeBucket(
override def doGenCode(ctx: CodegenContext, ev: ExprCode): ExprCode = {
val dtu = DateTimeUtils.getClass.getName.stripSuffix("$")
val zid = ctx.addReferenceObj("zoneId", zoneIdInEval, classOf[ZoneId].getName)
- first.dataType match {
- case _: DayTimeIntervalType =>
+ (first.dataType, ts.dataType) match {
+ case (_: DayTimeIntervalType, _: AnyTimestampNanoType) =>
+ defineCodeGen(ctx, ev, (bucketSizeCode, tsCode, originCode) =>
+ s"$dtu.timeBucketDTIntervalNanos($bucketSizeCode, $tsCode, $originCode, $zid)")
+ case (_: DayTimeIntervalType, _) =>
defineCodeGen(ctx, ev, (bucketSizeCode, tsCode, originCode) =>
s"$dtu.timeBucketDTInterval($bucketSizeCode, $tsCode, $originCode, $zid)")
- case _: YearMonthIntervalType =>
+ case (_: YearMonthIntervalType, _: AnyTimestampNanoType) =>
+ defineCodeGen(ctx, ev, (bucketSizeCode, tsCode, originCode) =>
+ s"$dtu.timeBucketYMIntervalNanos($bucketSizeCode, $tsCode, $originCode, $zid)")
+ case (_: YearMonthIntervalType, _) =>
defineCodeGen(ctx, ev, (bucketSizeCode, tsCode, originCode) =>
s"$dtu.timeBucketYMInterval($bucketSizeCode, $tsCode, $originCode, $zid)")
- case other => throw SparkException.internalError(
+ case (other, _) => throw SparkException.internalError(
s"Unexpected bucketSize type: $other")
}
}
@@ -5324,8 +5338,8 @@ case class TimeBucket(
arguments = """
Arguments:
* bucketSize - A day-time or year-month interval defining the bucket size. Must be positive and foldable.
- * ts - A TIMESTAMP or TIMESTAMP_NTZ value to bucket.
- * origin - Optional TIMESTAMP or TIMESTAMP_NTZ alignment anchor. Defaults to 1970-01-01 00:00:00. Must be the same type as ts and must be foldable.
+ * ts - A TIMESTAMP, TIMESTAMP_NTZ, or nanosecond-precision (TIMESTAMP_LTZ(p) / TIMESTAMP_NTZ(p), p in [7, 9]) value to bucket.
+ * origin - Optional alignment anchor. Defaults to 1970-01-01 00:00:00. Must be the same type as ts and must be foldable.
""",
examples = """
Examples:
@@ -5345,22 +5359,30 @@ object TimeBucketExpressionBuilder extends ExpressionBuilder {
case _ => e
}
- // Default origin: 1970-01-01 00:00:00 in the session time zone for TIMESTAMP, and
- // EPOCH (1970-01-01 00:00:00 UTC) for TIMESTAMP_NTZ.
+ // Default origin: 1970-01-01 00:00:00 in the session time zone for TIMESTAMP / TIMESTAMP_LTZ(p),
+ // and EPOCH (1970-01-01 00:00:00 UTC) for TIMESTAMP_NTZ / TIMESTAMP_NTZ(p).
private def defaultOrigin(tsType: DataType): Literal = tsType match {
case TimestampType =>
val zoneId = DateTimeUtils.getZoneId(SQLConf.get.sessionLocalTimeZone)
Literal(DateTimeUtils.daysToMicros(0, zoneId), TimestampType)
+ case _: TimestampLTZNanosType =>
+ val zoneId = DateTimeUtils.getZoneId(SQLConf.get.sessionLocalTimeZone)
+ Literal(TimestampNanosVal.fromParts(DateTimeUtils.daysToMicros(0, zoneId), 0.toShort), tsType)
+ case _: AnyTimestampNanoType =>
+ Literal(TimestampNanosVal.ZERO, tsType)
case _ => Literal(0L, tsType)
}
+ private def acceptsTsType(dt: DataType): Boolean =
+ AnyTimestampType.acceptsType(dt) || dt.isInstanceOf[AnyTimestampNanoType]
+
override def build(funcName: String, expressions: Seq[Expression]): Expression = {
expressions match {
case Seq(rawBucketSize, rawTs) =>
val bucketSize = retypeNull(rawBucketSize, DayTimeIntervalType())
// Fall back to TimestampType for bad ts types; ExpectsInputTypes will report it.
val tsType = rawTs.dataType match {
- case t if AnyTimestampType.acceptsType(t) => t
+ case t if acceptsTsType(t) => t
case _ => TimestampType
}
val ts = retypeNull(rawTs, tsType)
@@ -5368,7 +5390,7 @@ object TimeBucketExpressionBuilder extends ExpressionBuilder {
case Seq(rawBucketSize, rawTs, rawOrigin) =>
val bucketSize = retypeNull(rawBucketSize, DayTimeIntervalType())
val tsType = (rawTs.dataType, rawOrigin.dataType) match {
- case (NullType, t) if AnyTimestampType.acceptsType(t) => t
+ case (NullType, t) if acceptsTsType(t) => t
case (NullType, _) => TimestampType
case (t, _) => t
}
diff --git a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/DateTimeUtils.scala b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/DateTimeUtils.scala
index d605cbae7bb4..01d2a66b7508 100644
--- a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/DateTimeUtils.scala
+++ b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/DateTimeUtils.scala
@@ -1392,4 +1392,46 @@ object DateTimeUtils extends SparkDateTimeUtils {
}
c
}
+
+ /**
+ * Nanosecond-precision DayTimeInterval bucketing. `bucketMicros` is an integer number of
+ * microseconds, so every bucket boundary `origin + k * bucketSize` carries the same
+ * `nanosWithinMicro` remainder as `origin` -- only the choice of `k` needs nanosecond
+ * resolution, and only when the micro-level boundary lands on the same microsecond as `ts`:
+ * if `origin`'s remainder is larger than `ts`'s there, the true boundary is one bucket earlier.
+ */
+ def timeBucketDTIntervalNanos(
+ bucketMicros: Long,
+ ts: TimestampNanosVal,
+ origin: TimestampNanosVal,
+ zoneId: ZoneId): TimestampNanosVal = {
+ val startMicros = timeBucketDTInterval(bucketMicros, ts.epochMicros, origin.epochMicros, zoneId)
+ val adjustedStartMicros =
+ if (startMicros == ts.epochMicros && origin.nanosWithinMicro > ts.nanosWithinMicro) {
+ timeBucketDTInterval(bucketMicros, startMicros - 1, origin.epochMicros, zoneId)
+ } else {
+ startMicros
+ }
+ TimestampNanosVal.fromParts(adjustedStartMicros, origin.nanosWithinMicro)
+ }
+
+ /**
+ * Nanosecond-precision YearMonthInterval bucketing. See
+ * [[timeBucketDTIntervalNanos]] for why the result's `nanosWithinMicro` always equals
+ * `origin`'s, and why only the choice of `k` needs sub-microsecond resolution.
+ */
+ def timeBucketYMIntervalNanos(
+ bucketMonths: Int,
+ ts: TimestampNanosVal,
+ origin: TimestampNanosVal,
+ zoneId: ZoneId): TimestampNanosVal = {
+ val startMicros = timeBucketYMInterval(bucketMonths, ts.epochMicros, origin.epochMicros, zoneId)
+ val adjustedStartMicros =
+ if (startMicros == ts.epochMicros && origin.nanosWithinMicro > ts.nanosWithinMicro) {
+ timeBucketYMInterval(bucketMonths, startMicros - 1, origin.epochMicros, zoneId)
+ } else {
+ startMicros
+ }
+ TimestampNanosVal.fromParts(adjustedStartMicros, origin.nanosWithinMicro)
+ }
}
diff --git a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/DateExpressionsSuite.scala b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/DateExpressionsSuite.scala
index bcff9fa53c32..e6051177feaf 100644
--- a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/DateExpressionsSuite.scala
+++ b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/DateExpressionsSuite.scala
@@ -3434,6 +3434,207 @@ class DateExpressionsSuite extends SparkFunSuite with ExpressionEvalHelper {
}
}
+ test("time_bucket: nanosecond-precision day-time interval") {
+ import org.apache.spark.sql.catalyst.util.TimestampNanosTestUtils._
+ withSQLConf(
+ SQLConf.SESSION_LOCAL_TIMEZONE.key -> "UTC",
+ SQLConf.TIMESTAMP_NANOS_TYPES_ENABLED.key -> "true") {
+ foreachNanosPrecision { p =>
+ Seq(TimestampLTZNanosType(p), TimestampNTZNanosType(p)).foreach { dt =>
+ val bucketSize = Literal(Duration.ofSeconds(1))
+
+ // Normal case: ts strictly inside a bucket, unrelated to any sub-micro boundary.
+ // origin = (0us, 500ns); ts = (3_500_000us, 999ns) -> bucket = (3_000_000us, 500ns).
+ checkEvaluation(
+ TimeBucket(
+ bucketSize,
+ Literal.create(nanosVal(3500000L, 999), dt),
+ Literal.create(nanosVal(0L, 500), dt)),
+ nanosVal(3000000L, 500))
+
+ // Boundary case, ts's sub-micro remainder < origin's: ts's true instant falls just
+ // short of the nominal grid point at the same microsecond, so it belongs to the
+ // previous bucket. origin = (0us, 500ns); ts = (3_000_000us, 200ns) ->
+ // bucket = (2_000_000us, 500ns), not (3_000_000us, 500ns).
+ checkEvaluation(
+ TimeBucket(
+ bucketSize,
+ Literal.create(nanosVal(3000000L, 200), dt),
+ Literal.create(nanosVal(0L, 500), dt)),
+ nanosVal(2000000L, 500))
+
+ // Boundary case, ts's sub-micro remainder >= origin's: ts's true instant is at or
+ // after the nominal grid point, so it starts a new bucket there.
+ // origin = (0us, 500ns); ts = (3_000_000us, 700ns) -> bucket = (3_000_000us, 500ns).
+ checkEvaluation(
+ TimeBucket(
+ bucketSize,
+ Literal.create(nanosVal(3000000L, 700), dt),
+ Literal.create(nanosVal(0L, 500), dt)),
+ nanosVal(3000000L, 500))
+
+ // Multi-day bucket (calendar-aware path) with sub-micro origin remainder preserved.
+ checkEvaluation(
+ TimeBucket(
+ Literal(Duration.ofDays(1)),
+ Literal.create(nanosVal(DateTimeUtils.daysToMicros(3, UTC) + 500000L, 999), dt),
+ Literal.create(nanosVal(500, 250), dt)),
+ nanosVal(DateTimeUtils.daysToMicros(3, UTC) + 500, 250))
+
+ // NULL ts -> NULL
+ checkEvaluation(
+ TimeBucket(
+ bucketSize,
+ Literal.create(null, dt),
+ Literal.create(nanosVal(0L, 0), dt)),
+ null)
+
+ // NULL origin -> NULL
+ checkEvaluation(
+ TimeBucket(
+ bucketSize,
+ Literal.create(nanosVal(0L, 0), dt),
+ Literal.create(null, dt)),
+ null)
+ }
+ }
+ }
+ }
+
+ test("time_bucket: nanosecond-precision year-month interval") {
+ import org.apache.spark.sql.catalyst.util.TimestampNanosTestUtils._
+ withSQLConf(
+ SQLConf.SESSION_LOCAL_TIMEZONE.key -> "UTC",
+ SQLConf.TIMESTAMP_NANOS_TYPES_ENABLED.key -> "true") {
+ foreachNanosPrecision { p =>
+ Seq(TimestampLTZNanosType(p), TimestampNTZNanosType(p)).foreach { dt =>
+ val origin = localDateTimeToNanosVal(timestampNTZ(1970, 1, 1))
+ val originWithRem = nanosVal(origin.epochMicros, 500)
+
+ // 1-month bucket, ts well inside the bucket.
+ val ts = localDateTimeToNanosVal(timestampNTZ(2024, 3, 15, 11, 27, 0))
+ checkEvaluation(
+ TimeBucket(
+ Literal(Period.ofMonths(1)),
+ Literal.create(nanosVal(ts.epochMicros, 999), dt),
+ Literal.create(originWithRem, dt)),
+ nanosVal(localDateTimeToNanosVal(timestampNTZ(2024, 3, 1)).epochMicros, 500))
+
+ // Boundary case: ts lands exactly on the microsecond of a monthly grid point but
+ // with a smaller sub-micro remainder, so it belongs to the previous bucket.
+ val boundaryMicros = localDateTimeToNanosVal(timestampNTZ(2024, 3, 1)).epochMicros
+ checkEvaluation(
+ TimeBucket(
+ Literal(Period.ofMonths(1)),
+ Literal.create(nanosVal(boundaryMicros, 100), dt),
+ Literal.create(originWithRem, dt)),
+ nanosVal(localDateTimeToNanosVal(timestampNTZ(2024, 2, 1)).epochMicros, 500))
+ }
+ }
+ }
+ }
+
+ test("time_bucket: nanosecond-precision honors session time zone for TIMESTAMP_LTZ") {
+ import org.apache.spark.sql.catalyst.util.TimestampNanosTestUtils._
+ val laZone = getZoneId("America/Los_Angeles")
+ withSQLConf(
+ SQLConf.SESSION_LOCAL_TIMEZONE.key -> "America/Los_Angeles",
+ SQLConf.TIMESTAMP_NANOS_TYPES_ENABLED.key -> "true") {
+ foreachNanosPrecision { p =>
+ val dt = TimestampLTZNanosType(p)
+
+ // origin = 1970-01-01 00:00:00 LA, with a 500ns sub-micro remainder. The bucket start
+ // lands on a different microsecond than ts in every case below, so the result always
+ // carries origin's remainder (500ns).
+ val originMicros = instantToNanosVal(timestampLTZ(1970, 1, 1, zoneId = laZone)).epochMicros
+ val origin = Literal.create(nanosVal(originMicros, 500), dt)
+
+ // Year-month bucket, summer (PDT): 2024-07-15 10:00 LA -> 2024-07-01 00:00 LA.
+ val summerTs = instantToNanosVal(timestampLTZ(2024, 7, 15, 10, zoneId = laZone))
+ val summerBucket = instantToNanosVal(timestampLTZ(2024, 7, 1, zoneId = laZone))
+ checkEvaluation(
+ TimeBucket(
+ Literal(Period.ofMonths(1)),
+ Literal.create(nanosVal(summerTs.epochMicros, 999), dt),
+ origin),
+ nanosVal(summerBucket.epochMicros, 500))
+
+ // Year-month bucket, winter (PST): 2024-02-15 10:00 LA -> 2024-02-01 00:00 LA. The
+ // UTC offset differs from the summer case, so the session zone must be honored.
+ val winterTs = instantToNanosVal(timestampLTZ(2024, 2, 15, 10, zoneId = laZone))
+ val winterBucket = instantToNanosVal(timestampLTZ(2024, 2, 1, zoneId = laZone))
+ checkEvaluation(
+ TimeBucket(
+ Literal(Period.ofMonths(1)),
+ Literal.create(nanosVal(winterTs.epochMicros, 999), dt),
+ origin),
+ nanosVal(winterBucket.epochMicros, 500))
+
+ // Day-time 1-day bucket aligns to the LA calendar day, not the UTC day: the instant
+ // 2024-07-15 05:00 LA (2024-07-15 12:00 UTC) buckets to 2024-07-15 00:00 LA.
+ val dayTs = instantToNanosVal(timestampLTZ(2024, 7, 15, 5, zoneId = laZone))
+ val dayBucket = instantToNanosVal(timestampLTZ(2024, 7, 15, 0, zoneId = laZone))
+ checkEvaluation(
+ TimeBucket(
+ Literal(Duration.ofDays(1)),
+ Literal.create(nanosVal(dayTs.epochMicros, 999), dt),
+ origin),
+ nanosVal(dayBucket.epochMicros, 500))
+ }
+ }
+ }
+
+ test("time_bucket: checkInputDataTypes with nanosecond timestamps") {
+ val ntzTsLit = Literal.create(TimestampNanosVal.ZERO, TimestampNTZNanosType(9))
+ val ltzTsLit = Literal.create(TimestampNanosVal.ZERO, TimestampLTZNanosType(9))
+ val hour = Literal(Duration.ofHours(1))
+
+ // ts/origin type mismatch: TIMESTAMP_NTZ(p) ts vs TIMESTAMP_LTZ(p) origin
+ val expr1 = TimeBucket(hour, ntzTsLit, ltzTsLit)
+ val r1 = expr1.checkInputDataTypes().asInstanceOf[DataTypeMismatch]
+ assert(r1.errorSubClass == "UNEXPECTED_INPUT_TYPE")
+
+ // ts/origin type mismatch: TIMESTAMP_NTZ(p) ts vs TIMESTAMP_NTZ origin (micro)
+ val micronOrigin = Literal(LocalDateTime.of(1970, 1, 1, 0, 0, 0))
+ val expr2 = TimeBucket(hour, ntzTsLit, micronOrigin)
+ val r2 = expr2.checkInputDataTypes().asInstanceOf[DataTypeMismatch]
+ assert(r2.errorSubClass == "UNEXPECTED_INPUT_TYPE")
+
+ // Matching nano types succeed
+ val expr3 = TimeBucket(hour, ntzTsLit, ntzTsLit)
+ assert(expr3.checkInputDataTypes().isSuccess)
+ }
+
+ test("time_bucket: ExpressionBuilder with nanosecond timestamps") {
+ withSQLConf(
+ SQLConf.SESSION_LOCAL_TIMEZONE.key -> "UTC",
+ SQLConf.TIMESTAMP_NANOS_TYPES_ENABLED.key -> "true") {
+ val hour = Literal(Duration.ofHours(1))
+ val tsNtzNanos =
+ Literal.create(TimestampNanosVal.fromParts(123L, 456), TimestampNTZNanosType(9))
+ val tsLtzNanos =
+ Literal.create(TimestampNanosVal.fromParts(123L, 456), TimestampLTZNanosType(9))
+
+ // 2-arg with TIMESTAMP_NTZ(p) ts: default origin is epoch, same type, remainder 0.
+ val builtNtz = TimeBucketExpressionBuilder.build("time_bucket", Seq(hour, tsNtzNanos))
+ .asInstanceOf[TimeBucket]
+ assert(builtNtz.originTs == Literal(TimestampNanosVal.ZERO, TimestampNTZNanosType(9)))
+
+ // 2-arg with TIMESTAMP_LTZ(p) ts: default origin is local-midnight epoch instant.
+ val builtLtz = TimeBucketExpressionBuilder.build("time_bucket", Seq(hour, tsLtzNanos))
+ .asInstanceOf[TimeBucket]
+ assert(builtLtz.originTs == Literal(
+ TimestampNanosVal.fromParts(DateTimeUtils.daysToMicros(0, UTC), 0.toShort),
+ TimestampLTZNanosType(9)))
+
+ // NULL ts + TIMESTAMP_NTZ(p) origin: ts retyped to match origin's nano type.
+ val builtRetyped = TimeBucketExpressionBuilder.build(
+ "time_bucket", Seq(hour, Literal(null, NullType), tsNtzNanos))
+ .asInstanceOf[TimeBucket]
+ assert(builtRetyped.ts.dataType == TimestampNTZNanosType(9))
+ }
+ }
+
test("time_bucket: ExpressionBuilder") {
// Pin session zone to UTC so the LTZ default origin resolves to 0L. The non-UTC case
// is covered by the dedicated test below.
diff --git a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/util/DateTimeUtilsSuite.scala b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/util/DateTimeUtilsSuite.scala
index c46daf803598..5b71273e92e5 100644
--- a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/util/DateTimeUtilsSuite.scala
+++ b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/util/DateTimeUtilsSuite.scala
@@ -32,6 +32,7 @@ import org.apache.spark.sql.catalyst.util.DateTimeConstants._
import org.apache.spark.sql.catalyst.util.DateTimeTestUtils._
import org.apache.spark.sql.catalyst.util.DateTimeUtils._
import org.apache.spark.sql.catalyst.util.RebaseDateTime.rebaseJulianToGregorianMicros
+import org.apache.spark.sql.catalyst.util.TimestampNanosTestUtils.nanosVal
import org.apache.spark.sql.errors.DataTypeErrors.toSQLConf
import org.apache.spark.sql.internal.SqlApiConf
import org.apache.spark.sql.types.{Decimal, TimeType}
@@ -2236,6 +2237,77 @@ class DateTimeUtilsSuite extends SparkFunSuite with Matchers with SQLHelper {
}
}
+ test("timeBucketDTIntervalNanos") {
+ val utc = ZoneOffset.UTC
+ // Micro-parity: zero remainder on both ts and origin matches the microsecond function.
+ assert(
+ timeBucketDTIntervalNanos(
+ 15 * MICROS_PER_MINUTE,
+ nanosVal(date(2024, 1, 1, 11, 27, 0), 0),
+ nanosVal(0L, 0),
+ utc)
+ === nanosVal(
+ timeBucketDTInterval(15 * MICROS_PER_MINUTE, date(2024, 1, 1, 11, 27, 0), 0L, utc), 0))
+ // Boundary case: ts lands on the same microsecond as a bucket grid point, but with a
+ // smaller sub-micro remainder than the origin's -- ts's true instant is just before the
+ // grid point, so it belongs to the previous bucket.
+ assert(
+ timeBucketDTIntervalNanos(
+ MICROS_PER_SECOND,
+ nanosVal(3000000L, 200),
+ nanosVal(0L, 500),
+ utc)
+ === nanosVal(2000000L, 500))
+ // Same microsecond, but ts's remainder is >= origin's -- ts's true instant is at or after
+ // the grid point, so it starts the bucket there.
+ assert(
+ timeBucketDTIntervalNanos(
+ MICROS_PER_SECOND,
+ nanosVal(3000000L, 700),
+ nanosVal(0L, 500),
+ utc)
+ === nanosVal(3000000L, 500))
+ // Non-boundary case: ts's microsecond doesn't match any grid point's microsecond, so the
+ // sub-micro remainder never comes into play; result carries origin's remainder.
+ assert(
+ timeBucketDTIntervalNanos(
+ MICROS_PER_SECOND,
+ nanosVal(3500000L, 999),
+ nanosVal(0L, 500),
+ utc)
+ === nanosVal(3000000L, 500))
+ // Calendar-aware (multi-day) path also preserves origin's sub-micro remainder.
+ assert(
+ timeBucketDTIntervalNanos(
+ MICROS_PER_DAY,
+ nanosVal(date(2024, 1, 4, 0, 0, 0, 500), 999),
+ nanosVal(500, 250),
+ utc)
+ === nanosVal(date(2024, 1, 4, 0, 0, 0, 500), 250))
+ }
+
+ test("timeBucketYMIntervalNanos") {
+ val utc = ZoneOffset.UTC
+ // Micro-parity: zero remainder on both ts and origin matches the microsecond function.
+ assert(
+ timeBucketYMIntervalNanos(
+ 1,
+ nanosVal(date(2024, 3, 15, 11, 27, 0), 0),
+ nanosVal(0L, 0),
+ utc)
+ === nanosVal(timeBucketYMInterval(1, date(2024, 3, 15, 11, 27, 0), 0L, utc), 0))
+ // Boundary case: ts lands exactly on the microsecond of a monthly grid point, but with a
+ // smaller sub-micro remainder than the origin's -- steps back to the previous bucket.
+ val marchFirst = date(2024, 3, 1, 0, 0, 0)
+ assert(
+ timeBucketYMIntervalNanos(1, nanosVal(marchFirst, 100), nanosVal(0L, 500), utc)
+ === nanosVal(date(2024, 2, 1, 0, 0, 0), 500))
+ // Same microsecond, but ts's remainder is >= origin's -- stays in the bucket starting there.
+ assert(
+ timeBucketYMIntervalNanos(1, nanosVal(marchFirst, 700), nanosVal(0L, 500), utc)
+ === nanosVal(marchFirst, 500))
+ }
+
test("SPARK-57033: java.time.LocalDateTime <-> TimestampNanosVal roundtrip") {
val cases = Seq(
LocalDateTime.parse("0001-01-01T00:00:00"),
diff --git a/sql/core/src/test/resources/sql-tests/analyzer-results/time-bucket.sql.out b/sql/core/src/test/resources/sql-tests/analyzer-results/time-bucket.sql.out
index 31eccd02d824..2bc6cc2be0f1 100644
--- a/sql/core/src/test/resources/sql-tests/analyzer-results/time-bucket.sql.out
+++ b/sql/core/src/test/resources/sql-tests/analyzer-results/time-bucket.sql.out
@@ -318,7 +318,7 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
"inputSql" : "\"DATE '2024-01-15'\"",
"inputType" : "\"DATE\"",
"paramIndex" : "second",
- "requiredType" : "\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\"",
+ "requiredType" : "(\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\" or \"(TIMESTAMP_LTZ(P) OR TIMESTAMP_NTZ(P) WITH P IN [7, 9])\")",
"sqlExpr" : "\"time_bucket(INTERVAL '15' MINUTE, DATE '2024-01-15', TIMESTAMP '1970-01-01 00:00:00')\""
},
"queryContext" : [ {
@@ -342,7 +342,7 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
"inputSql" : "\"2024-01-15 10:23:00\"",
"inputType" : "\"STRING\"",
"paramIndex" : "second",
- "requiredType" : "\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\"",
+ "requiredType" : "(\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\" or \"(TIMESTAMP_LTZ(P) OR TIMESTAMP_NTZ(P) WITH P IN [7, 9])\")",
"sqlExpr" : "\"time_bucket(INTERVAL '15' MINUTE, 2024-01-15 10:23:00, TIMESTAMP '1970-01-01 00:00:00')\""
},
"queryContext" : [ {
@@ -366,7 +366,7 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
"inputSql" : "\"DATE '2024-01-01'\"",
"inputType" : "\"DATE\"",
"paramIndex" : "third",
- "requiredType" : "\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\"",
+ "requiredType" : "(\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\" or \"(TIMESTAMP_LTZ(P) OR TIMESTAMP_NTZ(P) WITH P IN [7, 9])\")",
"sqlExpr" : "\"time_bucket(INTERVAL '15' MINUTE, TIMESTAMP '2024-01-15 10:23:00', DATE '2024-01-01')\""
},
"queryContext" : [ {
@@ -390,7 +390,7 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
"inputSql" : "\"2024-01-01 00:00:00\"",
"inputType" : "\"STRING\"",
"paramIndex" : "third",
- "requiredType" : "\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\"",
+ "requiredType" : "(\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\" or \"(TIMESTAMP_LTZ(P) OR TIMESTAMP_NTZ(P) WITH P IN [7, 9])\")",
"sqlExpr" : "\"time_bucket(INTERVAL '15' MINUTE, TIMESTAMP '2024-01-15 10:23:00', 2024-01-01 00:00:00)\""
},
"queryContext" : [ {
@@ -870,6 +870,90 @@ SELECT t, time_bucket(INTERVAL '1' MONTH, t) AS bucket
[Analyzer test output redacted due to nondeterminism]
+-- !query
+SELECT time_bucket(INTERVAL '1' SECOND, CAST('2024-06-20 10:00:00.123456789' AS timestamp_ntz(9)))
+-- !query analysis
+Project [time_bucket(INTERVAL '01' SECOND, cast(2024-06-20 10:00:00.123456789 as timestamp_ntz(9)), 1970-01-01 00:00:00, Some(UTC)) AS time_bucket(INTERVAL '01' SECOND, CAST(2024-06-20 10:00:00.123456789 AS TIMESTAMP_NTZ(9)), TIMESTAMP_NTZ '1970-01-01 00:00:00.000000000')#x]
++- OneRowRelation
+
+
+-- !query
+SELECT time_bucket(INTERVAL '1' MONTH, CAST('2024-06-20 10:00:00.123456789' AS timestamp_ltz(9)))
+-- !query analysis
+Project [time_bucket(INTERVAL '1' MONTH, cast(2024-06-20 10:00:00.123456789 as timestamp_ltz(9)), 1969-12-31 16:00:00, Some(UTC)) AS time_bucket(INTERVAL '1' MONTH, CAST(2024-06-20 10:00:00.123456789 AS TIMESTAMP_LTZ(9)), TIMESTAMP_LTZ '1970-01-01 00:00:00.000000000')#x]
++- OneRowRelation
+
+
+-- !query
+SELECT time_bucket(
+ INTERVAL '1' SECOND,
+ CAST('2024-06-20 10:00:03.500000999' AS timestamp_ntz(9)),
+ CAST('1970-01-01 00:00:00.000000500' AS timestamp_ntz(9)))
+-- !query analysis
+Project [time_bucket(INTERVAL '01' SECOND, cast(2024-06-20 10:00:03.500000999 as timestamp_ntz(9)), cast(1970-01-01 00:00:00.000000500 as timestamp_ntz(9)), Some(UTC)) AS time_bucket(INTERVAL '01' SECOND, CAST(2024-06-20 10:00:03.500000999 AS TIMESTAMP_NTZ(9)), CAST(1970-01-01 00:00:00.000000500 AS TIMESTAMP_NTZ(9)))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT time_bucket(
+ INTERVAL '1' SECOND,
+ CAST('2024-06-20 10:00:04.000000100' AS timestamp_ntz(9)),
+ CAST('1970-01-01 00:00:00.000000500' AS timestamp_ntz(9)))
+-- !query analysis
+Project [time_bucket(INTERVAL '01' SECOND, cast(2024-06-20 10:00:04.000000100 as timestamp_ntz(9)), cast(1970-01-01 00:00:00.000000500 as timestamp_ntz(9)), Some(UTC)) AS time_bucket(INTERVAL '01' SECOND, CAST(2024-06-20 10:00:04.000000100 AS TIMESTAMP_NTZ(9)), CAST(1970-01-01 00:00:00.000000500 AS TIMESTAMP_NTZ(9)))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT time_bucket(
+ INTERVAL '1' SECOND,
+ CAST('2024-06-20 10:00:04.000000700' AS timestamp_ntz(9)),
+ CAST('1970-01-01 00:00:00.000000500' AS timestamp_ntz(9)))
+-- !query analysis
+Project [time_bucket(INTERVAL '01' SECOND, cast(2024-06-20 10:00:04.000000700 as timestamp_ntz(9)), cast(1970-01-01 00:00:00.000000500 as timestamp_ntz(9)), Some(UTC)) AS time_bucket(INTERVAL '01' SECOND, CAST(2024-06-20 10:00:04.000000700 AS TIMESTAMP_NTZ(9)), CAST(1970-01-01 00:00:00.000000500 AS TIMESTAMP_NTZ(9)))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT time_bucket(
+ INTERVAL '1' HOUR,
+ CAST('2024-01-01 11:27:00.123456789' AS timestamp_ntz(9)),
+ TIMESTAMP_NTZ '2024-01-01 00:00:00')
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
+ "sqlState" : "42K09",
+ "messageParameters" : {
+ "inputSql" : "\"TIMESTAMP_NTZ '2024-01-01 00:00:00'\"",
+ "inputType" : "\"TIMESTAMP_NTZ\"",
+ "paramIndex" : "third",
+ "requiredType" : "\"TIMESTAMP_NTZ(9)\"",
+ "sqlExpr" : "\"time_bucket(INTERVAL '01' HOUR, CAST(2024-01-01 11:27:00.123456789 AS TIMESTAMP_NTZ(9)), TIMESTAMP_NTZ '2024-01-01 00:00:00')\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 140,
+ "fragment" : "time_bucket(\n INTERVAL '1' HOUR,\n CAST('2024-01-01 11:27:00.123456789' AS timestamp_ntz(9)),\n TIMESTAMP_NTZ '2024-01-01 00:00:00')"
+ } ]
+}
+
+
+-- !query
+SELECT t, time_bucket(INTERVAL '1' HOUR, t) AS bucket
+ FROM VALUES
+ (CAST('2024-01-15 10:23:00.123456789' AS timestamp_ltz(9))),
+ (CAST('2024-01-15 14:45:00.987654321' AS timestamp_ltz(9))) tab(t)
+ ORDER BY t
+-- !query analysis
+Sort [t#x ASC NULLS FIRST], true
++- Project [t#x, time_bucket(INTERVAL '01' HOUR, t#x, 1969-12-31 16:00:00, Some(UTC)) AS bucket#x]
+ +- SubqueryAlias tab
+ +- LocalRelation [t#x]
+
+
-- !query
SET TIME ZONE 'America/Los_Angeles'
-- !query analysis
diff --git a/sql/core/src/test/resources/sql-tests/inputs/time-bucket.sql b/sql/core/src/test/resources/sql-tests/inputs/time-bucket.sql
index b012fd5330ef..d13ad227a0ea 100644
--- a/sql/core/src/test/resources/sql-tests/inputs/time-bucket.sql
+++ b/sql/core/src/test/resources/sql-tests/inputs/time-bucket.sql
@@ -1,5 +1,7 @@
-- time_bucket function tests
+--SET spark.sql.timestampNanosTypes.enabled=true
+
-- Pin session timezone to UTC. With UTC as the session zone, TIMESTAMP (LTZ)
-- bucketing produces the same results as TIMESTAMP_NTZ. The session-zone-aware
-- behavior is exercised in a dedicated `SET TIME ZONE 'America/Los_Angeles'`
@@ -216,6 +218,52 @@ SELECT t, time_bucket(INTERVAL '1' MONTH, t) AS bucket
ORDER BY t;
+-- Nanosecond-precision timestamps (SPARK-57820): TIMESTAMP_NTZ(p) / TIMESTAMP_LTZ(p),
+-- p in [7, 9]. Bucket boundaries fall on whole microseconds, so the result's sub-microsecond
+-- digits always equal the origin's; only placement of `ts` needs nanosecond resolution.
+
+-- DayTimeInterval bucket, default (epoch, zero sub-micro remainder) origin: result has no
+-- fractional digits left (all-zero fraction renders as none).
+SELECT time_bucket(INTERVAL '1' SECOND, CAST('2024-06-20 10:00:00.123456789' AS timestamp_ntz(9)));
+
+-- YearMonthInterval bucket, default origin, TIMESTAMP_LTZ(p).
+SELECT time_bucket(INTERVAL '1' MONTH, CAST('2024-06-20 10:00:00.123456789' AS timestamp_ltz(9)));
+
+-- Custom origin with a nonzero sub-micro remainder: ts's own microsecond doesn't match any
+-- grid point's, so the remainder never comes into play and the result carries origin's.
+SELECT time_bucket(
+ INTERVAL '1' SECOND,
+ CAST('2024-06-20 10:00:03.500000999' AS timestamp_ntz(9)),
+ CAST('1970-01-01 00:00:00.000000500' AS timestamp_ntz(9)));
+
+-- Boundary case: ts lands on the same microsecond as a grid point, but with a smaller
+-- sub-micro remainder than the origin's -- steps back to the previous bucket.
+SELECT time_bucket(
+ INTERVAL '1' SECOND,
+ CAST('2024-06-20 10:00:04.000000100' AS timestamp_ntz(9)),
+ CAST('1970-01-01 00:00:00.000000500' AS timestamp_ntz(9)));
+
+-- Same microsecond, ts's remainder >= origin's: stays in the bucket starting there.
+SELECT time_bucket(
+ INTERVAL '1' SECOND,
+ CAST('2024-06-20 10:00:04.000000700' AS timestamp_ntz(9)),
+ CAST('1970-01-01 00:00:00.000000500' AS timestamp_ntz(9)));
+
+-- ts and origin must be the same TIMESTAMP flavor: TIMESTAMP_NTZ(p) vs microsecond
+-- TIMESTAMP_NTZ is rejected, same as the microsecond LTZ/NTZ mismatch above.
+SELECT time_bucket(
+ INTERVAL '1' HOUR,
+ CAST('2024-01-01 11:27:00.123456789' AS timestamp_ntz(9)),
+ TIMESTAMP_NTZ '2024-01-01 00:00:00');
+
+-- Column reference as ts, TIMESTAMP_LTZ(p).
+SELECT t, time_bucket(INTERVAL '1' HOUR, t) AS bucket
+ FROM VALUES
+ (CAST('2024-01-15 10:23:00.123456789' AS timestamp_ltz(9))),
+ (CAST('2024-01-15 14:45:00.987654321' AS timestamp_ltz(9))) tab(t)
+ ORDER BY t;
+
+
-- Bucketing in a non-UTC session: TIMESTAMP (LTZ) values bucket in the session time
-- zone, so monthly and daily boundaries land at local calendar boundaries across DST
-- (America/Los_Angeles springs forward 2024-03-10 and falls back 2024-11-03). NTZ
diff --git a/sql/core/src/test/resources/sql-tests/results/time-bucket.sql.out b/sql/core/src/test/resources/sql-tests/results/time-bucket.sql.out
index e1f79ff43e4b..7e59efc9c1e0 100644
--- a/sql/core/src/test/resources/sql-tests/results/time-bucket.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/time-bucket.sql.out
@@ -348,7 +348,7 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
"inputSql" : "\"DATE '2024-01-15'\"",
"inputType" : "\"DATE\"",
"paramIndex" : "second",
- "requiredType" : "\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\"",
+ "requiredType" : "(\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\" or \"(TIMESTAMP_LTZ(P) OR TIMESTAMP_NTZ(P) WITH P IN [7, 9])\")",
"sqlExpr" : "\"time_bucket(INTERVAL '15' MINUTE, DATE '2024-01-15', TIMESTAMP '1970-01-01 00:00:00')\""
},
"queryContext" : [ {
@@ -374,7 +374,7 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
"inputSql" : "\"2024-01-15 10:23:00\"",
"inputType" : "\"STRING\"",
"paramIndex" : "second",
- "requiredType" : "\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\"",
+ "requiredType" : "(\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\" or \"(TIMESTAMP_LTZ(P) OR TIMESTAMP_NTZ(P) WITH P IN [7, 9])\")",
"sqlExpr" : "\"time_bucket(INTERVAL '15' MINUTE, 2024-01-15 10:23:00, TIMESTAMP '1970-01-01 00:00:00')\""
},
"queryContext" : [ {
@@ -400,7 +400,7 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
"inputSql" : "\"DATE '2024-01-01'\"",
"inputType" : "\"DATE\"",
"paramIndex" : "third",
- "requiredType" : "\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\"",
+ "requiredType" : "(\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\" or \"(TIMESTAMP_LTZ(P) OR TIMESTAMP_NTZ(P) WITH P IN [7, 9])\")",
"sqlExpr" : "\"time_bucket(INTERVAL '15' MINUTE, TIMESTAMP '2024-01-15 10:23:00', DATE '2024-01-01')\""
},
"queryContext" : [ {
@@ -426,7 +426,7 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
"inputSql" : "\"2024-01-01 00:00:00\"",
"inputType" : "\"STRING\"",
"paramIndex" : "third",
- "requiredType" : "\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\"",
+ "requiredType" : "(\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\" or \"(TIMESTAMP_LTZ(P) OR TIMESTAMP_NTZ(P) WITH P IN [7, 9])\")",
"sqlExpr" : "\"time_bucket(INTERVAL '15' MINUTE, TIMESTAMP '2024-01-15 10:23:00', 2024-01-01 00:00:00)\""
},
"queryContext" : [ {
@@ -1002,6 +1002,97 @@ struct<t:timestamp,bucket:timestamp>
2024-06-01 00:00:00 2024-06-01 00:00:00
+-- !query
+SELECT time_bucket(INTERVAL '1' SECOND, CAST('2024-06-20 10:00:00.123456789' AS timestamp_ntz(9)))
+-- !query schema
+struct<time_bucket(INTERVAL '01' SECOND, CAST(2024-06-20 10:00:00.123456789 AS TIMESTAMP_NTZ(9)), TIMESTAMP_NTZ '1970-01-01 00:00:00.000000000'):timestamp_ntz(9)>
+-- !query output
+2024-06-20 10:00:00
+
+
+-- !query
+SELECT time_bucket(INTERVAL '1' MONTH, CAST('2024-06-20 10:00:00.123456789' AS timestamp_ltz(9)))
+-- !query schema
+struct<time_bucket(INTERVAL '1' MONTH, CAST(2024-06-20 10:00:00.123456789 AS TIMESTAMP_LTZ(9)), TIMESTAMP_LTZ '1970-01-01 00:00:00.000000000'):timestamp_ltz(9)>
+-- !query output
+2024-06-01 00:00:00
+
+
+-- !query
+SELECT time_bucket(
+ INTERVAL '1' SECOND,
+ CAST('2024-06-20 10:00:03.500000999' AS timestamp_ntz(9)),
+ CAST('1970-01-01 00:00:00.000000500' AS timestamp_ntz(9)))
+-- !query schema
+struct<time_bucket(INTERVAL '01' SECOND, CAST(2024-06-20 10:00:03.500000999 AS TIMESTAMP_NTZ(9)), CAST(1970-01-01 00:00:00.000000500 AS TIMESTAMP_NTZ(9))):timestamp_ntz(9)>
+-- !query output
+2024-06-20 10:00:03.0000005
+
+
+-- !query
+SELECT time_bucket(
+ INTERVAL '1' SECOND,
+ CAST('2024-06-20 10:00:04.000000100' AS timestamp_ntz(9)),
+ CAST('1970-01-01 00:00:00.000000500' AS timestamp_ntz(9)))
+-- !query schema
+struct<time_bucket(INTERVAL '01' SECOND, CAST(2024-06-20 10:00:04.000000100 AS TIMESTAMP_NTZ(9)), CAST(1970-01-01 00:00:00.000000500 AS TIMESTAMP_NTZ(9))):timestamp_ntz(9)>
+-- !query output
+2024-06-20 10:00:03.0000005
+
+
+-- !query
+SELECT time_bucket(
+ INTERVAL '1' SECOND,
+ CAST('2024-06-20 10:00:04.000000700' AS timestamp_ntz(9)),
+ CAST('1970-01-01 00:00:00.000000500' AS timestamp_ntz(9)))
+-- !query schema
+struct<time_bucket(INTERVAL '01' SECOND, CAST(2024-06-20 10:00:04.000000700 AS TIMESTAMP_NTZ(9)), CAST(1970-01-01 00:00:00.000000500 AS TIMESTAMP_NTZ(9))):timestamp_ntz(9)>
+-- !query output
+2024-06-20 10:00:04.0000005
+
+
+-- !query
+SELECT time_bucket(
+ INTERVAL '1' HOUR,
+ CAST('2024-01-01 11:27:00.123456789' AS timestamp_ntz(9)),
+ TIMESTAMP_NTZ '2024-01-01 00:00:00')
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
+ "sqlState" : "42K09",
+ "messageParameters" : {
+ "inputSql" : "\"TIMESTAMP_NTZ '2024-01-01 00:00:00'\"",
+ "inputType" : "\"TIMESTAMP_NTZ\"",
+ "paramIndex" : "third",
+ "requiredType" : "\"TIMESTAMP_NTZ(9)\"",
+ "sqlExpr" : "\"time_bucket(INTERVAL '01' HOUR, CAST(2024-01-01 11:27:00.123456789 AS TIMESTAMP_NTZ(9)), TIMESTAMP_NTZ '2024-01-01 00:00:00')\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 140,
+ "fragment" : "time_bucket(\n INTERVAL '1' HOUR,\n CAST('2024-01-01 11:27:00.123456789' AS timestamp_ntz(9)),\n TIMESTAMP_NTZ '2024-01-01 00:00:00')"
+ } ]
+}
+
+
+-- !query
+SELECT t, time_bucket(INTERVAL '1' HOUR, t) AS bucket
+ FROM VALUES
+ (CAST('2024-01-15 10:23:00.123456789' AS timestamp_ltz(9))),
+ (CAST('2024-01-15 14:45:00.987654321' AS timestamp_ltz(9))) tab(t)
+ ORDER BY t
+-- !query schema
+struct<t:timestamp_ltz(9),bucket:timestamp_ltz(9)>
+-- !query output
+2024-01-15 10:23:00.123456789 2024-01-15 10:00:00
+2024-01-15 14:45:00.987654321 2024-01-15 14:00:00
+
+
-- !query
SET TIME ZONE 'America/Los_Angeles'
-- !query schema
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@spark.apache.org
For additional commands, e-mail: commits-help@spark.apache.org