I'm developing a PHP/MySQL webapp
that tracks the status of some devices over time, receiving a series of snapshots sent by a Windows software through a web service
(I have the control of the Windows software too, so I can apply modifications if necessary).
I have to face the timezones problem, since the Windows software often needs to pass the literal values of the timestamps (e.g. "2018-12-06 15:30:00") instead of NOW()
, referring to its system time. The computer that runs it can be located in any state.
I've already migrated my MySQL data from the datetime format to timestamp, since the first one doesn't handle timezones, while timestamp - if I understood correctly - always stores the data related to GMT.
How can I pass this literal values from the Windows software making also note the reference timezone, to give MySQL a way to convert it correctly into GMT? I'm thinking about something like "2018-12-06 15:30:00+01:00" (which I have tried, but unsuccessfully).
In addition to this, I'd need a way to find out if DST is in effect or not from PHP, in order to decide how to offset the timestamp value. Is there a way to make this work?
EDIT: Regarding DST, I've found out that MySQL should have a way to know the time offset of a country compared to GMT in any time, but often the tables that deal with this are not populated on the MySQL server. Unfortunately it is also my case, and I cannot populate them manually because my hosting does not provide the access to them.