Selecting between dates

Thanks Aaron,

That is exactly what I wanted. Much more simple then what I was thinking.

On 10-Aug-99 Aaron Seigo wrote:

hi wim..

select * from company where created
is between (lastpaid + 14 days) and (created + 28 days);

assuming that lastpaid and created are dates, pgsql will handle the
addition quite nicely for you... as examples:

test=> select '4-1-1999'::date + 7;
?column?
----------
04-08-1999
(1 row)

test=> select '4-26-1999'::date + 7;
?column?
----------
05-03-1999

test=> select '12-26-1999'::date + 7;
?column?
----------
01-02-2000
(1 row)

Regards,
---------------------------
Wim Kerkhoff
wim@netmaster.ca
www.canadianhomes.net/wim
ICQ: 23284586

hi wim..

select * from company where created
is between (lastpaid + 14 days) and (created + 28 days);

assuming that lastpaid and created are dates, pgsql will handle the
addition quite nicely for you... as examples:

test=> select '4-1-1999'::date + 7;
?column?
----------
04-08-1999
(1 row)

test=> select '4-26-1999'::date + 7;
?column?
----------
05-03-1999

test=> select '12-26-1999'::date + 7;
?column?
----------
01-02-2000
(1 row)

Aaron J. Seigo
Systems Analyst/Administrator

On 11-Aug-99 Simon Drabble wrote:

On Tue, 10 Aug 1999, Wim Kerkhoff wrote:

Hi,

I am creating a script that sends out reminders to subscribers to a site who
have not paid. I can't figure out the math that will add 7 days to a date.

I want something like this:

select * from company where created
is between (lastpaid + 14 days) and (created + 28 days);

I can't figure out the syntax that will let me add days to a date. I
checked
the archives and the Postgresql doc's, but nothing.

Ideas?

Use the ::reltime specifier, e.g.

select * from company
WHERE created > (lastpaid + ('14 days'::reltime))::datetime
AND created < (lastpaid + ('28 days'::reltime))::datetime;

Although I suspect you need "- ('14 days'..." if you are talking about the
past.

I ended up using something like:
"select pid,name,email from company
where date('now'::date) between (lastpaid + 7) and
(lastpaid + 14);"

as per someone else's idea. This is simple and shorter than what I was first
thinking of, and your idea.

The date functions are documented very well in the postgres docs,
specifically
the user section.

I checked out the docs, but I couldn't find any usage the above example.

Well, I got it working just dandy now, and learned a bunch of new stuff again.

Thanks everyone,
---------------------------
Wim Kerkhoff
wim@netmaster.ca
www.canadianhomes.net/wim
ICQ: 23284586

On Tue, 10 Aug 1999, Wim Kerkhoff wrote:

Hi,

I am creating a script that sends out reminders to subscribers to a site who
have not paid. I can't figure out the math that will add 7 days to a date.

I want something like this:

select * from company where created
is between (lastpaid + 14 days) and (created + 28 days);

I can't figure out the syntax that will let me add days to a date. I checked
the archives and the Postgresql doc's, but nothing.

Ideas?

Thanks,

Wim Kerkhoff.

Use the ::reltime specifier, e.g.

select * from company
WHERE created > (lastpaid + ('14 days'::reltime))::datetime
AND created < (lastpaid + ('28 days'::reltime))::datetime;

Although I suspect you need "- ('14 days'..." if you are talking about the
past.

The date functions are documented very well in the postgres docs, specifically
the user section.

Simon.

--
"Don't anthropomorphise computers - they don't like it."

Simon Drabble It's like karma for your brain.
simon@eskimo.com