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.