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

Collapse
1: month info query & date arithmetic
Posted by Marshall Trammell,III on 12/20/01 02:11 AM
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
2: Response to month info query & date arithmetic (response to 1)
Posted by Gilbert Wong on 12/20/01 02:57 AM

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
3: Response to month info query & date arithmetic (response to 1)
Posted by Peter Breugel on 12/20/01 09:29 AM
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
4: Response to month info query & date arithmetic (response to 1)
Posted by Don Baccus on 12/20/01 05:53 PM
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')