drill-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Julian Hyde <jh...@apache.org>
Subject Re: Date Conversion Question
Date Wed, 18 Oct 2017 16:47:11 GMT
A question on StackOverflow asks how to do this using Oracle’s TO_TIMESTAMP function, and
there is a solution[1]. So, I tried

  SELECT to_timestamp ('2017-08-10T09:12:26.000Z', 'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"')
  FROM DUAL

on http://rextester.com/l/oracle_online_compiler <http://rextester.com/l/oracle_online_compiler>
and it worked.

I presume Drill’s TO_TIMESTAMP is based is based on Oracle’s. In which case let’s fix
TO_TIMESTAMP.

Julian
 
[1] https://stackoverflow.com/questions/26671557/convert-string-iso-8601-date-to-oracles-timestamp-datatype
<https://stackoverflow.com/questions/26671557/convert-string-iso-8601-date-to-oracles-timestamp-datatype>


> On Oct 18, 2017, at 7:57 AM, Bob Rudis <bob@rud.is> wrote:
> 
> FWIW I was doing very similar substring (etc) machinations until we
> started converting output from back-end data-generation tools directly
> into parquet (using other tools). IMO it's a common enough format (at
> least in the types of data you and I likely have to work with :-) that
> it'd be great if there was direct support for it. If there is, I also
> missed it and would also be most appreciative of which incantations to
> use to take advantage of it.
> 
> On Wed, Oct 18, 2017 at 10:49 AM, Charles Givre <cgivre@gmail.com> wrote:
>> Hello Drillers,
>> I have a silly question which I’m a little stuck with.  I have some data in CSV
format with dates in the following format:  2017-08-10T09:12:26.000Z.  I’m trying to convert
this into a date time data field so that I have both the date and the hours, however I keep
running into road blocks.   I’ve tried the CAST( field AS DATE ) but in doing so I lose
the time component.  I’ve tried the TO_TIMESTAMP function, however the only success I’ve
had is using the substring function to remove the timezone at the end, then use regex_replace
to get rid of the literal ’T’ in the middle of the string, then TO_NUMBER.  (See query
below)
>> 
>> SELECT TO_TIMESTAMP( REGEXP_REPLACE( SUBSTR( <field>, 1,19), 'T', ' '), 'yyyy-MM-dd
HH:mm:ss'  ) AS dt,
>> EXTRACT(
>> hour FROM
>> TO_TIMESTAMP( REGEXP_REPLACE( SUBSTR( <field>, 1,19), 'T', ' '), 'yyyy-MM-dd
HH:mm:ss'  )
>> ) AS dt_hour
>> 
>> I’d think you could do this directly with the TO_TIMESTAMP function however, I
can’t figure out how include the literal ’T’ in the formatting string.  The escape character
seems to be the single quote which also is the only character allowed to denote the formatting
string.
>> 
>> So, questions:
>> 1.  Is there any way to include a literal character in a joda date format?
>> 2.  Is it possible to use any character besides a single quote to mark the beginning/end
of a format string?
>> 3.  Are there any ways to do this that I’m missing?
>> 
>> Thanks!
>> —C
>> 


Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message