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 <[email protected]> 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 <[email protected]> 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
>>
