drill-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Charles Givre <cgi...@gmail.com>
Subject Date Conversion Question
Date Wed, 18 Oct 2017 14:49:01 GMT
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,
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?


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