By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,708 Members | 2,086 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,708 IT Pros & Developers. It's quick & easy.

Help With date/time

P: n/a
Hi,
I want to remove data that is older that a certain number of months..
only full months.

example: today is 11-08 and I want do delete all data that is older than
this month - 3 month i.e. i want to delete all information that is older
than Aug. 1st.

Is there an easy way to do that ? Interval cannot be used and I cannot
just pass something like now()::date < '2003-08'

Thanks for any help here
Alex

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
On Sun, Nov 09, 2003 at 02:04:22 +0900,
Alex <al**@meerkatsoft.com> wrote:
Hi,
I want to remove data that is older that a certain number of months..
only full months.
One approach would be to get the year, month and day as numbers from
the current date. Then change the day to 1 and subtract 3 from the
month (borrowing 1 from the year if necessary) and then use these
numbers to build the date you want to test against.

example: today is 11-08 and I want do delete all data that is older than
this month - 3 month i.e. i want to delete all information that is older
than Aug. 1st.

Is there an easy way to do that ? Interval cannot be used and I cannot
just pass something like now()::date < '2003-08'

Thanks for any help here
Alex

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 12 '05 #2

P: n/a
On Saturday 08 November 2003 9:04 am, Alex wrote:
Hi,
I want to remove data that is older that a certain number of
months.. only full months.

example: today is 11-08 and I want do delete all data that is older
than this month - 3 month i.e. i want to delete all information
that is older than Aug. 1st.


select date_trunc('month', now()) - '3 months'::interval;
?column?
------------------------
2003-08-01 00:00:00-07

Cheers,
Steve

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.