Bug and Patch: Revenue Report in backend does not take timezone into account

[thebeuving]

When you run a revenue report the numbers don't line up perfectly if you aren't on UTC time because it doesn't take into account the joomla timezone. I am on Joomla 2.5.8 and Virtuemart 2.0.14

In administrator/components/com_virtuemart/models/report.php  replace line 111 - 129 with the following:

Code Select Expand


    switch ($intervals) {
      case 'day':
        $this->intervals = 'DATE( convert_tz(o.created_on, "UTC", "'.$this->tzoffset.'") )';
        break;
      case 'week':
        $this->intervals = 'WEEK( convert_tz(o.created_on, "UTC", "'.$this->tzoffset.'") )';
        break;
      case 'month':
        $this->intervals = 'MONTH( convert_tz(o.created_on, "UTC", "'.$this->tzoffset.'") )';
        break;
      case 'year':
        $this->intervals = 'YEAR( convert_tz(o.created_on, "UTC", "'.$this->tzoffset.'") )';
        break;
      default:
        // invidual grouping
        $this->intervals = 'convert_tz(o.created_on, "UTC", "'.$this->tzoffset.'")';
        break;
    }


I have also attached a patch if that will be easier to work with.

Thanks,
David

[attachment cleanup by admin]

[Milbo]

Great thank you

I also noticed that, thanks for the patch, added.

[Milbo]

I am sorry, but this does not work.
I changed it now so, that hte stored time is considering the offset.

[mdousset]

Milbo, what version of Virtuemart is this resolved in and is this issue related to this other post?
http://forum.virtuemart.net/index.php?topic=112093.0
Thanks

[Milbo]

The problem is a bit more complex than we all think in the first moment.

The idea is that you we use for storing always the GMT timezone. The world time zone.

But then, when you get the data out, should we consider the timezone of the server? or the timezone of the user checking it? I took me quite a while to understand that we need a configuration there. I fear there is no other chance. It should take maybe the timezone of the mainvendor, maybe that is the right solution.

[lindapowers]

It should take maybe the timezone of the mainvendor, maybe that is the right solution.

Yeah I think that is the most logic thing.

[mdousset]

From our perspective, storing the time in GMT is not very practical as we have to keep that in mind with every direct operations we do against the database.  It would seem a lot simpler to store the time using the server's local timezone or the Joomla/vendor timezone.  This way, the conversion is done once and does not have to be done upon every retrieval, either by Virtuemart or other.

[Milbo]

Mdouseet, but this is not the norm. The norm is that you always think in GMT. A good server is using internal always GMT and just displays it in your timezone.

Seems that you ahve to connect it to some software of the 70's. In the 80 we started to use always GMT,.. same like use always UTF-8. We store in UTF-8, but watch it on our browser with another charset, works.