Forum OpenACS Q&A: month info query & date arithmetic

if I add

to_char(to_date(now() +'7 days' ::interval,'YYYY-MM-DD'),'YYYY-MM-DD') as next_weeks_date

I get a date 7 days in advance like 2001-12-26

but if I substitute '$date' for now() the query fails.

can anyone tell me how to add next weeks date to the calendar query??

Collapse
Posted by Gilbert Wong on

Two things I can think of offhand.

  1. If you are using { } to enclose your sql query instead of " ", it will not substitute $date into your sql query. It will literally use $date.
  2. You need to make sure $date is in timestamp/date format.

to_char(timestamp($date, 'YYYY-MM-DD') + '7 days'::interval,'YYYY-MM-DD') as next_weeks_date

I think I got the syntax correct.

Collapse
Posted by Peter Breugel on
Hi, in addition to use the query within " ... " rather than
{ ... } and using the right iso format for $days,
e.g. what comes back from "select now();":
2001-12-20 10:36:53+01
I think you have to write '$date'.
And you don't have to use ::interval, simply add the number
of days, e.g. "now() + 7". Peter.
Collapse
Posted by Don Baccus on
All sorts of mini-issues here ...

As Peter noted you can just write

"now() + 7".

  If you do that, though, PG will return a date (no time info) not a timestamp.  Which happens to be what you want in this case.  However ... the PG group is busy tossing old date semantics overboard in favor of an approach more congruent with the standard.  So the above construct may not work in PG 7.3 or later versions.

To maximize your chances of not having to change your code in the future I'd suggest the following construct:

date (current_timestamp + interval '7 days')