Posted by Liberation on July 30, 2008 at 2:25am
| Project: | Event |
| Version: | 6.x-2.x-dev |
| Component: | Code |
| Category: | bug report |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | postponed (maintainer needs more info) |
Issue Summary
On my PostgreSQL installation, the upcoming events block is always empty. It appears in the existing 1.15 version of event_database.pgsql.inc that the function event_get_events_upcoming() needs amendment, and I propose the following fix:
Change at line 121:
$query = "SELECT n.nid, n.title, n.type, n.status, n.changed, e.event_start, e.event_end, e.timezone, e.has_time, e.has_end_date, extract('day' from event_start - '%s')*24 + cast(substring(cast(event_start - '%s' as varchar) from ' (..):') as integer) || substring(cast(event_start - '%s' as varchar) from ' ..(:..:..)') as time_left, 0 AS days_left FROM {node} n INNER JOIN {event} e ON n.nid = e.nid WHERE n.status = 1 AND (((e.event_start <> e.event_end) AND ('%s' >= e.event_start AND '%s' < e.event_end)) OR ((e.event_start = e.event_end) AND ('%s' <= e.event_start + '%d seconds')) OR e.event_start >= '%s') AND n.type IN ($placeholders) ORDER BY event_start";
return db_query_range(db_rewrite_sql($query, 'n', 'nid', array_merge(array('event' => TRUE, 'event_get_events' => TRUE, 'event_get_events_upcoming' => TRUE, 'event_types' => $types), $rewrite_parameter)), array_merge(array($time, $time, $time, $time, $time, $time, $time, $time, (60 * 60 * 2), $time), $types), 0, $limit);to:
$query = "SELECT n.nid, n.title, n.type, n.status, n.changed, e.event_start, e.event_end, e.timezone, e.has_time, e.has_end_date, CAST(((CAST(EXTRACT (EPOCH FROM event_start - '%s') AS INTEGER) % (24*60*60)) * INTERVAL '1 sec') AS VARCHAR) AS time_left, CAST(EXTRACT (EPOCH FROM event_start - '%s') AS INTEGER) / (24*60*60) AS days_left FROM {node} n INNER JOIN {event} e ON n.nid = e.nid WHERE n.status = 1 AND (((e.event_start <> e.event_end) AND ('%s' >= e.event_start AND '%s' < e.event_end)) OR ((e.event_start = e.event_end) AND ('%s' <= e.event_start + '%d seconds')) OR e.event_start >= '%s') AND n.type IN ($placeholders) ORDER BY event_start";
$query_full = db_rewrite_sql($query, 'n', 'nid', array_merge(array('event' => TRUE, 'event_get_events' => TRUE, 'event_get_events_upcoming' => TRUE, 'event_types' => $types), $rewrite_parameter));
return db_query_range($query_full, array_merge(array($time, $time, $time, $time, $time, (60 * 60 * 2), $time), $types), 0, $limit);
Comments
#1
For completeness, it's worth noting that the above solution displays the remaining period to the event correctly only where the user timezone and the event timezone have currently equal UTC/DST offsets. Although this will often be the case, in general it won't for a global user and event mix.
Unfortunately a full fix for this would require considerably more substantial modification to the Event module codebase, including the two MySQL source files. One approach would be to use UTC date/times as follows:
_event_block_upcoming(), invoke a (new) function_event_user_time_utc()instead of_event_user_time()event_get_events_upcoming():INNER JOIN {event_timezone} z ON e.timezone = z.timezonee.event_startoccurrences toe.event_start - (CASE e.start_in_dst WHEN 1 THEN z.offset_dst ELSE z.offset)Alternatively, it might be simpler to just return the join results and do the calculations programmatically rather than in the SQL.
Not ideal, I guess, but this is quite a tricky area.
#2
Please make a proper patch.
#3
What happened to this particular issue?
Event blocks are still broken for all versions of PostgreSQL. Sounds like you have a proposed solution. Why not finish it up and submit a patch? Do you have anything partially complete? Care to post it and let someone else work on it?
#4
Regrettably I've had to abandon PostgreSQL due to problems like this in numerous modules.
#5
Just got bitten by this bug. Here is the above fix as a patch against event-6.x-2.x-dev.tar.gz released on 2009-Aug-29.
Cheers