Converting date/time of MySQL into GMT time/date

PHP
1

Hi,
How can I convert the date and time from MySQL into something like this below,

last updated at 10:38 GMT, Sunday, 15 November 2009

Where I would like to have GMT - it is like ones in BBC news site,

this is the php code I use,

<?php echo $row_page['pg_updated'];>

and the result is - 2009-11-19 02:47:02

thanks,
L

2

You need to use the date() function to format it as you want. The date function takes a string for the format as the first parameter and an optional timestamp parameter. You will need to convert your string date from MySQL into a timestamp using the strtotime function.

You can figure out the correct string format at http://www.php.net/date

<?php
echo date(
       'd M Y', // String format
       strtotime($row_page['pg_updated']) // Timestamp
);
?>
3

You could also do this with sql: DATE_FORMAT

4

hi thanks for the tips,
how can i check whether the time/date is ‘GMT’ or not and print it out if I am using the code below?

<?php
echo date(
       'd M Y', // String format
       strtotime($row_page['pg_updated']) // Timestamp
);
?>

thanks,
L

5

Depends on what time is running on the MySQL server - if that’s GMT then it’s GMT… voila! Otherwise you might need to change it (look up MySQL functions for DATE_ADD and DATE_SUB)

6

In order to set your own timezone, just use:

function setTimeZone( $tz = '' ){
	if(empty($tz)){
		if(defined('DATE_TIMEZONE')){
			$tz = DATE_TIMEZONE;
		}else{
			$tz = getTimeZone();
		}	
	}	
	
    if (function_exists('date_default_timezone_set')) {
        date_default_timezone_set( $tz );
    }
    else {
        putenv("TZ=$tz");
    }
}

To get the timezone:
function getTimeZone(){
if (function_exists(‘date_default_timezone_get’)) {
return date_default_timezone_get();
}
else {
return getenv(“TZ”);
}
}

7

@PHPycho that will only work when dealing with dates generated by the php server. If the dates are entered into the MySQL server using the MySQL server time (such as NOW() in a SQL statement) then the time will be in that server’s timezone. If you are using a different timezone in your PHP app, and treating the times out of MySQL as if they were from the same timezone then you will have bugs galore.

8

This should set the MySQL server’s timezone to GMT(although I think it’s only for the session, so need to run it on every connection)

SET time_zone = '+0:00';
9

You can only set it per session for mysql unless you have access to set it before mysql starts.

You can set it in PHP by adding the following line to your php.ini

date.timezone = "GMT"
10

There should be single point of entry for data. So in my case i only use PHP’s date and pass to the mysql query. In this way you won’t face any problem. or am i missing somewhere???

11

That’s perfectly fine for you, as long as you keep to those rules - however your advise was based on rules that you hadn’t stated (such as only ever using the PHP server’s time).

It’s always safer to make sure mistakes can’t happen, and have everything running happily on the same timezone. If your php and db servers are at the same host it’s likely they’re set to the same timezone and you can just use PHP to figure out the current timezone, then alter all dates according to what timezone you want them to be in.

12

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_utc-timestamp

You can use MySQL’s UTC_TIMESTAMP() function, and PHP’s gmdate et. all to ensure that all your dates are consistently UTC.

13

One simple Question:
Is there any effective method/techniques for dealing with timezone problems?
In my case, Since if you use only PHP’s there care should be taken (date should be fed from php to mysql). So i think this method may not be appropriate when you deal with NOW() or any other mysql date function in Query.

14

thanks guys. i have got it sorted, i think my time is being store in GMT (I guess… ) so I just include ‘GMT’ in the output haha - simple as that! thanks!

echo date('H:i:s') . ' GMT on ' . date('jS F Y', strtotime($sqldate));
15