datetime numerations
Moderator: General Moderators
- mhouldridge
- Forum Contributor
- Posts: 267
- Joined: Wed Jan 26, 2005 5:13 am
datetime numerations
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!
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
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 dayCode: Select all
DELETE FROM table WHERE date_and_time < (NOW() - INTERVAL 30 DAY);- mhouldridge
- Forum Contributor
- Posts: 267
- Joined: Wed Jan 26, 2005 5:13 am
~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;Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
- trukfixer
- Forum Contributor
- Posts: 174
- Joined: Fri May 21, 2004 3:14 pm
- Location: Miami, Florida, USA
Actually you want date_sub()
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 30 day)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)- mhouldridge
- Forum Contributor
- Posts: 267
- Joined: Wed Jan 26, 2005 5:13 am
Here is my query now;
This doesnt seem to do anything
?
Code: Select all
$result = mysql_query("SELECT * FROM audithistory WHERE postdate < date_sub(now(),interval 1 month)");?
- mhouldridge
- Forum Contributor
- Posts: 267
- Joined: Wed Jan 26, 2005 5:13 am
- mhouldridge
- Forum Contributor
- Posts: 267
- Joined: Wed Jan 26, 2005 5:13 am
- mhouldridge
- Forum Contributor
- Posts: 267
- Joined: Wed Jan 26, 2005 5:13 am