date computation

[jansky]

to all guru
i have one field in my table called date_reg format is datetime
how can i compute for

15 days less
30 days or 1 month less but not lesser than 15 days
2 months
3 months
24 hours

[Sxooter]

Use intervals, assuming your database supports them, as they're quite transportable:

select '2005-10-01' - interval '1 month'
select * from db where somefield between now() - interval '30 days' and now() - interval '15 days'

Kinda stuff.

[superwormy]

Alternatively, you could pull the date out of the database, use strtotime(), and then subtract/add whatever number of seconds away from it... then convert it back with date()

[Sxooter]

Actually, I use a compromise of the two, superwormy. On the older versions of postgresql, now() - interval '1 month' type things aren't indexable, so I'd do a single query of :

select now() - interval '30 days'

Get that back and build the query with the date string I got out of that:

select * from table where date between '$start' and '$end';

The main advantage to doing all your date work in the database is that it handles all your time zone conversion for ya.