I'm working on some website (news portal) in PHP and MySQL. I have a rubric last post with time when news are posted. My timezone and server timezone is GMT+2. I want to display a time correctly to all clients from all time zones. Example, if news is posted at 13:05 at my timezone to display for clients from GMT+3 14:05, for clients from GMT+4 zone 15:05, for GMT+1 12:05, and so on. How to do that? What is solution and what is a best way to store date and time in MySQL database for that purpose? I'm also integrate Carbon in my website but I can't find method for that.
What we do in our company is we store all times in GMT. Even though we are in South-Africa. Then based on the user's location we convert the time to display correctly. There are a number of ways to determine where a user is located and convert the time, however the easiest method is to just ask them where they live and store that information.
After establishing connection to database run query like that:
SET @@session.time_zone = "+02:00";
orSET @@session.time_zone = "Europe/Warsaw";
Where timezone offset or name is value stored for client.
All timestamp values will be returned from queries in defined timezone.
While storing values in db store in UTC timezone something like below
date_default_timezone_set('UTC');
Then while displaying the values get the user timezone using any timezone js and send the timezone to server using ajax and store it in SESSION variable and while displayin the date add that timezone. For example 1)Download the timezone js from https://bitbucket.org/pellepim/jstimezonedetect/src
Following will give you the timezone
var tz = jstz.determine(); // Determines the time zone of the browser client
var timezone=tz.name(); // Returns the name of the time zone eg "Europe/Berlin"
Make ajax call like below
$.post("scripts/set_timezone.php", { timezone: timezone} );
and in set_timezone.php have code
session_start();
$_SESSION['user_timezone'] = $_POST['timezone'];
and while displaying display as follows
$date = new DateTime('2012-01-23 05:00:00', new DateTimeZone('UTC'));
$date->setTimezone(new DateTimeZone($_SESSION['user_timezone']));//This will from the session e.g Asia/Kolkata
$time= $date->format('Y-m-d H:i:s');