phpmyadmin Question posted in
The official documentation can be found here.

How to deal with the time zone difference – Phpmyadmin

Using mysql, in a post table I use CURRENT_TIMESTAMP to store the date_time of publication.

Everything was ok when I was working on wampserver on local.

But I hosted my site yesterday on a server located in France, 2 hours away from the time zone of my country.

I’m using a timeago plug-in which processes the date and displays the elapsed time (there is .. sec ago, there is .. min ago, there is .. h ago, there is .. year ago) since the publication of a content.

After posting, I see “2 hours ago” when I just published 30 seconds ago.

How can I take into account the time zone of my users compared to the date and time of the server of my host?

Sample: If I post a content, 30 seconds later Me and the user who is at china or anywhere must see “30 seconds ago”.

I tried that: 

$d ="2020-02-28T13:09:33Z";
<time class='timeago' datetime='<?= $d ?>'> </time>

But instead of giving me “30 seconds ago”, It gives me “2 hours ago”.

Thank you.

Tags:

2

Answers


  1. 0

    I suspect you are calculating the relative time on the client side. For example, by timego plugin.

    Neither MySQL, nor PHP know user’s location and timezone. So, it’s better to inform JS about server timezone and let it calculate the offset right.

    Use ISO 8601 date time format to let timeago pligin know about server timezone. All the rest is up to JS plugin:

    <?php

$ts = strtotime($row['date_time']);

?>

<time class="timeago" datetime="<?= date(DATE_ISO8601, $ts) ?>">

    <?= date('Y-m-d H:i:s', $ts) ?>

</time>


<script type="text/javascript">

    jQuery(function($) {

        $("time.DateTime").timeago();

    });

</script>
    Login or Signup to reply.
  2. 0

    MySQL stores DATETIME without timezone information, but datetime values that are not timezone-aware are semi-useless.

    So you have different options.

    First option: continue to store datetime values in your current timezone, but add a second column with the proper ‘offset’ versus UTC, for example +1 if you are in France. Then you can perform calculation on the fly to another timezone but it is tricky because of daylight saving settings. During a certain period of the year the offset will be +1, at other times it could be +2.
    So I don’t recommend this approach. It’s not going to scale well over time.

    Other option: you can store all datetime values as UTC in your database and
    then in your application you recalculate them by applying the correct offset according to your own timezone (or that of your web visitors).
    This is a better option.

    Whatever you do, it is important to know the timezone that applies to a given datetime value. So choose one and stick to it.

    PHP has functions to handle time zone conversions, see for example: https://www.php.net/manual/en/class.datetimezone.php

    Note that TIMESTAMP is limited to 1970-2038… Year 2038 problem

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search