1

I am comparing timestamp columns between 2 different database engines and I need to retrieve the time stamp column stored in YYY-MM-DD HH:mm:ss format to YYY-MM-DD HH:mm:ss.SSS, with SSS being 000 when no entry is there.

Can I do the above using Hive select query?

SMT
  • 96
  • 1
  • 4

2 Answers2

1

Split the timestamp to get milliseconds part, use rpad to add zeroes if there is no millisecond part at all or milliseconds part is less that 3 digits.

Demo:

 with your_data as (
    select stack(3, '2019-11-02 20:18:00.123',
                    '2019-11-02 20:18:00.12',
                    '2019-11-02 20:18:00'
                ) as ts
    )

 select concat(split(ts,'\\.')[0],'.',rpad(nvl(split(ts,'\\.')[1],''),3,0))
   from your_data d 
 ;

Result: 

2019-11-02 20:18:00.123
2019-11-02 20:18:00.120
2019-11-02 20:18:00.000
leftjoin
  • 36,950
  • 8
  • 57
  • 116
  • This is more powerful if there may be something behind the dot, but assuming it is all or nothing a simpler solution should also suffice. – Dennis Jaheruddin Dec 30 '19 at 14:15
0

Given that both formats (and their lengths) are strictly defined, you can use this simple logic:

left(concat(ts,'.000'),19)

Can't check the exact syntax, but basically you append extra zeros and cut them off if you don't need them.

Dennis Jaheruddin
  • 21,208
  • 8
  • 66
  • 122