The Bug Genie team blog

What's cooking behind the scenes of The Bug Genie

How The Bug Genie 3.2’s upgrader fixes your timestamps

with 2 comments

Note: The timezone fixer in the initial release of The Bug Genie 3.2 may not work if you have a huge number of issues and comments, as the process is very intensive. We will be performing further changes in the future, and a CLI task to perform this operation will be in a subsequent release of The Bug Genie 3.2. We therefore do not recommend using this tool if you have over 500 issues.

It’s a well known fact that The Bug Genie’s timezone support doesn’t work right in 3.0 and 3.1. One of the more serious bugs was with how timestamps were stored: they were stored in the timezone of the user who caused the action (i.e. opened the issue), and then adjusted to the timezone of the user viewing the thing which involved at time. Therefore, if you both created and viewed an issue which was opened at 03:00UTC, and your timezone is +3, it would be stored as 06:00, and you would see it opened as 09:00.

The cause of this is fixed in The Bug Genie 3.2, and new timestamps will be stored as UTC, so only your timezone offset is applied. This will fix your timezone problems for new issues, comments and so on, but not for previous ones. This is why the upgrade script gives you the option of fixing your timezones.

How does it work?

The script, if you ask it to, will go through every item in the database which involves a timestamp and will remove the offset coded into the database.

We look at the timezone of the user who caused the action (if he has one set, and it isn’t ‘sys’), and if one can’t be found we use the system timezone instead. Also note, if the guest user made a change, then the system timezone will also be used. If the outcome of this is UTC, then nothing needs to be done as there will be no error.

If, on the other hand, it is not UTC, then some work needs to be done. We can then perform the offset change on that timestamp, and this should in the majority of cases lead to the correct result. The advantage of this way is we catch cases where the user timezone is different to the system timezone.

There may still be some times which are incorrect, for example if either the user or system timezone had ever been something different prior to what it is during the upgrade, or if the user who caused the change cannot be identified (this is the case in the builds and milestones table). There is nothing that can be done in these cases to improve the accuracy, as we are suffering from either a lack of history of timestamp settings or users who performed actions, and so we give the option of disabling this conversion. You can also turn it off if you just want your timestamps left alone.

What will it convert?

Times will be corrected in the following tables:

  • articlehistory
  • articles
  • builds (will be corrected based on system timezone only)
  • comments
  • editions (will be corrected based on the system timezone only)
  • files
  • issues (posted, updated and being worked on since times)
  • log
  • milestones (will be corrected based on system timezone only)
  • projects (will be corrected based on the system timezone only)
  • vcsintegration

Written by lsproc

December 30, 2011 at 23:50

2 Responses

Subscribe to comments with RSS.

  1. So is this upgrader available already or still being worked on?

    Jeff C

    January 3, 2012 at 16:35

    • The upgrader already exists, and is used to upgrade from 3.1 to 3.0. The same script will upgrade from either 3.0 or 3.1 to 3.2, though it may be slightly out of date and is not officially supported right now, it will be later🙂 Timezone adjustment is not implemented yet, but it will be within the next few days.

      lsproc

      January 3, 2012 at 23:12


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: