Tuesday, November 25, 2014

Drill to_timestamp/to_date function

Drill's to_timestamp or to_date function should match JODA doc.
 Symbol  Meaning                      Presentation  Examples
 ------  -------                      ------------  -------
 G       era                          text          AD
 C       century of era (>=0)         number        20
 Y       year of era (>=0)            year          1996

 x       weekyear                     year          1996
 w       week of weekyear             number        27
 e       day of week                  number        2
 E       day of week                  text          Tuesday; Tue

 y       year                         year          1996
 D       day of year                  number        189
 M       month of year                month         July; Jul; 07
 d       day of month                 number        10

 a       halfday of day               text          PM
 K       hour of halfday (0~11)       number        0
 h       clockhour of halfday (1~12)  number        12

 H       hour of day (0~23)           number        0
 k       clockhour of day (1~24)      number        24
 m       minute of hour               number        30
 s       second of minute             number        55
 S       fraction of second           number        978

 z       time zone                    text          Pacific Standard Time; PST
 Z       time zone offset/id          zone          -0800; -08:00; America/Los_Angeles

 '       escape for text              delimiter
 ''      single quote                 literal   
For example:
Original timestamp is:
select columns[0] from dfs.tmp.`drilltest/a.csv`;
+------------+
|   EXPR$0   |
+------------+
| 2014-11-24 18:08:08 |
+------------+ 
"Cast as timestamp" is the same.
select cast(columns[0] as timestamp) from dfs.tmp.`drilltest/a.csv`;
+------------+
|   EXPR$0   |
+------------+
| 2014-11-24 18:08:08.0 |
+------------+
to_timestamp may confuse you because the format string may be different than other RDBMS.
select to_timestamp(columns[0], 'YYYY-MM-dd HH:mm:ss') from dfs.tmp.`drilltest/a.csv`;
+------------+
|   EXPR$0   |
+------------+
| 2014-11-24 18:08:08.0 |
+------------+

select to_timestamp(columns[0], 'YYYY-MM-DD HH:mm:ss') from dfs.tmp.`drilltest/a.csv`;
+------------+
|   EXPR$0   |
+------------+
| 2014-01-24 18:08:08.0 |
+------------+


No comments:

Post a Comment

Popular Posts