Re: [GENERAL] Selecting between dates

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