Hey Jim,
I will be working with the documentation team to include more details
about the date and interval formats Drill supports and how to use them.
Date: "yyyy-MM-dd" Eg: 2008-12-23
Timestamp: "yyyy-MM-dd HH:mm:ss.SSS" : Here the fractional seconds part
is optional. Eg: "2008-2-12 10:20:30" or "2008-2-12 10:20:30.123"
Time: "HH:mm:ss.SSS" Fractional seconds is optional.
We use Joda library to parse dates (this may change), here
<http://joda-time.sourceforge.net/apidocs/org/joda/time/format/DateTimeFormat.html>
you can find the complete list of symbols and explanation.
You can use date and time literals in the following way:
/*select date '2008-2-23', timestamp '2008-1-23 14:24:23', time
'10:20:30' from dfs.`/tmp/input.json`;
*/If you have varchar data in your files in the above format you can use
cast() functions to be able to cast them to date/time.
/*select cast(A as date), cast(B as timestamp), cast(C as time) from
dfs.`/tmp/dates.json`;
*/All dates and times are considered to be in UTC. We don't support
timezones yet.
Interval data type:
We supports two types of interval data types:
1. Interval Year: Represents a time duration that can be represented in
year and months.
2. Interval Day: Represents a time duration that can be represented in
days, hours, minutes and seconds
Following are some examples of interval literals:
/*select interval '1-2' year to month, interval '10 20:30:0.123' day to
second from dfs.`/tmp/input.json`;
*/Interval year can represent fields from year to month. Interval day
can represent fields from day all the way to seconds. But you needn't
have to specify all the fields in a given interval.
/*select interval '1' year, interval '10 20' day to hour from
dfs.`/tmp/input.json`;
*/However for the casting to interval type from files we use a different
format (ISO 8601 time intervals). The general format looks like:
http://en.wikipedia.org/wiki/ISO_8601#Durations , let me know if you
have more questions.
/*
*/Thanks
Mehant
On 8/1/14, 3:27 PM, Jim Scott wrote:
I am trying to figure out how to parse date fields. The first question,
which formatting for interpreting dates should be used? e.g. java date time
format,
http://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html
Is this the proper format that should be used to parse a field into a date?
to_date(FIELD, "FORMAT PATTERN")
Lastly, there needs to be some more clarification around how to use these
functions:
date_add(date,interval expr type)date/datetimedate_part(text, timestamp) double
precisiondate_part(text, interval)double precisiondate_sub(date,INTERVAL
expr type) date/datetimeextract(field from interval)double
precisionextract(field
from timestamp) double precision
date_add(date, "what is an interval expression type?")
What exactly does date_part do? if text is supposed to one of
DAY,DAYOFWEEK,YEAR, etc... what is interval or timestamp? Should those
actually be date's?
Basically, I think some clarification around "field", "interval", "text"
actually mean in these cases would be VERY helpful.
