You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@tajo.apache.org by si...@apache.org on 2014/01/23 02:44:48 UTC
git commit: TAJO-449: Implement extract() function (Keuntae Park)
Updated Branches:
refs/heads/master d06dd852e -> f3f4b0453
TAJO-449: Implement extract() function (Keuntae Park)
Project: http://git-wip-us.apache.org/repos/asf/incubator-tajo/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-tajo/commit/f3f4b045
Tree: http://git-wip-us.apache.org/repos/asf/incubator-tajo/tree/f3f4b045
Diff: http://git-wip-us.apache.org/repos/asf/incubator-tajo/diff/f3f4b045
Branch: refs/heads/master
Commit: f3f4b04538d479560e9c734a6422c26f17387946
Parents: d06dd85
Author: sirpkt <si...@apache.org>
Authored: Thu Jan 23 10:33:53 2014 +0900
Committer: sirpkt <si...@apache.org>
Committed: Thu Jan 23 10:33:53 2014 +0900
----------------------------------------------------------------------
CHANGES.txt | 2 +
.../java/org/apache/tajo/datum/DateDatum.java | 16 ++
.../org/apache/tajo/datum/TimestampDatum.java | 23 +-
.../org/apache/tajo/engine/parser/SQLLexer.g4 | 49 ++++
.../org/apache/tajo/engine/parser/SQLParser.g4 | 51 ++++
.../function/datetime/DatePartFromDate.java | 194 ++++++++++++++
.../function/datetime/DatePartFromTime.java | 140 ++++++++++
.../datetime/DatePartFromTimestamp.java | 259 +++++++++++++++++++
.../apache/tajo/engine/parser/SQLAnalyzer.java | 41 ++-
.../tajo/engine/eval/TestSQLDateTimeTypes.java | 4 +-
.../engine/function/TestDateTimeFunctions.java | 179 +++++++++++++
11 files changed, 951 insertions(+), 7 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-tajo/blob/f3f4b045/CHANGES.txt
----------------------------------------------------------------------
diff --git a/CHANGES.txt b/CHANGES.txt
index 020787c..91e7a64 100644
--- a/CHANGES.txt
+++ b/CHANGES.txt
@@ -4,6 +4,8 @@ Release 0.8.0 - unreleased
NEW FEATURES
+ TAJO-449: Implement extract() function. (Keuntae Park)
+
TAJO-482: Implements listing functions and describing a specified
function. (hyoungjunkim via hyunsik)
http://git-wip-us.apache.org/repos/asf/incubator-tajo/blob/f3f4b045/tajo-common/src/main/java/org/apache/tajo/datum/DateDatum.java
----------------------------------------------------------------------
diff --git a/tajo-common/src/main/java/org/apache/tajo/datum/DateDatum.java b/tajo-common/src/main/java/org/apache/tajo/datum/DateDatum.java
index a7f3072..89a4a99 100644
--- a/tajo-common/src/main/java/org/apache/tajo/datum/DateDatum.java
+++ b/tajo-common/src/main/java/org/apache/tajo/datum/DateDatum.java
@@ -57,6 +57,10 @@ public class DateDatum extends Datum {
this(Bytes.toInt(bytes));
}
+ public int getCenturyOfEra() {
+ return date.getCenturyOfEra();
+ }
+
public int getYear() {
return date.getYear();
}
@@ -65,6 +69,14 @@ public class DateDatum extends Datum {
return date.getMonthOfYear();
}
+ public int getWeekyear() {
+ return date.getWeekyear();
+ }
+
+ public int getWeekOfWeekyear() {
+ return date.getWeekOfWeekyear();
+ }
+
public int getDayOfWeek() {
return date.getDayOfWeek();
}
@@ -73,6 +85,10 @@ public class DateDatum extends Datum {
return date.getDayOfMonth();
}
+ public int getDayOfYear() {
+ return date.getDayOfYear();
+ }
+
public String toString() {
return asChars();
}
http://git-wip-us.apache.org/repos/asf/incubator-tajo/blob/f3f4b045/tajo-common/src/main/java/org/apache/tajo/datum/TimestampDatum.java
----------------------------------------------------------------------
diff --git a/tajo-common/src/main/java/org/apache/tajo/datum/TimestampDatum.java b/tajo-common/src/main/java/org/apache/tajo/datum/TimestampDatum.java
index ff3cc3b..039439c 100644
--- a/tajo-common/src/main/java/org/apache/tajo/datum/TimestampDatum.java
+++ b/tajo-common/src/main/java/org/apache/tajo/datum/TimestampDatum.java
@@ -18,6 +18,7 @@
package org.apache.tajo.datum;
+import org.apache.commons.lang.StringUtils;
import org.apache.tajo.common.TajoDataTypes;
import org.apache.tajo.datum.exception.InvalidOperationException;
import org.apache.tajo.util.Bytes;
@@ -66,6 +67,14 @@ public class TimestampDatum extends Datum {
return dateTime;
}
+ public int getCenturyOfEra() {
+ return dateTime.getCenturyOfEra();
+ }
+
+ public int getEra() {
+ return dateTime.getEra();
+ }
+
public int getYear() {
return dateTime.getYear();
}
@@ -78,6 +87,10 @@ public class TimestampDatum extends Datum {
return dateTime.getDayOfWeek();
}
+ public int getDayOfYear() {
+ return dateTime.getDayOfYear();
+ }
+
public int getDayOfMonth() {
return dateTime.getDayOfMonth();
}
@@ -102,6 +115,14 @@ public class TimestampDatum extends Datum {
return dateTime.getMillisOfSecond();
}
+ public int getWeekyear() {
+ return dateTime.getWeekyear();
+ }
+
+ public int getWeekOfWeekyear() {
+ return dateTime.getWeekOfWeekyear();
+ }
+
public String toString() {
return asChars();
}
@@ -109,7 +130,7 @@ public class TimestampDatum extends Datum {
@Override
public String asChars() {
if (getMillisOfSecond() > 0) {
- return dateTime.toString(FRACTION_FORMATTER);
+ return StringUtils.stripEnd(dateTime.toString(FRACTION_FORMATTER), "0");
} else {
return dateTime.toString(DEFAULT_FORMATTER);
}
http://git-wip-us.apache.org/repos/asf/incubator-tajo/blob/f3f4b045/tajo-core/tajo-core-backend/src/main/antlr4/org/apache/tajo/engine/parser/SQLLexer.g4
----------------------------------------------------------------------
diff --git a/tajo-core/tajo-core-backend/src/main/antlr4/org/apache/tajo/engine/parser/SQLLexer.g4 b/tajo-core/tajo-core-backend/src/main/antlr4/org/apache/tajo/engine/parser/SQLLexer.g4
index 184304a..2151387 100644
--- a/tajo-core/tajo-core-backend/src/main/antlr4/org/apache/tajo/engine/parser/SQLLexer.g4
+++ b/tajo-core/tajo-core-backend/src/main/antlr4/org/apache/tajo/engine/parser/SQLLexer.g4
@@ -224,6 +224,7 @@ AVG : A V G;
BETWEEN : B E T W E E N;
BY : B Y;
+CENTURY : C E N T U R Y;
CHARACTER : C H A R A C T E R;
COLLECT : C O L L E C T;
COALESCE : C O A L E S C E;
@@ -231,27 +232,45 @@ COLUMN : C O L U M N;
COUNT : C O U N T;
CUBE : C U B E;
+DAY : D A Y;
DEC : D E C;
+DECADE : D E C A D E;
+DOW : D O W;
+DOY : D O Y;
DROP : D R O P;
+EPOCH : E P O C H;
EVERY : E V E R Y;
EXISTS : E X I S T S;
EXTERNAL : E X T E R N A L;
+EXTRACT : E X T R A C T;
LESS : L E S S;
FUSION : F U S I O N;
+HOUR : H O U R;
+
INTERSECTION : I N T E R S E C T I O N;
+ISODOW : I S O D O W;
+ISOYEAR : I S O Y E A R;
MAXVALUE : M A X V A L U E;
+MICROSECONDS : M I C R O S E C O N D S;
+MILLENNIUM : M I L L E N N I U M;
+MILLISECONDS : M I L L I S E C O N D S;
+MINUTE : M I N U T E;
+MONTH : M O N T H;
PARTITION : P A R T I T I O N;
PARTITIONS : P A R T I T I O N S;
PURGE : P U R G E;
+QUARTER : Q U A R T E R;
+
ROLLUP : R O L L U P;
+SECOND : S E C O N D;
SIMILAR : S I M I L A R;
STDDEV_POP : S T D D E V UNDERLINE P O P;
STDDEV_SAMP : S T D D E V UNDERLINE S A M P;
@@ -260,40 +279,63 @@ SUM : S U M;
TABLESPACE : T A B L E S P A C E;
THAN : T H A N;
+TIMEZONE: T I M E Z O N E;
+TIMEZONE_HOUR: T I M E Z O N E UNDERLINE H O U R;
+TIMEZONE_MINUTE: T I M E Z O N E UNDERLINE M I N U T E;
TRIM : T R I M;
TO : T O;
VALUES : V A L U E S;
+WEEK : W E E K;
+
+YEAR : Y E A R;
+
Nonreserved_keywords
: AVG
| BETWEEN
| BY
+ | CENTURY
| CHARACTER
| COALESCE
| COLLECT
| COLUMN
| COUNT
| CUBE
+ | DAY
| DEC
+ | DECADE
+ | DOW
+ | DOY
| DROP
+ | EPOCH
| EVERY
| EXISTS
| EXTERNAL
+ | EXTRACT
| FUSION
| HASH
| INTERSECTION
+ | ISODOW
+ | ISOYEAR
| LESS
| LIST
| MAXVALUE
+ | MICROSECONDS
+ | MILLENNIUM
+ | MILLISECONDS
+ | MINUTE
+ | MONTH
| PARTITION
| PARTITIONS
+ | QUARTER
| ROLLUP
+ | SECOND
| SIMILAR
| STDDEV_POP
| STDDEV_SAMP
@@ -302,10 +344,17 @@ Nonreserved_keywords
| TABLESPACE
| THAN
+ | TIMEZONE
+ | TIMEZONE_HOUR
+ | TIMEZONE_MINUTE
| TRIM
| TO
| VALUES
+
+ | WEEK
+
+ | YEAR
;
/*
http://git-wip-us.apache.org/repos/asf/incubator-tajo/blob/f3f4b045/tajo-core/tajo-core-backend/src/main/antlr4/org/apache/tajo/engine/parser/SQLParser.g4
----------------------------------------------------------------------
diff --git a/tajo-core/tajo-core-backend/src/main/antlr4/org/apache/tajo/engine/parser/SQLParser.g4 b/tajo-core/tajo-core-backend/src/main/antlr4/org/apache/tajo/engine/parser/SQLParser.g4
index 9b693c7..0b7ced0 100644
--- a/tajo-core/tajo-core-backend/src/main/antlr4/org/apache/tajo/engine/parser/SQLParser.g4
+++ b/tajo-core/tajo-core-backend/src/main/antlr4/org/apache/tajo/engine/parser/SQLParser.g4
@@ -511,6 +511,7 @@ array
numeric_primary
: value_expression_primary (CAST_EXPRESSION cast_target)*
+ | numeric_value_function
;
sign
@@ -519,6 +520,35 @@ sign
/*
===============================================================================
+ 6.27 <numeric value function>
+===============================================================================
+*/
+
+numeric_value_function
+ : extract_expression
+ ;
+
+extract_expression
+ : EXTRACT LEFT_PAREN extract_field_string=extract_field FROM extract_source RIGHT_PAREN
+ ;
+
+extract_field
+ : primary_datetime_field
+ | time_zone_field
+ | extended_datetime_field
+ ;
+
+time_zone_field
+ : TIMEZONE | TIMEZONE_HOUR | TIMEZONE_MINUTE
+ ;
+
+extract_source
+ : column_reference
+ | datetime_literal
+ ;
+
+/*
+===============================================================================
6.28 <string value expression>
===============================================================================
*/
@@ -1089,6 +1119,27 @@ unique_predicate
/*
===============================================================================
+ 10.1 <interval qualifier>
+
+ Specify the precision of an interval data type.
+===============================================================================
+*/
+
+primary_datetime_field
+ : non_second_primary_datetime_field
+ | SECOND
+ ;
+
+non_second_primary_datetime_field
+ : YEAR | MONTH | DAY | HOUR | MINUTE
+ ;
+
+extended_datetime_field
+ : CENTURY | DECADE | DOW | DOY | EPOCH | ISODOW | ISOYEAR | MICROSECONDS | MILLENNIUM | MILLISECONDS | QUARTER | WEEK
+ ;
+
+/*
+===============================================================================
10.4 <routine invocation>
Invoke an SQL-invoked routine.
http://git-wip-us.apache.org/repos/asf/incubator-tajo/blob/f3f4b045/tajo-core/tajo-core-backend/src/main/java/org/apache/tajo/engine/function/datetime/DatePartFromDate.java
----------------------------------------------------------------------
diff --git a/tajo-core/tajo-core-backend/src/main/java/org/apache/tajo/engine/function/datetime/DatePartFromDate.java b/tajo-core/tajo-core-backend/src/main/java/org/apache/tajo/engine/function/datetime/DatePartFromDate.java
new file mode 100644
index 0000000..a010a7d
--- /dev/null
+++ b/tajo-core/tajo-core-backend/src/main/java/org/apache/tajo/engine/function/datetime/DatePartFromDate.java
@@ -0,0 +1,194 @@
+/**
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.tajo.engine.function.datetime;
+
+import org.apache.tajo.catalog.Column;
+import org.apache.tajo.common.TajoDataTypes;
+import org.apache.tajo.datum.*;
+import org.apache.tajo.engine.function.GeneralFunction;
+import org.apache.tajo.engine.function.annotation.Description;
+import org.apache.tajo.engine.function.annotation.ParamTypes;
+import org.apache.tajo.storage.Tuple;
+
+import static org.apache.tajo.common.TajoDataTypes.Type.*;
+
+@Description(
+ functionName = "date_part",
+ description = "Extract field from date",
+ example = "> SELECT date_part('month', date '2014-01-17');\n"
+ + "1.0",
+ returnType = TajoDataTypes.Type.FLOAT8,
+ paramTypes = {@ParamTypes(paramTypes = {TajoDataTypes.Type.TEXT, TajoDataTypes.Type.DATE})}
+)
+public class DatePartFromDate extends GeneralFunction {
+ public DatePartFromDate() {
+ super(new Column[] {
+ new Column("target", FLOAT8),
+ new Column("source", TEXT)
+ });
+ }
+
+ private DatePartExtractorFromDate extractor = null;
+
+ @Override
+ public Datum eval(Tuple params) {
+ Datum target = params.get(0);
+ DateDatum date = null;
+
+ if(target instanceof NullDatum || params.get(1) instanceof NullDatum) {
+ return NullDatum.get();
+ }
+
+ if(params.get(1) instanceof DateDatum) {
+ date = (DateDatum)(params.get(1));
+ } else {
+ return NullDatum.get();
+ }
+
+ if (extractor == null) {
+ String extractType = target.asChars().toLowerCase();
+
+ if (extractType.equals("century")) {
+ extractor = new CenturyExtractorFromDate();
+ } else if (extractType.equals("day")) {
+ extractor = new DayExtractorFromDate();
+ } else if (extractType.equals("decade")) {
+ extractor = new DecadeExtractorFromDate();
+ } else if (extractType.equals("dow")) {
+ extractor = new DowExtractorFromDate();
+ } else if (extractType.equals("doy")) {
+ extractor = new DoyExtractorFromDate();
+ } else if (extractType.equals("isodow")) {
+ extractor = new ISODowExtractorFromDate();
+ } else if (extractType.equals("isoyear")) {
+ extractor = new ISOYearExtractorFromDate();
+ } else if (extractType.equals("millennium")) {
+ extractor = new MillenniumExtractorFromDate();
+ } else if (extractType.equals("month")) {
+ extractor = new MonthExtractorFromDate();
+ } else if (extractType.equals("quarter")) {
+ extractor = new QuarterExtractorFromDate();
+ } else if (extractType.equals("week")) {
+ extractor = new WeekExtractorFromDate();
+ } else if (extractType.equals("year")) {
+ extractor = new YearExtractorFromDate();
+ } else {
+ extractor = new NullExtractorFromDate();
+ }
+ }
+
+ return extractor.extract(date);
+ }
+
+ private interface DatePartExtractorFromDate {
+ public Datum extract(DateDatum date);
+ }
+
+ private class CenturyExtractorFromDate implements DatePartExtractorFromDate {
+ @Override
+ public Datum extract(DateDatum date) {
+ return DatumFactory.createFloat8((double) date.getCenturyOfEra());
+ }
+ }
+
+ private class DayExtractorFromDate implements DatePartExtractorFromDate {
+ @Override
+ public Datum extract(DateDatum date) {
+ return DatumFactory.createFloat8((double) date.getDayOfMonth());
+ }
+ }
+
+ private class DecadeExtractorFromDate implements DatePartExtractorFromDate {
+ @Override
+ public Datum extract(DateDatum date) {
+ return DatumFactory.createFloat8((double) (date.getYear() / 10));
+ }
+ }
+
+ private class DowExtractorFromDate implements DatePartExtractorFromDate {
+ @Override
+ public Datum extract(DateDatum date) {
+ Integer tdow = date.getDayOfWeek();
+ return DatumFactory.createFloat8((double) ((tdow == 7) ? 0 : tdow));
+ }
+ }
+
+ private class DoyExtractorFromDate implements DatePartExtractorFromDate {
+ @Override
+ public Datum extract(DateDatum date) {
+ return DatumFactory.createFloat8((double) date.getDayOfYear());
+ }
+ }
+
+ private class ISODowExtractorFromDate implements DatePartExtractorFromDate {
+ @Override
+ public Datum extract(DateDatum date) {
+ return DatumFactory.createFloat8((double) date.getDayOfWeek());
+ }
+ }
+
+ private class ISOYearExtractorFromDate implements DatePartExtractorFromDate {
+ @Override
+ public Datum extract(DateDatum date) {
+ return DatumFactory.createFloat8((double) date.getWeekyear());
+ }
+ }
+
+ private class MillenniumExtractorFromDate implements DatePartExtractorFromDate {
+ @Override
+ public Datum extract(DateDatum date) {
+ return DatumFactory.createFloat8((double) (((date.getYear() - 1) / 1000) + 1));
+ }
+ }
+
+ private class MonthExtractorFromDate implements DatePartExtractorFromDate {
+ @Override
+ public Datum extract(DateDatum date) {
+ return DatumFactory.createFloat8((double) date.getMonthOfYear());
+ }
+ }
+
+ private class QuarterExtractorFromDate implements DatePartExtractorFromDate {
+ @Override
+ public Datum extract(DateDatum date) {
+ return DatumFactory.createFloat8((double) (((date.getMonthOfYear() - 1) / 3) + 1));
+ }
+ }
+
+ private class WeekExtractorFromDate implements DatePartExtractorFromDate {
+ @Override
+ public Datum extract(DateDatum date) {
+ return DatumFactory.createFloat8((double) date.getWeekOfWeekyear());
+ }
+ }
+
+ private class YearExtractorFromDate implements DatePartExtractorFromDate {
+ @Override
+ public Datum extract(DateDatum date) {
+ return DatumFactory.createFloat8((double) date.getYear());
+ }
+ }
+
+ private class NullExtractorFromDate implements DatePartExtractorFromDate {
+ @Override
+ public Datum extract(DateDatum date) {
+ return NullDatum.get();
+ }
+ }
+}
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/incubator-tajo/blob/f3f4b045/tajo-core/tajo-core-backend/src/main/java/org/apache/tajo/engine/function/datetime/DatePartFromTime.java
----------------------------------------------------------------------
diff --git a/tajo-core/tajo-core-backend/src/main/java/org/apache/tajo/engine/function/datetime/DatePartFromTime.java b/tajo-core/tajo-core-backend/src/main/java/org/apache/tajo/engine/function/datetime/DatePartFromTime.java
new file mode 100644
index 0000000..28e14fb
--- /dev/null
+++ b/tajo-core/tajo-core-backend/src/main/java/org/apache/tajo/engine/function/datetime/DatePartFromTime.java
@@ -0,0 +1,140 @@
+/**
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.tajo.engine.function.datetime;
+
+import org.apache.tajo.catalog.Column;
+import org.apache.tajo.common.TajoDataTypes;
+import org.apache.tajo.datum.*;
+import org.apache.tajo.engine.function.GeneralFunction;
+import org.apache.tajo.engine.function.annotation.Description;
+import org.apache.tajo.engine.function.annotation.ParamTypes;
+import org.apache.tajo.storage.Tuple;
+
+import static org.apache.tajo.common.TajoDataTypes.Type.*;
+
+@Description(
+ functionName = "date_part",
+ description = "Extract field from time",
+ example = "> SELECT date_part('second', time '10:09:37.5');\n"
+ + "37.5",
+ returnType = TajoDataTypes.Type.FLOAT8,
+ paramTypes = {@ParamTypes(paramTypes = {TajoDataTypes.Type.TEXT, TajoDataTypes.Type.TIME})}
+)
+public class DatePartFromTime extends GeneralFunction {
+ public DatePartFromTime() {
+ super(new Column[] {
+ new Column("target", FLOAT8),
+ new Column("source", TEXT)
+ });
+ }
+
+ private DatePartExtractorFromTime extractor = null;
+
+ @Override
+ public Datum eval(Tuple params) {
+ Datum target = params.get(0);
+ TimeDatum time = null;
+
+ if(target instanceof NullDatum || params.get(1) instanceof NullDatum) {
+ return NullDatum.get();
+ }
+
+ if(params.get(1) instanceof TimeDatum) {
+ time = (TimeDatum)(params.get(1));
+ } else {
+ return NullDatum.get();
+ }
+
+ if (extractor == null) {
+ String extractType = target.asChars().toLowerCase();
+
+ if (extractType.equals("hour")) {
+ extractor = new HourExtractorFromTime();
+ } else if (extractType.equals("microseconds")) {
+ extractor = new MicrosecondsExtractorFromTime();
+ } else if (extractType.equals("milliseconds")) {
+ extractor = new MillisecondsExtractorFromTime();
+ } else if (extractType.equals("minute")) {
+ extractor = new MinuteExtractorFromTime();
+ } else if (extractType.equals("second")) {
+ extractor = new SecondExtractorFromTime();
+ } else if (extractType.equals("timezone")) {
+ extractor = new NullExtractorFromTime();
+ } else if (extractType.equals("timezone_hour")) {
+ extractor = new NullExtractorFromTime();
+ } else if (extractType.equals("timezone_minute")) {
+ extractor = new NullExtractorFromTime();
+ } else {
+ extractor = new NullExtractorFromTime();
+ }
+ }
+
+ return extractor.extract(time);
+ }
+
+ private interface DatePartExtractorFromTime {
+ public Datum extract(TimeDatum time);
+ }
+
+ private class HourExtractorFromTime implements DatePartExtractorFromTime {
+ @Override
+ public Datum extract(TimeDatum time) {
+ return DatumFactory.createFloat8((double) time.getHourOfDay());
+ }
+ }
+
+ private class MicrosecondsExtractorFromTime implements DatePartExtractorFromTime {
+ @Override
+ public Datum extract(TimeDatum time) {
+ return DatumFactory.createFloat8((double) (time.getSecondOfMinute() * 1000000 + time.getMillisOfSecond() * 1000));
+ }
+ }
+
+ private class MillisecondsExtractorFromTime implements DatePartExtractorFromTime {
+ @Override
+ public Datum extract(TimeDatum time) {
+ return DatumFactory.createFloat8((double) (time.getSecondOfMinute() * 1000 + time.getMillisOfSecond()));
+ }
+ }
+
+ private class MinuteExtractorFromTime implements DatePartExtractorFromTime {
+ @Override
+ public Datum extract(TimeDatum time) {
+ return DatumFactory.createFloat8((double) time.getMinuteOfHour());
+ }
+ }
+
+ private class SecondExtractorFromTime implements DatePartExtractorFromTime {
+ @Override
+ public Datum extract(TimeDatum time) {
+ if (time.getMillisOfSecond() != 0) {
+ return DatumFactory.createFloat8(time.getSecondOfMinute() + (((double) time.getMillisOfSecond()) / 1000));
+ } else {
+ return DatumFactory.createFloat8((double) time.getSecondOfMinute());
+ }
+ }
+ }
+
+ private class NullExtractorFromTime implements DatePartExtractorFromTime {
+ @Override
+ public Datum extract(TimeDatum time) {
+ return NullDatum.get();
+ }
+ }
+}
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/incubator-tajo/blob/f3f4b045/tajo-core/tajo-core-backend/src/main/java/org/apache/tajo/engine/function/datetime/DatePartFromTimestamp.java
----------------------------------------------------------------------
diff --git a/tajo-core/tajo-core-backend/src/main/java/org/apache/tajo/engine/function/datetime/DatePartFromTimestamp.java b/tajo-core/tajo-core-backend/src/main/java/org/apache/tajo/engine/function/datetime/DatePartFromTimestamp.java
new file mode 100644
index 0000000..3b46929
--- /dev/null
+++ b/tajo-core/tajo-core-backend/src/main/java/org/apache/tajo/engine/function/datetime/DatePartFromTimestamp.java
@@ -0,0 +1,259 @@
+/**
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.tajo.engine.function.datetime;
+
+import org.apache.tajo.catalog.Column;
+import org.apache.tajo.common.TajoDataTypes;
+import org.apache.tajo.datum.*;
+import org.apache.tajo.engine.function.GeneralFunction;
+import org.apache.tajo.engine.function.annotation.Description;
+import org.apache.tajo.engine.function.annotation.ParamTypes;
+import org.apache.tajo.storage.Tuple;
+
+import static org.apache.tajo.common.TajoDataTypes.Type.*;
+
+@Description(
+ functionName = "date_part",
+ description = "Extract field from timestamp",
+ example = "> SELECT date_part('year', timestamp '2014-01-17 10:09:37.5');\n"
+ + "2014.0",
+ returnType = TajoDataTypes.Type.FLOAT8,
+ paramTypes = {@ParamTypes(paramTypes = {TajoDataTypes.Type.TEXT, TajoDataTypes.Type.TIMESTAMP})}
+)
+public class DatePartFromTimestamp extends GeneralFunction {
+ public DatePartFromTimestamp() {
+ super(new Column[] {
+ new Column("target", FLOAT8),
+ new Column("source", TEXT)
+ });
+ }
+
+ private DatePartExtractorFromTimestamp extractor = null;
+
+ @Override
+ public Datum eval(Tuple params) {
+ Datum target = params.get(0);
+ TimestampDatum timestamp = null;
+
+ if(target instanceof NullDatum || params.get(1) instanceof NullDatum) {
+ return NullDatum.get();
+ }
+
+ if(params.get(1) instanceof TimestampDatum) {
+ timestamp = (TimestampDatum)(params.get(1));
+ } else {
+ return NullDatum.get();
+ }
+
+ if (extractor == null) {
+ String extractType = target.asChars().toLowerCase();
+
+ if (extractType.equals("century")) {
+ extractor = new CenturyExtractorFromTimestamp();
+ } else if (extractType.equals("day")) {
+ extractor = new DayExtractorFromTimestamp();
+ } else if (extractType.equals("decade")) {
+ extractor = new DecadeExtractorFromTimestamp();
+ } else if (extractType.equals("dow")) {
+ extractor = new DowExtractorFromTimestamp();
+ } else if (extractType.equals("doy")) {
+ extractor = new DoyExtractorFromTimestamp();
+ } else if (extractType.equals("epoch")) {
+ extractor = new EpochExtractorFromTimestamp();
+ } else if (extractType.equals("hour")) {
+ extractor = new HourExtractorFromTimestamp();
+ } else if (extractType.equals("isodow")) {
+ extractor = new ISODowExtractorFromTimestamp();
+ } else if (extractType.equals("isoyear")) {
+ extractor = new ISOYearExtractorFromTimestamp();
+ } else if (extractType.equals("microseconds")) {
+ extractor = new MicrosecondsExtractorFromTimestamp();
+ } else if (extractType.equals("millennium")) {
+ extractor = new MillenniumExtractorFromTimestamp();
+ } else if (extractType.equals("milliseconds")) {
+ extractor = new MillisecondsExtractorFromTimestamp();
+ } else if (extractType.equals("minute")) {
+ extractor = new MinuteExtractorFromTimestamp();
+ } else if (extractType.equals("month")) {
+ extractor = new MonthExtractorFromTimestamp();
+ } else if (extractType.equals("quarter")) {
+ extractor = new QuarterExtractorFromTimestamp();
+ } else if (extractType.equals("second")) {
+ extractor = new SecondExtractorFromTimestamp();
+ } else if (extractType.equals("timezone")) {
+ extractor = new NullExtractorFromTimestamp();
+ } else if (extractType.equals("timezone_hour")) {
+ extractor = new NullExtractorFromTimestamp();
+ } else if (extractType.equals("timezone_minute")) {
+ extractor = new NullExtractorFromTimestamp();
+ } else if (extractType.equals("week")) {
+ extractor = new WeekExtractorFromTimestamp();
+ } else if (extractType.equals("year")) {
+ extractor = new YearExtractorFromTimestamp();
+ } else {
+ extractor = new NullExtractorFromTimestamp();
+ }
+ }
+
+ return extractor.extract(timestamp);
+ }
+
+ private interface DatePartExtractorFromTimestamp {
+ public Datum extract(TimestampDatum timestamp);
+ }
+
+ private class CenturyExtractorFromTimestamp implements DatePartExtractorFromTimestamp {
+ @Override
+ public Datum extract(TimestampDatum timestamp) {
+ return DatumFactory.createFloat8((double) timestamp.getCenturyOfEra());
+ }
+ }
+
+ private class DayExtractorFromTimestamp implements DatePartExtractorFromTimestamp {
+ @Override
+ public Datum extract(TimestampDatum timestamp) {
+ return DatumFactory.createFloat8((double) timestamp.getDayOfMonth());
+ }
+ }
+
+ private class DecadeExtractorFromTimestamp implements DatePartExtractorFromTimestamp {
+ @Override
+ public Datum extract(TimestampDatum timestamp) {
+ return DatumFactory.createFloat8((double) (timestamp.getYear() / 10));
+ }
+ }
+
+ private class DowExtractorFromTimestamp implements DatePartExtractorFromTimestamp {
+ @Override
+ public Datum extract(TimestampDatum timestamp) {
+ Integer tdow = timestamp.getDayOfWeek();
+ return DatumFactory.createFloat8((double) ((tdow == 7) ? 0 : tdow));
+ }
+ }
+
+ private class DoyExtractorFromTimestamp implements DatePartExtractorFromTimestamp {
+ @Override
+ public Datum extract(TimestampDatum timestamp) {
+ return DatumFactory.createFloat8((double) timestamp.getDayOfYear());
+ }
+ }
+
+ private class EpochExtractorFromTimestamp implements DatePartExtractorFromTimestamp {
+ @Override
+ public Datum extract(TimestampDatum timestamp) {
+ return DatumFactory.createFloat8((double) timestamp.getUnixTime());
+ }
+ }
+
+ private class HourExtractorFromTimestamp implements DatePartExtractorFromTimestamp {
+ @Override
+ public Datum extract(TimestampDatum timestamp) {
+ return DatumFactory.createFloat8((double) timestamp.getHourOfDay());
+ }
+ }
+
+ private class ISODowExtractorFromTimestamp implements DatePartExtractorFromTimestamp {
+ @Override
+ public Datum extract(TimestampDatum timestamp) {
+ return DatumFactory.createFloat8((double) timestamp.getDayOfWeek());
+ }
+ }
+
+ private class ISOYearExtractorFromTimestamp implements DatePartExtractorFromTimestamp {
+ @Override
+ public Datum extract(TimestampDatum timestamp) {
+ return DatumFactory.createFloat8((double) timestamp.getWeekyear());
+ }
+ }
+
+ private class MicrosecondsExtractorFromTimestamp implements DatePartExtractorFromTimestamp {
+ @Override
+ public Datum extract(TimestampDatum timestamp) {
+ return DatumFactory.createFloat8((double) (timestamp.getSecondOfMinute() * 1000000 + timestamp.getMillisOfSecond() * 1000));
+ }
+ }
+
+ private class MillenniumExtractorFromTimestamp implements DatePartExtractorFromTimestamp {
+ @Override
+ public Datum extract(TimestampDatum timestamp) {
+ return DatumFactory.createFloat8((double) (((timestamp.getYear() - 1) / 1000) + 1));
+ }
+ }
+
+ private class MillisecondsExtractorFromTimestamp implements DatePartExtractorFromTimestamp {
+ @Override
+ public Datum extract(TimestampDatum timestamp) {
+ return DatumFactory.createFloat8((double) (timestamp.getSecondOfMinute() * 1000 + timestamp.getMillisOfSecond()));
+ }
+ }
+
+ private class MinuteExtractorFromTimestamp implements DatePartExtractorFromTimestamp {
+ @Override
+ public Datum extract(TimestampDatum timestamp) {
+ return DatumFactory.createFloat8((double) timestamp.getMinuteOfHour());
+ }
+ }
+
+ private class MonthExtractorFromTimestamp implements DatePartExtractorFromTimestamp {
+ @Override
+ public Datum extract(TimestampDatum timestamp) {
+ return DatumFactory.createFloat8((double) timestamp.getMonthOfYear());
+ }
+ }
+
+ private class QuarterExtractorFromTimestamp implements DatePartExtractorFromTimestamp {
+ @Override
+ public Datum extract(TimestampDatum timestamp) {
+ return DatumFactory.createFloat8((double) (((timestamp.getMonthOfYear() - 1) / 3) + 1));
+ }
+ }
+
+ private class SecondExtractorFromTimestamp implements DatePartExtractorFromTimestamp {
+ @Override
+ public Datum extract(TimestampDatum timestamp) {
+ if (timestamp.getMillisOfSecond() != 0) {
+ return DatumFactory.createFloat8(timestamp.getSecondOfMinute() + (((double) timestamp.getMillisOfSecond()) / 1000));
+ } else {
+ return DatumFactory.createFloat8((double) timestamp.getSecondOfMinute());
+ }
+ }
+ }
+
+ private class WeekExtractorFromTimestamp implements DatePartExtractorFromTimestamp {
+ @Override
+ public Datum extract(TimestampDatum timestamp) {
+ return DatumFactory.createFloat8((double) timestamp.getWeekOfWeekyear());
+ }
+ }
+
+ private class YearExtractorFromTimestamp implements DatePartExtractorFromTimestamp {
+ @Override
+ public Datum extract(TimestampDatum timestamp) {
+ return DatumFactory.createFloat8((double) timestamp.getYear());
+ }
+ }
+
+ private class NullExtractorFromTimestamp implements DatePartExtractorFromTimestamp {
+ @Override
+ public Datum extract(TimestampDatum timestamp) {
+ return NullDatum.get();
+ }
+ }
+}
+
http://git-wip-us.apache.org/repos/asf/incubator-tajo/blob/f3f4b045/tajo-core/tajo-core-backend/src/main/java/org/apache/tajo/engine/parser/SQLAnalyzer.java
----------------------------------------------------------------------
diff --git a/tajo-core/tajo-core-backend/src/main/java/org/apache/tajo/engine/parser/SQLAnalyzer.java b/tajo-core/tajo-core-backend/src/main/java/org/apache/tajo/engine/parser/SQLAnalyzer.java
index 48d9e4d..5507ba0 100644
--- a/tajo-core/tajo-core-backend/src/main/java/org/apache/tajo/engine/parser/SQLAnalyzer.java
+++ b/tajo-core/tajo-core-backend/src/main/java/org/apache/tajo/engine/parser/SQLAnalyzer.java
@@ -28,6 +28,7 @@ import org.apache.commons.lang.StringEscapeUtils;
import org.apache.tajo.algebra.*;
import org.apache.tajo.algebra.Aggregation.GroupType;
import org.apache.tajo.algebra.LiteralValue.LiteralType;
+import org.apache.tajo.datum.TextDatum;
import org.apache.tajo.engine.parser.SQLParser.*;
import org.apache.tajo.storage.CSVFile;
@@ -654,10 +655,14 @@ public class SQLAnalyzer extends SQLParserBaseVisitor<Expr> {
@Override
public Expr visitNumeric_primary(SQLParser.Numeric_primaryContext ctx) {
- Expr current = visitValue_expression_primary(ctx.value_expression_primary());
-
- for (int i = 0; i < ctx.CAST_EXPRESSION().size(); i++) {
- current = new CastExpr(current, visitData_type(ctx.cast_target(i).data_type()));
+ Expr current = null;
+ if (checkIfExist(ctx.value_expression_primary())) {
+ current = visitValue_expression_primary(ctx.value_expression_primary());
+ for (int i = 0; i < ctx.CAST_EXPRESSION().size(); i++) {
+ current = new CastExpr(current, visitData_type(ctx.cast_target(i).data_type()));
+ }
+ } else if (checkIfExist(ctx.numeric_value_function())) {
+ current = visitNumeric_value_function(ctx.numeric_value_function());
}
return current;
@@ -863,6 +868,34 @@ public class SQLAnalyzer extends SQLParserBaseVisitor<Expr> {
return current;
}
+
+ @Override
+ public Expr visitNumeric_value_function(Numeric_value_functionContext ctx) {
+ if (checkIfExist(ctx.extract_expression())) {
+ return visitExtract_expression(ctx.extract_expression());
+ }
+
+ return null;
+ }
+
+ @Override
+ public Expr visitExtract_expression(Extract_expressionContext ctx) {
+ Expr extractTarget = new LiteralValue(ctx.extract_field_string.getText(), LiteralType.String);
+ Expr extractSource;
+ if (checkIfExist(ctx.extract_source().column_reference())) {
+ extractSource = visitColumn_reference(ctx.extract_source().column_reference());
+ } else if (checkIfExist(ctx.extract_source().datetime_literal())) {
+ extractSource = visitDatetime_literal(ctx.extract_source().datetime_literal());
+ } else {
+ return null;
+ }
+
+ String functionName = "date_part";
+ Expr [] params = new Expr[] {extractTarget, extractSource};
+
+ return new FunctionExpr(functionName, params);
+ }
+
@Override
public Expr visitTrim_function(SQLParser.Trim_functionContext ctx) {
Expr trimSource = visitChildren(ctx.trim_operands().trim_source);
http://git-wip-us.apache.org/repos/asf/incubator-tajo/blob/f3f4b045/tajo-core/tajo-core-backend/src/test/java/org/apache/tajo/engine/eval/TestSQLDateTimeTypes.java
----------------------------------------------------------------------
diff --git a/tajo-core/tajo-core-backend/src/test/java/org/apache/tajo/engine/eval/TestSQLDateTimeTypes.java b/tajo-core/tajo-core-backend/src/test/java/org/apache/tajo/engine/eval/TestSQLDateTimeTypes.java
index 6567b52..c9c8dd4 100644
--- a/tajo-core/tajo-core-backend/src/test/java/org/apache/tajo/engine/eval/TestSQLDateTimeTypes.java
+++ b/tajo-core/tajo-core-backend/src/test/java/org/apache/tajo/engine/eval/TestSQLDateTimeTypes.java
@@ -27,8 +27,8 @@ public class TestSQLDateTimeTypes extends ExprTestBase {
@Test
public void testTimestamp() throws IOException {
testSimpleEval("select TIMESTAMP '1970-01-17 10:09:37';", new String[]{"1970-01-17 10:09:37"});
- testSimpleEval("select TIMESTAMP '1970-01-17 10:09:37.5';", new String[]{"1970-01-17 10:09:37.500"});
- testSimpleEval("select TIMESTAMP '1970-01-17 10:09:37.01';", new String[]{"1970-01-17 10:09:37.010"});
+ testSimpleEval("select TIMESTAMP '1970-01-17 10:09:37.5';", new String[]{"1970-01-17 10:09:37.5"});
+ testSimpleEval("select TIMESTAMP '1970-01-17 10:09:37.01';", new String[]{"1970-01-17 10:09:37.01"});
testSimpleEval("select TIMESTAMP '1970-01-17 10:09:37.003';", new String[]{"1970-01-17 10:09:37.003"});
}
http://git-wip-us.apache.org/repos/asf/incubator-tajo/blob/f3f4b045/tajo-core/tajo-core-backend/src/test/java/org/apache/tajo/engine/function/TestDateTimeFunctions.java
----------------------------------------------------------------------
diff --git a/tajo-core/tajo-core-backend/src/test/java/org/apache/tajo/engine/function/TestDateTimeFunctions.java b/tajo-core/tajo-core-backend/src/test/java/org/apache/tajo/engine/function/TestDateTimeFunctions.java
index 8e2db6e..b882e84 100644
--- a/tajo-core/tajo-core-backend/src/test/java/org/apache/tajo/engine/function/TestDateTimeFunctions.java
+++ b/tajo-core/tajo-core-backend/src/test/java/org/apache/tajo/engine/function/TestDateTimeFunctions.java
@@ -19,6 +19,7 @@
package org.apache.tajo.engine.function;
+import org.apache.tajo.catalog.Schema;
import org.apache.tajo.datum.TimestampDatum;
import org.apache.tajo.engine.eval.ExprTestBase;
import org.joda.time.DateTime;
@@ -26,6 +27,10 @@ import org.junit.Test;
import java.io.IOException;
+import static org.apache.tajo.common.TajoDataTypes.Type.TIMESTAMP;
+import static org.apache.tajo.common.TajoDataTypes.Type.TIME;
+import static org.apache.tajo.common.TajoDataTypes.Type.DATE;
+
public class TestDateTimeFunctions extends ExprTestBase {
@Test
@@ -47,4 +52,178 @@ public class TestDateTimeFunctions extends ExprTestBase {
String q = String.format("select to_char(to_timestamp(%d), 'yyyy-MM');", (expectedTimestamp / 1000));
testSimpleEval(q, new String[]{expectedDateTime.toString(dateFormatStr)});
}
+
+ @Test
+ public void testExtract() throws IOException {
+ Schema schema2 = new Schema();
+ schema2.addColumn("col1", TIMESTAMP);
+ testEval(schema2, "table1", "1970-01-17 10:09:37", "select extract(year from col1), extract(month from col1), extract(day from col1) from table1;", new String[]{"1970.0", "1.0", "17.0"});
+
+ Schema schema3 = new Schema();
+ schema3.addColumn("col1", TIME);
+ testEval(schema3, "table1", "10:09:37.5", "select extract(hour from col1), extract(minute from col1), extract(second from col1) from table1;", new String[]{"10.0", "9.0", "37.5"});
+
+ Schema schema4 = new Schema();
+ schema4.addColumn("col1", DATE);
+ testEval(schema4, "table1", "1970-01-17", "select extract(year from col1), extract(month from col1), extract(day from col1) from table1;", new String[]{"1970.0", "1.0", "17.0"});
+
+ testSimpleEval("select extract(century from TIMESTAMP '1970-01-17 10:09:37');", new String[]{"19.0"});
+
+ testSimpleEval("select extract(century from DATE '1970-01-17');", new String[]{"19.0"});
+
+ testSimpleEval("select extract(decade from TIMESTAMP '1970-01-17 10:09:37');", new String[]{"197.0"});
+
+ testSimpleEval("select extract(decade from DATE '1970-01-17');", new String[]{"197.0"});
+
+ testSimpleEval("select extract(millennium from TIMESTAMP '2001-02-16 10:09:37');", new String[]{"3.0"});
+ testSimpleEval("select extract(millennium from TIMESTAMP '2000-02-16 10:09:37');", new String[]{"2.0"});
+
+ testSimpleEval("select extract(millennium from DATE '2001-02-16');", new String[]{"3.0"});
+ testSimpleEval("select extract(millennium from DATE '2000-02-16');", new String[]{"2.0"});
+
+ testSimpleEval("select extract(year from TIMESTAMP '1970-01-17 10:09:37');", new String[]{"1970.0"});
+ testSimpleEval("select extract(month from TIMESTAMP '1970-01-17 10:09:37');", new String[]{"1.0"});
+ testSimpleEval("select extract(day from TIMESTAMP '1970-01-17 10:09:37');", new String[]{"17.0"});
+
+ testSimpleEval("select extract(hour from TIMESTAMP '1970-01-17 10:09:37');", new String[]{"10.0"});
+ testSimpleEval("select extract(minute from TIMESTAMP '1970-01-17 10:09:37');", new String[]{"9.0"});
+ testSimpleEval("select extract(second from TIMESTAMP '1970-01-17 10:09:37');", new String[]{"37.0"});
+ testSimpleEval("select extract(second from TIMESTAMP '1970-01-17 10:09:37.5');", new String[]{"37.5"});
+
+ testSimpleEval("select extract(hour from TIME '10:09:37');", new String[]{"10.0"});
+ testSimpleEval("select extract(minute from TIME '10:09:37');", new String[]{"9.0"});
+ testSimpleEval("select extract(second from TIME '10:09:37');", new String[]{"37.0"});
+ testSimpleEval("select extract(second from TIME '10:09:37.5');", new String[]{"37.5"});
+
+ testSimpleEval("select extract(year from DATE '1970-01-17');", new String[]{"1970.0"});
+ testSimpleEval("select extract(month from DATE '1970-01-17');", new String[]{"1.0"});
+ testSimpleEval("select extract(day from DATE '1970-01-17');", new String[]{"17.0"});
+
+ testSimpleEval("select extract(milliseconds from TIMESTAMP '1970-01-17 10:09:37.5');", new String[]{"37500.0"});
+ testSimpleEval("select extract(milliseconds from TIME '10:09:37.123');", new String[]{"37123.0"});
+
+ testSimpleEval("select extract(microseconds from TIMESTAMP '1970-01-17 10:09:37.5');", new String[]{"3.75E7"});
+ testSimpleEval("select extract(microseconds from TIME '10:09:37.123');", new String[]{"3.7123E7"});
+
+ testSimpleEval("select extract(dow from TIMESTAMP '1970-01-17 10:09:37');", new String[]{"6.0"});
+ testSimpleEval("select extract(dow from TIMESTAMP '1970-01-18 10:09:37');", new String[]{"0.0"});
+ testSimpleEval("select extract(isodow from TIMESTAMP '1970-01-17 10:09:37');", new String[]{"6.0"});
+ testSimpleEval("select extract(isodow from TIMESTAMP '1970-01-18 10:09:37');", new String[]{"7.0"});
+
+ testSimpleEval("select extract(year from TIMESTAMP '2006-01-02 10:09:37');", new String[]{"2006.0"});
+ testSimpleEval("select extract(year from TIMESTAMP '2006-01-01 10:09:37');", new String[]{"2006.0"});
+ testSimpleEval("select extract(isoyear from TIMESTAMP '2006-01-02 10:09:37');", new String[]{"2006.0"});
+ testSimpleEval("select extract(isoyear from TIMESTAMP '2006-01-01 10:09:37');", new String[]{"2005.0"});
+
+ testSimpleEval("select extract(quarter from TIMESTAMP '2006-02-01 10:09:37');", new String[]{"1.0"});
+ testSimpleEval("select extract(quarter from TIMESTAMP '2006-04-01 10:09:37');", new String[]{"2.0"});
+ testSimpleEval("select extract(quarter from TIMESTAMP '2006-07-01 10:09:37');", new String[]{"3.0"});
+ testSimpleEval("select extract(quarter from TIMESTAMP '2006-12-01 10:09:37');", new String[]{"4.0"});
+
+ testSimpleEval("select extract(week from TIMESTAMP '1970-01-17 10:09:37');", new String[]{"3.0"});
+
+ testSimpleEval("select extract(dow from DATE '1970-01-17');", new String[]{"6.0"});
+ testSimpleEval("select extract(dow from DATE '1970-01-18');", new String[]{"0.0"});
+ testSimpleEval("select extract(isodow from DATE '1970-01-17');", new String[]{"6.0"});
+ testSimpleEval("select extract(isodow from DATE '1970-01-18');", new String[]{"7.0"});
+
+ testSimpleEval("select extract(year from DATE '2006-01-02');", new String[]{"2006.0"});
+ testSimpleEval("select extract(year from DATE '2006-01-01');", new String[]{"2006.0"});
+ testSimpleEval("select extract(isoyear from DATE '2006-01-02');", new String[]{"2006.0"});
+ testSimpleEval("select extract(isoyear from DATE '2006-01-01');", new String[]{"2005.0"});
+
+ testSimpleEval("select extract(quarter from DATE '2006-02-01');", new String[]{"1.0"});
+ testSimpleEval("select extract(quarter from DATE '2006-04-01');", new String[]{"2.0"});
+ testSimpleEval("select extract(quarter from DATE '2006-07-01');", new String[]{"3.0"});
+ testSimpleEval("select extract(quarter from DATE '2006-12-01');", new String[]{"4.0"});
+
+ testSimpleEval("select extract(week from DATE '1970-01-17');", new String[]{"3.0"});
+ }
+
+ @Test
+ public void testDatePart() throws IOException {
+ Schema schema2 = new Schema();
+ schema2.addColumn("col1", TIMESTAMP);
+ testEval(schema2, "table1", "1970-01-17 10:09:37", "select date_part('year', col1), date_part('month', col1), date_part('day', col1) from table1;", new String[]{"1970.0", "1.0", "17.0"});
+
+ Schema schema3 = new Schema();
+ schema3.addColumn("col1", TIME);
+ testEval(schema3, "table1", "10:09:37.5", "select date_part('hour', col1), date_part('minute', col1), date_part('second', col1) from table1;", new String[]{"10.0", "9.0", "37.5"});
+
+ Schema schema4 = new Schema();
+ schema4.addColumn("col1", DATE);
+ testEval(schema4, "table1", "1970-01-17", "select date_part('year', col1), date_part('month', col1), date_part('day', col1) from table1;", new String[]{"1970.0", "1.0", "17.0"});
+
+ testSimpleEval("select date_part('century', TIMESTAMP '1970-01-17 10:09:37');", new String[]{"19.0"});
+
+ testSimpleEval("select date_part('century', DATE '1970-01-17');", new String[]{"19.0"});
+
+ testSimpleEval("select date_part('decade', TIMESTAMP '1970-01-17 10:09:37');", new String[]{"197.0"});
+
+ testSimpleEval("select date_part('decade', DATE '1970-01-17');", new String[]{"197.0"});
+
+ testSimpleEval("select date_part('millennium', TIMESTAMP '2001-02-16 10:09:37');", new String[]{"3.0"});
+ testSimpleEval("select date_part('millennium', TIMESTAMP '2000-02-16 10:09:37');", new String[]{"2.0"});
+
+ testSimpleEval("select date_part('millennium', DATE '2001-02-16');", new String[]{"3.0"});
+ testSimpleEval("select date_part('millennium', DATE '2000-02-16');", new String[]{"2.0"});
+
+ testSimpleEval("select date_part('year', TIMESTAMP '1970-01-17 10:09:37');", new String[]{"1970.0"});
+ testSimpleEval("select date_part('month', TIMESTAMP '1970-01-17 10:09:37');", new String[]{"1.0"});
+ testSimpleEval("select date_part('day', TIMESTAMP '1970-01-17 10:09:37');", new String[]{"17.0"});
+
+ testSimpleEval("select date_part('hour', TIMESTAMP '1970-01-17 10:09:37');", new String[]{"10.0"});
+ testSimpleEval("select date_part('minute', TIMESTAMP '1970-01-17 10:09:37');", new String[]{"9.0"});
+ testSimpleEval("select date_part('second', TIMESTAMP '1970-01-17 10:09:37');", new String[]{"37.0"});
+ testSimpleEval("select date_part('second', TIMESTAMP '1970-01-17 10:09:37.5');", new String[]{"37.5"});
+
+ testSimpleEval("select date_part('hour', TIME '10:09:37');", new String[]{"10.0"});
+ testSimpleEval("select date_part('minute', TIME '10:09:37');", new String[]{"9.0"});
+ testSimpleEval("select date_part('second', TIME '10:09:37');", new String[]{"37.0"});
+ testSimpleEval("select date_part('second', TIME '10:09:37.5');", new String[]{"37.5"});
+
+ testSimpleEval("select date_part('year', DATE '1970-01-17');", new String[]{"1970.0"});
+ testSimpleEval("select date_part('month', DATE '1970-01-17');", new String[]{"1.0"});
+ testSimpleEval("select date_part('day', DATE '1970-01-17');", new String[]{"17.0"});
+
+ testSimpleEval("select date_part('milliseconds', TIMESTAMP '1970-01-17 10:09:37.5');", new String[]{"37500.0"});
+ testSimpleEval("select date_part('milliseconds', TIME '10:09:37.123');", new String[]{"37123.0"});
+
+ testSimpleEval("select date_part('microseconds', TIMESTAMP '1970-01-17 10:09:37.5');", new String[]{"3.75E7"});
+ testSimpleEval("select date_part('microseconds', TIME '10:09:37.123');", new String[]{"3.7123E7"});
+
+ testSimpleEval("select date_part('dow', TIMESTAMP '1970-01-17 10:09:37');", new String[]{"6.0"});
+ testSimpleEval("select date_part('dow', TIMESTAMP '1970-01-18 10:09:37');", new String[]{"0.0"});
+ testSimpleEval("select date_part('isodow', TIMESTAMP '1970-01-17 10:09:37');", new String[]{"6.0"});
+ testSimpleEval("select date_part('isodow', TIMESTAMP '1970-01-18 10:09:37');", new String[]{"7.0"});
+
+ testSimpleEval("select date_part('year', TIMESTAMP '2006-01-02 10:09:37');", new String[]{"2006.0"});
+ testSimpleEval("select date_part('year', TIMESTAMP '2006-01-01 10:09:37');", new String[]{"2006.0"});
+ testSimpleEval("select date_part('isoyear', TIMESTAMP '2006-01-02 10:09:37');", new String[]{"2006.0"});
+ testSimpleEval("select date_part('isoyear', TIMESTAMP '2006-01-01 10:09:37');", new String[]{"2005.0"});
+
+ testSimpleEval("select date_part('quarter', TIMESTAMP '2006-02-01 10:09:37');", new String[]{"1.0"});
+ testSimpleEval("select date_part('quarter', TIMESTAMP '2006-04-01 10:09:37');", new String[]{"2.0"});
+ testSimpleEval("select date_part('quarter', TIMESTAMP '2006-07-01 10:09:37');", new String[]{"3.0"});
+ testSimpleEval("select date_part('quarter', TIMESTAMP '2006-12-01 10:09:37');", new String[]{"4.0"});
+
+ testSimpleEval("select date_part('week', TIMESTAMP '1970-01-17 10:09:37');", new String[]{"3.0"});
+
+ testSimpleEval("select date_part('dow', DATE '1970-01-17');", new String[]{"6.0"});
+ testSimpleEval("select date_part('dow', DATE '1970-01-18');", new String[]{"0.0"});
+ testSimpleEval("select date_part('isodow', DATE '1970-01-17');", new String[]{"6.0"});
+ testSimpleEval("select date_part('isodow', DATE '1970-01-18');", new String[]{"7.0"});
+
+ testSimpleEval("select date_part('year', DATE '2006-01-02');", new String[]{"2006.0"});
+ testSimpleEval("select date_part('year', DATE '2006-01-01');", new String[]{"2006.0"});
+ testSimpleEval("select date_part('isoyear', DATE '2006-01-02');", new String[]{"2006.0"});
+ testSimpleEval("select date_part('isoyear', DATE '2006-01-01');", new String[]{"2005.0"});
+
+ testSimpleEval("select date_part('quarter', DATE '2006-02-01');", new String[]{"1.0"});
+ testSimpleEval("select date_part('quarter', DATE '2006-04-01');", new String[]{"2.0"});
+ testSimpleEval("select date_part('quarter', DATE '2006-07-01');", new String[]{"3.0"});
+ testSimpleEval("select date_part('quarter', DATE '2006-12-01');", new String[]{"4.0"});
+
+ testSimpleEval("select date_part('week', DATE '1970-01-17');", new String[]{"3.0"});
+ }
}