mySQL timestamp(14)

[web guru]

I am trying to find the difference between the current date and and the date each record was created in my database. I have used a field called date in my database of type timestamp(14) which automatically inserts the date and time the recorde was created.

Now how do I get the diff??

 

[deadserious]

That's a tough one. If you could get the date values in the format of yyyymm or yymm then you could easily get the difference in months and years with mysql's period_diff() function. Something like this:

$result = mysql_fetch_array(mysql_query("select period_diff(200309,199909) as diff;"));

That would give you the difference in months and then you could calculate the years with something like:
$yearsdif = $result['dif'] / 12;

Some pages that may or not help you:
http://www.faqts.com/knowledge_base/view.phtml/aid/12241/fid/15

http://www.mysql.com/doc/en/Date_and_time_functions.html

http://us3.php.net/manual/en/ref.datetime.php

 

[web guru]

I was trying to figure this out all last night and I finally cracked it. I was trying to find the difference using php, but it was proving difficult because, well lets just say it was to dam awkward. So I found that it was easier done using SQL, like the way you did it dead. The following peace of SQL checks the database and any records that are over five days old are deleted.

mysql_query("DELETE FROM pass WHERE TO_DAYS(NOW()) - TO_DAYS(date) > 5;")

I am going to run this as a cron job every night so that old entries are deleted out of my database.

 

[deadserious]

Cool, atleast you figured out something that works for what you wanted. :D

I wonder what you're doing though, deleting users after five days or just their password or? Just curious.

 

[web guru]

I am deleting users after five days, as they have up to five days to access the site and download the product that they have paid for.

 

[antoshka]

it is always better to store timestamps as int(11). it fits and there's less confusion