Event_views doesn't handle timezones correctly [#103374]

After talking with merlinofchaos and killes in IRC, we've found a bug in the way event_views.module handles timezones. Specifically, when creating a View of event nodes using arguments for year/month/day, events are selected based on UTC and then not properly translated to local time. The result is that events are selected based on an offset from their actual time.

For example, I have an event in GMT-6 at 10 pm on 2006-12-12, and I ask for events on 2006-12-13. Views first switches the timezone of the SQL server to UTC, then the SQL query selects records like so:

WHERE (YEAR(from_UNIXTIME(event.event_start)) = 2006)
AND (MONTH(from_UNIXTIME(event.event_start)) = 12)
AND (DAYOFMONTH(from_UNIXTIME(event.event_start)) = 13)

FROM_UNIXTIME() is now operating based on UTC, so since 10 pm GMT-6 on 12/12 is 4 am UTC, it matches and is returned.

My best guess for a solution is to add/subtract from the timestamp based on the timezone field inside the from_unixtime call, which would convert it to the desired timezone. I'm not sure how well that would work, though.

Comment	File	Size	Author
#2	timezones.patch	1.06 KB	Crell

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

Comment #1

KarenS CreditAttribution: KarenS commented 15 December 2006 at 17:25

Project:	Event	» Event Views
Version:	4.7.x-1.x-dev	»

Moving this issue, then I'll take a look at it...

Comment #2

Crell CreditAttribution: Crell commented 15 December 2006 at 20:30

Status:

Active

» Needs review

File	Size
timezones.patch	1.06 KB

Hm, Silly me, didn't realize this had a separate issue queue.

I think I found a simple solution, that seems to work so far. Patch attached. It just adds the timezone offset to to the timestamp before from_unixtime()ing it. It works for me so far, but I've not tried it with user-configurable timezones. Probably needs work. :-)

Comment #3

KarenS CreditAttribution: KarenS commented 15 December 2006 at 22:09

I may need to merge the issue queue back into the event queue now that the new release system is out since you can no longer tag issues with release numbers in this separate queue, but that's another story...

Anyway, I tried your solution first, and it does work in many cases, but the problem is more complex than it first appears. The views timezone handling methods ignore any preferences selected in the event settings, since views doesn't have any reason to care about events, so I think I am going to need to write an event-specific function to handle timezone conversions, then add the offset calculated by that function to the query as you have done in your patch.

Comment #4

KarenS CreditAttribution: KarenS commented 16 December 2006 at 13:25

I think I can fix every situation but one, and that is if you have selected that you want an event-specific timezone. We can't use either the user or the site's offset to search for that, we need the correct offset value for that timezone actually stored in the database so we can use it in the query.

When I saw that you can't do a proper query for event-specific timezones, I wondered how it is that it seems to work OK in the event module. After digging around it appears that the event module system is to do a very broad query (even if you're looking at a single day it queries the whole month), then do a node_load() on each event the query generates. The node_load() invokes a function that looks up the event timezone offset and adds it to the node. Then that info is used to place the event on the calendar so the event only gets added to the calendar if it fits in the viewed parameters.

To do something similiar in Views I would have to expand the query range to be sure I pick up events that are in different timezones that might not otherwise get picked up, then pull up each item found and do a node_load() on it to find the offset and figure out if it really belongs in the results, then knock any events that don't belong there out of the $items list before displaying it. That will work, but will throw off the item count, which might be completely wrong, plus it is a very messy and inefficient method. The better solution for Views is to store the event offset in the database for events that have their own timezones, then use that value in the query to find the right events.

I'm going to commit a fix that will handle user and site timezones, and start an issue to request that we store the event offset in the event table so that we can do a proper query for events that have event-specific timezones.

Comment #5

Crell CreditAttribution: Crell commented 18 December 2006 at 02:35

Yeah, it's a tricky question. But doing node_load() on an arbitrary number of possible matches is not a solution. I'm not sure if it's event or calendar module that's doing it, but when viewing a calendar.module block for a given month I'm getting upwards of 600 queries on a page. So far my client hasn't noticed, and I hope they don't, but that's not a real solution. :-) It's more the general case of Drupal not handling multi-node operations very well at all. (The really good solutions that I know of are all PHP 5-specific.)

Is this the patch you are talking about committing, or something else? I'd rather not redownload a -dev of the module, since I've had to make some site-specific changes already for this client. (Some things that have been submitted as bug patches, plus commenting out some special casing for jscalendar because it's more trouble than it's worth for the admin-side.)

Comment #6

KarenS CreditAttribution: KarenS commented 18 December 2006 at 10:57

I submitted a patch for the event module at http://drupal.org/node/103687. If killes is agreeable to that, that would be the best way to fix this since I can just use the offset in the database in my query for event-specific dates. If it gets added to the event module, I would then need to patch event views to use that column. Your patch is fine for other situations and is pretty close to what I'll put in until I get some idea on the direction of the event patch, so you're OK using what you have unless you have event-specific timezones.

Yep, I totally agree on the node_load, but right now for the calendar module to display events I have to do node_load on each event to get the relevant data. That needs to be reworked, and an offset column in the event table would help things a lot in that module, too. The issue queue for the calendar module is stacking up and I need to devote some time to it ASAP, so that's one of the things I'll be looking at.