Page 1 of 1

datetime numerations

Posted: Thu Dec 01, 2005 8:35 am
by mhouldridge
Hi,

I have a logs table that stores the following information;

id
user
action
date and time


Each of my scripts adds a new log to the logs table with information for the above fields.

I would like to show logs from he past 30days only, and delete any logs older than that (to save database size, and because i dont require logs that are older than this).

My data and time field is in the following format;

2005-12-01 14:13:02

Please help!

Re: datetime numerations

Posted: Thu Dec 01, 2005 9:29 am
by jmut
mhouldridge wrote:Hi,

I have a logs table that stores the following information;

id
user
action
date and time


Each of my scripts adds a new log to the logs table with information for the above fields.

I would like to show logs from he past 30days only, and delete any logs older than that (to save database size, and because i dont require logs that are older than this).

My data and time field is in the following format;

2005-12-01 14:13:02

Please help!

Code: Select all

SELECT (NOW() - INTERVAL 30 DAY);   ->  should return the date before 30 days from current day
UNTESTED code!!!! Backup you DB first :)

Code: Select all

DELETE FROM table WHERE date_and_time < (NOW() - INTERVAL 30 DAY);

Posted: Thu Dec 01, 2005 9:53 am
by mhouldridge
Hi,

Just tried echo'ing that, however the code errors....

It doesnt look right to me.

Are you sure INTERVAL 30 DAYS parts is right ?


thanks so far!

Posted: Thu Dec 01, 2005 9:58 am
by pickle
~jmut's method is simple, but you might run into trouble in February, or 31 numbered months. I think you can change "30 DAYS" to "1 MONTHS" Using PHP and strtotime() should also work.

Code: Select all

$thirty_days_ago = strtotime('-1 month');
$query = <<<SQL
DELETE
   *
FROM
   myTable
WHERE
   UNIX_TIMESTAMP(date_and_time) < '$thirty_days_ago'
SQL;

Posted: Thu Dec 01, 2005 10:05 am
by trukfixer
Actually you want date_sub()

Code: Select all

select * from table1 where date < date_sub(now(),interval 30 day)
Do note- just because it is more than one day, you dont say "interval 30 days" -
mysql's keywords for date sub are singular

second, minute, hour, day, month, year and so on...
thus:

Code: Select all

select * from table1 where date < date_sub(now(),interval 1 month)

Posted: Fri Dec 02, 2005 6:19 am
by mhouldridge
Here is my query now;

Code: Select all

$result = mysql_query("SELECT * FROM audithistory WHERE postdate < date_sub(now(),interval 1 month)");
This doesnt seem to do anything

?

Posted: Fri Dec 02, 2005 7:20 am
by mhouldridge
Ang on a second....

Yep, I think that's done it!

Thank you!

Posted: Tue Dec 06, 2005 4:13 am
by mhouldridge
Is the first reply to this a mysql query or just php?

Posted: Tue Dec 06, 2005 5:58 am
by mhouldridge
Scrap that, I dont know what im on about.