[prev in list] [next in list] [prev in thread] [next in thread] 

List:       drill-user
Subject:    Re: date formats and date parsing
From:       Mehant Baid <baid.mehant () gmail ! com>
Date:       2014-08-02 0:07:57
Message-ID: 53DC2BDD.1030504 () gmail ! com
[Download RAW message or body]


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.​
>
>



[prev in list] [next in list] [prev in thread] [next in thread]