1

I run a website for a local organisation where members log in using their own logins. I have quite basic knowledge, so this may be a simple fix..

I have the following code as part of the php run when they successfully log in which saves their log in date & time to the MySQL database.

$stmt = $pdo->prepare('UPDATE accounts SET lastlogin = CURRENT_TIMESTAMP WHERE id = ?');

The problem is that the date and time stored is 2 hours behind our local time (New Zealand). I've searched around the net for this answer but get lost in everything that's out there! What am I missing in this code to save the date & time as local?

Thanks!

Darryl
  • 13
  • 2
  • Update DB to use same timezone as application, or update code base to convert from GMT to local timezone. – user3783243 Feb 01 '22 at 00:46
  • Does this answer your question? [MySQL: setting time\_zone in my.cnf options file](https://stackoverflow.com/questions/4562456/mysql-setting-time-zone-in-my-cnf-options-file) – Sherif Feb 01 '22 at 01:31

1 Answers1

-1

That's because the database's time zone differs from your local time zone.

Make sure you first set default time zone in PHP like so:

date_default_timezone_set('Pacific/Auckland');

This is how I fixed it.

$current_time = date('Y-m-d H:i:s');
$stmt = $pdo->prepare("UPDATE accounts SET lastlogin = '{$current_time}' WHERE id = ?");
ruleboy21
  • 5,510
  • 4
  • 17
  • 34
  • `$current_time` should be bound. As is SQL wouldn't run. `H:is` also is missing a colon. – user3783243 Feb 01 '22 at 00:45
  • Thanks @ruleboy21, this is what im looking for. I've checked the default timezone and it is indeed set as Pacific/Auckland. When I apply the code you suggested, unfortunately it just cleared the date & time back to all zeros when I logged in.. – Darryl Feb 01 '22 at 01:38
  • I've updated my answer. Please check the `$current_time`. – ruleboy21 Feb 01 '22 at 01:43
  • Excellent, that's got it. Cheers! – Darryl Feb 01 '22 at 01:46