Thread: 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