datetime numerations

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
mhouldridge
Forum Contributor
Posts: 267
Joined: Wed Jan 26, 2005 5:13 am

datetime numerations

Post 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!
jmut
Forum Regular
Posts: 945
Joined: Tue Jul 05, 2005 3:54 am
Location: Sofia, Bulgaria
Contact:

Re: datetime numerations

Post 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);
User avatar
mhouldridge
Forum Contributor
Posts: 267
Joined: Wed Jan 26, 2005 5:13 am

Post 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!
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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;
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
trukfixer
Forum Contributor
Posts: 174
Joined: Fri May 21, 2004 3:14 pm
Location: Miami, Florida, USA

Post 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)
User avatar
mhouldridge
Forum Contributor
Posts: 267
Joined: Wed Jan 26, 2005 5:13 am

Post 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

?
User avatar
mhouldridge
Forum Contributor
Posts: 267
Joined: Wed Jan 26, 2005 5:13 am

Post by mhouldridge »

Ang on a second....

Yep, I think that's done it!

Thank you!
User avatar
mhouldridge
Forum Contributor
Posts: 267
Joined: Wed Jan 26, 2005 5:13 am

Post by mhouldridge »

Is the first reply to this a mysql query or just php?
User avatar
mhouldridge
Forum Contributor
Posts: 267
Joined: Wed Jan 26, 2005 5:13 am

Post by mhouldridge »

Scrap that, I dont know what im on about.
Post Reply