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.

Files: 
CommentFileSizeAuthor
#4 signup-cron-date.patch545 bytesjfranklin

Comments

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

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.

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

Version:6.x-1.0-rc4» 6.x-1.0-rc6
Issue tags:+PostgreSQL, +date, +interval, +interpolation
StatusFileSize
new545 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.

This occurs in signup 6x-1.0 for me.

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

See: #1112576: Postgres Error with Signup - sql_date_math

Tagging