Review Board 1.7.22


DRILL-356 : Support for Date, Time data types

Review Request #18055 - Created Feb. 13, 2014 and updated

Mehant Baid
Reviewers
drill-git
drill-git
Added value vectors, holders, cast and comparison functions for the following data types.

Date
DateTime
Time
TimeStamp
Interval

Internal representation of these data types:
For the data types Date, DateTime, TimeStamp we internally store milliseconds (from epoch) in UTC. We use a 'long' to store the milliseconds. Since TimeStamp has a timezone associated with it, we use another field (int) to store the index of the timezone into a static list of timezones we maintain (in DateUtility class)

For the Time data type we again store milliseconds, but since Time has a small range (00:00:00.000 - 23:59:59.999) we use an 'int' to store the value.

For Interval data type, which can be used to express an interval of time using one or more of the parameters: years, months, days, hours, minutes, seconds, milliseconds we internally store three fields. Months (int), days (int) and milliseconds (long). Using these three fields we can express all the above parameters with simple conversions. Eg: Years (parameter) - Months (internal) is a simple 1 Year = 12 months conversion. Similar conversion can be applied to hours, minutes and seconds to convert them to milliseconds. 

Sorting, comparison functions for Date, DateTime, TimeStamp, Time are straight forward as internally they are stored as long or int.

In addition to these types we also have a way to represent each type as a literal. For eg: a Date literal can be expressed using the expression: "datetype(2008, 1, 20)", a TimeStamp literal can be expressed using the expression: "timestamptype(2008, 1, 27, 0, 0, 0, 0, 'UTC')" and so on for all the types. 

For performing date functions the design is to use the internal representation to construct a Joda object (MutableDateTime) and use Joda for date arithmetic and other functions. As an example in this patch I've added a date_add() function that adds a Date and an Interval (DateTypeFunctions class)



Added unit tests and some manual testing. 
common/src/main/antlr3/org/apache/drill/common/expression/parser/ExprLexer.g
Revision be2a3f2 New Change
[20] 43 lines
[+20]
44
BIGINT	 : 'bigint';
44
BIGINT	 : 'bigint';
45
FLOAT4   : 'float4';
45
FLOAT4   : 'float4';
46
FLOAT8   : 'float8';
46
FLOAT8   : 'float8';
47
VARCHAR  : 'varchar';
47
VARCHAR  : 'varchar';
48
VARBINARY: 'varbinary';
48
VARBINARY: 'varbinary';

    
   
49
DATE     : 'date';

    
   
50
DATETIME : 'datetime';

    
   
51
TIME     : 'time';

    
   
52
TIMESTAMP: 'timestamp';

    
   
53
INTERVAL : 'interval';

    
   
54
INTERVALYEAR : 'intervalyear';

    
   
55
INTERVALDAY : 'intervalday';
49

    
   
56

   
50
Or       : '||' | 'or' | 'OR' | 'Or';
57
Or       : '||' | 'or' | 'OR' | 'Or';
51
And      : '&&' | 'and' | 'AND' ;
58
And      : '&&' | 'and' | 'AND' ;
52
Equals   : '==' | '=';
59
Equals   : '==' | '=';
53
NEquals  : '<>' | '!=';
60
NEquals  : '<>' | '!=';
[+20] [20] 69 lines
common/src/main/antlr3/org/apache/drill/common/expression/parser/ExprParser.g
Revision b5cf292 New Change
 
common/src/main/java/org/apache/drill/common/expression/FunctionRegistry.java
Revision 7cb386a New Change
 
common/src/main/java/org/apache/drill/common/expression/OutputTypeDeterminer.java
Revision 69acf12 New Change
 
common/src/main/java/org/apache/drill/common/expression/fn/CastFunctionDefs.java
Revision 4be3820 New Change
 
common/src/main/java/org/apache/drill/common/types/Types.java
Revision fd8991d New Change
 
exec/java-exec/pom.xml
Revision bb044ad New Change
 
exec/java-exec/src/main/codegen/config.fmpp
Revision 8f1060a New Change
 
exec/java-exec/src/main/codegen/data/Casts.tdd
Revision fde63cf New Change
 
exec/java-exec/src/main/codegen/data/DateTypes.tdd
New File
 
exec/java-exec/src/main/codegen/data/ValueVectorTypes.tdd
Revision 88a5974 New Change
 
exec/java-exec/src/main/codegen/includes/vv_imports.ftl
Revision 5083f74 New Change
 
exec/java-exec/src/main/codegen/templates/DateCastFunctions.java
New File
 
exec/java-exec/src/main/codegen/templates/DateFunctions.java
New File
 
exec/java-exec/src/main/codegen/templates/FixedValueVectors.java
Revision 2d81299 New Change
 
exec/java-exec/src/main/codegen/templates/NullableValueVectors.java
Revision 051c62d New Change
 
exec/java-exec/src/main/codegen/templates/SqlAccessors.java
Revision efaf9a6 New Change
 
exec/java-exec/src/main/codegen/templates/ValueHolders.java
Revision 41dc049 New Change
 
exec/java-exec/src/main/java/org/apache/drill/exec/expr/fn/impl/DateTypeFunctions.java
New File
 
exec/java-exec/src/main/java/org/apache/drill/exec/expr/fn/impl/DateUtility.java
New File
 
  1. common/src/main/antlr3/org/apache/drill/common/expression/parser/ExprLexer.g: Loading...
  2. common/src/main/antlr3/org/apache/drill/common/expression/parser/ExprParser.g: Loading...
  3. common/src/main/java/org/apache/drill/common/expression/FunctionRegistry.java: Loading...
  4. common/src/main/java/org/apache/drill/common/expression/OutputTypeDeterminer.java: Loading...
  5. common/src/main/java/org/apache/drill/common/expression/fn/CastFunctionDefs.java: Loading...
  6. common/src/main/java/org/apache/drill/common/types/Types.java: Loading...
  7. exec/java-exec/pom.xml: Loading...
  8. exec/java-exec/src/main/codegen/config.fmpp: Loading...
  9. exec/java-exec/src/main/codegen/data/Casts.tdd: Loading...
  10. exec/java-exec/src/main/codegen/data/DateTypes.tdd: Loading...
  11. exec/java-exec/src/main/codegen/data/ValueVectorTypes.tdd: Loading...
  12. exec/java-exec/src/main/codegen/includes/vv_imports.ftl: Loading...
  13. exec/java-exec/src/main/codegen/templates/DateCastFunctions.java: Loading...
  14. exec/java-exec/src/main/codegen/templates/DateFunctions.java: Loading...
  15. exec/java-exec/src/main/codegen/templates/FixedValueVectors.java: Loading...
  16. exec/java-exec/src/main/codegen/templates/NullableValueVectors.java: Loading...
  17. exec/java-exec/src/main/codegen/templates/SqlAccessors.java: Loading...
  18. exec/java-exec/src/main/codegen/templates/ValueHolders.java: Loading...
  19. exec/java-exec/src/main/java/org/apache/drill/exec/expr/fn/impl/DateTypeFunctions.java: Loading...
  20. exec/java-exec/src/main/java/org/apache/drill/exec/expr/fn/impl/DateUtility.java: Loading...
This diff has been split across 2 pages: 1 2 >