A time offset should not be stored naively in an INT field, because there are half-hour and quarter-hour offsets.Avoid TIMESTAMP because it's limited to the year 2038, and MySQL relates it to the server timezone, which is probably undesired.Use two columns: DATETIME, and a VARCHAR to hold the time zone information, which may be in several forms:Ī timezone or location such as America/New_York is the highest data fidelity.Ī timezone abbreviation such as PST is the next highest fidelity.Ī time offset such as -2:00 is the smallest amount of data in this regard. How to store a datetime in MySQL with timezone info None of the answers here quite hit the nail on the head. This date and time exists in Tanzania, but not in the U.S., where the database is. I send the command: INSERT INTO Images (CaptureEvent, SequenceNum, PathFilename, TimestampJPG) Here's an example of the problem I'm running into. ![]() Otherwise, any suggestions on how to approach this issue is greatly appreciated. I'm not an SQL expert is there a way to specify timezone when setting DATETIMEs? I haven't seen one. MySQL datetime fields and daylight savings time - how do I reference the "extra" hour? andīut none of them seems to address my particular problem. And I do not have authority to change anything about the server.ĭaylight saving time and time zone best practices and I'm reluctant to set session times because I know that there will be problems when someone sometime forgets to set a session time and gets the times out all wrong. I want them to always come out as Tanzanian time and not in the local times that various collaborator are in. ![]() Tanzania doesn't do DST, so the time is an actually valid time.Īdditional complications are that there are collaborators from many different timezones who will need to access the date-time values stored in the database. and I run into problems when I try to store a Tanzanian date-time that falls within the "invalid" hour during spring Daylight Savings time (in the U.S.). ![]() The server, however, is located in the U.S. I have thousands of photos that were taken in Tanzania and I want to store the date and time each photo was taken in a MySQL database.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |