I have a string '20141014123456789' which represents a timestamp with milliseconds that I need to convert to a timestamp in Hive (0.13.0) without losing the milliseconds.
I tried this but unix_timestamp returns an integer, so I lose the milliseconds:
from_unixtime(unix_timestamp('20141014123456789', 'yyyyMMddHHmmssSSS')) >> 2014-10-14 12:34:56
Casting a string works:
cast('2014-10-14 12:34:56.789' as timestamp) >> 2014-10-14 12:34:56.789
but my string isn't in that form.
I think I need to reformat my string from '20141014123456789' to '2014-10-14 12:34:56.789'. My challenge is how to do that without a messy concatenation of substrings.
David Ford :
I found a way to avoid the messy concatenation of substrings using the following code:\n\nselect cast(regexp_replace('20141014123456789', \n '(\\\\d{4})(\\\\d{2})(\\\\d{2})(\\\\d{2})(\\\\d{2})(\\\\d{2})(\\\\d{3})',\n '$1-$2-$3 $4:$5:$6.$7') as timestamp) \n",
2014-10-16T06:35:04
FMeh :
A simple strategy would be to use date_format(arg1, arg2), where arg1 is the timestamp either as formatted string, date, or timestamp and the arg2 is the format of the string (in arg1). Refer to the SimpleDateFormat java documentation for what is acceptable in the format argument.\n\nSo, in this case:\n\ndate_format('20141014123456789', 'yyyyMMddHHmmssSSS')\n\n\nwould yield the following string: '2014-10-14 12:34:56.789' which can then be cast as timestamp:\n\ncast(date_format('20141014123456789', 'yyyyMMddHHmmssSSS') as timestamp)\n\n\nThe above statement would return timestamp (as desired).",
2016-04-28T13:50:18
Vini :
i had the date field in this form 2015-07-22T09:00:32.956443Z(stored as string). i needed to do some date manipulations.\nthe following command even though little messy worked fine for me:)\n\nselect cast(concat(concat(substr(date_created,1,10),' '),substr(date_created,12,15)) as timestamp) from tablename;\n\n\nthis looks confusing but it is quite easy if you break it down. \nextracting the date and time with milliseconds and concat a space in between and then concat the whole thing and casting it into timestamp. now this can be used for date or timestamp manipulations. ",
2016-08-12T19:29:03
Terminator17 :
Let say you have a column 'birth_date' in your table which is in string format,\nyou should use the following query to filter using birth_date\n\ndate_Format(birth_date, 'yyyy-MM-dd HH:mm:ssSSS')\n\n\nYou can use it in a query in the following way\n\nselect * from yourtable\nwhere \ndate_Format(birth_date, 'yyyy-MM-dd HH:mm:ssSSS') = '2019-04-16 07:12:59999';\n",
2019-04-16T19:17:15