If you’ve worked with times and dates you know how much fun they can be especially when you deal with different time zones, differing Daylight savings time rules, if they observe DST and all the other intricacies tracking dates and times bring up.
And very often you need to save data to a database. So how do you store the data in your database so you can properly deal with it? And does your chosen database make that easy?
Some database engines have no ability to store time zone data so you HAVE to worry about how you store data in the database in order to reconstitute it correctly again across time zones. MySQL has no built in ability to store times with time zones. So you have two choices: write code to store the time zone offset data, or when you insert times you convert them to GMT and when you pull them out you deal with the mas GMT.
The downside to this is the data in your database is in GMT – not local time – so all queries have to take this into account.
SQLite and PostgreSQL do have the ability to deal with time with time zone data. However in PostregSQL it’s not very common to find columns defined as a time with time zone (see http://www.postgresql.org/docs/9.3/interactive/datatype-datetime.html). Usually they are timestamped, or times that do not include the time zone.
MS SQL varies in how you deal with it but behaves mostly like mySQL.
So what’s a person to do?
If you’ve used a database that doesn’t directly support time zones and you write queries to try and find all data between two dates and you have manually stored time zone data, you will have a problem with your query because the database engine can’t help you – it doesn’t understand time zones.
So, don’t store times in any form other than GMT. But this means your queries have to also be converted to use GMT and not local time.
If you do use an engine that supports time zones, then you can use that and query date or time ranges that will be correct because the engine can understand differences in time zones and can run the query taking that into consideration.
So how does this impact how you use Xojo?
When you insert a “Date” to a database that doesn’t support time zones that database will drop the time zone data. So the date you insert, which is likely in local time, is suddenly in GMT since the offset is implied to be 0. Be aware of this when you grab that data back from the database. What went in as GMT-7 is now coming back as GMT+0.
Be careful how you handle your dates when they get inserted and retrieved from a database. Convert your dates to GMT and then insert them.