This is an automated email from the ASF dual-hosted git repository.
wenchen pushed a commit to branch branch-3.0
in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/branch-3.0 by this push:
new 004952d [SPARK-31507][SQL] Remove uncommon fields support and update
some fields with meaningful names for extract function
004952d is described below
commit 004952d26139cee9c03bf5300fc815940efb79c3
Author: Kent Yao <[email protected]>
AuthorDate: Wed Apr 22 10:24:49 2020 +0000
[SPARK-31507][SQL] Remove uncommon fields support and update some fields
with meaningful names for extract function
### What changes were proposed in this pull request?
Extracting millennium, century, decade, millisecond, microsecond and epoch
from datetime is neither ANSI standard nor quite common in modern SQL
platforms. Most of the systems listing below does not support these except
PostgreSQL and redshift.
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF
https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions050.htm
https://prestodb.io/docs/current/functions/datetime.html
https://docs.cloudera.com/documentation/enterprise/5-8-x/topics/impala_datetime_functions.html
https://docs.snowflake.com/en/sql-reference/functions-date-time.html#label-supported-date-time-parts
https://www.postgresql.org/docs/9.1/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT
This PR removes these extract fields support from extract function for date
and timestamp values
`isoyear` is PostgreSQL specific but `yearofweek` is more commonly used
across platforms
`isodow` is PostgreSQL specific but `iso` as a suffix is more commonly used
across platforms so, `dow_iso` and `dayofweek_iso` is used to replace it.
For historical reasons, we have [`dayofweek`, `dow`] implemented for
representing a non-ISO day-of-week and a newly added `isodow` from PostgreSQL
for ISO day-of-week. Many other systems only have one week-numbering system
support and use either full names or abbreviations. Things in spark become a
little bit complicated.
1. because of the existence of `isodow`, so we need to add iso-prefix to
`dayofweek` to make a pair for it too. [`dayofweek`, `isodayofweek`, `dow` and
`isodow`]
2. because there are rare `iso`-prefixed systems and more systems choose
`iso`-suffixed way, so we may result in [`dayofweek`, `dayofweekiso`, `dow`,
`dowiso`]
3. `dayofweekiso` looks nice and has use cases in the platforms listed
above, e.g. snowflake, but `dowiso` looks weird and no use cases found.
4. with a discussion the community，we have agreed with an underscore before
`iso` may look much better because `isodow` is new and there is no standard for
`iso` kind of things, so this may be good for us to make it simple and clear
for end-users if they are well documented too.
Thus, we finally result in [`dayofweek`, `dow`] for Non-ISO day-of-week
system and [`dayofweek_iso`, `dow_iso`] for ISO system
### Why are the changes needed?
Remove some nonstandard and uncommon features as we can add them back if
necessary
### Does this PR introduce any user-facing change?
NO, we should target this to 3.0.0 and these are added during 3.0.0
### How was this patch tested?
Remove unused tests
Closes #28284 from yaooqinn/SPARK-31507.
Authored-by: Kent Yao <[email protected]>
Signed-off-by: Wenchen Fan <[email protected]>
(cherry picked from commit 37d2e037ed804a414ed874c829e0139a277a5ae8)
Signed-off-by: Wenchen Fan <[email protected]>
---
.../catalyst/expressions/datetimeExpressions.scala | 152 +----
.../spark/sql/catalyst/util/DateTimeUtils.scala | 39 +-
.../expressions/DateExpressionsSuite.scala | 89 +--
.../benchmarks/ExtractBenchmark-jdk11-results.txt | 168 +++---
sql/core/benchmarks/ExtractBenchmark-results.txt | 180 +++---
.../test/resources/sql-tests/inputs/date_part.sql | 111 ----
.../test/resources/sql-tests/inputs/extract.sql | 92 ++-
.../resources/sql-tests/inputs/postgreSQL/date.sql | 76 +--
.../sql-tests/inputs/postgreSQL/timestamp.sql | 18 +-
.../resources/sql-tests/results/date_part.sql.out | 670 ---------------------
.../resources/sql-tests/results/extract.sql.out | 441 +++++++++-----
.../sql-tests/results/postgreSQL/date.sql.out | 306 +---------
.../sql-tests/results/postgreSQL/timestamp.sql.out | 32 +-
.../sql/execution/benchmark/ExtractBenchmark.scala | 8 +-
14 files changed, 576 insertions(+), 1806 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 63d6fcc..6ca6ab7 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
@@ -334,48 +334,6 @@ case class SecondWithFraction(child: Expression,
timeZoneId: Option[String] = No
}
}
-case class Milliseconds(child: Expression, timeZoneId: Option[String] = None)
- extends UnaryExpression with ImplicitCastInputTypes with
TimeZoneAwareExpression {
-
- override def inputTypes: Seq[AbstractDataType] = Seq(TimestampType)
- // DecimalType is used here to not lose precision while converting
microseconds to
- // the fractional part of milliseconds. Scale 3 is taken to have all
microseconds as
- // the fraction. The precision 8 should cover 2 digits for seconds, 3 digits
for
- // milliseconds and 3 digits for microseconds.
- override def dataType: DataType = DecimalType(8, 3)
- override def withTimeZone(timeZoneId: String): TimeZoneAwareExpression =
- copy(timeZoneId = Option(timeZoneId))
-
- override protected def nullSafeEval(timestamp: Any): Any = {
- DateTimeUtils.getMilliseconds(timestamp.asInstanceOf[Long], zoneId)
- }
-
- override protected def doGenCode(ctx: CodegenContext, ev: ExprCode):
ExprCode = {
- val zid = ctx.addReferenceObj("zoneId", zoneId, classOf[ZoneId].getName)
- val dtu = DateTimeUtils.getClass.getName.stripSuffix("$")
- defineCodeGen(ctx, ev, c => s"$dtu.getMilliseconds($c, $zid)")
- }
-}
-
-case class Microseconds(child: Expression, timeZoneId: Option[String] = None)
- extends UnaryExpression with ImplicitCastInputTypes with
TimeZoneAwareExpression {
-
- override def inputTypes: Seq[AbstractDataType] = Seq(TimestampType)
- override def dataType: DataType = IntegerType
- override def withTimeZone(timeZoneId: String): TimeZoneAwareExpression =
- copy(timeZoneId = Option(timeZoneId))
-
- override protected def nullSafeEval(timestamp: Any): Any = {
- DateTimeUtils.getMicroseconds(timestamp.asInstanceOf[Long], zoneId)
- }
-
- override protected def doGenCode(ctx: CodegenContext, ev: ExprCode):
ExprCode = {
- val zid = ctx.addReferenceObj("zoneId", zoneId, classOf[ZoneId].getName)
- val dtu = DateTimeUtils.getClass.getName.stripSuffix("$")
- defineCodeGen(ctx, ev, c => s"$dtu.getMicroseconds($c, $zid)")
- }
-}
-
@ExpressionDescription(
usage = "_FUNC_(date) - Returns the day of year of the date/timestamp.",
examples = """
@@ -426,19 +384,19 @@ case class Year(child: Expression) extends
UnaryExpression with ImplicitCastInpu
}
}
-case class IsoYear(child: Expression) extends UnaryExpression with
ImplicitCastInputTypes {
+case class YearOfWeek(child: Expression) extends UnaryExpression with
ImplicitCastInputTypes {
override def inputTypes: Seq[AbstractDataType] = Seq(DateType)
override def dataType: DataType = IntegerType
override protected def nullSafeEval(date: Any): Any = {
- DateTimeUtils.getIsoYear(date.asInstanceOf[Int])
+ DateTimeUtils.getWeekBasedYear(date.asInstanceOf[Int])
}
override protected def doGenCode(ctx: CodegenContext, ev: ExprCode):
ExprCode = {
val dtu = DateTimeUtils.getClass.getName.stripSuffix("$")
- defineCodeGen(ctx, ev, c => s"$dtu.getIsoYear($c)")
+ defineCodeGen(ctx, ev, c => s"$dtu.getWeekBasedYear($c)")
}
}
@@ -2032,108 +1990,26 @@ case class MakeTimestamp(
override def prettyName: String = "make_timestamp"
}
-case class Millennium(child: Expression) extends UnaryExpression with
ImplicitCastInputTypes {
-
- override def inputTypes: Seq[AbstractDataType] = Seq(DateType)
-
- override def dataType: DataType = IntegerType
-
- override protected def nullSafeEval(date: Any): Any = {
- DateTimeUtils.getMillennium(date.asInstanceOf[Int])
- }
-
- override protected def doGenCode(ctx: CodegenContext, ev: ExprCode):
ExprCode = {
- val dtu = DateTimeUtils.getClass.getName.stripSuffix("$")
- defineCodeGen(ctx, ev, c => s"$dtu.getMillennium($c)")
- }
-}
-
-case class Century(child: Expression) extends UnaryExpression with
ImplicitCastInputTypes {
-
- override def inputTypes: Seq[AbstractDataType] = Seq(DateType)
-
- override def dataType: DataType = IntegerType
-
- override protected def nullSafeEval(date: Any): Any = {
- DateTimeUtils.getCentury(date.asInstanceOf[Int])
- }
-
- override protected def doGenCode(ctx: CodegenContext, ev: ExprCode):
ExprCode = {
- val dtu = DateTimeUtils.getClass.getName.stripSuffix("$")
- defineCodeGen(ctx, ev, c => s"$dtu.getCentury($c)")
- }
-}
-
-case class Decade(child: Expression) extends UnaryExpression with
ImplicitCastInputTypes {
-
- override def inputTypes: Seq[AbstractDataType] = Seq(DateType)
-
- override def dataType: DataType = IntegerType
-
- override protected def nullSafeEval(date: Any): Any = {
- DateTimeUtils.getDecade(date.asInstanceOf[Int])
- }
-
- override protected def doGenCode(ctx: CodegenContext, ev: ExprCode):
ExprCode = {
- val dtu = DateTimeUtils.getClass.getName.stripSuffix("$")
- defineCodeGen(ctx, ev, c => s"$dtu.getDecade($c)")
- }
-}
-
-case class Epoch(child: Expression, timeZoneId: Option[String] = None)
- extends UnaryExpression with ImplicitCastInputTypes with
TimeZoneAwareExpression {
-
- override def inputTypes: Seq[AbstractDataType] = Seq(TimestampType)
- // DecimalType is used to not lose precision while converting microseconds to
- // the fractional part of seconds. Scale 6 is taken to have all microseconds
as
- // the fraction. The precision 20 should cover whole valid range of years
[1, 9999]
- // plus negative years that can be used in some cases though are not
officially supported.
- override def dataType: DataType = DecimalType(20, 6)
- override def withTimeZone(timeZoneId: String): TimeZoneAwareExpression =
- copy(timeZoneId = Option(timeZoneId))
-
- override protected def nullSafeEval(timestamp: Any): Any = {
- DateTimeUtils.getEpoch(timestamp.asInstanceOf[Long], zoneId)
- }
-
- override protected def doGenCode(ctx: CodegenContext, ev: ExprCode):
ExprCode = {
- val dtu = DateTimeUtils.getClass.getName.stripSuffix("$")
- val zid = ctx.addReferenceObj("zoneId", zoneId, classOf[ZoneId].getName)
- defineCodeGen(ctx, ev, c => s"$dtu.getEpoch($c, $zid)")
- }
-}
-
object DatePart {
def parseExtractField(
extractField: String,
source: Expression,
errorHandleFunc: => Nothing): Expression =
extractField.toUpperCase(Locale.ROOT) match {
- case "MILLENNIUM" | "MILLENNIA" | "MIL" | "MILS" => Millennium(source)
- case "CENTURY" | "CENTURIES" | "C" | "CENT" => Century(source)
- case "DECADE" | "DECADES" | "DEC" | "DECS" => Decade(source)
case "YEAR" | "Y" | "YEARS" | "YR" | "YRS" => Year(source)
- case "ISOYEAR" => IsoYear(source)
+ case "YEAROFWEEK" => YearOfWeek(source)
case "QUARTER" | "QTR" => Quarter(source)
case "MONTH" | "MON" | "MONS" | "MONTHS" => Month(source)
case "WEEK" | "W" | "WEEKS" => WeekOfYear(source)
case "DAY" | "D" | "DAYS" => DayOfMonth(source)
case "DAYOFWEEK" | "DOW" => DayOfWeek(source)
- case "ISODOW" => Add(WeekDay(source), Literal(1))
+ case "DAYOFWEEK_ISO" | "DOW_ISO" => Add(WeekDay(source), Literal(1))
case "DOY" => DayOfYear(source)
case "HOUR" | "H" | "HOURS" | "HR" | "HRS" => Hour(source)
case "MINUTE" | "M" | "MIN" | "MINS" | "MINUTES" => Minute(source)
case "SECOND" | "S" | "SEC" | "SECONDS" | "SECS" =>
SecondWithFraction(source)
- case "MILLISECONDS" | "MSEC" | "MSECS" | "MILLISECON" | "MSECONDS" | "MS"
=>
- Milliseconds(source)
- case "MICROSECONDS" | "USEC" | "USECS" | "USECONDS" | "MICROSECON" | "US"
=>
- Microseconds(source)
- case "EPOCH" => Epoch(source)
case _ => errorHandleFunc
}
-}
-
-object DatePartLike {
def toEquivalentExpr(field: Expression, source: Expression): Expression = {
if (!field.foldable) {
@@ -2191,7 +2067,7 @@ case class DatePart(field: Expression, source:
Expression, child: Expression)
extends RuntimeReplaceable {
def this(field: Expression, source: Expression) = {
- this(field, source, DatePartLike.toEquivalentExpr(field, source))
+ this(field, source, DatePart.toEquivalentExpr(field, source))
}
override def flatArguments: Iterator[Any] = Iterator(field, source)
@@ -2205,26 +2081,20 @@ case class DatePart(field: Expression, source:
Expression, child: Expression)
arguments = """
Arguments:
* field - selects which part of the source should be extracted
- - Supported string values of `field` for dates and timestamps are:
- - "MILLENNIUM", ("MILLENNIA", "MIL", "MILS") - the conventional
numbering of millennia
- - "CENTURY", ("CENTURIES", "C", "CENT") - the conventional
numbering of centuries
- - "DECADE", ("DECADES", "DEC", "DECS") - the year field divided
by 10
+ - Supported string values of `field` for dates and timestamps
are(case insensitive):
- "YEAR", ("Y", "YEARS", "YR", "YRS") - the year field
- - "ISOYEAR" - the ISO 8601 week-numbering year that the datetime
falls in
+ - "YEAROFWEEK" - the ISO 8601 week-numbering year that the
datetime falls in. For example, 2005-01-02 is part of the 53rd week of year
2004, so the result is 2004
- "QUARTER", ("QTR") - the quarter (1 - 4) of the year that the
datetime falls in
- "MONTH", ("MON", "MONS", "MONTHS") - the month field (1 - 12)
- "WEEK", ("W", "WEEKS") - the number of the ISO 8601
week-of-week-based-year. A week is considered to start on a Monday and week 1
is the first week with >3 days. In the ISO week-numbering system, it is
possible for early-January dates to be part of the 52nd or 53rd week of the
previous year, and for late-December dates to be part of the first week of the
next year. For example, 2005-01-02 is part of the 53rd week of year 2004, while
2012-12-31 is part of the first week of 2013
- "DAY", ("D", "DAYS") - the day of the month field (1 - 31)
- "DAYOFWEEK",("DOW") - the day of the week for datetime as
Sunday(1) to Saturday(7)
- - "ISODOW" - ISO 8601 based day of the week for datetime as
Monday(1) to Sunday(7)
+ - "DAYOFWEEK_ISO",("DOW_ISO") - ISO 8601 based day of the week
for datetime as Monday(1) to Sunday(7)
- "DOY" - the day of the year (1 - 365/366)
- "HOUR", ("H", "HOURS", "HR", "HRS") - The hour field (0 - 23)
- "MINUTE", ("M", "MIN", "MINS", "MINUTES") - the minutes field
(0 - 59)
- "SECOND", ("S", "SEC", "SECONDS", "SECS") - the seconds field,
including fractional parts
- - "MILLISECONDS", ("MSEC", "MSECS", "MILLISECON", "MSECONDS",
"MS") - the seconds field, including fractional parts, multiplied by 1000. Note
that this includes full seconds
- - "MICROSECONDS", ("USEC", "USECS", "USECONDS", "MICROSECON",
"US") - The seconds field, including fractional parts, multiplied by 1000000.
Note that this includes full seconds
- - "EPOCH" - the number of seconds with fractional part in
microsecond precision since 1970-01-01 00:00:00 local time (can be negative)
- - Supported string values of `field` for interval(which consists of
`months`, `days`, `microseconds`) are:
+ - Supported string values of `field` for interval(which consists of
`months`, `days`, `microseconds`) are(case insensitive):
- "YEAR", ("Y", "YEARS", "YR", "YRS") - the total `months` / 12
- "MONTH", ("MON", "MONS", "MONTHS") - the total `months` % 12
- "DAY", ("D", "DAYS") - the `days` part of interval
@@ -2257,7 +2127,7 @@ case class Extract(field: Expression, source: Expression,
child: Expression)
extends RuntimeReplaceable {
def this(field: Expression, source: Expression) = {
- this(field, source, DatePartLike.toEquivalentExpr(field, source))
+ this(field, source, DatePart.toEquivalentExpr(field, source))
}
override def flatArguments: Iterator[Any] = Iterator(field, source)
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 8486bba..0e9a15b 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
@@ -527,14 +527,6 @@ object DateTimeUtils {
}
/**
- * Returns seconds, including fractional parts, multiplied by 1000. The
timestamp
- * is expressed in microseconds since the epoch.
- */
- def getMilliseconds(timestamp: SQLTimestamp, zoneId: ZoneId): Decimal = {
- Decimal(getMicroseconds(timestamp, zoneId), 8, 3)
- }
-
- /**
* Returns seconds, including fractional parts, multiplied by 1000000. The
timestamp
* is expressed in microseconds since the epoch.
*/
@@ -551,24 +543,6 @@ object DateTimeUtils {
LocalDate.ofEpochDay(date).getDayOfYear
}
- private def extractFromYear(date: SQLDate, divider: Int): Int = {
- val localDate = daysToLocalDate(date)
- val yearOfEra = localDate.get(ChronoField.YEAR_OF_ERA)
- var result = yearOfEra / divider
- if ((yearOfEra % divider) != 0 || yearOfEra <= 1) result += 1
- if (localDate.get(ChronoField.ERA) == 0) result = -result
- result
- }
-
- /** Returns the millennium for the given date. The date is expressed in days
since 1.1.1970. */
- def getMillennium(date: SQLDate): Int = extractFromYear(date, 1000)
-
- /** Returns the century for the given date. The date is expressed in days
since 1.1.1970. */
- def getCentury(date: SQLDate): Int = extractFromYear(date, 100)
-
- /** Returns the decade for the given date. The date is expressed in days
since 1.1.1970. */
- def getDecade(date: SQLDate): Int = Math.floorDiv(getYear(date), 10)
-
/**
* Returns the year value for the given date. The date is expressed in days
* since 1.1.1970.
@@ -581,7 +555,7 @@ object DateTimeUtils {
* Returns the year which conforms to ISO 8601. Each ISO 8601 week-numbering
* year begins with the Monday of the week containing the 4th of January.
*/
- def getIsoYear(date: SQLDate): Int = {
+ def getWeekBasedYear(date: SQLDate): Int = {
daysToLocalDate(date).get(IsoFields.WEEK_BASED_YEAR)
}
@@ -869,17 +843,6 @@ object DateTimeUtils {
convertTz(time, getZoneId(timeZone), ZoneOffset.UTC)
}
- /**
- * Returns the number of seconds with fractional part in microsecond
precision
- * since 1970-01-01 00:00:00 local time.
- */
- def getEpoch(timestamp: SQLTimestamp, zoneId: ZoneId): Decimal = {
- val offset = SECONDS.toMicros(
- zoneId.getRules.getOffset(microsToInstant(timestamp)).getTotalSeconds)
- val sinceEpoch = timestamp + offset
- Decimal(sinceEpoch, 20, 6)
- }
-
def currentTimestamp(): SQLTimestamp = instantToMicros(Instant.now())
def currentDate(zoneId: ZoneId): SQLDate =
localDateToDays(LocalDate.now(zoneId))
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 7f235d6..b354717 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
@@ -1032,94 +1032,9 @@ class DateExpressionsSuite extends SparkFunSuite with
ExpressionEvalHelper {
checkEvaluation(makeTimestampExpr, Timestamp.valueOf("2019-08-12
00:00:58.000001"))
}
- test("millennium") {
- val date = MakeDate(Literal(2019), Literal(1), Literal(1))
- checkEvaluation(Millennium(date), 3)
- checkEvaluation(Millennium(date.copy(year = Literal(2001))), 3)
- checkEvaluation(Millennium(date.copy(year = Literal(2000))), 2)
- checkEvaluation(Millennium(date.copy(year = Literal(1001), day =
Literal(28))), 2)
- checkEvaluation(Millennium(date.copy(year = Literal(1))), 1)
- checkEvaluation(Millennium(date.copy(year = Literal(-1))), -1)
- checkEvaluation(Millennium(date.copy(year = Literal(-100), month =
Literal(12))), -1)
- checkEvaluation(Millennium(date.copy(year = Literal(-2019))), -3)
- }
-
- test("century") {
- val date = MakeDate(Literal(2019), Literal(1), Literal(1))
- checkEvaluation(Century(date), 21)
- checkEvaluation(Century(date.copy(year = Literal(2001))), 21)
- checkEvaluation(Century(date.copy(year = Literal(2000))), 20)
- checkEvaluation(Century(date.copy(year = Literal(1001), day =
Literal(28))), 11)
- checkEvaluation(Century(date.copy(year = Literal(1))), 1)
- checkEvaluation(Century(date.copy(year = Literal(-1))), -1)
- checkEvaluation(Century(date.copy(year = Literal(-100), month =
Literal(12))), -2)
- checkEvaluation(Century(date.copy(year = Literal(-2019))), -21)
- }
-
- test("decade") {
- val date = MakeDate(Literal(2019), Literal(8), Literal(8))
- checkEvaluation(Decade(date), 201)
- checkEvaluation(Decade(date.copy(year = Literal(2011))), 201)
- checkEvaluation(Decade(date.copy(year = Literal(2010))), 201)
- checkEvaluation(Decade(date.copy(year = Literal(2009))), 200)
- checkEvaluation(Decade(date.copy(year = Literal(10))), 1)
- checkEvaluation(Decade(date.copy(year = Literal(1))), 0)
- checkEvaluation(Decade(date.copy(year = Literal(-1))), -1)
- checkEvaluation(Decade(date.copy(year = Literal(-10))), -1)
- checkEvaluation(Decade(date.copy(year = Literal(-11))), -2)
- checkEvaluation(Decade(date.copy(year = Literal(-2019))), -202)
- }
-
- test("milliseconds and microseconds") {
- outstandingTimezonesIds.foreach { timezone =>
- var timestamp = MakeTimestamp(Literal(2019), Literal(8), Literal(10),
- Literal(0), Literal(0), Literal(Decimal(BigDecimal(10.123456789), 8,
6)),
- Some(Literal(timezone)), Some(timezone))
- def millis(ts: MakeTimestamp): Milliseconds = Milliseconds(timestamp,
Some(timezone))
- def micros(ts: MakeTimestamp): Microseconds = Microseconds(timestamp,
Some(timezone))
-
- checkEvaluation(millis(timestamp), Decimal(BigDecimal(10123.457), 8, 3))
- checkEvaluation(
- millis(timestamp.copy(year = Literal(10))),
- Decimal(BigDecimal(10123.457), 8, 3))
-
- checkEvaluation(micros(timestamp), 10123457)
- checkEvaluation(
- micros(timestamp.copy(year = Literal(10))),
- 10123457)
-
- timestamp = timestamp.copy(sec = Literal(Decimal(0.0, 8, 6)))
- checkEvaluation(millis(timestamp), Decimal(0, 8, 3))
- checkEvaluation(micros(timestamp), 0)
-
- timestamp = timestamp.copy(sec = Literal(Decimal(BigDecimal(59.999999),
8, 6)))
- checkEvaluation(millis(timestamp), Decimal(BigDecimal(59999.999), 8, 3))
- checkEvaluation(micros(timestamp), 59999999)
-
- timestamp = timestamp.copy(sec = Literal(Decimal(BigDecimal(60.0), 8,
6)))
- checkEvaluation(millis(timestamp), Decimal(0, 8, 3))
- checkEvaluation(micros(timestamp), 0)
- }
- }
-
- test("epoch") {
- val zoneId = ZoneId.systemDefault()
- val nanos = 123456000
- val timestamp = Epoch(MakeTimestamp(
- Literal(2019), Literal(8), Literal(9), Literal(0), Literal(0),
- Literal(Decimal(nanos / NANOS_PER_SECOND.toDouble, 8, 6)),
- Some(Literal(zoneId.getId))))
- val instant = LocalDateTime.of(2019, 8, 9, 0, 0, 0, nanos)
- .atZone(zoneId).toInstant
- val expected = Decimal(BigDecimal(nanos) / NANOS_PER_SECOND +
- instant.getEpochSecond +
- zoneId.getRules.getOffset(instant).getTotalSeconds)
- checkEvaluation(timestamp, expected)
- }
-
test("ISO 8601 week-numbering year") {
- checkEvaluation(IsoYear(MakeDate(Literal(2006), Literal(1), Literal(1))),
2005)
- checkEvaluation(IsoYear(MakeDate(Literal(2006), Literal(1), Literal(2))),
2006)
+ checkEvaluation(YearOfWeek(MakeDate(Literal(2006), Literal(1),
Literal(1))), 2005)
+ checkEvaluation(YearOfWeek(MakeDate(Literal(2006), Literal(1),
Literal(2))), 2006)
}
test("extract the seconds part with fraction from timestamps") {
diff --git a/sql/core/benchmarks/ExtractBenchmark-jdk11-results.txt
b/sql/core/benchmarks/ExtractBenchmark-jdk11-results.txt
index 41bb42c..77f1bdb 100644
--- a/sql/core/benchmarks/ExtractBenchmark-jdk11-results.txt
+++ b/sql/core/benchmarks/ExtractBenchmark-jdk11-results.txt
@@ -2,123 +2,103 @@ Java HotSpot(TM) 64-Bit Server VM 11.0.5+10-LTS on Mac OS X
10.15.4
Intel(R) Core(TM) i9-9980HK CPU @ 2.40GHz
Invoke extract for timestamp: Best Time(ms) Avg Time(ms)
Stdev(ms) Rate(M/s) Per Row(ns) Relative
------------------------------------------------------------------------------------------------------------------------
-cast to timestamp 322 327
4 31.1 32.2 1.0X
-MILLENNIUM of timestamp 834 841
8 12.0 83.4 0.4X
-CENTURY of timestamp 828 841
15 12.1 82.8 0.4X
-DECADE of timestamp 813 825
13 12.3 81.3 0.4X
-YEAR of timestamp 800 817
18 12.5 80.0 0.4X
-ISOYEAR of timestamp 882 889
6 11.3 88.2 0.4X
-QUARTER of timestamp 900 908
12 11.1 90.0 0.4X
-MONTH of timestamp 809 816
8 12.4 80.9 0.4X
-WEEK of timestamp 1119 1123
4 8.9 111.9 0.3X
-DAY of timestamp 801 808
7 12.5 80.1 0.4X
-DAYOFWEEK of timestamp 946 952
5 10.6 94.6 0.3X
-DOW of timestamp 939 945
10 10.6 93.9 0.3X
-ISODOW of timestamp 869 874
5 11.5 86.9 0.4X
-DOY of timestamp 822 835
14 12.2 82.2 0.4X
-HOUR of timestamp 633 647
12 15.8 63.3 0.5X
-MINUTE of timestamp 635 636
1 15.8 63.5 0.5X
-SECOND of timestamp 774 778
4 12.9 77.4 0.4X
-MILLISECONDS of timestamp 735 743
7 13.6 73.5 0.4X
-MICROSECONDS of timestamp 652 657
4 15.3 65.2 0.5X
-EPOCH of timestamp 794 800
5 12.6 79.4 0.4X
+cast to timestamp 333 345
13 30.0 33.3 1.0X
+YEAR of timestamp 826 840
16 12.1 82.6 0.4X
+YEAROFWEEK of timestamp 1161 1168
7 8.6 116.1 0.3X
+QUARTER of timestamp 961 966
6 10.4 96.1 0.3X
+MONTH of timestamp 831 838
6 12.0 83.1 0.4X
+WEEK of timestamp 1155 1159
4 8.7 115.5 0.3X
+DAY of timestamp 822 828
6 12.2 82.2 0.4X
+DAYOFWEEK of timestamp 968 972
6 10.3 96.8 0.3X
+DOW of timestamp 960 966
9 10.4 96.0 0.3X
+DOW_ISO of timestamp 961 967
9 10.4 96.1 0.3X
+DAYOFWEEK_ISO of timestamp 965 968
5 10.4 96.5 0.3X
+DOY of timestamp 864 873
9 11.6 86.4 0.4X
+HOUR of timestamp 631 647
14 15.9 63.1 0.5X
+MINUTE of timestamp 638 651
12 15.7 63.8 0.5X
+SECOND of timestamp 758 760
2 13.2 75.8 0.4X
Java HotSpot(TM) 64-Bit Server VM 11.0.5+10-LTS on Mac OS X 10.15.4
Intel(R) Core(TM) i9-9980HK CPU @ 2.40GHz
Invoke date_part for timestamp: Best Time(ms) Avg Time(ms)
Stdev(ms) Rate(M/s) Per Row(ns) Relative
------------------------------------------------------------------------------------------------------------------------
-cast to timestamp 281 291
11 35.6 28.1 1.0X
-MILLENNIUM of timestamp 814 825
19 12.3 81.4 0.3X
-CENTURY of timestamp 842 850
7 11.9 84.2 0.3X
-DECADE of timestamp 812 814
3 12.3 81.2 0.3X
-YEAR of timestamp 787 798
10 12.7 78.7 0.4X
-ISOYEAR of timestamp 956 962
6 10.5 95.6 0.3X
-QUARTER of timestamp 948 954
6 10.6 94.8 0.3X
-MONTH of timestamp 797 817
26 12.5 79.7 0.4X
-WEEK of timestamp 1126 1134
10 8.9 112.6 0.2X
-DAY of timestamp 800 803
2 12.5 80.0 0.4X
-DAYOFWEEK of timestamp 953 969
16 10.5 95.3 0.3X
-DOW of timestamp 978 986
9 10.2 97.8 0.3X
-ISODOW of timestamp 907 913
7 11.0 90.7 0.3X
-DOY of timestamp 831 844
14 12.0 83.1 0.3X
-HOUR of timestamp 646 650
3 15.5 64.6 0.4X
-MINUTE of timestamp 639 643
5 15.7 63.9 0.4X
-SECOND of timestamp 740 747
6 13.5 74.0 0.4X
-MILLISECONDS of timestamp 745 752
7 13.4 74.5 0.4X
-MICROSECONDS of timestamp 653 658
5 15.3 65.3 0.4X
-EPOCH of timestamp 724 738
12 13.8 72.4 0.4X
+cast to timestamp 301 307
8 33.2 30.1 1.0X
+YEAR of timestamp 797 805
7 12.6 79.7 0.4X
+YEAROFWEEK of timestamp 1147 1156
8 8.7 114.7 0.3X
+QUARTER of timestamp 965 974
9 10.4 96.5 0.3X
+MONTH of timestamp 801 815
17 12.5 80.1 0.4X
+WEEK of timestamp 1133 1157
21 8.8 113.3 0.3X
+DAY of timestamp 825 828
4 12.1 82.5 0.4X
+DAYOFWEEK of timestamp 929 937
8 10.8 92.9 0.3X
+DOW of timestamp 933 940
11 10.7 93.3 0.3X
+DOW_ISO of timestamp 944 954
11 10.6 94.4 0.3X
+DAYOFWEEK_ISO of timestamp 948 965
21 10.5 94.8 0.3X
+DOY of timestamp 894 898
6 11.2 89.4 0.3X
+HOUR of timestamp 678 683
6 14.7 67.8 0.4X
+MINUTE of timestamp 669 670
2 15.0 66.9 0.5X
+SECOND of timestamp 762 771
12 13.1 76.2 0.4X
Java HotSpot(TM) 64-Bit Server VM 11.0.5+10-LTS on Mac OS X 10.15.4
Intel(R) Core(TM) i9-9980HK CPU @ 2.40GHz
Invoke extract for date: Best Time(ms) Avg Time(ms)
Stdev(ms) Rate(M/s) Per Row(ns) Relative
------------------------------------------------------------------------------------------------------------------------
-cast to date 613 621
8 16.3 61.3 1.0X
-MILLENNIUM of date 738 741
3 13.6 73.8 0.8X
-CENTURY of date 735 737
3 13.6 73.5 0.8X
-DECADE of date 722 731
7 13.8 72.2 0.8X
-YEAR of date 715 719
4 14.0 71.5 0.9X
-ISOYEAR of date 881 883
2 11.4 88.1 0.7X
-QUARTER of date 857 867
10 11.7 85.7 0.7X
-MONTH of date 719 727
7 13.9 71.9 0.9X
-WEEK of date 1038 1043
8 9.6 103.8 0.6X
-DAY of date 715 719
4 14.0 71.5 0.9X
-DAYOFWEEK of date 861 873
12 11.6 86.1 0.7X
-DOW of date 847 865
16 11.8 84.7 0.7X
-ISODOW of date 788 796
7 12.7 78.8 0.8X
-DOY of date 744 747
4 13.4 74.4 0.8X
-HOUR of date 1485 1506
30 6.7 148.5 0.4X
-MINUTE of date 1502 1503
1 6.7 150.2 0.4X
-SECOND of date 1641 1650
9 6.1 164.1 0.4X
-MILLISECONDS of date 1624 1639
14 6.2 162.4 0.4X
-MICROSECONDS of date 1504 1505
2 6.6 150.4 0.4X
-EPOCH of date 1733 1748
19 5.8 173.3 0.4X
+cast to date 737 751
23 13.6 73.7 1.0X
+YEAR of date 804 811
11 12.4 80.4 0.9X
+YEAROFWEEK of date 1151 1155
4 8.7 115.1 0.6X
+QUARTER of date 964 987
26 10.4 96.4 0.8X
+MONTH of date 831 835
6 12.0 83.1 0.9X
+WEEK of date 1162 1163
2 8.6 116.2 0.6X
+DAY of date 826 833
7 12.1 82.6 0.9X
+DAYOFWEEK of date 984 1003
26 10.2 98.4 0.7X
+DOW of date 995 997
2 10.0 99.5 0.7X
+DOW_ISO of date 985 992
8 10.2 98.5 0.7X
+DAYOFWEEK_ISO of date 985 1001
19 10.1 98.5 0.7X
+DOY of date 884 890
8 11.3 88.4 0.8X
+HOUR of date 1630 1640
13 6.1 163.0 0.5X
+MINUTE of date 1631 1636
6 6.1 163.1 0.5X
+SECOND of date 1737 1739
2 5.8 173.7 0.4X
Java HotSpot(TM) 64-Bit Server VM 11.0.5+10-LTS on Mac OS X 10.15.4
Intel(R) Core(TM) i9-9980HK CPU @ 2.40GHz
Invoke date_part for date: Best Time(ms) Avg Time(ms)
Stdev(ms) Rate(M/s) Per Row(ns) Relative
------------------------------------------------------------------------------------------------------------------------
-cast to date 613 627
12 16.3 61.3 1.0X
-MILLENNIUM of date 722 734
11 13.9 72.2 0.8X
-CENTURY of date 740 746
8 13.5 74.0 0.8X
-DECADE of date 723 726
3 13.8 72.3 0.8X
-YEAR of date 705 717
11 14.2 70.5 0.9X
-ISOYEAR of date 876 885
9 11.4 87.6 0.7X
-QUARTER of date 857 877
21 11.7 85.7 0.7X
-MONTH of date 714 715
2 14.0 71.4 0.9X
-WEEK of date 1040 1043
4 9.6 104.0 0.6X
-DAY of date 703 714
10 14.2 70.3 0.9X
-DAYOFWEEK of date 861 869
7 11.6 86.1 0.7X
-DOW of date 856 863
8 11.7 85.6 0.7X
-ISODOW of date 791 797
5 12.6 79.1 0.8X
-DOY of date 721 728
6 13.9 72.1 0.9X
-HOUR of date 1488 1493
7 6.7 148.8 0.4X
-MINUTE of date 1488 1502
12 6.7 148.8 0.4X
-SECOND of date 1636 1651
14 6.1 163.6 0.4X
-MILLISECONDS of date 1644 1665
34 6.1 164.4 0.4X
-MICROSECONDS of date 1482 1516
42 6.7 148.2 0.4X
-EPOCH of date 1752 1790
34 5.7 175.2 0.4X
+cast to date 746 753
8 13.4 74.6 1.0X
+YEAR of date 841 843
1 11.9 84.1 0.9X
+YEAROFWEEK of date 1169 1191
20 8.6 116.9 0.6X
+QUARTER of date 1008 1012
4 9.9 100.8 0.7X
+MONTH of date 838 844
6 11.9 83.8 0.9X
+WEEK of date 1179 1182
3 8.5 117.9 0.6X
+DAY of date 849 850
1 11.8 84.9 0.9X
+DAYOFWEEK of date 984 988
4 10.2 98.4 0.8X
+DOW of date 986 994
7 10.1 98.6 0.8X
+DOW_ISO of date 974 992
17 10.3 97.4 0.8X
+DAYOFWEEK_ISO of date 979 994
14 10.2 97.9 0.8X
+DOY of date 874 878
3 11.4 87.4 0.9X
+HOUR of date 1644 1649
6 6.1 164.4 0.5X
+MINUTE of date 1610 1626
15 6.2 161.0 0.5X
+SECOND of date 1768 1789
30 5.7 176.8 0.4X
Java HotSpot(TM) 64-Bit Server VM 11.0.5+10-LTS on Mac OS X 10.15.4
Intel(R) Core(TM) i9-9980HK CPU @ 2.40GHz
Invoke extract for interval: Best Time(ms) Avg Time(ms)
Stdev(ms) Rate(M/s) Per Row(ns) Relative
------------------------------------------------------------------------------------------------------------------------
-cast to interval 921 929
9 10.9 92.1 1.0X
-YEAR of interval 927 942
22 10.8 92.7 1.0X
-MONTH of interval 956 974
22 10.5 95.6 1.0X
-DAY of interval 897 915
16 11.2 89.7 1.0X
-HOUR of interval 952 975
35 10.5 95.2 1.0X
-MINUTE of interval 938 955
14 10.7 93.8 1.0X
-SECOND of interval 1066 1080
24 9.4 106.6 0.9X
+cast to interval 989 1003
13 10.1 98.9 1.0X
+YEAR of interval 980 999
16 10.2 98.0 1.0X
+MONTH of interval 988 1000
15 10.1 98.8 1.0X
+DAY of interval 970 983
11 10.3 97.0 1.0X
+HOUR of interval 989 1000
13 10.1 98.9 1.0X
+MINUTE of interval 1019 1034
13 9.8 101.9 1.0X
+SECOND of interval 1084 1095
10 9.2 108.4 0.9X
Java HotSpot(TM) 64-Bit Server VM 11.0.5+10-LTS on Mac OS X 10.15.4
Intel(R) Core(TM) i9-9980HK CPU @ 2.40GHz
Invoke date_part for interval: Best Time(ms) Avg Time(ms)
Stdev(ms) Rate(M/s) Per Row(ns) Relative
------------------------------------------------------------------------------------------------------------------------
-cast to interval 944 960
21 10.6 94.4 1.0X
-YEAR of interval 978 1011
33 10.2 97.8 1.0X
-MONTH of interval 961 984
27 10.4 96.1 1.0X
-DAY of interval 921 927
7 10.9 92.1 1.0X
-HOUR of interval 913 919
8 11.0 91.3 1.0X
-MINUTE of interval 920 941
22 10.9 92.0 1.0X
-SECOND of interval 994 1012
19 10.1 99.4 0.9X
+cast to interval 955 969
15 10.5 95.5 1.0X
+YEAR of interval 1004 1007
3 10.0 100.4 1.0X
+MONTH of interval 989 990
2 10.1 98.9 1.0X
+DAY of interval 1006 1011
6 9.9 100.6 0.9X
+HOUR of interval 986 997
10 10.1 98.6 1.0X
+MINUTE of interval 1004 1014
15 10.0 100.4 1.0X
+SECOND of interval 1083 1088
6 9.2 108.3 0.9X
diff --git a/sql/core/benchmarks/ExtractBenchmark-results.txt
b/sql/core/benchmarks/ExtractBenchmark-results.txt
index bc766b4..11144a0 100644
--- a/sql/core/benchmarks/ExtractBenchmark-results.txt
+++ b/sql/core/benchmarks/ExtractBenchmark-results.txt
@@ -1,124 +1,104 @@
-Java HotSpot(TM) 64-Bit Server VM 1.8.0_231-b11 on Mac OS X 10.15.4
+Java HotSpot(TM) 64-Bit Server VM 1.8.0_251-b08 on Mac OS X 10.15.4
Intel(R) Core(TM) i9-9980HK CPU @ 2.40GHz
Invoke extract for timestamp: Best Time(ms) Avg Time(ms)
Stdev(ms) Rate(M/s) Per Row(ns) Relative
------------------------------------------------------------------------------------------------------------------------
-cast to timestamp 306 337
27 32.7 30.6 1.0X
-MILLENNIUM of timestamp 1002 1012
15 10.0 100.2 0.3X
-CENTURY of timestamp 1055 1064
8 9.5 105.5 0.3X
-DECADE of timestamp 940 968
36 10.6 94.0 0.3X
-YEAR of timestamp 922 938
27 10.8 92.2 0.3X
-ISOYEAR of timestamp 1036 1061
23 9.7 103.6 0.3X
-QUARTER of timestamp 1081 1098
27 9.2 108.1 0.3X
-MONTH of timestamp 876 888
17 11.4 87.6 0.3X
-WEEK of timestamp 1218 1259
55 8.2 121.8 0.3X
-DAY of timestamp 890 911
33 11.2 89.0 0.3X
-DAYOFWEEK of timestamp 1006 1019
17 9.9 100.6 0.3X
-DOW of timestamp 990 1006
22 10.1 99.0 0.3X
-ISODOW of timestamp 953 992
35 10.5 95.3 0.3X
-DOY of timestamp 883 889
6 11.3 88.3 0.3X
-HOUR of timestamp 661 667
6 15.1 66.1 0.5X
-MINUTE of timestamp 665 678
12 15.0 66.5 0.5X
-SECOND of timestamp 787 791
6 12.7 78.7 0.4X
-MILLISECONDS of timestamp 800 814
12 12.5 80.0 0.4X
-MICROSECONDS of timestamp 700 706
8 14.3 70.0 0.4X
-EPOCH of timestamp 793 819
40 12.6 79.3 0.4X
+cast to timestamp 292 310
16 34.3 29.2 1.0X
+YEAR of timestamp 847 875
26 11.8 84.7 0.3X
+YEAROFWEEK of timestamp 964 981
24 10.4 96.4 0.3X
+QUARTER of timestamp 1217 1219
2 8.2 121.7 0.2X
+MONTH of timestamp 835 844
10 12.0 83.5 0.3X
+WEEK of timestamp 1173 1183
15 8.5 117.3 0.2X
+DAY of timestamp 851 878
25 11.7 85.1 0.3X
+DAYOFWEEK of timestamp 946 970
22 10.6 94.6 0.3X
+DOW of timestamp 935 959
21 10.7 93.5 0.3X
+DOW_ISO of timestamp 947 961
13 10.6 94.7 0.3X
+DAYOFWEEK_ISO of timestamp 965 992
26 10.4 96.5 0.3X
+DOY of timestamp 886 904
26 11.3 88.6 0.3X
+HOUR of timestamp 697 700
4 14.3 69.7 0.4X
+MINUTE of timestamp 654 665
10 15.3 65.4 0.4X
+SECOND of timestamp 770 778
8 13.0 77.0 0.4X
-Java HotSpot(TM) 64-Bit Server VM 1.8.0_231-b11 on Mac OS X 10.15.4
+Java HotSpot(TM) 64-Bit Server VM 1.8.0_251-b08 on Mac OS X 10.15.4
Intel(R) Core(TM) i9-9980HK CPU @ 2.40GHz
Invoke date_part for timestamp: Best Time(ms) Avg Time(ms)
Stdev(ms) Rate(M/s) Per Row(ns) Relative
------------------------------------------------------------------------------------------------------------------------
-cast to timestamp 252 259
7 39.7 25.2 1.0X
-MILLENNIUM of timestamp 917 936
18 10.9 91.7 0.3X
-CENTURY of timestamp 943 952
12 10.6 94.3 0.3X
-DECADE of timestamp 863 867
5 11.6 86.3 0.3X
-YEAR of timestamp 839 854
16 11.9 83.9 0.3X
-ISOYEAR of timestamp 1023 1027
4 9.8 102.3 0.2X
-QUARTER of timestamp 1052 1060
10 9.5 105.2 0.2X
-MONTH of timestamp 835 846
14 12.0 83.5 0.3X
-WEEK of timestamp 1182 1198
14 8.5 118.2 0.2X
-DAY of timestamp 837 845
10 11.9 83.7 0.3X
-DAYOFWEEK of timestamp 947 962
14 10.6 94.7 0.3X
-DOW of timestamp 946 964
17 10.6 94.6 0.3X
-ISODOW of timestamp 934 946
13 10.7 93.4 0.3X
-DOY of timestamp 857 859
3 11.7 85.7 0.3X
-HOUR of timestamp 660 661
1 15.1 66.0 0.4X
-MINUTE of timestamp 649 654
4 15.4 64.9 0.4X
-SECOND of timestamp 801 805
5 12.5 80.1 0.3X
-MILLISECONDS of timestamp 820 825
7 12.2 82.0 0.3X
-MICROSECONDS of timestamp 700 708
12 14.3 70.0 0.4X
-EPOCH of timestamp 789 797
10 12.7 78.9 0.3X
+cast to timestamp 233 243
9 43.0 23.3 1.0X
+YEAR of timestamp 810 826
15 12.3 81.0 0.3X
+YEAROFWEEK of timestamp 996 1019
21 10.0 99.6 0.2X
+QUARTER of timestamp 1037 1049
11 9.6 103.7 0.2X
+MONTH of timestamp 822 852
30 12.2 82.2 0.3X
+WEEK of timestamp 1179 1220
35 8.5 117.9 0.2X
+DAY of timestamp 822 825
3 12.2 82.2 0.3X
+DAYOFWEEK of timestamp 937 941
3 10.7 93.7 0.2X
+DOW of timestamp 931 970
34 10.7 93.1 0.2X
+DOW_ISO of timestamp 927 948
22 10.8 92.7 0.3X
+DAYOFWEEK_ISO of timestamp 896 918
20 11.2 89.6 0.3X
+DOY of timestamp 863 891
25 11.6 86.3 0.3X
+HOUR of timestamp 639 645
6 15.7 63.9 0.4X
+MINUTE of timestamp 639 647
12 15.7 63.9 0.4X
+SECOND of timestamp 785 796
11 12.7 78.5 0.3X
-Java HotSpot(TM) 64-Bit Server VM 1.8.0_231-b11 on Mac OS X 10.15.4
+Java HotSpot(TM) 64-Bit Server VM 1.8.0_251-b08 on Mac OS X 10.15.4
Intel(R) Core(TM) i9-9980HK CPU @ 2.40GHz
Invoke extract for date: Best Time(ms) Avg Time(ms)
Stdev(ms) Rate(M/s) Per Row(ns) Relative
------------------------------------------------------------------------------------------------------------------------
-cast to date 747 751
6 13.4 74.7 1.0X
-MILLENNIUM of date 941 947
5 10.6 94.1 0.8X
-CENTURY of date 952 961
8 10.5 95.2 0.8X
-DECADE of date 864 877
15 11.6 86.4 0.9X
-YEAR of date 850 862
11 11.8 85.0 0.9X
-ISOYEAR of date 1031 1036
8 9.7 103.1 0.7X
-QUARTER of date 1038 1048
9 9.6 103.8 0.7X
-MONTH of date 852 865
12 11.7 85.2 0.9X
-WEEK of date 1174 1178
5 8.5 117.4 0.6X
-DAY of date 830 836
5 12.0 83.0 0.9X
-DAYOFWEEK of date 949 972
21 10.5 94.9 0.8X
-DOW of date 960 969
8 10.4 96.0 0.8X
-ISODOW of date 918 928
9 10.9 91.8 0.8X
-DOY of date 858 867
11 11.7 85.8 0.9X
-HOUR of date 1584 1591
10 6.3 158.4 0.5X
-MINUTE of date 1583 1589
6 6.3 158.3 0.5X
-SECOND of date 1721 1728
7 5.8 172.1 0.4X
-MILLISECONDS of date 1711 1731
17 5.8 171.1 0.4X
-MICROSECONDS of date 1647 1656
8 6.1 164.7 0.5X
-EPOCH of date 1796 1805
10 5.6 179.6 0.4X
+cast to date 690 693
3 14.5 69.0 1.0X
+YEAR of date 822 841
17 12.2 82.2 0.8X
+YEAROFWEEK of date 967 974
7 10.3 96.7 0.7X
+QUARTER of date 1034 1044
11 9.7 103.4 0.7X
+MONTH of date 831 836
5 12.0 83.1 0.8X
+WEEK of date 1152 1177
34 8.7 115.2 0.6X
+DAY of date 836 873
34 12.0 83.6 0.8X
+DAYOFWEEK of date 960 992
30 10.4 96.0 0.7X
+DOW of date 1011 1016
4 9.9 101.1 0.7X
+DOW_ISO of date 969 984
16 10.3 96.9 0.7X
+DAYOFWEEK_ISO of date 967 986
19 10.3 96.7 0.7X
+DOY of date 901 953
47 11.1 90.1 0.8X
+HOUR of date 1581 1586
5 6.3 158.1 0.4X
+MINUTE of date 1570 1584
13 6.4 157.0 0.4X
+SECOND of date 1713 1740
27 5.8 171.3 0.4X
-Java HotSpot(TM) 64-Bit Server VM 1.8.0_231-b11 on Mac OS X 10.15.4
+Java HotSpot(TM) 64-Bit Server VM 1.8.0_251-b08 on Mac OS X 10.15.4
Intel(R) Core(TM) i9-9980HK CPU @ 2.40GHz
Invoke date_part for date: Best Time(ms) Avg Time(ms)
Stdev(ms) Rate(M/s) Per Row(ns) Relative
------------------------------------------------------------------------------------------------------------------------
-cast to date 731 740
8 13.7 73.1 1.0X
-MILLENNIUM of date 939 948
12 10.7 93.9 0.8X
-CENTURY of date 929 940
10 10.8 92.9 0.8X
-DECADE of date 845 861
15 11.8 84.5 0.9X
-YEAR of date 848 853
5 11.8 84.8 0.9X
-ISOYEAR of date 1011 1027
14 9.9 101.1 0.7X
-QUARTER of date 1038 1050
12 9.6 103.8 0.7X
-MONTH of date 847 872
22 11.8 84.7 0.9X
-WEEK of date 1143 1152
13 8.7 114.3 0.6X
-DAY of date 844 848
5 11.8 84.4 0.9X
-DAYOFWEEK of date 947 957
9 10.6 94.7 0.8X
-DOW of date 963 972
8 10.4 96.3 0.8X
-ISODOW of date 906 919
11 11.0 90.6 0.8X
-DOY of date 866 868
3 11.6 86.6 0.8X
-HOUR of date 1599 1604
7 6.3 159.9 0.5X
-MINUTE of date 1578 1594
13 6.3 157.8 0.5X
-SECOND of date 1722 1731
11 5.8 172.2 0.4X
-MILLISECONDS of date 1699 1730
27 5.9 169.9 0.4X
-MICROSECONDS of date 1620 1623
5 6.2 162.0 0.5X
-EPOCH of date 1742 1767
32 5.7 174.2 0.4X
+cast to date 756 762
6 13.2 75.6 1.0X
+YEAR of date 843 857
13 11.9 84.3 0.9X
+YEAROFWEEK of date 1055 1065
16 9.5 105.5 0.7X
+QUARTER of date 1066 1073
6 9.4 106.6 0.7X
+MONTH of date 856 890
44 11.7 85.6 0.9X
+WEEK of date 1155 1204
59 8.7 115.5 0.7X
+DAY of date 749 762
12 13.3 74.9 1.0X
+DAYOFWEEK of date 850 865
15 11.8 85.0 0.9X
+DOW of date 878 893
16 11.4 87.8 0.9X
+DOW_ISO of date 865 869
5 11.6 86.5 0.9X
+DAYOFWEEK_ISO of date 914 967
76 10.9 91.4 0.8X
+DOY of date 789 792
4 12.7 78.9 1.0X
+HOUR of date 1558 1659
168 6.4 155.8 0.5X
+MINUTE of date 1581 1673
80 6.3 158.1 0.5X
+SECOND of date 1646 1881
319 6.1 164.6 0.5X
-Java HotSpot(TM) 64-Bit Server VM 1.8.0_231-b11 on Mac OS X 10.15.4
+Java HotSpot(TM) 64-Bit Server VM 1.8.0_251-b08 on Mac OS X 10.15.4
Intel(R) Core(TM) i9-9980HK CPU @ 2.40GHz
Invoke extract for interval: Best Time(ms) Avg Time(ms)
Stdev(ms) Rate(M/s) Per Row(ns) Relative
------------------------------------------------------------------------------------------------------------------------
-cast to interval 920 941
18 10.9 92.0 1.0X
-YEAR of interval 955 967
12 10.5 95.5 1.0X
-MONTH of interval 954 966
11 10.5 95.4 1.0X
-DAY of interval 967 970
4 10.3 96.7 1.0X
-HOUR of interval 959 975
21 10.4 95.9 1.0X
-MINUTE of interval 966 981
16 10.4 96.6 1.0X
-SECOND of interval 1048 1066
16 9.5 104.8 0.9X
+cast to interval 925 941
20 10.8 92.5 1.0X
+YEAR of interval 903 919
14 11.1 90.3 1.0X
+MONTH of interval 944 958
17 10.6 94.4 1.0X
+DAY of interval 917 925
7 10.9 91.7 1.0X
+HOUR of interval 925 940
17 10.8 92.5 1.0X
+MINUTE of interval 951 962
12 10.5 95.1 1.0X
+SECOND of interval 1017 1036
19 9.8 101.7 0.9X
-Java HotSpot(TM) 64-Bit Server VM 1.8.0_231-b11 on Mac OS X 10.15.4
+Java HotSpot(TM) 64-Bit Server VM 1.8.0_251-b08 on Mac OS X 10.15.4
Intel(R) Core(TM) i9-9980HK CPU @ 2.40GHz
Invoke date_part for interval: Best Time(ms) Avg Time(ms)
Stdev(ms) Rate(M/s) Per Row(ns) Relative
------------------------------------------------------------------------------------------------------------------------
-cast to interval 928 954
26 10.8 92.8 1.0X
-YEAR of interval 955 960
4 10.5 95.5 1.0X
-MONTH of interval 956 971
13 10.5 95.6 1.0X
-DAY of interval 964 1003
41 10.4 96.4 1.0X
-HOUR of interval 1057 1293
248 9.5 105.7 0.9X
-MINUTE of interval 975 1091
155 10.3 97.5 1.0X
-SECOND of interval 1104 1286
311 9.1 110.4 0.8X
+cast to interval 913 920
8 11.0 91.3 1.0X
+YEAR of interval 930 935
4 10.8 93.0 1.0X
+MONTH of interval 930 943
14 10.7 93.0 1.0X
+DAY of interval 933 946
12 10.7 93.3 1.0X
+HOUR of interval 951 953
3 10.5 95.1 1.0X
+MINUTE of interval 923 958
30 10.8 92.3 1.0X
+SECOND of interval 993 995
1 10.1 99.3 0.9X
diff --git a/sql/core/src/test/resources/sql-tests/inputs/date_part.sql
b/sql/core/src/test/resources/sql-tests/inputs/date_part.sql
deleted file mode 100644
index b36b98b..0000000
--- a/sql/core/src/test/resources/sql-tests/inputs/date_part.sql
+++ /dev/null
@@ -1,111 +0,0 @@
-CREATE TEMPORARY VIEW t AS select '2011-05-06 07:08:09.1234567' as c;
-
-select date_part('millennium', c) from t;
-select date_part('millennia', c) from t;
-select date_part('mil', c) from t;
-select date_part('mils', c) from t;
-
-select date_part('century', c) from t;
-select date_part('centuries', c) from t;
-select date_part('c', c) from t;
-select date_part('cent', c) from t;
-
-select date_part('decade', c) from t;
-select date_part('decades', c) from t;
-select date_part('dec', c) from t;
-select date_part('decs', c) from t;
-
-select date_part('year', c) from t;
-select date_part('y', c) from t;
-select date_part('years', c) from t;
-select date_part('yr', c) from t;
-select date_part('yrs', c) from t;
-
-select date_part('quarter', c) from t;
-select date_part('qtr', c) from t;
-
-select date_part('month', c) from t;
-select date_part('mon', c) from t;
-select date_part('mons', c) from t;
-select date_part('months', c) from t;
-
-select date_part('week', c) from t;
-select date_part('w', c) from t;
-select date_part('weeks', c) from t;
-
-select date_part('day', c) from t;
-select date_part('d', c) from t;
-select date_part('days', c) from t;
-
-select date_part('dayofweek', c) from t;
-
-select date_part('dow', c) from t;
-
-select date_part('isodow', c) from t;
-
-select date_part('doy', c) from t;
-
-select date_part('hour', c) from t;
-select date_part('h', c) from t;
-select date_part('hours', c) from t;
-select date_part('hr', c) from t;
-select date_part('hrs', c) from t;
-
-select date_part('minute', c) from t;
-select date_part('m', c) from t;
-select date_part('min', c) from t;
-select date_part('mins', c) from t;
-select date_part('minutes', c) from t;
-
-select date_part('second', c) from t;
-select date_part('s', c) from t;
-select date_part('sec', c) from t;
-select date_part('seconds', c) from t;
-select date_part('secs', c) from t;
-
-select date_part('not_supported', c) from t;
-
-select date_part(c, c) from t;
-
-select date_part(null, c) from t;
-
-CREATE TEMPORARY VIEW t2 AS select interval 1010 year 9 month 8 day 7 hour 6
minute 5 second 4 millisecond 3 microsecond as c;
-
-select date_part('year', c) from t2;
-select date_part('y', c) from t2;
-select date_part('years', c) from t2;
-select date_part('yr', c) from t2;
-select date_part('yrs', c) from t2;
-
-select date_part('month', c) from t2;
-select date_part('mon', c) from t2;
-select date_part('mons', c) from t2;
-select date_part('months', c) from t2;
-
-select date_part('day', c) from t2;
-select date_part('d', c) from t2;
-select date_part('days', c) from t2;
-
-select date_part('hour', c) from t2;
-select date_part('h', c) from t2;
-select date_part('hours', c) from t2;
-select date_part('hr', c) from t2;
-select date_part('hrs', c) from t2;
-
-select date_part('minute', c) from t2;
-select date_part('m', c) from t2;
-select date_part('min', c) from t2;
-select date_part('mins', c) from t2;
-select date_part('minutes', c) from t2;
-
-select date_part('second', c) from t2;
-select date_part('s', c) from t2;
-select date_part('sec', c) from t2;
-select date_part('seconds', c) from t2;
-select date_part('secs', c) from t2;
-
-select date_part('not_supported', c) from t2;
-
-select date_part(c, c) from t2;
-
-select date_part(null, c) from t2;
diff --git a/sql/core/src/test/resources/sql-tests/inputs/extract.sql
b/sql/core/src/test/resources/sql-tests/inputs/extract.sql
index 12068d2..cba7fc2 100644
--- a/sql/core/src/test/resources/sql-tests/inputs/extract.sql
+++ b/sql/core/src/test/resources/sql-tests/inputs/extract.sql
@@ -1,27 +1,12 @@
CREATE TEMPORARY VIEW t AS select '2011-05-06 07:08:09.1234567' as c, interval
10 year 20 month 30 day 40 hour 50 minute 6.7890 second as i;
-select extract(millennium from c) from t;
-select extract(millennia from c) from t;
-select extract(mil from c) from t;
-select extract(mils from c) from t;
-
-select extract(century from c) from t;
-select extract(centuries from c) from t;
-select extract(c from c) from t;
-select extract(cent from c)from t;
-
-select extract(decade from c) from t;
-select extract(decades from c) from t;
-select extract(dec from c) from t;
-select extract(decs from c) from t;
-
select extract(year from c), extract(year from i) from t;
select extract(y from c), extract(y from i) from t;
select extract(years from c), extract(years from i) from t;
select extract(yr from c), extract(yr from i) from t;
select extract(yrs from c), extract(yrs from i) from t;
-select extract(isoyear from c) from t;
+select extract(yearofweek from c) from t;
select extract(quarter from c) from t;
select extract(qtr from c) from t;
@@ -40,10 +25,10 @@ select extract(d from c), extract(d from i) from t;
select extract(days from c), extract(days from i) from t;
select extract(dayofweek from c) from t;
-
select extract(dow from c) from t;
-select extract(isodow from c) from t;
+select extract(dayofweek_iso from c) from t;
+select extract(dow_iso from c) from t;
select extract(doy from c) from t;
@@ -65,21 +50,62 @@ select extract(sec from c), extract(sec from i) from t;
select extract(seconds from c), extract(seconds from i) from t;
select extract(secs from c), extract(secs from i) from t;
-select extract(milliseconds from c) from t;
-select extract(msec from c) from t;
-select extract(msecs from c) from t;
-select extract(millisecon from c) from t;
-select extract(mseconds from c) from t;
-select extract(ms from c) from t;
+select extract(not_supported from c) from t;
+select extract(not_supported from i) from t;
+
+select date_part('year', c), date_part('year', i) from t;
+select date_part('y', c), date_part('y', i) from t;
+select date_part('years', c), date_part('years', i) from t;
+select date_part('yr', c), date_part('yr', i) from t;
+select date_part('yrs', c), date_part('yrs', i) from t;
-select extract(microseconds from c) from t;
-select extract(usec from c) from t;
-select extract(usecs from c) from t;
-select extract(useconds from c) from t;
-select extract(microsecon from c) from t;
-select extract(us from c) from t;
+select date_part('yearofweek', c) from t;
-select extract(epoch from c) from t;
+select date_part('quarter', c) from t;
+select date_part('qtr', c) from t;
-select extract(not_supported from c) from t;
-select extract(not_supported from i) from t;
+select date_part('month', c), date_part('month', i) from t;
+select date_part('mon', c), date_part('mon', i) from t;
+select date_part('mons', c), date_part('mons', i) from t;
+select date_part('months', c), date_part('months', i) from t;
+
+select date_part('week', c) from t;
+select date_part('w', c) from t;
+select date_part('weeks', c) from t;
+
+select date_part('day', c), date_part('day', i) from t;
+select date_part('d', c), date_part('d', i) from t;
+select date_part('days', c), date_part('days', i) from t;
+
+select date_part('dayofweek', c) from t;
+select date_part('dow', c) from t;
+
+select date_part('dayofweek_iso', c) from t;
+select date_part('dow_iso', c) from t;
+
+select date_part('doy', c) from t;
+
+select date_part('hour', c), date_part('hour', i) from t;
+select date_part('h', c), date_part('h', i) from t;
+select date_part('hours', c), date_part('hours', i) from t;
+select date_part('hr', c), date_part('hr', i) from t;
+select date_part('hrs', c), date_part('hrs', i) from t;
+
+select date_part('minute', c), date_part('minute', i) from t;
+select date_part('m', c), date_part('m', i) from t;
+select date_part('min', c), date_part('min', i) from t;
+select date_part('mins', c), date_part('mins', i) from t;
+select date_part('minutes', c), date_part('minutes', i) from t;
+
+select date_part('second', c), date_part('second', i) from t;
+select date_part('s', c), date_part('s', i) from t;
+select date_part('sec', c), date_part('sec', i) from t;
+select date_part('seconds', c), date_part('seconds', i) from t;
+select date_part('secs', c), date_part('secs', i) from t;
+
+select date_part('not_supported', c) from t;
+select date_part(c, c) from t;
+select date_part(null, c) from t;
+
+select date_part(i, i) from t;
+select date_part(null, i) from t;
\ No newline at end of file
diff --git a/sql/core/src/test/resources/sql-tests/inputs/postgreSQL/date.sql
b/sql/core/src/test/resources/sql-tests/inputs/postgreSQL/date.sql
index 0bab2f8..6985108 100644
--- a/sql/core/src/test/resources/sql-tests/inputs/postgreSQL/date.sql
+++ b/sql/core/src/test/resources/sql-tests/inputs/postgreSQL/date.sql
@@ -230,49 +230,49 @@ SELECT date 'tomorrow' - date 'yesterday' AS `Two days`;
--
-- epoch
--
-SELECT EXTRACT(EPOCH FROM DATE '1970-01-01'); -- 0
-SELECT EXTRACT(EPOCH FROM TIMESTAMP '1970-01-01'); -- 0
+-- SELECT EXTRACT(EPOCH FROM DATE '1970-01-01'); -- 0
+-- SELECT EXTRACT(EPOCH FROM TIMESTAMP '1970-01-01'); -- 0
-- SELECT EXTRACT(EPOCH FROM TIMESTAMPTZ '1970-01-01+00'); -- 0
--
-- century
--
-SELECT EXTRACT(CENTURY FROM TO_DATE('0101-12-31 BC', 'yyyy-MM-dd G')); -- -2
-SELECT EXTRACT(CENTURY FROM TO_DATE('0100-12-31 BC', 'yyyy-MM-dd G')); -- -1
-SELECT EXTRACT(CENTURY FROM TO_DATE('0001-12-31 BC', 'yyyy-MM-dd G')); -- -1
-SELECT EXTRACT(CENTURY FROM DATE '0001-01-01'); -- 1
-SELECT EXTRACT(CENTURY FROM DATE '0001-01-01 AD'); -- 1
-SELECT EXTRACT(CENTURY FROM DATE '1900-12-31'); -- 19
-SELECT EXTRACT(CENTURY FROM DATE '1901-01-01'); -- 20
-SELECT EXTRACT(CENTURY FROM DATE '2000-12-31'); -- 20
-SELECT EXTRACT(CENTURY FROM DATE '2001-01-01'); -- 21
-SELECT EXTRACT(CENTURY FROM CURRENT_DATE)>=21 AS True; -- true
+-- SELECT EXTRACT(CENTURY FROM TO_DATE('0101-12-31 BC', 'yyyy-MM-dd G')); -- -2
+-- SELECT EXTRACT(CENTURY FROM TO_DATE('0100-12-31 BC', 'yyyy-MM-dd G')); -- -1
+-- SELECT EXTRACT(CENTURY FROM TO_DATE('0001-12-31 BC', 'yyyy-MM-dd G')); -- -1
+-- SELECT EXTRACT(CENTURY FROM DATE '0001-01-01'); -- 1
+-- SELECT EXTRACT(CENTURY FROM DATE '0001-01-01 AD'); -- 1
+-- SELECT EXTRACT(CENTURY FROM DATE '1900-12-31'); -- 19
+-- SELECT EXTRACT(CENTURY FROM DATE '1901-01-01'); -- 20
+-- SELECT EXTRACT(CENTURY FROM DATE '2000-12-31'); -- 20
+-- SELECT EXTRACT(CENTURY FROM DATE '2001-01-01'); -- 21
+-- SELECT EXTRACT(CENTURY FROM CURRENT_DATE)>=21 AS True; -- true
--
-- millennium
--
-SELECT EXTRACT(MILLENNIUM FROM TO_DATE('0001-12-31 BC', 'yyyy-MM-dd G')); -- -1
-SELECT EXTRACT(MILLENNIUM FROM DATE '0001-01-01 AD'); -- 1
-SELECT EXTRACT(MILLENNIUM FROM DATE '1000-12-31'); -- 1
-SELECT EXTRACT(MILLENNIUM FROM DATE '1001-01-01'); -- 2
-SELECT EXTRACT(MILLENNIUM FROM DATE '2000-12-31'); -- 2
-SELECT EXTRACT(MILLENNIUM FROM DATE '2001-01-01'); -- 3
+-- SELECT EXTRACT(MILLENNIUM FROM TO_DATE('0001-12-31 BC', 'yyyy-MM-dd G'));
-- -1
+-- SELECT EXTRACT(MILLENNIUM FROM DATE '0001-01-01 AD'); -- 1
+-- SELECT EXTRACT(MILLENNIUM FROM DATE '1000-12-31'); -- 1
+-- SELECT EXTRACT(MILLENNIUM FROM DATE '1001-01-01'); -- 2
+-- SELECT EXTRACT(MILLENNIUM FROM DATE '2000-12-31'); -- 2
+-- SELECT EXTRACT(MILLENNIUM FROM DATE '2001-01-01'); -- 3
-- next test to be fixed on the turn of the next millennium;-)
-SELECT EXTRACT(MILLENNIUM FROM CURRENT_DATE); -- 3
+-- SELECT EXTRACT(MILLENNIUM FROM CURRENT_DATE); -- 3
--
-- decade
--
-SELECT EXTRACT(DECADE FROM DATE '1994-12-25'); -- 199
-SELECT EXTRACT(DECADE FROM DATE '0010-01-01'); -- 1
-SELECT EXTRACT(DECADE FROM DATE '0009-12-31'); -- 0
-SELECT EXTRACT(DECADE FROM TO_DATE('0001-01-01 BC', 'yyyy-MM-dd G')); -- 0
-SELECT EXTRACT(DECADE FROM TO_DATE('0002-12-31 BC', 'yyyy-MM-dd G')); -- -1
-SELECT EXTRACT(DECADE FROM TO_DATE('0011-01-01 BC', 'yyyy-MM-dd G')); -- -1
-SELECT EXTRACT(DECADE FROM TO_DATE('0012-12-31 BC', 'yyyy-MM-dd G')); -- -2
+-- SELECT EXTRACT(DECADE FROM DATE '1994-12-25'); -- 199
+-- SELECT EXTRACT(DECADE FROM DATE '0010-01-01'); -- 1
+-- SELECT EXTRACT(DECADE FROM DATE '0009-12-31'); -- 0
+-- SELECT EXTRACT(DECADE FROM TO_DATE('0001-01-01 BC', 'yyyy-MM-dd G')); -- 0
+-- SELECT EXTRACT(DECADE FROM TO_DATE('0002-12-31 BC', 'yyyy-MM-dd G')); -- -1
+-- SELECT EXTRACT(DECADE FROM TO_DATE('0011-01-01 BC', 'yyyy-MM-dd G')); -- -1
+-- SELECT EXTRACT(DECADE FROM TO_DATE('0012-12-31 BC', 'yyyy-MM-dd G')); -- -2
--
-- some other types:
--
-- on a timestamp.
-SELECT EXTRACT(CENTURY FROM NOW())>=21 AS True; -- true
-SELECT EXTRACT(CENTURY FROM TIMESTAMP '1970-03-20 04:30:00.00000'); -- 20
+-- SELECT EXTRACT(CENTURY FROM NOW())>=21 AS True; -- true
+-- SELECT EXTRACT(CENTURY FROM TIMESTAMP '1970-03-20 04:30:00.00000'); -- 20
-- on an interval
-- SELECT EXTRACT(CENTURY FROM INTERVAL '100 y'); -- 1
-- SELECT EXTRACT(CENTURY FROM INTERVAL '99 y'); -- 0
@@ -280,16 +280,16 @@ SELECT EXTRACT(CENTURY FROM TIMESTAMP '1970-03-20
04:30:00.00000'); -- 20
-- SELECT EXTRACT(CENTURY FROM INTERVAL '-100 y'); -- -1
--
-- test trunc function!
-SELECT DATE_TRUNC('MILLENNIUM', TIMESTAMP '1970-03-20 04:30:00.00000'); -- 1001
-SELECT DATE_TRUNC('MILLENNIUM', DATE '1970-03-20'); -- 1001-01-01
-SELECT DATE_TRUNC('CENTURY', TIMESTAMP '1970-03-20 04:30:00.00000'); -- 1901
-SELECT DATE_TRUNC('CENTURY', DATE '1970-03-20'); -- 1901
-SELECT DATE_TRUNC('CENTURY', DATE '2004-08-10'); -- 2001-01-01
-SELECT DATE_TRUNC('CENTURY', DATE '0002-02-04'); -- 0001-01-01
-SELECT DATE_TRUNC('CENTURY', TO_DATE('0055-08-10 BC', 'yyyy-MM-dd G')); --
0100-01-01 BC
-SELECT DATE_TRUNC('DECADE', DATE '1993-12-25'); -- 1990-01-01
-SELECT DATE_TRUNC('DECADE', DATE '0004-12-25'); -- 0001-01-01 BC
-SELECT DATE_TRUNC('DECADE', TO_DATE('0002-12-31 BC', 'yyyy-MM-dd G')); --
0011-01-01 BC
+-- SELECT DATE_TRUNC('MILLENNIUM', TIMESTAMP '1970-03-20 04:30:00.00000'); --
1001
+-- SELECT DATE_TRUNC('MILLENNIUM', DATE '1970-03-20'); -- 1001-01-01
+-- SELECT DATE_TRUNC('CENTURY', TIMESTAMP '1970-03-20 04:30:00.00000'); -- 1901
+-- SELECT DATE_TRUNC('CENTURY', DATE '1970-03-20'); -- 1901
+-- SELECT DATE_TRUNC('CENTURY', DATE '2004-08-10'); -- 2001-01-01
+-- SELECT DATE_TRUNC('CENTURY', DATE '0002-02-04'); -- 0001-01-01
+-- SELECT DATE_TRUNC('CENTURY', TO_DATE('0055-08-10 BC', 'yyyy-MM-dd G')); --
0100-01-01 BC
+-- SELECT DATE_TRUNC('DECADE', DATE '1993-12-25'); -- 1990-01-01
+-- SELECT DATE_TRUNC('DECADE', DATE '0004-12-25'); -- 0001-01-01 BC
+-- SELECT DATE_TRUNC('DECADE', TO_DATE('0002-12-31 BC', 'yyyy-MM-dd G')); --
0011-01-01 BC
-- [SPARK-29006] Support special date/timestamp values `infinity`/`-infinity`
--
diff --git
a/sql/core/src/test/resources/sql-tests/inputs/postgreSQL/timestamp.sql
b/sql/core/src/test/resources/sql-tests/inputs/postgreSQL/timestamp.sql
index bf69da2..ade29cc 100644
--- a/sql/core/src/test/resources/sql-tests/inputs/postgreSQL/timestamp.sql
+++ b/sql/core/src/test/resources/sql-tests/inputs/postgreSQL/timestamp.sql
@@ -200,15 +200,15 @@ SELECT '' AS `54`, d1 as `timestamp`,
date_part( 'minute', d1) AS `minute`, date_part( 'second', d1) AS `second`
FROM TIMESTAMP_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01';
-SELECT '' AS `54`, d1 as `timestamp`,
- date_part( 'quarter', d1) AS quarter, date_part( 'msec', d1) AS msec,
- date_part( 'usec', d1) AS usec
- FROM TIMESTAMP_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01';
-
-SELECT '' AS `54`, d1 as `timestamp`,
- date_part( 'isoyear', d1) AS isoyear, date_part( 'week', d1) AS week,
- date_part( 'dow', d1) AS dow
- FROM TIMESTAMP_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01';
+-- SELECT '' AS `54`, d1 as `timestamp`,
+-- date_part( 'quarter', d1) AS quarter, date_part( 'msec', d1) AS msec,
+-- date_part( 'usec', d1) AS usec
+-- FROM TIMESTAMP_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01';
+
+-- SELECT '' AS `54`, d1 as `timestamp`,
+-- date_part( 'isoyear', d1) AS isoyear, date_part( 'week', d1) AS week,
+-- date_part( 'dow', d1) AS dow
+-- FROM TIMESTAMP_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01';
-- [SPARK-28137] Data Type Formatting Functions
-- TO_CHAR()
diff --git a/sql/core/src/test/resources/sql-tests/results/date_part.sql.out
b/sql/core/src/test/resources/sql-tests/results/date_part.sql.out
deleted file mode 100644
index 028448b..0000000
--- a/sql/core/src/test/resources/sql-tests/results/date_part.sql.out
+++ /dev/null
@@ -1,670 +0,0 @@
--- Automatically generated by SQLQueryTestSuite
--- Number of queries: 83
-
-
--- !query
-CREATE TEMPORARY VIEW t AS select '2011-05-06 07:08:09.1234567' as c
--- !query schema
-struct<>
--- !query output
-
-
-
--- !query
-select date_part('millennium', c) from t
--- !query schema
-struct<date_part('millennium', t.`c`):int>
--- !query output
-3
-
-
--- !query
-select date_part('millennia', c) from t
--- !query schema
-struct<date_part('millennia', t.`c`):int>
--- !query output
-3
-
-
--- !query
-select date_part('mil', c) from t
--- !query schema
-struct<date_part('mil', t.`c`):int>
--- !query output
-3
-
-
--- !query
-select date_part('mils', c) from t
--- !query schema
-struct<date_part('mils', t.`c`):int>
--- !query output
-3
-
-
--- !query
-select date_part('century', c) from t
--- !query schema
-struct<date_part('century', t.`c`):int>
--- !query output
-21
-
-
--- !query
-select date_part('centuries', c) from t
--- !query schema
-struct<date_part('centuries', t.`c`):int>
--- !query output
-21
-
-
--- !query
-select date_part('c', c) from t
--- !query schema
-struct<date_part('c', t.`c`):int>
--- !query output
-21
-
-
--- !query
-select date_part('cent', c) from t
--- !query schema
-struct<date_part('cent', t.`c`):int>
--- !query output
-21
-
-
--- !query
-select date_part('decade', c) from t
--- !query schema
-struct<date_part('decade', t.`c`):int>
--- !query output
-201
-
-
--- !query
-select date_part('decades', c) from t
--- !query schema
-struct<date_part('decades', t.`c`):int>
--- !query output
-201
-
-
--- !query
-select date_part('dec', c) from t
--- !query schema
-struct<date_part('dec', t.`c`):int>
--- !query output
-201
-
-
--- !query
-select date_part('decs', c) from t
--- !query schema
-struct<date_part('decs', t.`c`):int>
--- !query output
-201
-
-
--- !query
-select date_part('year', c) from t
--- !query schema
-struct<date_part('year', t.`c`):int>
--- !query output
-2011
-
-
--- !query
-select date_part('y', c) from t
--- !query schema
-struct<date_part('y', t.`c`):int>
--- !query output
-2011
-
-
--- !query
-select date_part('years', c) from t
--- !query schema
-struct<date_part('years', t.`c`):int>
--- !query output
-2011
-
-
--- !query
-select date_part('yr', c) from t
--- !query schema
-struct<date_part('yr', t.`c`):int>
--- !query output
-2011
-
-
--- !query
-select date_part('yrs', c) from t
--- !query schema
-struct<date_part('yrs', t.`c`):int>
--- !query output
-2011
-
-
--- !query
-select date_part('quarter', c) from t
--- !query schema
-struct<date_part('quarter', t.`c`):int>
--- !query output
-2
-
-
--- !query
-select date_part('qtr', c) from t
--- !query schema
-struct<date_part('qtr', t.`c`):int>
--- !query output
-2
-
-
--- !query
-select date_part('month', c) from t
--- !query schema
-struct<date_part('month', t.`c`):int>
--- !query output
-5
-
-
--- !query
-select date_part('mon', c) from t
--- !query schema
-struct<date_part('mon', t.`c`):int>
--- !query output
-5
-
-
--- !query
-select date_part('mons', c) from t
--- !query schema
-struct<date_part('mons', t.`c`):int>
--- !query output
-5
-
-
--- !query
-select date_part('months', c) from t
--- !query schema
-struct<date_part('months', t.`c`):int>
--- !query output
-5
-
-
--- !query
-select date_part('week', c) from t
--- !query schema
-struct<date_part('week', t.`c`):int>
--- !query output
-18
-
-
--- !query
-select date_part('w', c) from t
--- !query schema
-struct<date_part('w', t.`c`):int>
--- !query output
-18
-
-
--- !query
-select date_part('weeks', c) from t
--- !query schema
-struct<date_part('weeks', t.`c`):int>
--- !query output
-18
-
-
--- !query
-select date_part('day', c) from t
--- !query schema
-struct<date_part('day', t.`c`):int>
--- !query output
-6
-
-
--- !query
-select date_part('d', c) from t
--- !query schema
-struct<date_part('d', t.`c`):int>
--- !query output
-6
-
-
--- !query
-select date_part('days', c) from t
--- !query schema
-struct<date_part('days', t.`c`):int>
--- !query output
-6
-
-
--- !query
-select date_part('dayofweek', c) from t
--- !query schema
-struct<date_part('dayofweek', t.`c`):int>
--- !query output
-6
-
-
--- !query
-select date_part('dow', c) from t
--- !query schema
-struct<date_part('dow', t.`c`):int>
--- !query output
-6
-
-
--- !query
-select date_part('isodow', c) from t
--- !query schema
-struct<date_part('isodow', t.`c`):int>
--- !query output
-5
-
-
--- !query
-select date_part('doy', c) from t
--- !query schema
-struct<date_part('doy', t.`c`):int>
--- !query output
-126
-
-
--- !query
-select date_part('hour', c) from t
--- !query schema
-struct<date_part('hour', t.`c`):int>
--- !query output
-7
-
-
--- !query
-select date_part('h', c) from t
--- !query schema
-struct<date_part('h', t.`c`):int>
--- !query output
-7
-
-
--- !query
-select date_part('hours', c) from t
--- !query schema
-struct<date_part('hours', t.`c`):int>
--- !query output
-7
-
-
--- !query
-select date_part('hr', c) from t
--- !query schema
-struct<date_part('hr', t.`c`):int>
--- !query output
-7
-
-
--- !query
-select date_part('hrs', c) from t
--- !query schema
-struct<date_part('hrs', t.`c`):int>
--- !query output
-7
-
-
--- !query
-select date_part('minute', c) from t
--- !query schema
-struct<date_part('minute', t.`c`):int>
--- !query output
-8
-
-
--- !query
-select date_part('m', c) from t
--- !query schema
-struct<date_part('m', t.`c`):int>
--- !query output
-8
-
-
--- !query
-select date_part('min', c) from t
--- !query schema
-struct<date_part('min', t.`c`):int>
--- !query output
-8
-
-
--- !query
-select date_part('mins', c) from t
--- !query schema
-struct<date_part('mins', t.`c`):int>
--- !query output
-8
-
-
--- !query
-select date_part('minutes', c) from t
--- !query schema
-struct<date_part('minutes', t.`c`):int>
--- !query output
-8
-
-
--- !query
-select date_part('second', c) from t
--- !query schema
-struct<date_part('second', t.`c`):decimal(8,6)>
--- !query output
-9.123456
-
-
--- !query
-select date_part('s', c) from t
--- !query schema
-struct<date_part('s', t.`c`):decimal(8,6)>
--- !query output
-9.123456
-
-
--- !query
-select date_part('sec', c) from t
--- !query schema
-struct<date_part('sec', t.`c`):decimal(8,6)>
--- !query output
-9.123456
-
-
--- !query
-select date_part('seconds', c) from t
--- !query schema
-struct<date_part('seconds', t.`c`):decimal(8,6)>
--- !query output
-9.123456
-
-
--- !query
-select date_part('secs', c) from t
--- !query schema
-struct<date_part('secs', t.`c`):decimal(8,6)>
--- !query output
-9.123456
-
-
--- !query
-select date_part('not_supported', c) from t
--- !query schema
-struct<>
--- !query output
-org.apache.spark.sql.AnalysisException
-Literals of type 'not_supported' are currently not supported for the string
type.;; line 1 pos 7
-
-
--- !query
-select date_part(c, c) from t
--- !query schema
-struct<>
--- !query output
-org.apache.spark.sql.AnalysisException
-The field parameter needs to be a foldable string value.;; line 1 pos 7
-
-
--- !query
-select date_part(null, c) from t
--- !query schema
-struct<date_part(NULL, t.`c`):double>
--- !query output
-NULL
-
-
--- !query
-CREATE TEMPORARY VIEW t2 AS select interval 1010 year 9 month 8 day 7 hour 6
minute 5 second 4 millisecond 3 microsecond as c
--- !query schema
-struct<>
--- !query output
-
-
-
--- !query
-select date_part('year', c) from t2
--- !query schema
-struct<date_part('year', t2.`c`):int>
--- !query output
-1010
-
-
--- !query
-select date_part('y', c) from t2
--- !query schema
-struct<date_part('y', t2.`c`):int>
--- !query output
-1010
-
-
--- !query
-select date_part('years', c) from t2
--- !query schema
-struct<date_part('years', t2.`c`):int>
--- !query output
-1010
-
-
--- !query
-select date_part('yr', c) from t2
--- !query schema
-struct<date_part('yr', t2.`c`):int>
--- !query output
-1010
-
-
--- !query
-select date_part('yrs', c) from t2
--- !query schema
-struct<date_part('yrs', t2.`c`):int>
--- !query output
-1010
-
-
--- !query
-select date_part('month', c) from t2
--- !query schema
-struct<date_part('month', t2.`c`):tinyint>
--- !query output
-9
-
-
--- !query
-select date_part('mon', c) from t2
--- !query schema
-struct<date_part('mon', t2.`c`):tinyint>
--- !query output
-9
-
-
--- !query
-select date_part('mons', c) from t2
--- !query schema
-struct<date_part('mons', t2.`c`):tinyint>
--- !query output
-9
-
-
--- !query
-select date_part('months', c) from t2
--- !query schema
-struct<date_part('months', t2.`c`):tinyint>
--- !query output
-9
-
-
--- !query
-select date_part('day', c) from t2
--- !query schema
-struct<date_part('day', t2.`c`):int>
--- !query output
-8
-
-
--- !query
-select date_part('d', c) from t2
--- !query schema
-struct<date_part('d', t2.`c`):int>
--- !query output
-8
-
-
--- !query
-select date_part('days', c) from t2
--- !query schema
-struct<date_part('days', t2.`c`):int>
--- !query output
-8
-
-
--- !query
-select date_part('hour', c) from t2
--- !query schema
-struct<date_part('hour', t2.`c`):bigint>
--- !query output
-7
-
-
--- !query
-select date_part('h', c) from t2
--- !query schema
-struct<date_part('h', t2.`c`):bigint>
--- !query output
-7
-
-
--- !query
-select date_part('hours', c) from t2
--- !query schema
-struct<date_part('hours', t2.`c`):bigint>
--- !query output
-7
-
-
--- !query
-select date_part('hr', c) from t2
--- !query schema
-struct<date_part('hr', t2.`c`):bigint>
--- !query output
-7
-
-
--- !query
-select date_part('hrs', c) from t2
--- !query schema
-struct<date_part('hrs', t2.`c`):bigint>
--- !query output
-7
-
-
--- !query
-select date_part('minute', c) from t2
--- !query schema
-struct<date_part('minute', t2.`c`):tinyint>
--- !query output
-6
-
-
--- !query
-select date_part('m', c) from t2
--- !query schema
-struct<date_part('m', t2.`c`):tinyint>
--- !query output
-6
-
-
--- !query
-select date_part('min', c) from t2
--- !query schema
-struct<date_part('min', t2.`c`):tinyint>
--- !query output
-6
-
-
--- !query
-select date_part('mins', c) from t2
--- !query schema
-struct<date_part('mins', t2.`c`):tinyint>
--- !query output
-6
-
-
--- !query
-select date_part('minutes', c) from t2
--- !query schema
-struct<date_part('minutes', t2.`c`):tinyint>
--- !query output
-6
-
-
--- !query
-select date_part('second', c) from t2
--- !query schema
-struct<date_part('second', t2.`c`):decimal(8,6)>
--- !query output
-5.004003
-
-
--- !query
-select date_part('s', c) from t2
--- !query schema
-struct<date_part('s', t2.`c`):decimal(8,6)>
--- !query output
-5.004003
-
-
--- !query
-select date_part('sec', c) from t2
--- !query schema
-struct<date_part('sec', t2.`c`):decimal(8,6)>
--- !query output
-5.004003
-
-
--- !query
-select date_part('seconds', c) from t2
--- !query schema
-struct<date_part('seconds', t2.`c`):decimal(8,6)>
--- !query output
-5.004003
-
-
--- !query
-select date_part('secs', c) from t2
--- !query schema
-struct<date_part('secs', t2.`c`):decimal(8,6)>
--- !query output
-5.004003
-
-
--- !query
-select date_part('not_supported', c) from t2
--- !query schema
-struct<>
--- !query output
-org.apache.spark.sql.AnalysisException
-Literals of type 'not_supported' are currently not supported for the interval
type.;; line 1 pos 7
-
-
--- !query
-select date_part(c, c) from t2
--- !query schema
-struct<>
--- !query output
-org.apache.spark.sql.AnalysisException
-The field parameter needs to be a foldable string value.;; line 1 pos 7
-
-
--- !query
-select date_part(null, c) from t2
--- !query schema
-struct<date_part(NULL, t2.`c`):double>
--- !query output
-NULL
diff --git a/sql/core/src/test/resources/sql-tests/results/extract.sql.out
b/sql/core/src/test/resources/sql-tests/results/extract.sql.out
index e6635e608..220acfb 100644
--- a/sql/core/src/test/resources/sql-tests/results/extract.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/extract.sql.out
@@ -1,5 +1,5 @@
-- Automatically generated by SQLQueryTestSuite
--- Number of queries: 65
+-- Number of queries: 84
-- !query
@@ -11,102 +11,6 @@ struct<>
-- !query
-select extract(millennium from c) from t
--- !query schema
-struct<extract('millennium' FROM t.`c`):int>
--- !query output
-3
-
-
--- !query
-select extract(millennia from c) from t
--- !query schema
-struct<extract('millennia' FROM t.`c`):int>
--- !query output
-3
-
-
--- !query
-select extract(mil from c) from t
--- !query schema
-struct<extract('mil' FROM t.`c`):int>
--- !query output
-3
-
-
--- !query
-select extract(mils from c) from t
--- !query schema
-struct<extract('mils' FROM t.`c`):int>
--- !query output
-3
-
-
--- !query
-select extract(century from c) from t
--- !query schema
-struct<extract('century' FROM t.`c`):int>
--- !query output
-21
-
-
--- !query
-select extract(centuries from c) from t
--- !query schema
-struct<extract('centuries' FROM t.`c`):int>
--- !query output
-21
-
-
--- !query
-select extract(c from c) from t
--- !query schema
-struct<extract('c' FROM t.`c`):int>
--- !query output
-21
-
-
--- !query
-select extract(cent from c)from t
--- !query schema
-struct<extract('cent' FROM t.`c`):int>
--- !query output
-21
-
-
--- !query
-select extract(decade from c) from t
--- !query schema
-struct<extract('decade' FROM t.`c`):int>
--- !query output
-201
-
-
--- !query
-select extract(decades from c) from t
--- !query schema
-struct<extract('decades' FROM t.`c`):int>
--- !query output
-201
-
-
--- !query
-select extract(dec from c) from t
--- !query schema
-struct<extract('dec' FROM t.`c`):int>
--- !query output
-201
-
-
--- !query
-select extract(decs from c) from t
--- !query schema
-struct<extract('decs' FROM t.`c`):int>
--- !query output
-201
-
-
--- !query
select extract(year from c), extract(year from i) from t
-- !query schema
struct<extract('year' FROM t.`c`):int,extract('year' FROM t.`i`):int>
@@ -147,9 +51,9 @@ struct<extract('yrs' FROM t.`c`):int,extract('yrs' FROM
t.`i`):int>
-- !query
-select extract(isoyear from c) from t
+select extract(yearofweek from c) from t
-- !query schema
-struct<extract('isoyear' FROM t.`c`):int>
+struct<extract('yearofweek' FROM t.`c`):int>
-- !query output
2011
@@ -267,9 +171,17 @@ struct<extract('dow' FROM t.`c`):int>
-- !query
-select extract(isodow from c) from t
+select extract(dayofweek_iso from c) from t
+-- !query schema
+struct<extract('dayofweek_iso' FROM t.`c`):int>
+-- !query output
+5
+
+
+-- !query
+select extract(dow_iso from c) from t
-- !query schema
-struct<extract('isodow' FROM t.`c`):int>
+struct<extract('dow_iso' FROM t.`c`):int>
-- !query output
5
@@ -403,111 +315,329 @@ struct<extract('secs' FROM
t.`c`):decimal(8,6),extract('secs' FROM t.`i`):decima
-- !query
-select extract(milliseconds from c) from t
+select extract(not_supported from c) from t
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+Literals of type 'not_supported' are currently not supported for the string
type.;; line 1 pos 7
+
+
+-- !query
+select extract(not_supported from i) from t
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+Literals of type 'not_supported' are currently not supported for the interval
type.;; line 1 pos 7
+
+
+-- !query
+select date_part('year', c), date_part('year', i) from t
-- !query schema
-struct<extract('milliseconds' FROM t.`c`):decimal(8,3)>
+struct<date_part('year', t.`c`):int,date_part('year', t.`i`):int>
-- !query output
-9123.456
+2011 11
-- !query
-select extract(msec from c) from t
+select date_part('y', c), date_part('y', i) from t
-- !query schema
-struct<extract('msec' FROM t.`c`):decimal(8,3)>
+struct<date_part('y', t.`c`):int,date_part('y', t.`i`):int>
-- !query output
-9123.456
+2011 11
-- !query
-select extract(msecs from c) from t
+select date_part('years', c), date_part('years', i) from t
-- !query schema
-struct<extract('msecs' FROM t.`c`):decimal(8,3)>
+struct<date_part('years', t.`c`):int,date_part('years', t.`i`):int>
-- !query output
-9123.456
+2011 11
-- !query
-select extract(millisecon from c) from t
+select date_part('yr', c), date_part('yr', i) from t
-- !query schema
-struct<extract('millisecon' FROM t.`c`):decimal(8,3)>
+struct<date_part('yr', t.`c`):int,date_part('yr', t.`i`):int>
-- !query output
-9123.456
+2011 11
-- !query
-select extract(mseconds from c) from t
+select date_part('yrs', c), date_part('yrs', i) from t
-- !query schema
-struct<extract('mseconds' FROM t.`c`):decimal(8,3)>
+struct<date_part('yrs', t.`c`):int,date_part('yrs', t.`i`):int>
-- !query output
-9123.456
+2011 11
-- !query
-select extract(ms from c) from t
+select date_part('yearofweek', c) from t
-- !query schema
-struct<extract('ms' FROM t.`c`):decimal(8,3)>
+struct<date_part('yearofweek', t.`c`):int>
-- !query output
-9123.456
+2011
-- !query
-select extract(microseconds from c) from t
+select date_part('quarter', c) from t
-- !query schema
-struct<extract('microseconds' FROM t.`c`):int>
+struct<date_part('quarter', t.`c`):int>
-- !query output
-9123456
+2
-- !query
-select extract(usec from c) from t
+select date_part('qtr', c) from t
-- !query schema
-struct<extract('usec' FROM t.`c`):int>
+struct<date_part('qtr', t.`c`):int>
-- !query output
-9123456
+2
-- !query
-select extract(usecs from c) from t
+select date_part('month', c), date_part('month', i) from t
-- !query schema
-struct<extract('usecs' FROM t.`c`):int>
+struct<date_part('month', t.`c`):int,date_part('month', t.`i`):tinyint>
-- !query output
-9123456
+5 8
-- !query
-select extract(useconds from c) from t
+select date_part('mon', c), date_part('mon', i) from t
-- !query schema
-struct<extract('useconds' FROM t.`c`):int>
+struct<date_part('mon', t.`c`):int,date_part('mon', t.`i`):tinyint>
-- !query output
-9123456
+5 8
-- !query
-select extract(microsecon from c) from t
+select date_part('mons', c), date_part('mons', i) from t
-- !query schema
-struct<extract('microsecon' FROM t.`c`):int>
+struct<date_part('mons', t.`c`):int,date_part('mons', t.`i`):tinyint>
-- !query output
-9123456
+5 8
-- !query
-select extract(us from c) from t
+select date_part('months', c), date_part('months', i) from t
-- !query schema
-struct<extract('us' FROM t.`c`):int>
+struct<date_part('months', t.`c`):int,date_part('months', t.`i`):tinyint>
-- !query output
-9123456
+5 8
-- !query
-select extract(epoch from c) from t
+select date_part('week', c) from t
-- !query schema
-struct<extract('epoch' FROM t.`c`):decimal(20,6)>
+struct<date_part('week', t.`c`):int>
-- !query output
-1304665689.123456
+18
-- !query
-select extract(not_supported from c) from t
+select date_part('w', c) from t
+-- !query schema
+struct<date_part('w', t.`c`):int>
+-- !query output
+18
+
+
+-- !query
+select date_part('weeks', c) from t
+-- !query schema
+struct<date_part('weeks', t.`c`):int>
+-- !query output
+18
+
+
+-- !query
+select date_part('day', c), date_part('day', i) from t
+-- !query schema
+struct<date_part('day', t.`c`):int,date_part('day', t.`i`):int>
+-- !query output
+6 30
+
+
+-- !query
+select date_part('d', c), date_part('d', i) from t
+-- !query schema
+struct<date_part('d', t.`c`):int,date_part('d', t.`i`):int>
+-- !query output
+6 30
+
+
+-- !query
+select date_part('days', c), date_part('days', i) from t
+-- !query schema
+struct<date_part('days', t.`c`):int,date_part('days', t.`i`):int>
+-- !query output
+6 30
+
+
+-- !query
+select date_part('dayofweek', c) from t
+-- !query schema
+struct<date_part('dayofweek', t.`c`):int>
+-- !query output
+6
+
+
+-- !query
+select date_part('dow', c) from t
+-- !query schema
+struct<date_part('dow', t.`c`):int>
+-- !query output
+6
+
+
+-- !query
+select date_part('dayofweek_iso', c) from t
+-- !query schema
+struct<date_part('dayofweek_iso', t.`c`):int>
+-- !query output
+5
+
+
+-- !query
+select date_part('dow_iso', c) from t
+-- !query schema
+struct<date_part('dow_iso', t.`c`):int>
+-- !query output
+5
+
+
+-- !query
+select date_part('doy', c) from t
+-- !query schema
+struct<date_part('doy', t.`c`):int>
+-- !query output
+126
+
+
+-- !query
+select date_part('hour', c), date_part('hour', i) from t
+-- !query schema
+struct<date_part('hour', t.`c`):int,date_part('hour', t.`i`):bigint>
+-- !query output
+7 40
+
+
+-- !query
+select date_part('h', c), date_part('h', i) from t
+-- !query schema
+struct<date_part('h', t.`c`):int,date_part('h', t.`i`):bigint>
+-- !query output
+7 40
+
+
+-- !query
+select date_part('hours', c), date_part('hours', i) from t
+-- !query schema
+struct<date_part('hours', t.`c`):int,date_part('hours', t.`i`):bigint>
+-- !query output
+7 40
+
+
+-- !query
+select date_part('hr', c), date_part('hr', i) from t
+-- !query schema
+struct<date_part('hr', t.`c`):int,date_part('hr', t.`i`):bigint>
+-- !query output
+7 40
+
+
+-- !query
+select date_part('hrs', c), date_part('hrs', i) from t
+-- !query schema
+struct<date_part('hrs', t.`c`):int,date_part('hrs', t.`i`):bigint>
+-- !query output
+7 40
+
+
+-- !query
+select date_part('minute', c), date_part('minute', i) from t
+-- !query schema
+struct<date_part('minute', t.`c`):int,date_part('minute', t.`i`):tinyint>
+-- !query output
+8 50
+
+
+-- !query
+select date_part('m', c), date_part('m', i) from t
+-- !query schema
+struct<date_part('m', t.`c`):int,date_part('m', t.`i`):tinyint>
+-- !query output
+8 50
+
+
+-- !query
+select date_part('min', c), date_part('min', i) from t
+-- !query schema
+struct<date_part('min', t.`c`):int,date_part('min', t.`i`):tinyint>
+-- !query output
+8 50
+
+
+-- !query
+select date_part('mins', c), date_part('mins', i) from t
+-- !query schema
+struct<date_part('mins', t.`c`):int,date_part('mins', t.`i`):tinyint>
+-- !query output
+8 50
+
+
+-- !query
+select date_part('minutes', c), date_part('minutes', i) from t
+-- !query schema
+struct<date_part('minutes', t.`c`):int,date_part('minutes', t.`i`):tinyint>
+-- !query output
+8 50
+
+
+-- !query
+select date_part('second', c), date_part('second', i) from t
+-- !query schema
+struct<date_part('second', t.`c`):decimal(8,6),date_part('second',
t.`i`):decimal(8,6)>
+-- !query output
+9.123456 6.789000
+
+
+-- !query
+select date_part('s', c), date_part('s', i) from t
+-- !query schema
+struct<date_part('s', t.`c`):decimal(8,6),date_part('s', t.`i`):decimal(8,6)>
+-- !query output
+9.123456 6.789000
+
+
+-- !query
+select date_part('sec', c), date_part('sec', i) from t
+-- !query schema
+struct<date_part('sec', t.`c`):decimal(8,6),date_part('sec',
t.`i`):decimal(8,6)>
+-- !query output
+9.123456 6.789000
+
+
+-- !query
+select date_part('seconds', c), date_part('seconds', i) from t
+-- !query schema
+struct<date_part('seconds', t.`c`):decimal(8,6),date_part('seconds',
t.`i`):decimal(8,6)>
+-- !query output
+9.123456 6.789000
+
+
+-- !query
+select date_part('secs', c), date_part('secs', i) from t
+-- !query schema
+struct<date_part('secs', t.`c`):decimal(8,6),date_part('secs',
t.`i`):decimal(8,6)>
+-- !query output
+9.123456 6.789000
+
+
+-- !query
+select date_part('not_supported', c) from t
-- !query schema
struct<>
-- !query output
@@ -516,9 +646,34 @@ Literals of type 'not_supported' are currently not
supported for the string type
-- !query
-select extract(not_supported from i) from t
+select date_part(c, c) from t
-- !query schema
struct<>
-- !query output
org.apache.spark.sql.AnalysisException
-Literals of type 'not_supported' are currently not supported for the interval
type.;; line 1 pos 7
+The field parameter needs to be a foldable string value.;; line 1 pos 7
+
+
+-- !query
+select date_part(null, c) from t
+-- !query schema
+struct<date_part(NULL, t.`c`):double>
+-- !query output
+NULL
+
+
+-- !query
+select date_part(i, i) from t
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+The field parameter needs to be a foldable string value.;; line 1 pos 7
+
+
+-- !query
+select date_part(null, i) from t
+-- !query schema
+struct<date_part(NULL, t.`i`):double>
+-- !query output
+NULL
diff --git
a/sql/core/src/test/resources/sql-tests/results/postgreSQL/date.sql.out
b/sql/core/src/test/resources/sql-tests/results/postgreSQL/date.sql.out
index 0dac13d..1d862ba 100755
--- a/sql/core/src/test/resources/sql-tests/results/postgreSQL/date.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/postgreSQL/date.sql.out
@@ -1,5 +1,5 @@
-- Automatically generated by SQLQueryTestSuite
--- Number of queries: 98
+-- Number of queries: 60
-- !query
@@ -572,310 +572,6 @@ struct<Two days:interval>
-- !query
-SELECT EXTRACT(EPOCH FROM DATE '1970-01-01')
--- !query schema
-struct<extract('EPOCH' FROM DATE '1970-01-01'):decimal(20,6)>
--- !query output
-0.000000
-
-
--- !query
-SELECT EXTRACT(EPOCH FROM TIMESTAMP '1970-01-01')
--- !query schema
-struct<extract('EPOCH' FROM TIMESTAMP '1970-01-01 00:00:00'):decimal(20,6)>
--- !query output
-0.000000
-
-
--- !query
-SELECT EXTRACT(CENTURY FROM TO_DATE('0101-12-31 BC', 'yyyy-MM-dd G'))
--- !query schema
-struct<extract('CENTURY' FROM to_date('0101-12-31 BC', 'yyyy-MM-dd G')):int>
--- !query output
--2
-
-
--- !query
-SELECT EXTRACT(CENTURY FROM TO_DATE('0100-12-31 BC', 'yyyy-MM-dd G'))
--- !query schema
-struct<extract('CENTURY' FROM to_date('0100-12-31 BC', 'yyyy-MM-dd G')):int>
--- !query output
--1
-
-
--- !query
-SELECT EXTRACT(CENTURY FROM TO_DATE('0001-12-31 BC', 'yyyy-MM-dd G'))
--- !query schema
-struct<extract('CENTURY' FROM to_date('0001-12-31 BC', 'yyyy-MM-dd G')):int>
--- !query output
--1
-
-
--- !query
-SELECT EXTRACT(CENTURY FROM DATE '0001-01-01')
--- !query schema
-struct<extract('CENTURY' FROM DATE '0001-01-01'):int>
--- !query output
-1
-
-
--- !query
-SELECT EXTRACT(CENTURY FROM DATE '0001-01-01 AD')
--- !query schema
-struct<extract('CENTURY' FROM DATE '0001-01-01'):int>
--- !query output
-1
-
-
--- !query
-SELECT EXTRACT(CENTURY FROM DATE '1900-12-31')
--- !query schema
-struct<extract('CENTURY' FROM DATE '1900-12-31'):int>
--- !query output
-19
-
-
--- !query
-SELECT EXTRACT(CENTURY FROM DATE '1901-01-01')
--- !query schema
-struct<extract('CENTURY' FROM DATE '1901-01-01'):int>
--- !query output
-20
-
-
--- !query
-SELECT EXTRACT(CENTURY FROM DATE '2000-12-31')
--- !query schema
-struct<extract('CENTURY' FROM DATE '2000-12-31'):int>
--- !query output
-20
-
-
--- !query
-SELECT EXTRACT(CENTURY FROM DATE '2001-01-01')
--- !query schema
-struct<extract('CENTURY' FROM DATE '2001-01-01'):int>
--- !query output
-21
-
-
--- !query
-SELECT EXTRACT(CENTURY FROM CURRENT_DATE)>=21 AS True
--- !query schema
-struct<True:boolean>
--- !query output
-true
-
-
--- !query
-SELECT EXTRACT(MILLENNIUM FROM TO_DATE('0001-12-31 BC', 'yyyy-MM-dd G'))
--- !query schema
-struct<extract('MILLENNIUM' FROM to_date('0001-12-31 BC', 'yyyy-MM-dd G')):int>
--- !query output
--1
-
-
--- !query
-SELECT EXTRACT(MILLENNIUM FROM DATE '0001-01-01 AD')
--- !query schema
-struct<extract('MILLENNIUM' FROM DATE '0001-01-01'):int>
--- !query output
-1
-
-
--- !query
-SELECT EXTRACT(MILLENNIUM FROM DATE '1000-12-31')
--- !query schema
-struct<extract('MILLENNIUM' FROM DATE '1000-12-31'):int>
--- !query output
-1
-
-
--- !query
-SELECT EXTRACT(MILLENNIUM FROM DATE '1001-01-01')
--- !query schema
-struct<extract('MILLENNIUM' FROM DATE '1001-01-01'):int>
--- !query output
-2
-
-
--- !query
-SELECT EXTRACT(MILLENNIUM FROM DATE '2000-12-31')
--- !query schema
-struct<extract('MILLENNIUM' FROM DATE '2000-12-31'):int>
--- !query output
-2
-
-
--- !query
-SELECT EXTRACT(MILLENNIUM FROM DATE '2001-01-01')
--- !query schema
-struct<extract('MILLENNIUM' FROM DATE '2001-01-01'):int>
--- !query output
-3
-
-
--- !query
-SELECT EXTRACT(MILLENNIUM FROM CURRENT_DATE)
--- !query schema
-struct<extract('MILLENNIUM' FROM current_date()):int>
--- !query output
-3
-
-
--- !query
-SELECT EXTRACT(DECADE FROM DATE '1994-12-25')
--- !query schema
-struct<extract('DECADE' FROM DATE '1994-12-25'):int>
--- !query output
-199
-
-
--- !query
-SELECT EXTRACT(DECADE FROM DATE '0010-01-01')
--- !query schema
-struct<extract('DECADE' FROM DATE '0010-01-01'):int>
--- !query output
-1
-
-
--- !query
-SELECT EXTRACT(DECADE FROM DATE '0009-12-31')
--- !query schema
-struct<extract('DECADE' FROM DATE '0009-12-31'):int>
--- !query output
-0
-
-
--- !query
-SELECT EXTRACT(DECADE FROM TO_DATE('0001-01-01 BC', 'yyyy-MM-dd G'))
--- !query schema
-struct<extract('DECADE' FROM to_date('0001-01-01 BC', 'yyyy-MM-dd G')):int>
--- !query output
-0
-
-
--- !query
-SELECT EXTRACT(DECADE FROM TO_DATE('0002-12-31 BC', 'yyyy-MM-dd G'))
--- !query schema
-struct<extract('DECADE' FROM to_date('0002-12-31 BC', 'yyyy-MM-dd G')):int>
--- !query output
--1
-
-
--- !query
-SELECT EXTRACT(DECADE FROM TO_DATE('0011-01-01 BC', 'yyyy-MM-dd G'))
--- !query schema
-struct<extract('DECADE' FROM to_date('0011-01-01 BC', 'yyyy-MM-dd G')):int>
--- !query output
--1
-
-
--- !query
-SELECT EXTRACT(DECADE FROM TO_DATE('0012-12-31 BC', 'yyyy-MM-dd G'))
--- !query schema
-struct<extract('DECADE' FROM to_date('0012-12-31 BC', 'yyyy-MM-dd G')):int>
--- !query output
--2
-
-
--- !query
-SELECT EXTRACT(CENTURY FROM NOW())>=21 AS True
--- !query schema
-struct<True:boolean>
--- !query output
-true
-
-
--- !query
-SELECT EXTRACT(CENTURY FROM TIMESTAMP '1970-03-20 04:30:00.00000')
--- !query schema
-struct<extract('CENTURY' FROM TIMESTAMP '1970-03-20 04:30:00'):int>
--- !query output
-20
-
-
--- !query
-SELECT DATE_TRUNC('MILLENNIUM', TIMESTAMP '1970-03-20 04:30:00.00000')
--- !query schema
-struct<date_trunc(MILLENNIUM, TIMESTAMP '1970-03-20 04:30:00'):timestamp>
--- !query output
-1001-01-01 00:00:00
-
-
--- !query
-SELECT DATE_TRUNC('MILLENNIUM', DATE '1970-03-20')
--- !query schema
-struct<date_trunc(MILLENNIUM, CAST(DATE '1970-03-20' AS TIMESTAMP)):timestamp>
--- !query output
-1001-01-01 00:00:00
-
-
--- !query
-SELECT DATE_TRUNC('CENTURY', TIMESTAMP '1970-03-20 04:30:00.00000')
--- !query schema
-struct<date_trunc(CENTURY, TIMESTAMP '1970-03-20 04:30:00'):timestamp>
--- !query output
-1901-01-01 00:00:00
-
-
--- !query
-SELECT DATE_TRUNC('CENTURY', DATE '1970-03-20')
--- !query schema
-struct<date_trunc(CENTURY, CAST(DATE '1970-03-20' AS TIMESTAMP)):timestamp>
--- !query output
-1901-01-01 00:00:00
-
-
--- !query
-SELECT DATE_TRUNC('CENTURY', DATE '2004-08-10')
--- !query schema
-struct<date_trunc(CENTURY, CAST(DATE '2004-08-10' AS TIMESTAMP)):timestamp>
--- !query output
-2001-01-01 00:00:00
-
-
--- !query
-SELECT DATE_TRUNC('CENTURY', DATE '0002-02-04')
--- !query schema
-struct<date_trunc(CENTURY, CAST(DATE '0002-02-04' AS TIMESTAMP)):timestamp>
--- !query output
-0001-01-01 00:00:00
-
-
--- !query
-SELECT DATE_TRUNC('CENTURY', TO_DATE('0055-08-10 BC', 'yyyy-MM-dd G'))
--- !query schema
-struct<date_trunc(CENTURY, CAST(to_date('0055-08-10 BC', 'yyyy-MM-dd G') AS
TIMESTAMP)):timestamp>
--- !query output
--0099-01-01 00:00:00
-
-
--- !query
-SELECT DATE_TRUNC('DECADE', DATE '1993-12-25')
--- !query schema
-struct<date_trunc(DECADE, CAST(DATE '1993-12-25' AS TIMESTAMP)):timestamp>
--- !query output
-1990-01-01 00:00:00
-
-
--- !query
-SELECT DATE_TRUNC('DECADE', DATE '0004-12-25')
--- !query schema
-struct<date_trunc(DECADE, CAST(DATE '0004-12-25' AS TIMESTAMP)):timestamp>
--- !query output
-0000-01-01 00:00:00
-
-
--- !query
-SELECT DATE_TRUNC('DECADE', TO_DATE('0002-12-31 BC', 'yyyy-MM-dd G'))
--- !query schema
-struct<date_trunc(DECADE, CAST(to_date('0002-12-31 BC', 'yyyy-MM-dd G') AS
TIMESTAMP)):timestamp>
--- !query output
--0010-01-01 00:00:00
-
-
--- !query
select make_date(2013, 7, 15)
-- !query schema
struct<make_date(2013, 7, 15):date>
diff --git
a/sql/core/src/test/resources/sql-tests/results/postgreSQL/timestamp.sql.out
b/sql/core/src/test/resources/sql-tests/results/postgreSQL/timestamp.sql.out
index abfce91..5b0b636 100644
--- a/sql/core/src/test/resources/sql-tests/results/postgreSQL/timestamp.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/postgreSQL/timestamp.sql.out
@@ -1,5 +1,5 @@
-- Automatically generated by SQLQueryTestSuite
--- Number of queries: 32
+-- Number of queries: 30
-- !query
@@ -266,36 +266,6 @@
struct<54:string,timestamp:timestamp,year:int,month:int,day:int,hour:int,minute:
-- !query
-SELECT '' AS `54`, d1 as `timestamp`,
- date_part( 'quarter', d1) AS quarter, date_part( 'msec', d1) AS msec,
- date_part( 'usec', d1) AS usec
- FROM TIMESTAMP_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01'
--- !query schema
-struct<54:string,timestamp:timestamp,quarter:int,msec:decimal(8,3),usec:int>
--- !query output
- 1969-12-31 16:00:00 4 0.000 0
- 1997-01-02 00:00:00 1 0.000 0
- 1997-01-02 03:04:05 1 5000.000 5000000
- 1997-02-10 17:32:01 1 1000.000 1000000
- 2001-09-22 18:19:20 3 20000.000 20000000
-
-
--- !query
-SELECT '' AS `54`, d1 as `timestamp`,
- date_part( 'isoyear', d1) AS isoyear, date_part( 'week', d1) AS week,
- date_part( 'dow', d1) AS dow
- FROM TIMESTAMP_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01'
--- !query schema
-struct<54:string,timestamp:timestamp,isoyear:int,week:int,dow:int>
--- !query output
- 1969-12-31 16:00:00 1970 1 4
- 1997-01-02 00:00:00 1997 1 5
- 1997-01-02 03:04:05 1997 1 5
- 1997-02-10 17:32:01 1997 7 2
- 2001-09-22 18:19:20 2001 38 7
-
-
--- !query
SELECT make_timestamp(2014,12,28,6,30,45.887)
-- !query schema
struct<make_timestamp(2014, 12, 28, 6, 30, CAST(45.887 AS
DECIMAL(8,6))):timestamp>
diff --git
a/sql/core/src/test/scala/org/apache/spark/sql/execution/benchmark/ExtractBenchmark.scala
b/sql/core/src/test/scala/org/apache/spark/sql/execution/benchmark/ExtractBenchmark.scala
index 6984d94..287854d 100644
---
a/sql/core/src/test/scala/org/apache/spark/sql/execution/benchmark/ExtractBenchmark.scala
+++
b/sql/core/src/test/scala/org/apache/spark/sql/execution/benchmark/ExtractBenchmark.scala
@@ -86,12 +86,8 @@ object ExtractBenchmark extends SqlBasedBenchmark {
override def runBenchmarkSuite(mainArgs: Array[String]): Unit = {
val N = 10000000L
- val datetimeFields = Seq(
- "MILLENNIUM", "CENTURY", "DECADE", "YEAR",
- "ISOYEAR", "QUARTER", "MONTH", "WEEK",
- "DAY", "DAYOFWEEK", "DOW", "ISODOW",
- "DOY", "HOUR", "MINUTE", "SECOND",
- "MILLISECONDS", "MICROSECONDS", "EPOCH")
+ val datetimeFields = Seq("YEAR", "YEAROFWEEK", "QUARTER", "MONTH", "WEEK",
"DAY", "DAYOFWEEK",
+ "DOW", "DOW_ISO", "DAYOFWEEK_ISO", "DOY", "HOUR", "MINUTE", "SECOND")
val intervalFields = Seq("YEAR", "MONTH", "DAY", "HOUR", "MINUTE",
"SECOND")
val settings = Map(
"timestamp" -> datetimeFields,
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
