1

I am trying to subtract 1 millisecond from the update time (existing timestamp in dd-MMM-yy hh.mm.ss.MS format but I am getting null when I write -1. Please help. Below is my query in which I need to subtract 1 from the result I get by the lead of update time.

I tried this

nvl( lead(updatetime) over (partition by id order by updatetime asc)-1, now()) DW_END_DATE_TIME

Does not work. I am new to hive, I have no idea why this is not working.

Narendra
  • 1,511
  • 1
  • 10
  • 20
Rumana
  • 21
  • 1
  • 4
  • Make sure this query does what you want. https://stackoverflow.com/q/17905873/1531971 (Also, you ought to [edit] your question and format the query so it is clear what is text and what is code.) –  Apr 06 '18 at 18:00
  • Yes, I need to subtract 1 from the lead of update time and if it is null, replace it with current time. That is why I used nvl. It works fine when I remove the -1. But When I include -1, it gives current time in all columns which means that it is taking null. – Rumana Apr 06 '18 at 18:25
  • I don't think you are subtracting 1 from an epoch time. You should break this down into smaller pieces and verify that whatever you are attempted to -1 from is actually something you can do that to. –  Apr 06 '18 at 20:03
  • Hi jdv, I tried doing the following but this doesn't work either. select nvl( (lead(updatetime) over (partition by id order by updatetime asc)) - INTERVAL '0.001' SECOND , '9999-12-31') DW_END_DATE_TIME Also, the format of my updatetime is 2018-02-21 14:57:58.458 – Rumana Apr 12 '18 at 14:32
  • You should show your research by doing an [edit] of the original question. I think you should prove to yourself that you can actually subtract 1 from whatever `(partition by id order by updatetime asc)` (or whatever) is returning. The date you show is a formatted date string, not an epoch time. Make sure you aren't trying to subtract 1 from the formatted time _string_. –  Apr 12 '18 at 16:39

0 Answers0