0

I'm based in a certain timezone but my server is one hour ahead. When I save new records with a timestamp column, it displays the time as one hour ahead.

How will this affect me in the long run? How will I need to alter my queries to return my time instead of the server's time? Or should I not worry about this at all? I'm new to web dev...

whatwhatwhat
  • 1,991
  • 4
  • 31
  • 50

1 Answers1

1

After connecting to database execute query:

SET time_zone = 'Europe/London';

List of timezones: http://php.net/manual/en/timezones.php


Or change it via WordPress settings:

http://help.coschedule.com/hc/en-us/articles/214455448-How-To-Change-Your-WordPress-Timezone

Thomas Orlita
  • 1,554
  • 14
  • 28
  • I'm using CPanel's PHPMyAdmin, so I don't have a script that initializes the connection to the DB. Is there a way to do this in PHPMyAdmin? – whatwhatwhat Jan 01 '16 at 00:34
  • Do you have `PHP config` option in your cPanel? – Thomas Orlita Jan 01 '16 at 00:36
  • Or type `SET GLOBAL time_zone = 'Europe/London';` in SQL tab in PHPMyAdmin. But you must have SUPER privileges. – Thomas Orlita Jan 01 '16 at 00:43
  • I don't have PHP config and unfortunately also don't have SUPER privs. I'm curious though - let's say that eventually my website takes on employees from all over the world that will handle customer service (no code writing). All that really matters then is to output the appropriate time based on whatever user is retrieving the info, right? Setting the time zone in PHPMyAdmin to my time then seems rather unecessary...no? – whatwhatwhat Jan 02 '16 at 03:55
  • So after doing a lot of research the general consensus agrees with what you are saying which is to store as UTC in the server and format when outputting to client side. However, I don't understand how this should all look with my PHP script and the MySQL setup. I found that `time()` returns the UTC time in seconds, but am I really supposed to store it like that in my DB? Is that how most people do it? Also, what field type would I use - DATETIME or VARCHAR or something? Do I need to format it in the PHP script before storing? – whatwhatwhat Jan 03 '16 at 06:50
  • If you have default tz UTC, you'll store the data in database in UTC as datetime. If you want to output the datetime in user's timezone, you can via PHP function timezone_offset_get. You can get user's timezone name via JS function and store it in cookie or $_SESSION. Hope it helps! http://www.onlineaspect.com/2007/06/08/auto-detect-a-time-zone-with-javascript/ http://php.net/manual/en/function.timezone-offset-get.php – Thomas Orlita Jan 03 '16 at 10:26
  • [This question](http://stackoverflow.com/questions/1349280/storing-datetime-as-utc-in-php-mysql) says to use `gmdate()` in my PHP script to generate the UTC time. My concern is that this outputs a string. Wouldn't this be a problem if I'm going to define the field in MySQL as `DATETIME`? – whatwhatwhat Jan 04 '16 at 03:17
  • Easily do `gmdate("Y-m-d H:i:s")` – Thomas Orlita Jan 04 '16 at 19:03