Defect #10996

Time zones lost when upgrading from Redmine 1.4 to 2.0

Added by Jack Christensen over 5 years ago. Updated over 5 years ago.

Status:ClosedStart date:
Priority:NormalDue date:
Assignee:-% Done:

0%

Category:Database
Target version:2.0.1
Resolution:Fixed Affected version:2.0.0

Description

It seems that previous versions of Redmine stored times in database in local time. In 2.0 it stores it in UTC. This causes all old times to be offset by the time zone. e.g. A task I entered right before the upgrade showed as being 5 hours old.

I just ran SQL to update the time stamps appropriately, but it might be nice to have this done automatically.

Here's the script I used (PostgreSQL):

select 'UPDATE ' || table_name || ' SET ' || column_name || ' = ' || column_name || '::timestamptz at time zone ''utc'' WHERE ' || column_name || ' < ''2012-05-22 10:40:00'';'
from information_schema.columns
where table_schema='public'
  and data_type like 'timestamp without time zone'

The hard-coded time is the time that the upgrade occurred so as not to update times from after the upgrade.


Related issues

Related to Redmine - Defect #14658: Wrong activity timezone on user page Closed

Associated revisions

Revision 9719
Added by Jean-Philippe Lang over 5 years ago

Restores local time zone for timestamps for 1.x compatibility (#10996).

Revision 9726
Added by Jean-Philippe Lang over 5 years ago

Fixed time zone issues introduced by r9719 (#10996).

Revision 9727
Added by Jean-Philippe Lang over 5 years ago

Fixed test failures on wiki_page updated_on eager loading (#10996).

History

#1 Updated by Etienne Massip over 5 years ago

  • Category set to Database

#2 Updated by Xinruo Sun over 5 years ago

I confirm the issue when upgrading from 1.1 to 2.0, with sqlite3 as the database. All redmine time information is incorrect.

#3 Updated by Jean-Philippe Lang over 5 years ago

  • Status changed from New to Resolved
  • Target version set to 2.0.1
  • Resolution set to Fixed

Rails 3 uses UTC timestamps by default. I've restored the previous behaviour for compatibility with Redmine 1.x in r9719 (timestamps stored in local timezone).
If you want to use UTC timestamps instead, just change config.active_record.default_timezone to :utc.

#4 Updated by Jean-Philippe Lang over 5 years ago

  • Status changed from Resolved to Closed

#5 Updated by Go MAEDA 11 months ago

  • Related to Defect #14658: Wrong activity timezone on user page added

Also available in: Atom PDF