As a side note... in all the applications where I use SQLite3, when storing DateAndTime I use the Unix representation (milliseconds since epoch) in UTC, although it is unreadable for the end user, it is more compact, and faster to read/store.
Regards! Esteban A. Maringolo 2016-06-29 15:43 GMT-03:00 Alistair Grant <akgrant0...@gmail.com>: > Hi Sven, > > On Sun, Jun 26, 2016 at 03:34:15PM +0200, Sven Van Caekenberghe wrote: >> >> > On 26 Jun 2016, at 14:59, Alistair Grant <akgrant0...@gmail.com> wrote: >> > >> > Hi All, >> > >> > UDBCSQLite3Statement>>at:putDateTime: currently writes date and times >> > using DateAndTime's default print string, i.e. >> > YYYY-MM-DDTHH:MM:SS.mmmmmm+hh:ss >> > >> > SQLite3 doesn't support timezones, and expects text formatted date/times >> > to be: >> > >> > YYYY-MM-DD HH:MM:SS.mmm >> > >> > See: https://www.sqlite.org/datatype3.html >> > >> > The concensus, as far as I can tell, is that UTC times should always be >> > stored (which seems to be the only sensible option). >> > >> > Changing UDBCSQLite3Statement>>at:putDateTime: to: >> > >> > at: aColumn putDateTime: aDateTime >> > "Put the supplied DateAndTime in column aColumn. >> > >> > SQLite3 supports three date/time formats: text, real (Julian day), >> > integer (unix seconds). >> > We use the ISO8601 text format: YYYY-MM-DD HH:MM:SS.SSS >> > See https://www.sqlite.org/datatype3.html"; >> > >> > | s | >> > >> > s := UDBCSQLite3DateTimeString streamContents: [ :stream | |utc | >> > utc := aDateTime asDateAndTime asUTC. >> > utc printYMDOn: stream. >> > stream nextPut: Character space. >> > utc printHMSOn: stream. >> > stream nextPut: $.. >> > stream nextPutAll: ((aDateTime nanoSecond / 1000000) rounded >> > printPaddedWith: $0 to: 3) ]. >> > ^ self library with: handle at: aColumn putString: s >> > >> > >> > writes date / times in the correct format. >> > >> > Note that SQLite3 doesn't do much in the way of format checking, so the >> > old implementation didn't raise an error, but can cause problems if the >> > database is shared with other applications that follow the rules (which >> > is why I hit this problem). >> > >> > To provide some backward compatibility, and follow the rule of being >> > strict in what you write and forgiving in what you read, we need to >> > modify UDBCSQLite3Statement>>dateTimeAt: to accept timezones when >> > supplied, or default to UTC time. >> > >> > Unfortunately, the current DateAndTime>>readFrom: method assumes the >> > local timezone if none is specified. >> > >> > I can think of two approaches to solve this: >> > >> > 1. Modify UDBCSQLite3Statement>>dateTimeAt: to add 'Z' to the string if >> > no timezone is specified, i.e. make UTC explicit. >> > >> > 2. Extend DateAndTime class to support #readFrom:defaultOffset: so >> > that UTC can be specified as the default, instead of the local timezone, >> > and modify UDBCSQLite3Statement>>dateTimeAt: to use UTC as the default. >> > >> > I prefer the second approach, but realise that this is a change to the >> > core Pharo classes, so will implement the first if that is the concensus. >> > I've included the modified methods for the second approach below. >> >> Yes, option 2 is the right way to go. Your code looks OK from a cursory look. >> >> Please propose a real slice and make certain you add a couple of good unit >> tests, this is really crucial. > > Thanks for your feedback. > > I'll certainly include automated tests in the slice. > > I'm still getting up to speed with fogbugz and the patch system. Should > this be submitted as two separate issues (one for the UDBC package and > one for Kernel-Chronolgy), or as a single issue? > > Thanks again, > Alistair > >
