I have a list of events in table A.
In table A I have a location ID.
Table B is a list of locations which the location ID in table A joins too. Each row has a 'timezone' saved in it. Table A as also has a datetime column which is local to this this timzone.
https://www.php.net/manual/en/timezones.php
I want to order records from table A in time order based on the timezone
order by eventDateTime
Doesn't work as no timezone is consdered
Maybe use CONVERT_TZ()?
Perfect, thank you.
Out of interest. When I do a where g.kickOff > now is it possible to change the now to be the same timezone as g.kickOff. My system time is after my games time so when displaying this I'm now getting the correct results unitll after my server has gone past the time.
Sounds like there are numerous good reasons to convert all the datetimes to UTC when inputting them, then if desired use the value from the timezone field to convert them in local time upon output. This would have the added benefit of making all sorts on the datetime more efficient, as the DB would not have to read every field and convert it before sorting.
