When running Signup 6.x-1.0-rc4 on Drupal 6.13 and Postgres 8.3 you get an error due to incorrect syntax.

query: SELECT n.title, n.nid, n.type, s.reminder_email, s.forwarding_email, e.event_start AS event_start, e.timezone FROM node n INNER JOIN signup s ON s.nid = n.nid INNER JOIN event e ON n.nid = e.nid INNER JOIN event_timezones tz ON e.timezone = tz.timezone WHERE (s.send_reminder = 1) AND (n.type = 'event') AND (('2009-06-22 06:21:03' > ((e.event_start - CASE WHEN tz.is_dst = 1 THEN tz.offset_dst ELSE tz."offset" END) - INTERVAL 's.reminder_days_before days'))) AND (('2009-06-22 06:21:03' <= ((e.event_start - CASE WHEN tz.is_dst = 1 THEN tz.offset_dst ELSE tz."offset" END)))) in /var/www/intranet/sites/all/modules/signup/includes/cron.inc on line 46.

Changing line 110 in signup/includes/event.6x-2.inc from:

    "('". gmdate('Y-m-d H:i:s') ."' > (". event_where_utc() ." - INTERVAL 's.reminder_days_before days'))",

to:

    "('". gmdate('Y-m-d H:i:s') ."' > (". event_where_utc() ." - INTERVAL '1 days'*s.reminder_days_before))",

fixed the problem for me.

CommentFileSizeAuthor
#4 signup-cron-date.patch545 bytesjfranklin
Members fund testing for the Drupal project. Drupal Association Learn more

Comments

dww’s picture

I'd love to know if the proposed syntax works on other versions of pgsql, too...

poumol’s picture

It does. :) (At least in 6.5 and 7.2)

Although maybe one (if one where a bit better at php than I am) would want a neater or more elegant solution that what I proposed. The real error is that the string containing the query that is passed to Postgres contains the variables name (s.reminder_days_before) instead of the value. I assume it has something to do with how quotations are handled in php, since it works when I moved the variable outside the single quotes in the string.

marlatt’s picture

Thank you for this post, was just hunting down this problem.

jfranklin’s picture

Version: 6.x-1.0-rc4 » 6.x-1.0-rc6
Issue tags: +PostgreSQL, +date, +interval, +interpolation
FileSize
545 bytes

It's back. This is with Signup 1.0-rc6 and PostgreSQL 8.4. (Ubuntu Karmic)

warning: pg_query() [function.pg-query]: Query failed: ERROR: invalid input syntax for type interval: "s.reminder_days_before DAYS" LINE 1: ..., 'FMYYYY-FMMM-FMDDTFMHH24:FMMI:FMSS') - INTERVAL 's.reminde... ^ in /usr/share/drupal6/includes/database.pgsql.inc on line 139.
user warning: query: SELECT n.title, n.nid, n.type, s.reminder_email, s.forwarding_email, field_date_value FROM node n INNER JOIN signup s ON s.nid = n.nid INNER JOIN content_field_date ON content_field_date.vid = n.vid WHERE (s.send_reminder = 1) AND (n.type = 'event') AND ('2010-01-21 19:44:41' >= (TO_DATE(field_date_value, 'FMYYYY-FMMM-FMDDTFMHH24:FMMI:FMSS') - INTERVAL 's.reminder_days_before DAYS')) AND ('2010-01-21 19:44:41' <= (TO_DATE(field_date_value, 'FMYYYY-FMMM-FMDDTFMHH24:FMMI:FMSS') + INTERVAL '1 HOURS')) in /etc/drupal/6/sites/all/modules/signup/includes/cron.inc on line 46.

The patch in the description is already in place. It seems the same syntax was used in the includes/date.inc _signup_date_sql_math() function. The attached patch fixes it. It is similar to the patch above.

maikeru’s picture

This occurs in signup 6x-1.0 for me.

Although the problem seems to be in the date module rather then signup.

See: #1112576: SQL: Field reference as adjusting value of time interval

Liam Morland’s picture

Tagging