Обсуждение: Selecting between dates

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.

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

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

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

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