Fix incorrect ticket dates after NTP problems?

[Mothra]

Facing a bit of a crisis... last night the NTP daemon failed on our live OTRS server, and the OS reverted back to the system clock which is, rather unhelpfully, one hour ahead of my timezone.

This has resulted in many tickets being created with wrong "create times" in the future, which has had a knock on impact on escalation times, ticket age, etc.

Is there a way to fix this, beyond manually going into the database and changing these times?

[crythias]

since the time stamps are attached to the ticket table, you'd have to adjust them in the table, and I doubt that Generic Agent would allow you to do that.

DISCLAIMER: BACKUP FIRST. Don't blindly copy/paste stuff and mess up your database. Some changes are unrecoverable. Try to SELECT before UPDATE to make sure this will do what you want.


The following code may be useful, though I have not tried it; adjust time stamps appropriately, and back up.
http://ldev.mysql.com/doc/refman/5.1/en ... mestampadd

Code: Select all

UPDATE ticket SET create_time=TIMESTAMPADD(HOUR,-1,create_time), change_time=NOW() WHERE create_time >= '2011-02-01 12:00:00'
       AND create_time < '2011-02-02 9:00:00' + INTERVAL 1 DAY;

If escalations have been set in ticket, you'll have to adjust them as well. Don't forget ticket_history, where you might have to do the same thing.

[Mothra]

I was crossing my fingers and hoping OTRS might have already provided a safe script to fix my problem... all the while suspecting that the answer would be something along the lines of what you've printed above.

Unfortunately, changing stuff in the database on a production server, with only 2 days to run in my contract, would be reckless in the extreme so, thanks, but I don't think I'll be attempting it on this occasion!