Updating server timezone

Added by Jeffrey Jones about 14 years ago

Hello all,

Well to my annoyance I found out that I didn't properly set the timezone on my server so while I (and all the users) are in Japan the Server is set to Pacific Standard Time (about 17 hours behind).

I then found out to my annoyance even more that RedMine doesn't store timestamps as UTC by default so this means that every timestamp in the DB is off by 17 hours and updating the server timezone throws the timestamps 17 hours ahead.

This has already caused a UI hiccup or two in RedMine but the main problem is exporting the data.

So my question is, has anyone here faced the same problem and got any advice on how to fix it? Is there an SQL guru in the house? I think I have to fix this at the DB level to avoid triggering all the updated_at fields.

I imagine I will have to comb through each table looking for the timestamp fields and add 17 hours to the date (Accounting for things like new days, months etc.).

Replies (2)

RE: Updating server timezone - Added by Jeffrey Jones about 14 years ago

For "Add 17 hours" of course that should be "Go back 17 hours"...

RE: Updating server timezone - Added by Jeffrey Jones about 14 years ago

Nope, I was right the first time, the redmine TZ threw me off. For future reference if anyone needs to update all the timestamps the following SQL will work for Postgres

update attachments set created_on = created_on + INTERVAL '17 hours';
update changesets set committed_on = committed_on + INTERVAL '17 hours';
update changesets set commit_date = committed_on;
update comments set created_on = created_on + INTERVAL '17 hours';
update comments set updated_on = updated_on + INTERVAL '17 hours';
update documents set created_on = created_on + INTERVAL '17 hours';
update issues set created_on = created_on + INTERVAL '17 hours';
update issues set updated_on = updated_on + INTERVAL '17 hours';
update journals set created_on = created_on + INTERVAL '17 hours';
update members set created_on = created_on + INTERVAL '17 hours';
update messages set created_on = created_on + INTERVAL '17 hours';
update messages set updated_on = updated_on + INTERVAL '17 hours';
update news set created_on = created_on + INTERVAL '17 hours';
update projects set created_on = created_on + INTERVAL '17 hours';
update projects set updated_on = updated_on + INTERVAL '17 hours';
update settings set updated_on = updated_on + INTERVAL '17 hours';
update time_entries set created_on = created_on + INTERVAL '17 hours';
update time_entries set updated_on = updated_on + INTERVAL '17 hours';
update tokens set created_on = created_on + INTERVAL '17 hours';
update users set created_on = created_on + INTERVAL '17 hours';
update users set updated_on = updated_on + INTERVAL '17 hours';
update versions set created_on = created_on + INTERVAL '17 hours';
update versions set updated_on = updated_on + INTERVAL '17 hours';
update wiki_content_versions set updated_on = updated_on + INTERVAL '17 hours';
update wiki_contents set updated_on = updated_on + INTERVAL '17 hours';
update wiki_pages set created_on = created_on + INTERVAL '17 hours';
update wiki_redirects set created_on = created_on + INTERVAL '17 hours';

(1-2/2)

Project

General

Profile

Redmine