Really only a mysql issue.
To get the number of days between now and the oldest row in the table, use
this query
SELECT TO_DAYS(NOW()) - TO_DAYS(MIN(date_column)) AS Num_Days FROM table;
To erase all rows that are older than $X days, use this query:
DELETE FROM table WHERE date_column < NOW() - INTERVAL $X DAY;
Easy, eh?
---John Holmes...
----- Original Message -----
From: "Brian McGarvie" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Thursday, May 23, 2002 8:27 AM
Subject: RE: [PHP] Date/Time...
I have an audit table which stores events like faile logins etc,
they are stored with a time in the format 2002-05-23 12:19:34
i want to know the number of days between 'now' and the oldest date in
the database.
This is so I can give them a drop-down list of the number of days they
might want to trim...
say a 'date_a' - 'date_b' = '15' [days] they could select '5' for
example and it'll remove all records older than 'now - 5'
at present I have done this...
$date_secs = strtotime ($date_oldest);
changes the '2002-05-23 12:19:34' format date to a timestamp, however
oldest time from database: converted to:972225660 converted back
(1970-01-01 00:00:00) has lost it somewhere ;\
(fuller code)
$date_oldest = $myrow["audit_timestamp"];
$now = time();
// format both back to 'nice' dates
$nice_now = date ("Y-m-d H:i:s" , $now);
$nice_old = date ("Y-m-d H:i:s" , $date_secs);
// change oldest date to timestamp
$date_secs = strtotime ($date_oldest);
echo "now: ".time()."($nice_now), oldest time from database: $date_secs
($nice_old)";
// compute the difference
$timediff = $date_secs - $now;
//get the int val of the days passed
$dayspassed = intval(abs(((($timediff/60)/60)/24)));
echo "<br><br>days elapsed: $dayspassed";
any ideas? on-top of what you mentioned?
> -----Original Message-----
> From: John Holmes [mailto:[EMAIL PROTECTED]]
> Sent: 23 May 2002 1:18 PM
> To: Brian McGarvie; [EMAIL PROTECTED]
> Subject: RE: [PHP] Date/Time...
>
>
> What are you trying to do? Compare dates from where to what? To the
> current time, to times in a database???
>
> strtotime() and UNIX_TIMESTAMP() are probably going to be part of your
> solution, but I don't know what your doing.
>
> ---John Holmes...
>
> > -----Original Message-----
> > From: Brian McGarvie [mailto:[EMAIL PROTECTED]]
> > Sent: Thursday, May 23, 2002 7:22 AM
> > To: [EMAIL PROTECTED]
> > Subject: [PHP] Date/Time...
> >
> > OK, give up.. been trying to compare dates in the format;
> >
> > 2002-05-23 12:19:34
> >
> > I just cant workout how to compare, I've converted to a timestamp -
> but
> > the resulting timestamp was wrong.
> >
> > Any help much appreciated...
> >
> > TIA...
> >
> > --
> > PHP General Mailing List (http://www.php.net/)
> > To unsubscribe, visit: http://www.php.net/unsub.php
>
>
>
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
