MySQL NOW() does not match PHP timezone

Question

I made an Email Verification Link where the link will valid for only the next 10 minutes from the time of mail sent but my code as given below is not works.

Wait, I show all the related threads on StackOverflow and offcourse I got the same question-related post but I also do that result answer but it not works for me, so that I posted this question might somebody have the same issue and it will help to others.

Please do not marks it as duplicate and under review mode, and try to understand my query.

Please help me how I fix this issue and what would be correct code. :(

MY Code is Below : -

Other Code

date_default_timezone_set('Asia/Kolkata');
tokenExpire formats: (new DateTime('+10 minutes'))->format('Y-m-d H:i:s')

DB Structure[name as signup]

-----------------------------------------------------
Email            |   token    | tokenExpire          |
----------------------------------------------------
abcd@domain.com  | {randNum}  | 2019-10-19 09:42:10  |
-----------------------------------------------------

PDO Statement

$sql = $con->prepare("SELECT `Email`,`token` FROM `signup` WHERE Email= :1 AND token= :2 AND tokenExpire > NOW()");
    $sql->execute(array(
       ':1' => $emailid,
       ':2' => $tokenum
    ));

if ($sql->rowCount() > 0) { echo "Link Is Valid"; }
    else { echo "Link Expired"; }

I expects it must show time expire or valid info behalf of my code but it not works, and show Link valid even the time has past.

All you need to manipulate and convert dates, time zone and UTC here : [Should MySQL have its timezone set to UTC](https://stackoverflow.com/questions/19023978/should-mysql-have-its-timezone-set-to-utc) — Shim-Sao, Oct 20 '19 at 02:37

score 0 · Answer 1 · answered Oct 19 '19 at 09:09

0

Looks like you have different Apache (or whatever you are running on) and MySQL time zones. Make a short script, change PHP timezone, print DateTime value and at the same time insert row to MySQL with NOW() and check if time in MySQL and printed one differs.

Info how to deal with MySQL time

answered Oct 19 '19 at 09:09

user11222393

3,245
3
13
23

I also though that issue came from time zone. But How I fix this, I visit your link but not understand. – John Cart Oct 19 '19 at 09:14
You need to change MySQL timezone settings, the way to do it depends on system you are using and what access you have. Quick workaround: https://stackoverflow.com/a/34428852/11222393 – user11222393 Oct 19 '19 at 09:22

Top-Master · Answer 2 · 2019-10-20T05:17:22.143

This seems to be related to time-zone differences, and you should be able to change the settings of both PHP and MySQL, for example based on another answer, you can configure PHP like below:

<?php
define('TIMEZONE', 'UTC');
date_default_timezone_set(TIMEZONE);

and for MySQL you should run a query like below:

<?php
$now = new DateTime();
$mins = $now->getOffset() / 60;
$sgn = ($mins < 0 ? -1 : 1);
$mins = abs($mins);
$hrs = floor($mins / 60);
$mins -= $hrs * 60;
$offset = sprintf('%+d:%02d', $hrs*$sgn, $mins);

//Your DB Connection - sample
$db = new PDO('mysql:host=localhost;dbname=test', 'dbuser', 'dbpassword');
$db->exec("SET time_zone='$offset';");

The PHP and MySQL timezones are now synchronized within your application. No need to go for php.ini or MySQL console!

But you may want to convert the timezone before showing to users which for example could look like below:

/**
 * Converts server date to user's local-date.
 */
function GetUserDate($date, $format = 'n/j/Y g:i A', $userTimeZone = 'Asia/Kolkata') {
  // Use previous TIMEZONE constant just in case
  // (If "date_default_timezone_set(TIMEZONE)" is called 
  // it's not required to pass "DateTimeZone" in below line)
  $dateTime = new DateTime($date, new DateTimeZone(TIMEZONE));

  $dateTime->setTimezone(new DateTimeZone($userTimeZone));
  return $dateTime->format($format);
}

Note: above should solve your problem but any existing value on your tokenExpire column might still be having a different time-zone (only newly created entries will follow your new time-zone settings).

(Based on an article on SitePoint)

What happend when I want show Asia India TimeZone is there any way to convert it? — John Cart, Oct 19 '19 at 12:29
Updated! (It's now using `UTC` and `GetUserDate(...)` shows how to later convert it, but you could for `Delhi` simply use `Asia/Kolkata` instead of `UTC`, I mean if you don't like the idea of being able to show each user a date based on their Geo-location). — Top-Master, Oct 20 '19 at 05:06

MySQL NOW() does not match PHP timezone

2 Answers2