-1

I am creating a question answer forum site. However I am facing issue while handling date/time issue. Lets suppose there are two users - USER1 and USER2. USER1 is in India while USER2 is in US. Now, when USER1 posts a question, I am storing that question in the database with timestamp as CURRENT TIMESTAMP. This question will now appear in the common dashboard page. Now, at the same time (lets say after 2 minutes), USER2 who is in US will need to see the question posted w.r.t his timezone. That is, he need to see that the question was posted 2 minutes ago. How do we implement this in my PHP code. I am using Mysql server as database.

When the user posts a question, it it necessary to store the user's timezone in the database as well so that another user's relative time needs to be calculated? If so, how do we store timestamp as well the the timezone in the mysql database?

venky
  • 41
  • 1
  • 1
  • 8
  • 1
    If you intend on just displaying "1 hour ago" or "2 minutes ago", then you don't need to worry about timezones at all. Then you just store everything in the servers timezone. https://stackoverflow.com/questions/1416697/converting-timestamp-to-time-ago-in-php-e-g-1-day-ago-2-days-ago – Qirel Sep 15 '17 at 17:33
  • Possible duplicate of [Converting timestamp to time ago in PHP e.g 1 day ago, 2 days ago...](https://stackoverflow.com/questions/1416697/converting-timestamp-to-time-ago-in-php-e-g-1-day-ago-2-days-ago) – Qirel Sep 15 '17 at 17:33
  • @Qirel If it is less than lets say 10 minutes, then I will post it in that sense. But main problem lies in posting it in real date and time. If it is > 10 mins, I need to display exact date and time. – venky Sep 15 '17 at 17:36
  • That dupe takes care of displaying "1 min ago" and up to 10 years ago. If you display the actual date, you run into issues with timezones - in which case it's probably easier to just say its from a specific timezone. – Qirel Sep 15 '17 at 17:39
  • @Qirel As I said, I may not want to display it in the sense "ago". If I want to display the exact date and time of the posted question, what approach would I need to take? – venky Sep 16 '17 at 06:48

3 Answers3

1

Although MySQL will display timestamps to you in your current timezone (or more specifically the server's timezone), they're stored in the generic UTC timezone.

When a user creates an account on your forum, they should select their desired timezone. (You may also be able to auto-detect it from their browser.)

Then, each time after your PHP script establishes a database connection, you'll run the query, "SET time_zone = ?" where the "?" will be the timezone you've saved with their profile.

After that, every query they make through that connection will automatically convert the UTC timestamps into their timezone, and you should be able to display the results directly to the user.

MySQL timezone reference

Mark Bench
  • 111
  • 3
  • Thanks for your response!! If I store the date and time in datetime column data type of the mysql db and when I try to fetch that value, can I convert it into user specific timezone without the knowledge of in which timezone the question was posted? – venky Sep 16 '17 at 06:50
  • Yes, because it's stored in UTC. If two different connections have two different timezones set and each store a timestamp, the two values will be identical in your database, because the UTC value is the same regardless of timezone. It's a little hard to wrap your mind around, but it works. Put another way, if you set one timezone, store a value, then set another timezone and retrieve the value, it will be correct, because under the hood it's simply UTC, which is independent of the timezone. – Mark Bench Sep 18 '17 at 16:32
0

Set your default timezone for your page yourself, so all users get to use the time zone you set there. E.g 

<?php date_default_timezone_set("Africa/Lagos"); ?>
UniQue
  • 96
  • 1
  • 9
  • Yes but how will the PHP understand in which timezone the question was posted. Do I need to store that too in my mysql db along with the date and time values? – venky Sep 16 '17 at 06:50
  • I ll advise you save whatever you are bringing into the system in the default date time zone of a country with GMT : 00 Something like Then when you are retrieving the time from your db , you then get the users location based with geo-ip and use this to set the time zone with which you convert the new date time to – UniQue Sep 18 '17 at 09:33
0

It is simple. Don't store timestamp directly tends to user timezone.

Whatever user time zone u convert to your php default timezone and store it to Database

If user1 from india ,convert it to your php timezone save the message post time, while showing to user2 convert the time stamp from your php timezone to user2 time zone and show the data. It will give correct value

Make sure your php and MySQL timezone are same. 

For example 
User 1 from IST
User 2 from UTC

Your php and MySQL time zone is PsT


If user1 post msg u convert IST to PST store to your db.
If user2 post msg u convert UTC to PST
store to it db.

While showing post to user1
U should convert PST to IST 
for user2 Convert PST to UTC


USE MYSQL FUNCTION convert_tz 


IN PHP to show ago follow below method

function time_elapsed_string($datetime, $full = false) {
    $now = new DateTime;
    $ago = new DateTime($datetime);
    $diff = $now->diff($ago);

    $diff->w = floor($diff->d / 7);
    $diff->d -= $diff->w * 7;

    $string = array(
        'y' => 'year',
        'm' => 'month',
        'w' => 'week',
        'd' => 'day',
        'h' => 'hour',
        'i' => 'minute',
        's' => 'second',
    );
    foreach ($string as $k => &$v) {
        if ($diff->$k) {
            $v = $diff->$k . ' ' . $v . ($diff->$k > 1 ? 's' : '');
        } else {
            unset($string[$k]);
        }
    }

    if (!$full) $string = array_slice($string, 0, 1);
    return $string ? implode(', ', $string) . ' ago' : 'just now';
}

Get User Time zone using http://ipinfodb.com/ip_location_api.php

or by Java script

get user timezone

Rajeshwar Dhayalan
  • 42
  • 3