RE: Date arithmatic question

Hi Bryan,

Try this:

select * from t where date_part('days', 'now'::timestamp - s) = ?;

Subtracting two timestamps in this way returns an 'interval', which
(unless you use the 'age' function) doesn't figure out the months and
the years - so 3 months would be rendered as "92 00:00"

Also, note that I think you need to use "days" rather than "day" as the
1st arg to date_part.

Hope this helps you.

Francis Solomon

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Bryan (Mailing
> Lists)
> Sent: 17 November 2000 22:34
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Date arithmatic question
>
>
> I have a table "t" with a timestamp column "s". I am trying
> to issue a query
> to find all rows where s is exactly some number of days old
> (rounded off).
>
> I have tried this:
>
> select * from t where date_part('day', age('now', s)) = ?
>
> But this only looks at the day of the month; e.g. if my
> parameter is "5",
> then it will return all rows that are 5 days old, as well as
> 1 month 5 days,
> as well as 2 months 5 days, etc.
>
> I have also tried this:
>
> select * from t where date_part('day', age('now', s)) = ? and
> date_part('month', age('now', status_last_update)) = 0
>
> But this restricts my parameter to 30 days or less; greater
> than 30 days and
> the query doesn't return anything.
>
> What is the correct way to express this?
>
> Thank you,
>
> Bryan
>
>
>