PHP - MySQL Timestamps 1

[jasc2k]

Hi All

I have googled this until my eyes hurt and I cant seem to find a consistant answer. What I basically would like to do is insert the current date and time into a MySQL table with a coloumn defined as 'datetime' and how to get it back acording to the users timezone.

Currently I am now adding the date into the database using:

$datetime = gmdate( 'Y-m-d H:i:s' );

Which I understand be be the GMT timezone. I have chosen this route as I would like to create a standard format in my database. This is correct in my timezone GMT(UK).

But when I retreive the date from my database how can I display it back to the user in their current timezone?

I have read many confusing forums now and am totally baffled as further to this I would like to then work out from the date how long ago it was as opposed to when it was and have modified the following script for this. I have added this as I fear this might be linked.

It probably does not help that I keep converting the type but I love this timeAgo function and its works great in my environment.

   /**
   * mysql2timestamp - Converts MySQL datetime to
   * a timestamp number format.
   */ 
   function mysql2timestamp($datetime){
       $val = explode(" ",$datetime);
       $date = explode("-",$val[0]);
       $time = explode(":",$val[1]);
       return mktime($time[0],$time[1],$time[2],$date[1],$date[2],$date[0]);
	}
	
	function timeAgo($datetime, $granularity=2, $format='d M Y @ G:i'){

		$timestamp = $this->mysql2timestamp($datetime);
		$difference = time() - $timestamp;
		
		if($difference < 0) return '0 seconds ago';		// if difference is lower than zero check server offset
		elseif($difference < 864000){					// if difference is over 10 days show normal time form
		
			$periods = array('week' => 604800,'day' => 86400,'hr' => 3600,'min' => 60,'sec' => 1);
			$output = '';
			foreach($periods as $key => $value){
			
				if($difference >= $value){
				
					$time = round($difference / $value);
					$difference %= $value;
					
					$output .= ($output ? ' ' : '').$time.' ';
					$output .= (($time > 1 /*&& $key == 'day'*/) ? $key.'s' : $key);
					
					$granularity--;
				}
				if($granularity == 0) break;
			}
			return $datetime." - ".($output ? $output : '0 seconds').' ago';
		}
		else return date($format, $timestamp);
	}

I appreciate anyone taking the time to read this and/or reply with your comments.

Thanks
James

 

[jpadie]

first off, you need to specify a timezone for your server in your script. use date_default_timezone_set() to do this.

date_default_timezone_set('UTC'); //for example

you obviously need to store the offset of your user from your server timezone somewhere.

then, to show the date in the user's timezone

date_default_timezone_set('UTC'); //you need this once per script
$dt = new datetime($datetimefrommysql);
//convert to, say, paris time
$dt->setTimeZone('Europe/Paris');
//output the date in the new timezone
echo $dt->format(DATE_RFC822);

 

[jasc2k]

hi there,

thanks for your reply, I am not sure I understand how I will know the 'users' timezone? Does DATE_RFC822 do that?

Cheers

 

[jpadie]

no. that tells php to output the date in the format specified in RFC822.

the normal way to determine a user's timezone is to ask them. you could also do some geo sniffing against a location library.

 

[jasc2k]

that would be why my google searches were not going so well then?

Thanks

 

[jpadie]

you could always grab the time from a javascript call too. but this assumes that the user has their time set properly.

 

[jasc2k]

lol I better not trust that then ) but good idea! Thanks