This is an automated email from the ASF dual-hosted git repository.
cloud-fan 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 a12be2798596 [SPARK-57821][SQL] Support nanosecond-precision
timestamps in date_trunc
a12be2798596 is described below
commit a12be2798596dee18370ad495b94f94d600fd02a
Author: Stevo Mitric <[email protected]>
AuthorDate: Thu Jul 16 21:59:44 2026 +0800
[SPARK-57821][SQL] Support nanosecond-precision timestamps in date_trunc
### What changes were proposed in this pull request?
Extend `date_trunc` (`TruncTimestamp`) to accept nanosecond-precision
timestamps (`TIMESTAMP_NTZ(p)`/`TIMESTAMP_LTZ(p)`, p in [7, 9]) in addition to
microsecond `TimestampType`. The result keeps the input's nanos type/family;
truncation floors `epochMicros` (NTZ at UTC, LTZ in the session zone) and
always zeroes the sub-microsecond remainder, since `date_trunc`'s finest unit
is MICROSECOND. Adds `DateTimeUtils.truncTimestampNanos` delegating to the
existing `truncTimestamp`.
### Why are the changes needed?
`date_trunc` previously narrowed a nanosecond argument to microseconds,
dropping the sub-microsecond digits and forcing an LTZ result even for NTZ
input.
### Does this PR introduce _any_ user-facing change?
Yes. `date_trunc(fmt, ts_nanos)` now returns the same nanosecond
type/family with correct truncation (preview
`spark.sql.timestampNanosTypes.enabled`).
### How was this patch tested?
`DateExpressionsSuite` and `timestamp-{ltz,ntz}-nanos.sql` golden files.
### Was this patch authored or co-authored using generative AI tooling?
Generated-by: Claude Code
Closes #57224 from stevomitric/stevomitric/spark-57821-nanos.
Authored-by: Stevo Mitric <[email protected]>
Signed-off-by: Wenchen Fan <[email protected]>
(cherry picked from commit f6a83dd22e3e436dd46c9166d8ea372167248ee5)
Signed-off-by: Wenchen Fan <[email protected]>
---
.../catalyst/expressions/datetimeExpressions.scala | 36 ++++++++++---
.../spark/sql/catalyst/util/DateTimeUtils.scala | 15 ++++++
.../expressions/DateExpressionsSuite.scala | 63 ++++++++++++++++++++++
.../analyzer-results/timestamp-ltz-nanos.sql.out | 35 ++++++++++++
.../analyzer-results/timestamp-ntz-nanos.sql.out | 35 ++++++++++++
.../sql-tests/inputs/timestamp-ltz-nanos.sql | 11 ++++
.../sql-tests/inputs/timestamp-ntz-nanos.sql | 10 ++++
.../sql-tests/results/timestamp-ltz-nanos.sql.out | 40 ++++++++++++++
.../sql-tests/results/timestamp-ntz-nanos.sql.out | 40 ++++++++++++++
9 files changed, 278 insertions(+), 7 deletions(-)
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 291051d481c1..98742e4ec810 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
@@ -2916,7 +2916,7 @@ case class TruncDate(date: Expression, format: Expression)
/**
* Returns timestamp truncated to the unit specified by the format.
*/
-// scalastyle:off line.size.limit
+// scalastyle:off line.size.limit line.contains.tab
@ExpressionDescription(
usage = """
_FUNC_(fmt, ts) - Returns timestamp `ts` truncated to the unit specified
by the format model `fmt`.
@@ -2948,10 +2948,14 @@ case class TruncDate(date: Expression, format:
Expression)
2015-03-05 09:00:00
> SELECT _FUNC_('MILLISECOND', '2015-03-05T09:32:05.123456');
2015-03-05 09:32:05.123
+ > SET spark.sql.timestampNanosTypes.enabled=true;
+ spark.sql.timestampNanosTypes.enabled true
+ > SELECT _FUNC_('MICROSECOND', TIMESTAMP_NTZ '2015-03-05
09:32:05.123456789');
+ 2015-03-05 09:32:05.123456
""",
group = "datetime_funcs",
since = "2.3.0")
-// scalastyle:on line.size.limit
+// scalastyle:on line.size.limit line.contains.tab
case class TruncTimestamp(
format: Expression,
timestamp: Expression,
@@ -2961,26 +2965,44 @@ case class TruncTimestamp(
override def right: Expression = timestamp
override def inputTypes: Seq[AbstractDataType] =
- Seq(StringTypeWithCollation(supportsTrimCollation = true), TimestampType)
- override def dataType: TimestampType = TimestampType
+ Seq(
+ StringTypeWithCollation(supportsTrimCollation = true),
+ TypeCollection(TimestampType, AnyTimestampNanoType))
+ override def dataType: DataType = timestamp.dataType match {
+ case _: AnyTimestampNanoType => timestamp.dataType
+ case _ => TimestampType
+ }
override def prettyName: String = "date_trunc"
override val instant = timestamp
override def withTimeZone(timeZoneId: String): TimeZoneAwareExpression =
copy(timeZoneId = Option(timeZoneId))
+ // TIMESTAMP_NTZ(p) carries wall-clock fields (evaluated at UTC);
TIMESTAMP_LTZ(p) and the
+ // microsecond TimestampType use the session zone. For micro NTZ the
analyzer casts to LTZ
+ // micro before eval, so this reduces to `zoneId`.
+ @transient private lazy val zoneIdInEval: ZoneId =
zoneIdForType(timestamp.dataType)
+
def this(format: Expression, timestamp: Expression) = this(format,
timestamp, None)
override def eval(input: InternalRow): Any = {
evalHelper(input, minLevel = MIN_LEVEL_OF_TIMESTAMP_TRUNC) { (t: Any,
level: Int) =>
- DateTimeUtils.truncTimestamp(t.asInstanceOf[Long], level, zoneId)
+ timestamp.dataType match {
+ case _: AnyTimestampNanoType =>
+ DateTimeUtils.truncTimestampNanos(t.asInstanceOf[TimestampNanosVal],
level, zoneIdInEval)
+ case _ =>
+ DateTimeUtils.truncTimestamp(t.asInstanceOf[Long], level,
zoneIdInEval)
+ }
}
}
override def doGenCode(ctx: CodegenContext, ev: ExprCode): ExprCode = {
- val zid = ctx.addReferenceObj("zoneId", zoneId, classOf[ZoneId].getName)
+ val zid = ctx.addReferenceObj("zoneId", zoneIdInEval,
classOf[ZoneId].getName)
codeGenHelper(ctx, ev, minLevel = MIN_LEVEL_OF_TIMESTAMP_TRUNC, true) {
(date: String, fmt: String) =>
- s"truncTimestamp($date, $fmt, $zid);"
+ timestamp.dataType match {
+ case _: AnyTimestampNanoType => s"truncTimestampNanos($date, $fmt,
$zid);"
+ case _ => s"truncTimestamp($date, $fmt, $zid);"
+ }
}
}
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 b1bf6526c181..2fe2e5f4c988 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
@@ -645,6 +645,21 @@ object DateTimeUtils extends SparkDateTimeUtils {
}
}
+ /**
+ * Truncates a nanosecond-precision timestamp to the unit given by `level`.
`epochMicros` is
+ * truncated with the same [[truncTimestamp]] used by microsecond
timestamps, and the
+ * sub-microsecond `nanosWithinMicro` is always dropped: `date_trunc`'s
finest supported unit
+ * is MICROSECOND (see `MIN_LEVEL_OF_TIMESTAMP_TRUNC`), which already
discards everything below
+ * a microsecond. NTZ vs. LTZ zone handling is the caller's responsibility
via `zoneId`.
+ */
+ def truncTimestampNanos(
+ value: TimestampNanosVal,
+ level: Int,
+ zoneId: ZoneId): TimestampNanosVal = {
+ val truncatedMicros = truncTimestamp(value.epochMicros, level, zoneId)
+ TimestampNanosVal.fromParts(truncatedMicros, 0.toShort)
+ }
+
/**
* Returns time truncated to the unit specified by the level.
*/
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 a9ee2a8ba476..690256156069 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
@@ -911,6 +911,69 @@ class DateExpressionsSuite extends SparkFunSuite with
ExpressionEvalHelper {
}
}
+ test("SPARK-57821: date_trunc on nanosecond timestamps") {
+ withDefaultTimeZone(UTC) {
+ Seq(7, 8, 9).foreach { p =>
+ // Full 9-digit fraction floored to precision p by the carrier
builders.
+ val ntz = DateTimeUtils.localDateTimeToTimestampNanos(
+ LocalDateTime.parse("2015-07-22T05:30:06.123456789"), precision = p)
+ val ltz = DateTimeUtils.instantToTimestampNanos(
+ Instant.parse("2015-07-22T05:30:06.123456789Z"), precision = p)
+
+ def ntzExpected(s: String): TimestampNanosVal =
+ DateTimeUtils.localDateTimeToTimestampNanos(LocalDateTime.parse(s),
precision = p)
+ def ltzExpected(s: String): TimestampNanosVal =
+ DateTimeUtils.instantToTimestampNanos(Instant.parse(s), precision =
p)
+
+ // (fmt, NTZ-expected-localdatetime, LTZ-expected-instant). Every unit
zeroes the
+ // whole fraction (incl. nanosWithinMicro); MICROSECOND keeps
epochMicros only.
+ val cases = Seq(
+ ("YEAR", "2015-01-01T00:00:00", "2015-01-01T00:00:00Z"),
+ ("QUARTER", "2015-07-01T00:00:00", "2015-07-01T00:00:00Z"),
+ ("MONTH", "2015-07-01T00:00:00", "2015-07-01T00:00:00Z"),
+ ("WEEK", "2015-07-20T00:00:00", "2015-07-20T00:00:00Z"),
+ ("DAY", "2015-07-22T00:00:00", "2015-07-22T00:00:00Z"),
+ ("HOUR", "2015-07-22T05:00:00", "2015-07-22T05:00:00Z"),
+ ("MINUTE", "2015-07-22T05:30:00", "2015-07-22T05:30:00Z"),
+ ("SECOND", "2015-07-22T05:30:06", "2015-07-22T05:30:06Z"),
+ ("MILLISECOND", "2015-07-22T05:30:06.123",
"2015-07-22T05:30:06.123Z"),
+ ("MICROSECOND", "2015-07-22T05:30:06.123456",
"2015-07-22T05:30:06.123456Z"))
+
+ cases.foreach { case (fmt, ntzExp, ltzExp) =>
+ checkEvaluation(
+ TruncTimestamp(Literal.create(fmt, StringType),
+ Literal.create(ntz, TimestampNTZNanosType(p)), UTC_OPT),
+ ntzExpected(ntzExp))
+ checkEvaluation(
+ TruncTimestamp(Literal.create(fmt, StringType),
+ Literal.create(ltz, TimestampLTZNanosType(p)), UTC_OPT),
+ ltzExpected(ltzExp))
+ // nanosWithinMicro is always zeroed.
+ assert(ntzExpected(ntzExp).nanosWithinMicro == 0)
+ }
+
+ // NTZ stays NTZ, LTZ stays LTZ.
+ assert(TruncTimestamp(Literal("DAY"), Literal.create(ntz,
TimestampNTZNanosType(p)),
+ UTC_OPT).dataType == TimestampNTZNanosType(p))
+ assert(TruncTimestamp(Literal("DAY"), Literal.create(ltz,
TimestampLTZNanosType(p)),
+ UTC_OPT).dataType == TimestampLTZNanosType(p))
+
+ // Unsupported / null format => null.
+ checkEvaluation(
+ TruncTimestamp(Literal.create("INVALID", StringType),
+ Literal.create(ntz, TimestampNTZNanosType(p)), UTC_OPT), null)
+
+ // eval / codegen parity.
+ checkConsistencyBetweenInterpretedAndCodegen(
+ (fmt: Expression, ts: Expression) => TruncTimestamp(fmt, ts,
UTC_OPT),
+ StringType, TimestampNTZNanosType(p))
+ checkConsistencyBetweenInterpretedAndCodegen(
+ (fmt: Expression, ts: Expression) => TruncTimestamp(fmt, ts,
UTC_OPT),
+ StringType, TimestampLTZNanosType(p))
+ }
+ }
+ }
+
test("unsupported fmt fields for trunc/date_trunc results null") {
Seq("INVALID", "decade", "century", "millennium", "whatever",
null).foreach { field =>
testTruncDate(Date.valueOf("2000-03-08"), field, null)
diff --git
a/sql/core/src/test/resources/sql-tests/analyzer-results/timestamp-ltz-nanos.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/timestamp-ltz-nanos.sql.out
index ee73c05a24fb..454eeddf2f23 100644
---
a/sql/core/src/test/resources/sql-tests/analyzer-results/timestamp-ltz-nanos.sql.out
+++
b/sql/core/src/test/resources/sql-tests/analyzer-results/timestamp-ltz-nanos.sql.out
@@ -1034,3 +1034,38 @@ Sort [v#x ASC NULLS FIRST], true
: +- OneRowRelation
+- Project [2020-01-01 00:00:00.0000005 AS TIMESTAMP_LTZ
'2020-01-01 00:00:00.000000500'#x]
+- OneRowRelation
+
+
+-- !query
+SELECT date_trunc('SECOND', TIMESTAMP_LTZ '2020-01-01 12:34:56.123456789 UTC')
+-- !query analysis
+Project [date_trunc(SECOND, 2020-01-01 04:34:56.123456789,
Some(America/Los_Angeles)) AS date_trunc(SECOND, TIMESTAMP_LTZ '2020-01-01
04:34:56.123456789')#x]
++- OneRowRelation
+
+
+-- !query
+SELECT date_trunc('MICROSECOND', TIMESTAMP_LTZ '2020-01-01 12:34:56.123456789
UTC')
+-- !query analysis
+Project [date_trunc(MICROSECOND, 2020-01-01 04:34:56.123456789,
Some(America/Los_Angeles)) AS date_trunc(MICROSECOND, TIMESTAMP_LTZ '2020-01-01
04:34:56.123456789')#x]
++- OneRowRelation
+
+
+-- !query
+SELECT date_trunc('HOUR', '2020-01-01 12:34:56.123456789 UTC' ::
timestamp_ltz(9))
+-- !query analysis
+Project [date_trunc(HOUR, cast(2020-01-01 12:34:56.123456789 UTC as
timestamp_ltz(9)), Some(America/Los_Angeles)) AS date_trunc(HOUR,
CAST(2020-01-01 12:34:56.123456789 UTC AS TIMESTAMP_LTZ(9)))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT date_trunc('DAY', '2020-01-01 04:00:00.000000123 UTC' ::
timestamp_ltz(7))
+-- !query analysis
+Project [date_trunc(DAY, cast(2020-01-01 04:00:00.000000123 UTC as
timestamp_ltz(7)), Some(America/Los_Angeles)) AS date_trunc(DAY,
CAST(2020-01-01 04:00:00.000000123 UTC AS TIMESTAMP_LTZ(7)))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT date_trunc('NANOSECOND', TIMESTAMP_LTZ '2020-01-01 12:34:56.123456789
UTC')
+-- !query analysis
+Project [date_trunc(NANOSECOND, 2020-01-01 04:34:56.123456789,
Some(America/Los_Angeles)) AS date_trunc(NANOSECOND, TIMESTAMP_LTZ '2020-01-01
04:34:56.123456789')#x]
++- OneRowRelation
diff --git
a/sql/core/src/test/resources/sql-tests/analyzer-results/timestamp-ntz-nanos.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/timestamp-ntz-nanos.sql.out
index abba0bc6b43f..879c084a7496 100644
---
a/sql/core/src/test/resources/sql-tests/analyzer-results/timestamp-ntz-nanos.sql.out
+++
b/sql/core/src/test/resources/sql-tests/analyzer-results/timestamp-ntz-nanos.sql.out
@@ -868,3 +868,38 @@ Sort [v#x ASC NULLS FIRST], true
: +- OneRowRelation
+- Project [2020-01-01 00:00:00.0000005 AS TIMESTAMP_NTZ
'2020-01-01 00:00:00.000000500'#x]
+- OneRowRelation
+
+
+-- !query
+SELECT date_trunc('SECOND', TIMESTAMP_NTZ '2020-01-01 12:34:56.123456789')
+-- !query analysis
+Project [date_trunc(SECOND, 2020-01-01 12:34:56.123456789,
Some(America/Los_Angeles)) AS date_trunc(SECOND, TIMESTAMP_NTZ '2020-01-01
12:34:56.123456789')#x]
++- OneRowRelation
+
+
+-- !query
+SELECT date_trunc('MICROSECOND', TIMESTAMP_NTZ '2020-01-01 12:34:56.123456789')
+-- !query analysis
+Project [date_trunc(MICROSECOND, 2020-01-01 12:34:56.123456789,
Some(America/Los_Angeles)) AS date_trunc(MICROSECOND, TIMESTAMP_NTZ '2020-01-01
12:34:56.123456789')#x]
++- OneRowRelation
+
+
+-- !query
+SELECT date_trunc('HOUR', '2020-01-01 12:34:56.123456789' :: timestamp_ntz(9))
+-- !query analysis
+Project [date_trunc(HOUR, cast(2020-01-01 12:34:56.123456789 as
timestamp_ntz(9)), Some(America/Los_Angeles)) AS date_trunc(HOUR,
CAST(2020-01-01 12:34:56.123456789 AS TIMESTAMP_NTZ(9)))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT date_trunc('DAY', '2020-06-21 23:30:00.000000123' :: timestamp_ntz(7))
+-- !query analysis
+Project [date_trunc(DAY, cast(2020-06-21 23:30:00.000000123 as
timestamp_ntz(7)), Some(America/Los_Angeles)) AS date_trunc(DAY,
CAST(2020-06-21 23:30:00.000000123 AS TIMESTAMP_NTZ(7)))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT date_trunc('NANOSECOND', TIMESTAMP_NTZ '2020-01-01 12:34:56.123456789')
+-- !query analysis
+Project [date_trunc(NANOSECOND, 2020-01-01 12:34:56.123456789,
Some(America/Los_Angeles)) AS date_trunc(NANOSECOND, TIMESTAMP_NTZ '2020-01-01
12:34:56.123456789')#x]
++- OneRowRelation
diff --git
a/sql/core/src/test/resources/sql-tests/inputs/timestamp-ltz-nanos.sql
b/sql/core/src/test/resources/sql-tests/inputs/timestamp-ltz-nanos.sql
index f3327244541a..a6bed71cc1ac 100644
--- a/sql/core/src/test/resources/sql-tests/inputs/timestamp-ltz-nanos.sql
+++ b/sql/core/src/test/resources/sql-tests/inputs/timestamp-ltz-nanos.sql
@@ -328,3 +328,14 @@ SELECT v, lead(v) OVER (ORDER BY v) AS next_v FROM (
SELECT TIMESTAMP_LTZ '2020-01-01 00:00:00.000000900' AS v
UNION ALL SELECT TIMESTAMP_LTZ '2020-01-01 00:00:00.000000100'
UNION ALL SELECT TIMESTAMP_LTZ '2020-01-01 00:00:00.000000500') ORDER BY v;
+
+-- SPARK-57821: date_trunc keeps the nanosecond type/family and zeroes the
whole fraction (including
+-- the sub-microsecond digits); MICROSECOND keeps epochMicros and only drops
nanosWithinMicro. LTZ
+-- truncates in the session time zone (America/Los_Angeles, UTC-08:00), so DAY
over an early-hours
+-- UTC instant floors to the previous calendar day in the session zone.
+SELECT date_trunc('SECOND', TIMESTAMP_LTZ '2020-01-01 12:34:56.123456789 UTC');
+SELECT date_trunc('MICROSECOND', TIMESTAMP_LTZ '2020-01-01 12:34:56.123456789
UTC');
+SELECT date_trunc('HOUR', '2020-01-01 12:34:56.123456789 UTC' ::
timestamp_ltz(9));
+SELECT date_trunc('DAY', '2020-01-01 04:00:00.000000123 UTC' ::
timestamp_ltz(7));
+-- An unsupported (sub-microsecond) unit yields NULL; the result still carries
the nanos type.
+SELECT date_trunc('NANOSECOND', TIMESTAMP_LTZ '2020-01-01 12:34:56.123456789
UTC');
diff --git
a/sql/core/src/test/resources/sql-tests/inputs/timestamp-ntz-nanos.sql
b/sql/core/src/test/resources/sql-tests/inputs/timestamp-ntz-nanos.sql
index b248913813a6..aa173fb72620 100644
--- a/sql/core/src/test/resources/sql-tests/inputs/timestamp-ntz-nanos.sql
+++ b/sql/core/src/test/resources/sql-tests/inputs/timestamp-ntz-nanos.sql
@@ -268,3 +268,13 @@ SELECT v, lead(v) OVER (ORDER BY v) AS next_v FROM (
SELECT TIMESTAMP_NTZ '2020-01-01 00:00:00.000000900' AS v
UNION ALL SELECT TIMESTAMP_NTZ '2020-01-01 00:00:00.000000100'
UNION ALL SELECT TIMESTAMP_NTZ '2020-01-01 00:00:00.000000500') ORDER BY v;
+
+-- SPARK-57821: date_trunc keeps the nanosecond type/family and zeroes the
whole fraction (including
+-- the sub-microsecond digits); MICROSECOND keeps epochMicros and only drops
nanosWithinMicro. NTZ
+-- is zone-independent, so DAY/HOUR read the wall clock and never shift.
+SELECT date_trunc('SECOND', TIMESTAMP_NTZ '2020-01-01 12:34:56.123456789');
+SELECT date_trunc('MICROSECOND', TIMESTAMP_NTZ '2020-01-01
12:34:56.123456789');
+SELECT date_trunc('HOUR', '2020-01-01 12:34:56.123456789' :: timestamp_ntz(9));
+SELECT date_trunc('DAY', '2020-06-21 23:30:00.000000123' :: timestamp_ntz(7));
+-- An unsupported (sub-microsecond) unit yields NULL; the result still carries
the nanos type.
+SELECT date_trunc('NANOSECOND', TIMESTAMP_NTZ '2020-01-01 12:34:56.123456789');
diff --git
a/sql/core/src/test/resources/sql-tests/results/timestamp-ltz-nanos.sql.out
b/sql/core/src/test/resources/sql-tests/results/timestamp-ltz-nanos.sql.out
index 845b1e30137c..8aa3586481e7 100644
--- a/sql/core/src/test/resources/sql-tests/results/timestamp-ltz-nanos.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/timestamp-ltz-nanos.sql.out
@@ -1125,3 +1125,43 @@ struct<v:timestamp_ltz(9),next_v:timestamp_ltz(9)>
2020-01-01 00:00:00.0000001 2020-01-01 00:00:00.0000005
2020-01-01 00:00:00.0000005 2020-01-01 00:00:00.0000009
2020-01-01 00:00:00.0000009 NULL
+
+
+-- !query
+SELECT date_trunc('SECOND', TIMESTAMP_LTZ '2020-01-01 12:34:56.123456789 UTC')
+-- !query schema
+struct<date_trunc(SECOND, TIMESTAMP_LTZ '2020-01-01
04:34:56.123456789'):timestamp_ltz(9)>
+-- !query output
+2020-01-01 04:34:56
+
+
+-- !query
+SELECT date_trunc('MICROSECOND', TIMESTAMP_LTZ '2020-01-01 12:34:56.123456789
UTC')
+-- !query schema
+struct<date_trunc(MICROSECOND, TIMESTAMP_LTZ '2020-01-01
04:34:56.123456789'):timestamp_ltz(9)>
+-- !query output
+2020-01-01 04:34:56.123456
+
+
+-- !query
+SELECT date_trunc('HOUR', '2020-01-01 12:34:56.123456789 UTC' ::
timestamp_ltz(9))
+-- !query schema
+struct<date_trunc(HOUR, CAST(2020-01-01 12:34:56.123456789 UTC AS
TIMESTAMP_LTZ(9))):timestamp_ltz(9)>
+-- !query output
+2020-01-01 04:00:00
+
+
+-- !query
+SELECT date_trunc('DAY', '2020-01-01 04:00:00.000000123 UTC' ::
timestamp_ltz(7))
+-- !query schema
+struct<date_trunc(DAY, CAST(2020-01-01 04:00:00.000000123 UTC AS
TIMESTAMP_LTZ(7))):timestamp_ltz(7)>
+-- !query output
+2019-12-31 00:00:00
+
+
+-- !query
+SELECT date_trunc('NANOSECOND', TIMESTAMP_LTZ '2020-01-01 12:34:56.123456789
UTC')
+-- !query schema
+struct<date_trunc(NANOSECOND, TIMESTAMP_LTZ '2020-01-01
04:34:56.123456789'):timestamp_ltz(9)>
+-- !query output
+NULL
diff --git
a/sql/core/src/test/resources/sql-tests/results/timestamp-ntz-nanos.sql.out
b/sql/core/src/test/resources/sql-tests/results/timestamp-ntz-nanos.sql.out
index 5311019e61f3..acb2fe195115 100644
--- a/sql/core/src/test/resources/sql-tests/results/timestamp-ntz-nanos.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/timestamp-ntz-nanos.sql.out
@@ -922,3 +922,43 @@ struct<v:timestamp_ntz(9),next_v:timestamp_ntz(9)>
2020-01-01 00:00:00.0000001 2020-01-01 00:00:00.0000005
2020-01-01 00:00:00.0000005 2020-01-01 00:00:00.0000009
2020-01-01 00:00:00.0000009 NULL
+
+
+-- !query
+SELECT date_trunc('SECOND', TIMESTAMP_NTZ '2020-01-01 12:34:56.123456789')
+-- !query schema
+struct<date_trunc(SECOND, TIMESTAMP_NTZ '2020-01-01
12:34:56.123456789'):timestamp_ntz(9)>
+-- !query output
+2020-01-01 12:34:56
+
+
+-- !query
+SELECT date_trunc('MICROSECOND', TIMESTAMP_NTZ '2020-01-01 12:34:56.123456789')
+-- !query schema
+struct<date_trunc(MICROSECOND, TIMESTAMP_NTZ '2020-01-01
12:34:56.123456789'):timestamp_ntz(9)>
+-- !query output
+2020-01-01 12:34:56.123456
+
+
+-- !query
+SELECT date_trunc('HOUR', '2020-01-01 12:34:56.123456789' :: timestamp_ntz(9))
+-- !query schema
+struct<date_trunc(HOUR, CAST(2020-01-01 12:34:56.123456789 AS
TIMESTAMP_NTZ(9))):timestamp_ntz(9)>
+-- !query output
+2020-01-01 12:00:00
+
+
+-- !query
+SELECT date_trunc('DAY', '2020-06-21 23:30:00.000000123' :: timestamp_ntz(7))
+-- !query schema
+struct<date_trunc(DAY, CAST(2020-06-21 23:30:00.000000123 AS
TIMESTAMP_NTZ(7))):timestamp_ntz(7)>
+-- !query output
+2020-06-21 00:00:00
+
+
+-- !query
+SELECT date_trunc('NANOSECOND', TIMESTAMP_NTZ '2020-01-01 12:34:56.123456789')
+-- !query schema
+struct<date_trunc(NANOSECOND, TIMESTAMP_NTZ '2020-01-01
12:34:56.123456789'):timestamp_ntz(9)>
+-- !query output
+NULL
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
