Supported Date/Time Data Type Formats

You must use supported date and time formats when you SELECT date and time literals or when you CAST() from VARCHARto date and time data types. Apache Drill currently supports specific formats for the following date and time data types:

The following query provides an example of how to SELECT a few of the supported date and time formats as literals:

select date '2008-2-23', timestamp '2008-1-23 14:24:23', time '10:20:30' from dfs.`/tmp/input.json`;

The following query provides an example where VARCHAR data in a file is CAST() to supported dateand time formats:

select cast(col_A as date), cast(col_B as timestamp), cast(col_C as time) from dfs.`/tmp/dates.json`;

Date, timestamp, andtime data types store values in UTC. Currently, Apache Drill does not support timestamp with time zone.

Date

Drill supports the date data type in the following format:

YYYY-MM-DD (year-month-date)

The following table provides some examples for the date data type:

| Use | Example | | --- | ------- | |Literal| select date ‘2008-2-23’ from dfs./tmp/input.json;| |JSON input | {"date_col" : "2008-2-23"} |CASTfromVARCHAR|select CAST(date_col as date) as CAST_DATE from dfs./tmp/input.json;`|

Timestamp

Drill supports the timestamp data type in the following format:

yyyy-MM-dd HH:mm:ss.SSS (year-month-date hour:minute:sec.milliseconds)

The following table provides some examples for the timestamp data type:

Use CAST Example
Literal select timestamp ‘2008-2-23 10:20:30.345’, timestamp ‘2008-2-23 10:20:30’ from dfs.`/tmp/input.json`;
JSON Input {“timestamp_col”: “2008-2-23 15:20:30.345”}
{“timestamp_col”: “2008-2-23 10:20:30”}The fractional millisecond component is optional.
CAST from VARCHAR select cast(timestamp_col as timestamp) from dfs.`/tmp/input.json`;
## Time Drill supports the `time` data type in the following format: HH:mm:ss.SSS (hour:minute:sec.milliseconds) The following table provides some examples for the `time` data type:
Use Example
Literal select time ‘15:20:30’, time ‘10:20:30.123’ from dfs.`/tmp/input.json`;
JSON Input {“time_col” : “10:20:30.999”}
{“time_col”: “10:20:30”}
CAST from VARCHAR select cast(time_col as time) from dfs.`/tmp/input.json`;

Interval

Drill supports the interval year and interval day data types.

Interval Year

The interval year data type stores time duration in years and months. Drill supports the interval data type in the following format:

P [qty] Y [qty] M

The following table provides examples for interval year data type:

Use Example
Literals select interval ‘1-2’ year to month from dfs.`/tmp/input.json`;
select interval ‘1’ year from dfs.`/tmp/input.json`;
select interval '13’ month from dfs.`/tmp/input.json`;
JSON Input {“col” : “P1Y2M”}
{“col” : “P-1Y2M”}
{“col” : “P-1Y-2M”}
{“col”: “P10M”}
{“col”: “P5Y”}
CAST from VARCHAR select cast(col as interval year) from dfs.`/tmp/input.json`;

Interval Day

The interval day data type stores time duration in days, hours, minutes, and seconds. You do not need to specify all fields in a given interval. Drill supports the interval day data type in the following format:

P [qty] D T [qty] H [qty] M [qty] S

The following table provides examples for interval day data type:

UseExample
Literalselect interval '1 10:20:30.123' day to second from dfs.`/tmp/input.json`;
select interval '1 10' day to hour from dfs.`/tmp/input.json`;
select interval '10' day from dfs.`/tmp/input.json`;
select interval '10' hour from dfs.`/tmp/input.json`;select interval '10.999' second from dfs.`/tmp/input.json`;
JSON Input{"col" : "P1DT10H20M30S"}
{"col" : "P1DT10H20M30.123S"}
{"col" : "P1D"}
{"col" : "PT10H"}
{"col" : "PT10.10S"}
{"col" : "PT20S"}
{"col" : "PT10H10S"}
CAST from VARCHARselect cast(col as interval day) from dfs.`/tmp/input.json`;

Literal

The following table provides a list of date/time literals that Drill supports with examples of each:

FormatInterpretationExample
interval '1 10:20:30.123' day to second1 day, 10 hours, 20 minutes, 30 seconds, and 123 thousandths of a secondselect interval '1 10:20:30.123' day to second from dfs.`/tmp/input.json`;
interval '1 10' day to hour1 day 10 hoursselect interval '1 10' day to hour from dfs.`/tmp/input.json`;
interval '10' day10 daysselect interval '10' day from dfs.`/tmp/input.json`;
interval '10' hour10 hoursselect interval '10' hour from dfs.`/tmp/input.json`;
interval '10.999' second10.999 secondsselect interval '10.999' second from dfs.`/tmp/input.json`;