1

In Hive, is there anyway to reduce millisecond precision (not rounding)?

For example I have the following timestamp with millisecond in 3 decimal places

2019-10-08 21:21:39.163

I want to get a timestamp exactly in 1 decimal place (remove two last milliseconds 63):

2019-10-08 21:21:39.1

I only get so far as to turning the timestamp into a decimal with one value precision:

cast(floor(cast(2019-10-08 21:21:39.163 AS double)/0.100)*0.100 AS decimal(16,1)) AS updatetime

This gives:

1570537299.1

The problem: I do not know how to turn the above value back to a timestamp in millisecond. Even better, if there is a better way to reduce timestamp precision from 3 to 1 decimal place, I will appreciate it.

The reason I have to cast the above code into decimal is because if I only do:

floor(cast(2019-10-08 21:21:39.163 AS double)/0.100)*0.100 AS exec_time

This gives something like:

1570537299.100000001

This is not good, since I need to join this table X with another table Y. Table X has timestamp like 2019-10-08 21:21:39.163. But table Y stores data in each 100ms interval, whose timestamp is exactly: 2019-10-08 21:21:39.1 The trailing 00000001 would prevent the timestamp from Table X to map exactly with Table Y

leftjoin
  • 36,950
  • 8
  • 57
  • 116
Long Le
  • 404
  • 5
  • 18

1 Answers1

0

If you need to remove last two milliseconds, use substr() and cast to timestamp again if necessary. For example:

with your_data as
(
select timestamp('2019-10-08 21:21:39.163') as original_timestamp --your example
)

select original_timestamp, 
       substr(original_timestamp,1,21)            truncated_string,
       timestamp(substr(original_timestamp,1,21)) truncated_timestamp --this may be not necessary, timestamp is compatible w string
from your_data

Returns:

original_timestamp      truncated_string        truncated_timestamp
2019-10-08 21:21:39.163 2019-10-08 21:21:39.1   2019-10-08 21:21:39.1
leftjoin
  • 36,950
  • 8
  • 57
  • 116