By Axxel

2018-12-06 15:03:16 8 Comments

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.


Related Questions

Sponsored Content

28 Answered Questions

[SOLVED] How can I prevent SQL injection in PHP?

36 Answered Questions

[SOLVED] Should I use the datetime or timestamp data type in MySQL?

35 Answered Questions

[SOLVED] PHP: Delete an element from an array

  • 2008-12-15 20:28:55
  • Ben
  • 2229365 View
  • 2214 Score
  • 35 Answer
  • Tags:   php arrays

18 Answered Questions

[SOLVED] Reference — What does this symbol mean in PHP?

15 Answered Questions

[SOLVED] Why shouldn't I use mysql_* functions in PHP?

  • 2012-10-12 13:18:39
  • Madara Uchiha
  • 200110 View
  • 2356 Score
  • 15 Answer
  • Tags:   php mysql database

11 Answered Questions

[SOLVED] Sqlite: CURRENT_TIMESTAMP is in GMT, not the timezone of the machine

2 Answered Questions

[SOLVED] timezone with DST handling by PHP

1 Answered Questions

0 Answered Questions

Handling timezones in MySQL without zoneinfo

2 Answered Questions

[SOLVED] TimeZone not detecting DST

Sponsored Content