SQlite Dates and GMTOffset

Using the Xojo DB API it looks like the GMTOffset is not saved with the date. It stores the SQLDateTime as string and the date object it returns assumes the the local GMTOffset… How do most deal with that?

Do you ignore it?

Do you set the GMTOffset to 0 when you store the data and after getting the data object set it to the local GMTOffset
For do you store total seconds , which I think is independent of GMTOffset?
Everything has plus and minuses

Thanks,

  • karen

I have a note on dates and databases inside the free download of Studio Stable Database.

Brad,

Your notes don’t say exactly what you are doing.

Anyway the ways i was thinking implementing a TIMESTAMP Datatype are:

  1. Transform the input (be it a date object, TotalSeconds or SQLDateTime string) to a zero GMT Offset Date object and store THAT SQLDateTime string in the DB… When retrieving it create a date object and adjust the GMTOffset to local value… What is lost there is the original timezone and you have to adjust dates for searching… but it is clean otherwise

  2. Another approach might be to save D.SQLDateTime + " " + Str(D.GMTOffset) and parse it to create the dateobject… That way You can have get LocalTime and OriginalTime but will searching , sorting and may have other side effects because of the non standard date format.

  3. Saving Total seconds would work to save absolute date, but complicates a lot of stuff and you still lose the original timezone

Leaning toward 1 but it would be nice to reserve the original timezone data somehow

Anybody have any thoughts?

  • karen

They say this: “This method will convert the Date to UTC before inserting into the database.” And the source code is provided so you can actually dig in and see that’s what’s happening.

Could I be any clearer?

Sorry, I downloaded the wrong Zip file, did not realize it and so did not see it and thought you were referring to the notes on the website and i was getting confused between UTC time itself and the various ways IS0-8601 allows it to be formatted… (see: http://www.w3.org/TR/NOTE-datetime )

Looks like you are using my option 1, storing it in zulu time in Date.SQLDateTime format.

After a little research, the IS0-8601 formats that support preserving the original timezone are not supported by SQLite, won’t sort right anyway (because “-” > “+”) and would make Where causes with dates a nightmare.

Still wish there was a good way to both save both the absolute datetime AND preserve the original timezone in a single column.

  • Karen

PostgreSQL has the TIMESTAMP WITH TIME ZONE data type for exactly this situation. You can then do cool tricks like select the value in any time zone you like.

Still, my preferred method is just to always use GMT.