How to convert a string to timestamp with milliseconds in Hive?

0 votes

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

Oct 31, 2018 in Big Data Hadoop by Frankie
• 9,810 points
2,017 views

1 answer to this question.

0 votes

 concatenation of substrings using the following code:

select cast(regexp_replace('20141014123456789', 
                           '(\\d{4})(\\d{2})(\\d{2})(\\d{2})(\\d{2})(\\d{2})(\\d{3})',
                           '$1-$2-$3 $4:$5:$6.$7') as timestamp) 
answered Oct 31, 2018 by Neha
• 6,280 points

Related Questions In Big Data Hadoop

0 votes
1 answer

How to write a file in hdfs with Java?

You could pass the URI when getting ...READ MORE

answered Sep 26, 2018 in Big Data Hadoop by digger
• 27,630 points
111 views
0 votes
1 answer

Hadoop Hive: How to split string in Hive?

You can use the split function along ...READ MORE

answered Nov 6, 2018 in Big Data Hadoop by Omkar
• 67,380 points
1,874 views
0 votes
1 answer

Hadoop: How to get the column name along with the output in Hive?

You can get the column names by ...READ MORE

answered Nov 20, 2018 in Big Data Hadoop by Omkar
• 67,380 points
233 views
0 votes
1 answer

Hadoop Mapreduce word count Program

Firstly you need to understand the concept ...READ MORE

answered Mar 16, 2018 in Data Analytics by nitinrawat895
• 10,670 points
2,657 views
0 votes
10 answers

hadoop fs -put command?

put syntax: put <localSrc> <dest> copy syntax: copyFr ...READ MORE

answered Dec 7, 2018 in Big Data Hadoop by Aditya
13,229 views
0 votes
1 answer

Hadoop dfs -ls command?

In your case there is no difference ...READ MORE

answered Mar 16, 2018 in Big Data Hadoop by kurt_cobain
• 9,240 points
972 views
0 votes
1 answer
0 votes
1 answer

How to create a project for the first time in Hadoop.?

If you want to learn Hadoop framework ...READ MORE

answered Jul 26, 2018 in Big Data Hadoop by Neha
• 6,280 points
69 views
0 votes
1 answer

How to count lines in a file on hdfs command?

Use the below commands: Total number of files: hadoop ...READ MORE

answered Aug 10, 2018 in Big Data Hadoop by Neha
• 6,280 points
2,731 views