Page 1 of 1

MySQL Time zones table

Posted: Wed 06 Dec 2017 2:53 pm
by Mapantz
I just thought i'd make a post to see how many have this issue with shared hosting..

Basically, when retrieving my data from MySQL, you need timezone info:

Code: Select all

$result = $mysqli->query("SET time_zone='Europe/London'");
However, I cannot use that, because the time zone tables are not loaded in to MySQL. So, the way I can only get it to work is by using

Code: Select all

$result = $mysqli->query("SET time_zone='+00:00'"); or $result = $mysqli->query("SET time_zone='+01:00'");
But this messes up my sunshine graphs due to clocks going back and forward throughout the year. For example: If in May, I record sunshine from, 5am to 6am, and then call it back, it could show as 4am to 5am due to BST. You can fix it by using +01:00, but then you have the issue of sunshine in the evening being one hour out and vice versa. The Europe/London way fixes this problem. The hosting i'm using now refuses to load the time zone tables in to MySQL because it is shared hosting. I have asked another 5 hosting companies, and they all say the same thing! The only answer is to move to a dedicated server, but i'm not paying £30 a month for overkill hardware to host my simple weather site.

I don't know how i can get past this problem! I didn't have the problem with GoDaddy, but I had to ditch them, as it they were slow and I used to get so many issues with them, and I prefer SSD hosting. So I refuse to ever go back with them. So I think i'm completely stuck?!

Re: MySQL Time zones table

Posted: Wed 06 Dec 2017 3:09 pm
by mcrossley
Can you create the zone table in your user space and populate it. The comments on this page suggests you can...
https://dev.mysql.com/doc/refman/5.7/en ... o-sql.html

Re: MySQL Time zones table

Posted: Wed 06 Dec 2017 3:15 pm
by Mapantz
mcrossley wrote:Can you create the zone table in your user space and populate it. The comments on this page suggests you can...
https://dev.mysql.com/doc/refman/5.7/en ... o-sql.html
I've managed to run that and create the table, but I don't know what I need to do next. It is beyond my capabilities after that :oops:

Re: MySQL Time zones table

Posted: Wed 06 Dec 2017 3:55 pm
by mcrossley
I can send you the exports of my tables if you like - then just import them into your db?

Re: MySQL Time zones table

Posted: Wed 06 Dec 2017 3:59 pm
by Mapantz
mcrossley wrote:I can send you the exports of my tables if you like - then just import them into your db?
OK - Let's give that a try.

Re: MySQL Time zones table

Posted: Wed 06 Dec 2017 6:59 pm
by Mapantz
That's 11 hosting companies now, that say the timezone tables aren't loaded in to sql, and they refuse to do it unless it is a dedicated server. How are other people managing this? It's really confusing to me. :?

Re: MySQL Time zones table

Posted: Sat 09 Dec 2017 7:05 am
by Steepleian
Have you thought about hosting yourself? It really is very straightforward and you won’t have to worry about restrictions.

Re: MySQL Time zones table

Posted: Sat 09 Dec 2017 11:53 am
by Mapantz
Steepleian wrote:Have you thought about hosting yourself? It really is very straightforward and you won’t have to worry about restrictions.
Not on my broadband connection, it's too slow. As it is, my internet slows right up if I get more than 2 people viewing my webcam. :lol: