mySQL timestamp(14)

web guru · 10-12-2003, 02:47 PM

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 · 10-13-2003, 01:59 AM

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:
????: NamePros.com http://www.namepros.com/programming/15371-mysql-timestamp-14-a.html

$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/...d/12241/fid/15

http://www.mysql.com/doc/en/Date_and...functions.html

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

web guru · 10-13-2003, 02:10 AM

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.
????: NamePros.com http://www.namepros.com/showthread.php?t=15371

Code:

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 · 10-13-2003, 02:59 AM

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

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

web guru · 10-13-2003, 03:54 AM

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 · 10-20-2003, 07:57 PM

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

10-12-2003, 02:47 PM	THREAD STARTER #1 (permalink)
web guru NamePros Member Join Date: Jul 2003 Posts: 118	mySQL timestamp(14) 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?? __________________ Paulicon Web design - Your one stop shop

10-13-2003, 02:10 AM	THREAD STARTER #3 (permalink)
web guru NamePros Member Join Date: Jul 2003 Posts: 118	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. ????: NamePros.com http://www.namepros.com/showthread.php?t=15371 Code: 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. __________________ Paulicon Web design - Your one stop shop

10-13-2003, 03:54 AM	THREAD STARTER #5 (permalink)
web guru NamePros Member Join Date: Jul 2003 Posts: 118	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. __________________ Paulicon Web design - Your one stop shop

10-20-2003, 07:57 PM	#6 (permalink)
antoshka New Member Join Date: Oct 2003 Posts: 4	it is always better to store timestamps as int(11). it fits and there's less confusion __________________ $1.95 Web Hosting - RouTHost.com

10-13-2003, 01:59 AM	#2 (permalink)
deadserious Senior Member Join Date: Aug 2002 Posts: 1,255	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: ????: NamePros.com http://www.namepros.com/programming/15371-mysql-timestamp-14-a.html $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/...d/12241/fid/15 http://www.mysql.com/doc/en/Date_and...functions.html http://us3.php.net/manual/en/ref.datetime.php

10-13-2003, 02:59 AM	#4 (permalink)
deadserious Senior Member Join Date: Aug 2002 Posts: 1,255	Cool, atleast you figured out something that works for what you wanted. I wonder what you're doing though, deleting users after five days or just their password or? Just curious.

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)