HIVE datatype Timestamp with miliseconds

[#1]

Hello Friends,

I have some data with Timestamp that has mili seconds as

'2015-01-16 20:40:00.0' 'YYYY-MM-DD HH:MM:SS.sss' When I chose data type as TimeStamp in HIVE its removing the mili seconds part from the time stamp.

Any suggestion is highly appreciated.

Thank you,

Rohit Sureka

[#2]

@Rohit Sureka ,

You need to cast them as below.

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) 

REF : http://stackoverflow.com/questions/26355194/how-to-convert-a-string-to-timestamp-with-milliseconds-i...

[#3]

@jramakrishnan

Thank you very much for the answer. However this is not so valid for me as my data in HDFS is already in TimeStamp

'YYYY-MM-DD HH:MM:SS.sss'

just that HIVE TimeStamp data type is chopping off the miliseconds part.

Regards,

Rohit

[#4]

@Rohit Sureka . Yes. But when you query the data as above , Are you not getting Milli Second Info.

As per the official doc, Hive Supports traditional UNIX timestamp with optional nanosecond precision.

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types#LanguageManualTypes-Timestamps

[#5]

@jramakrishnan

Yes that's the problem. I also read this document it says it supports miliseconds but when I select the data it chops the miliseconds.

[#6 · Accepted solution]

Hi @Rohit Sureka, I just tried (HDP-2.3.2 sandbox) and for me the timestamp works per documentation, with up to 9 decimal places (nanoseconds). Can you check your input data, and delimiters of your fields, are they as expected. Here is my test. My table:

hive> create table ts(id int, t timestamp) row format delimited fields terminated by ',' stored as textfile location '/user/it1/hive/ts';

A few lines of my input file

11,2015-11-01 21:10:00 
12,2015-11-01 21:10:00.1 
15,2015-11-01 21:10:00.123

And a select/order by command

hive> select * from ts order by t;
OK
11	2015-11-01 21:10:00
12	2015-11-01 21:10:00.1
25	2015-11-01 21:10:00.1190011
37	2015-11-01 21:10:00.12
15	2015-11-01 21:10:00.123
31	2015-11-01 21:10:00.1234
17	2015-11-01 21:10:00.12345
19	2015-11-01 21:10:00.123456789
21	2015-11-01 21:10:00.490155
57	2015-11-01 21:10:00.60015589
Time taken: 2.34 seconds, Fetched: 10 row(s)

[#7]

When we ingest the following data

2015-11-01 21:10:00.1
2015-11-01 21:10:00.1190011
2015-11-01 21:10:00.12
2015-11-01 21:10:00.123
2015-11-01 21:10:00.1234
2015-11-01 21:10:00.12345
2015-11-01 21:10:00.123456789
2015-11-01 21:10:00.490155
2015-11-01 21:10:00.1234567890
2015-11-01 21:10:00.1234567890123456789

I get the following when I do the "select", I get NULL for the last two rows instead of just truncating the additional digits. This is HDP 2.6.1 & Hive 1.2.1000

select * from test_timestamp;
+--------------------------------+--+
|       test_timestamp.col       |
+--------------------------------+--+
| 2015-11-01 21:10:00.1          |
| 2015-11-01 21:10:00.1190011    |
| 2015-11-01 21:10:00.12         |
| 2015-11-01 21:10:00.123        |
| 2015-11-01 21:10:00.1234       |
| 2015-11-01 21:10:00.12345      |
| 2015-11-01 21:10:00.123456789  |
| 2015-11-01 21:10:00.490155     |
| NULL                           |
| NULL                           |

[#8]

@Predrag Minovic

Thank you very much it helps. I checked it again its working.

[#9]

Hi @Rohit Sureka,

Did you find a solution for this? I am also facing same issue, In my use case I want to add a field for current timestamp with 9 decimal place precision.

Hive (v 1.1.0) is giving up to only 3 decimal place, I also tried by casting it in String but no luck.

As per hive official document: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types#LanguageManualTypes-Timestamps...

"

Timestamps

Supports traditional UNIX timestamp with optional nanosecond precision.

Supported conversions:

• Integer numeric types: Interpreted as UNIX timestamp in seconds
• Floating point numeric types: Interpreted as UNIX timestamp in seconds with decimal precision
• Strings: JDBC compliant java.sql.Timestamp format "YYYY-MM-DD HH:MM:SS.fffffffff" (9 decimal place precision)

"

Thanks In advance.