0

I have following problem: I am using a php/mysql script from a friend who made it for me several years ago. Normaly i would host that script on a server in my country so there would be no problem at all as servertime would be my local time (Europe/Berlin).

But now i have hosted my domain in USA (NY) and my scripts adding the wrong time into the database (currently -5 hours).

So instead of adding 2017-03-23 19:00 it adds 2017-03-23 14:00

So my solution would be to change the mySQL queries and subtract the 5 hours and it would fit ... for now ... BUT on summer/wintertime change i would have to adjust the scripts again :(

So my question is: HOW can is change my NOW() to fit to my timezone (Europe/Berlin) ?

Here is one of my quesries so you could help me to edit it as i am a total noob and would need our help with it, thank you very much!

$insQuery = "INSERT INTO tbl_anmeldungen (fld_raid_id,fld_user_id,fld_anmelddate,fld_anmelde_notiz,fld_random) VALUES ('$raidID','$userID',NOW(),'$notiz','$myrand')";

all which needs to be changed/edited is the NOW() part to fix it to my timezone. If this would NOT be possible, how do i need to change the NOW() part so it will subtract 5 hours?

I really appreciate your help! Thank you!

user7758351
  • 1
  • 2
  • **WARNING**: Whenever possible use **prepared statements** to avoid injecting arbitrary data in your queries and creating [SQL injection bugs](http://bobby-tables.com/). These are quite straightforward to do in [`mysqli`](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [PDO](http://php.net/manual/en/pdo.prepared-statements.php) where any user-supplied data is specified with a `?` or `:name` indicator that’s later populated using `bind_param` or `execute` depending on which one you’re using. – tadman Mar 23 '17 at 17:49
  • hi tadman, maybe it is not secure or up-to-date, i know, the code is 12 years old as written and it wasnt me who coded it (i cant even code). So please try to help me with my issue only as i am not even skilled enough to fix or make it more secure, sorry :( – user7758351 Mar 23 '17 at 17:53
  • 2
    Use the `CONVERT_TZ()` function. – Barmar Mar 23 '17 at 17:53
  • By the way: Before someone may answer: "Why dont you just change the servertime to your local timezone"? I can't as it is shared webspace and not a VPN or dedicated server :( – user7758351 Mar 23 '17 at 17:54
  • @Barmar: i guess this is a good idea. But as i am a total noob and havnt made that script, could you please use my code above to show me how it would look with using CONVERT_TZ() ? How would: VALUES ('$raidID','$userID',NOW(),'$notiz','$myrand') look then? Thanks! – user7758351 Mar 23 '17 at 17:56
  • Total noobs can't read documentation? – Barmar Mar 23 '17 at 17:59
  • You shouldn't change the timezone when inserting, times should be stored in the server's timezone. Instead, when you retrieve the time and you want to display it, you should use `SELECT CONVERT_TZ(fld_anmelddate, 'US/Eastern', 'Europe/Berlin')` – Barmar Mar 23 '17 at 18:03
  • @user7758351 Hence "whenever possible". Just be aware this is probably a massive security hole in your site. – tadman Mar 23 '17 at 18:06
  • @Barmar: Oops, just seen your comment NOW, 1h after i already found a solution/way on my own (read my own answer below) :) ... So instead of using my solution CONVERT_TZ(NOW(),'+00:00','+5:00') ou would say that CONVERT_TZ(NOW(),'US/Eastern','Europe/Berlin') will work too? This would be great as i do not have to care about summer-/wintertime changes then, i guess ;) – user7758351 Mar 23 '17 at 21:30

2 Answers2

0

I have found some other "idea" from another user:

After you open the connection to MySQL, run the following as a query: SET time_zone = timezone; Then all functions you do will run for that timezone for that connection (i.e. until you close the "link" to the database".

So someone can tell me if the following would work?

$conn = mysql_connect($dbhostname,$dbusername,$dbpassword) or die ("db down..."); mysql_select_db($dbname);

SET time_zone = "Europe/Berlin";

Then all my NOW() would use my timezone, right? See following link: https://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html

user7758351
  • 1
  • 2
0

I would like to thank User Barmar!

He didn't help me exactly or in detail, but he brought me on the right road ;) I have "fixed" my problem now with:

CONVERT_TZ(NOW(),'+00:00','+5:00')

Now mysql enters the correct time in my database (added +5 hours to the servertime, which is NewYork, to fit my timezone, which is Europe/Berlin)

AND IT WORKS! ... BUT:

This will only work until there will be some sommer-/wintertime change. Then i will have to fix all my code and make it +4 or +6 hours to fit again.

So this is just a semi-professionell fid. If someone knows a better way to implement the correct timezone "by name" so summer-/wintertime changes will adjust automaticall, i would be very happy!

user7758351
  • 1
  • 2
  • The summer/winter issue is why you should use named time zones like `US/Eastern` and `Europe/Berlin`. These will automatically adjust based on daylight saving time. – Barmar Mar 23 '17 at 21:36
  • Yes i understand, but CONVERT_TZ(NOW(),'US/Eastern','Europe/Berlin') will not work as i just tried it. I know it would if i would use CONVERT_TZ(fld_anmelddate, 'US/Eastern', 'Europe/Berlin') but i would like to convert it in the correct timezone BEFORE(!) it is written into the database. Do you have any idea how i could get this done? Thanks! – user7758351 Mar 23 '17 at 21:40
  • If it's not working with named zones, see http://stackoverflow.com/questions/14454304/convert-tz-returns-null/14454465#14454465 – Barmar Mar 23 '17 at 21:52
  • Yes exactly, result is null. So i have to talk to my hoster if its possible to load the info into sql cause its shared webspace. But if they do, it should work? – user7758351 Mar 23 '17 at 22:03
  • Yes, I think so. – Barmar Mar 23 '17 at 22:30
  • Ok, but they said they can't do it on shared servers anyway. So ... i have to live with this solution: CONVERT_TZ(NOW(),'+00:00','+5:00') and change/adopt it to the summer/wintertimes. Except someone else got an idea how to do it with timezones without them loaded into sql :) – user7758351 Mar 23 '17 at 22:41
  • It would probably be better to store UTC timestamps in the database, and do the conversion to local time in the PHP code. – Barmar Mar 23 '17 at 22:42