Search code examples
sqloracle-databasetimestampmilliseconds

Move milliseconds by one position value

as a result of "incorrect" java parsing date (my fault) I now have several thousands of entries in oracle DB with incorrect timestamp. The issue is as follows:

Timestamp of 2018-06-26 11:15:43.950 has been inserted into DB as 26-FEB-18 11.15.43.095000000 AM

Is there any function for narrowing the milliseconds? I only figured out that with some to_char , to_date functions combined with substring i could "remove" the 0 in front of the miliseconds but it seems to me as not good enough resolution.

Thanks in advance!

EDIT: Unfortunatelly, I can not re-upload data with corrected algorithm.

Solution

  • Best option: reload data from original source, after you fix your code.

    If you no longer have access to the original data, and you must fix it all in place, use the UPDATE statement below (shown in context):

    create table tbl ( ts timestamp );

insert into tbl ( ts ) values ( timestamp '2018-06-26 11:15:43.0950' );
commit;

select ts from tbl;

TS                                     
----------------------------------------
2018-06-26 11.15.43.095000000         

update tbl set ts = ts + 9 * (ts - cast(ts as timestamp(0)));

1 row updated.

commit;

select ts from tbl;

TS                                     
----------------------------------------

2018-06-26 11.15.43.950000000

    Explanation:

    If your original timestamp was of the form X + w where X is down to whole seconds, and w is the fractional second part, the current value is X + z, where z = w/10. (You added an errant 0 right after the decimal point, which means you divided w by ten). So: you currently have X + z but you want X + w, or in other words, X + 10 * z. So, you must add 9 * z to what you already have.

    To get z (the fractional part of the timestamp) you need to subtract X (the integral part) from the timestamp. X itself is the truncation of the timestamp to whole seconds. There is no TRUNC() function to truncate to whole seconds, but the CAST function to TIMESTAMP(0) does that job quite well.

    To use your sample data: X is the timestamp '2018-06-26 11:15:43.000000'. This is also the result of cast(ts as timestamp(0)). w is .9500 and z is what made it into your table, .0950. Your current value is X + z, but you want X + w. That is X + 10 * z = (X + z) + 9 * z, and now remember that (X + z) is just ts (the value you have in the table currently) so you only need to add nine times the value z, which is the difference (ts - X).