I noticed an odd issue with one of my sites that is doing a lot of date queries. The site's timezone is UTC+2. A lot of dates are saved at midnight ont the first of each month (e.g. date invoiced is April 1, May 1, etc.). The dates are saved correctly in the DB but when I ran some monthly reports on them, they were counted in the wrong month. I checked the queries in PHPMyAdmin and they were correct, but when run within Views, they returned the incorrect result.

After some search, I finally came across views_get_timezone() in includes/handlers.inc. I noticed that the DB session's timezone was changed from SYSTEM to UTC+0, hence the time of 1.4.2013 0:00 was interpreted as 31.3.2013 22:00 and counted towards the wrong month. I couldn't think of a reason why the DB session's timezone would be changed in such an arbitrary manner - does anyone have any ideas or is this a bug?

-Daniel Chvatik
Ensemble Manhattan Co-working space

Comments

merlinofchaos’s picture

merlinofchaos commented
Category: bug » support
Status: Active » Closed (works as designed)

You can't rely on the SYSTEM timezone, which is why Views does that. The assumption is that all timestamp dates are saved in UTC (this should be true of all the standard Drupal times) and interpreted to user-local timezone upon render. Any querying Views does should then compensate by putting all other dates to also be in said timezone.

kerberos’s picture

kerberos commented

Odd, in my case it appears that Date module wasn't saving them in UTC. Maybe it's a problem with Date module then. Thanks, Merlin.

-Daniel Chvatik
Ensemble Manhattan Co-working space