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
