Auto delete rows older than a month

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Steve - 17 Jan 2006 22:09 GMT

delete from MYTABLE where date(MYCOLUMN) = current date - 30

"MYCOLUMN" is of type timestamp.

This query doesnt' work. Anything I missed here? I am hoping to
automate it on a daily basis. Anyone knows a better way to do it? Much
appreciated.

Reply to this Message

Knut Stolze - 17 Jan 2006 22:12 GMT

> delete from MYTABLE where date(MYCOLUMN) = current date - 30
>
[quoted text clipped - 3 lines]
> automate it on a daily basis. Anyone knows a better way to do it? Much
> appreciated.

You probably meant:

delete from MYTABLE where date(MYCOLUMN) = current date - 30 DAYS

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message

Gert van der Kooij - 17 Jan 2006 22:22 GMT

> delete from MYTABLE where date(MYCOLUMN) = current date - 30
>
[quoted text clipped - 3 lines]
> automate it on a daily basis. Anyone knows a better way to do it? Much
> appreciated.

delete from MYTABLE where date(MYCOLUMN) = current date - 30 days

Reply to this Message

bughunter@ru - 18 Jan 2006 06:49 GMT

where date(MYCOLUMN) = current date - 30 DAYS

But this query will be not optimal, because it will be indexscan by
mycolumn index.

If query is long rewrite it like

where MYCOLUMN >= timestamp(current date - 30 DAYS,'00:00') and
MYCOLUMN < timestamp(current date - 29 DAYS,'00:00')

Andy

> delete from MYTABLE where date(MYCOLUMN) = current date - 30
>
[quoted text clipped - 3 lines]
> automate it on a daily basis. Anyone knows a better way to do it? Much
> appreciated.

Reply to this Message

Steve - 19 Jan 2006 22:45 GMT

"current date - 30 days" does what I need. Thanks a lot!

Reply to this Message