NOW()

Im using postgres 8.1 I'm trying to delete old records in a table by date.


table look like this:

Table "jobhist"
Column | Type | Modifiers
-----------------+-----------------------------+------------ -------------------
sched_date | date | not null default
'0000-01-01'
schedlog_file | character varying(16) | not null
start_time | timestamp without time zone |

How do I delete records over a month old from sched_date?

delete from jobhist where start_time < now() - interval '2 month';

I even tried :

delete from jobhist where sched_date < now() - interval '2 month';


Does not work!? What am I doing wrong? This is obviously not getting
the date in the correct format.



when I do now() I get:
now
-------------------------------
2006-10-13 17:02:18.067126-04
(1 row)

select now() - interval '2 month';
?column?
------------------------------
2006-08-13 16:51:09.40038-04
(1 row)

Anyone know what Im doing incorrectly?

thanks!

-Rob

Re: NOW()

at wrote:

>
> Im using postgres 8.1 I'm trying to delete old records in a table by date.
>
>
> table look like this:
>
> Table "jobhist"
> Column | Type | Modifiers
> -----------------+-----------------------------+------------ -------------------
>
> sched_date | date | not null default
> '0000-01-01'
> schedlog_file | character varying(16) | not null
> start_time | timestamp without time zone |
>
> How do I delete records over a month old from sched_date?
>
> delete from jobhist where start_time < now() - interval '2 month';
>
> I even tried :
>
> delete from jobhist where sched_date < now() - interval '2 month';
>
>
> Does not work!? What am I doing wrong? This is obviously not getting
> the date in the correct format.
>
>
>
> when I do now() I get:
> now
> -------------------------------
> 2006-10-13 17:02:18.067126-04
> (1 row)
>
> select now() - interval '2 month';
> ?column?
> ------------------------------
> 2006-08-13 16:51:09.40038-04
> (1 row)
>
> Anyone know what Im doing incorrectly?
>
I don't know what you are doing wrong, but you are going about it
dangerously; you could easily wipe out all of your records using your
technique.

Use "select" to retrieve the records, then delete them.
Mick

Re: NOW()

at wrote:
> Im using postgres 8.1 I'm trying to delete old records in a table by date.
>
>
> table look like this:
>
> Table "jobhist"
> Column | Type | Modifiers
> -----------------+-----------------------------+------------ -------------------
> sched_date | date | not null default
> '0000-01-01'
> schedlog_file | character varying(16) | not null
> start_time | timestamp without time zone |
>
> How do I delete records over a month old from sched_date?
>
> delete from jobhist where start_time < now() - interval '2 month';
>
> I even tried :
>
> delete from jobhist where sched_date < now() - interval '2 month';
>
>
> Does not work!? What am I doing wrong? This is obviously not getting
> the date in the correct format.
>
>
>
> when I do now() I get:
> now
> -------------------------------
> 2006-10-13 17:02:18.067126-04
> (1 row)
>
> select now() - interval '2 month';
> ?column?
> ------------------------------
> 2006-08-13 16:51:09.40038-04
> (1 row)
>
> Anyone know what Im doing incorrectly?
>
> thanks!
>
> -Rob

Well, I don't have a postgres database to play around with, but could
it be that the NOW() call and calculation is returning a TIMESTAMP (or
DATETIME) value which you are comparing to a DATE value.