Page 1 of 1

Date difference between dates

Posted: Mon Mar 05, 2007 8:50 am
by david swain
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Hello

I am working on a dynamic site map file. I want the <changefreq> to be calculated by getting an average update frequency based on the timestamps in the database table.

I'm startng with two as i am new at this !

I have the following code:

Code: Select all

$conn = mysql_pconnect($hostname, $username, $password) or trigger_error(mysql_error(),E_USER_ERROR);  
	mysql_select_db($database) or die ("Unable to select database") ;
		
			$sql = "SELECT * FROM tbl_blog ORDER BY timestamp_blog DESC LIMIT 2";
			$siteMap = mysql_query($sql);
		
			$var=0;
		
		while ($blogTime_Stamp = mysql_fetch_array($siteMap)) {
			$time[$var] = substr_replace(date(c, strtotime($blogTime_Stamp[timestamp_blog])), '', 10);
			$var++;
		}
		
	echo $time[0] . '<br />';
	echo $time[1]. '<br />';
As you can see i can get the dates in YYYY-mm-dd format using the rather chunky

Code: Select all

substr_replace(date(c, strtotime($blogTime_Stamp[timestamp_blog])), '', 10);
but when i try to convert this using the gregoriantojd function so i can calculate the days difference between posts (or whatever) it complains that i am entering a string and not a date.


Can anyone help me with this ?

Thanks

David


feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Mon Mar 05, 2007 8:52 am
by Chris Corbyn
MySQL has a function DATE_DIFF (or maybe DATEDIFF()). The YYYY-MM-DD format will work fine for that.

Posted: Mon Mar 05, 2007 9:09 am
by david swain
The data in the fields is a timestamp so i can use the current_timestamp function. I had a look at the documentation for this but i didnt think it would satisfy my needs.

Posted: Mon Mar 05, 2007 3:04 pm
by RobertGonzalez
There are load of MySQL data functions available, but the really good ones didn't come about until 4.1. More came out with 5. What version of MySQL are you using?

Posted: Tue Mar 06, 2007 1:16 am
by david swain
Im using version 5

Posted: Tue Mar 06, 2007 10:43 am
by RobertGonzalez
Then you have the full suite of date functions at your disposal.