What would be the 'correct' behaviour for an out of bounds day in a month? If you look at dates as an index + natural number offset then Jan 32 == Feb 1. What is January 0 or January -1? If expressing dates this way (and not as an int from an epoch) I think that it is up to the application to sanitize inputs for this one. About 8 years ago I decided that I was not going to to use the sqlite date functions at all, preferring to either store string dates for humans or the numeric value (unix epoch, or .net timestamp ), and write it into the design rules for the project that only UTC date values will be in the db. Within the bounds of time we use (I don't have to worry about handling product construction or delivery dates before the company's founding) this is ok. This smooths out time to simplify calculations and shoves the formatting & presentation of the data to the application's presentation layer, which knows things like what time zone the user is sitting in, and their cultural context.
Every once in a while there are some very interesting lessons about the precision of time on the list (the Earth used to turn faster) etc. Others can talk about extensions / modules handles in the extreme cases. I wouldn't suggest that we drop the date time functions due to a predictable storm of "we will not break backward compatibility", but I wouldn't care if sqlite dropped date formatting altogether. (At this point I do not need instructions as to compiling it out so save a few KB on the device.) People on the list are genuinely trying to be helpful, even if cross language text comes across as terse, try to keep that context. best wishes, Adam DeVita On Thu, May 5, 2016 at 8:52 AM, Cecil Westerhof <cldwesterhof at gmail.com> wrote: > 2016-05-05 12:39 GMT+02:00 Simon Slavin <slavins at bigfraud.org>: > > > > > On 5 May 2016, at 11:25am, Cecil Westerhof <cldwesterhof at gmail.com> > wrote: > > > > > At > > > the moment valid times can be marked as invalid and invalid times as > > valid. > > > Probably imposable to completely circumvent, but it can be done a lot > > > better. > > > > I don't know what TimeZone you're in (your surname looks German) but at > > this level of detail it becomes > > > ?German would be Westerhoff. I live in the Netherlands. > > ? > > > > important whether you're in the US or EU or any other place. The phrase > > 'valid times' covers a large number of subjects and we can't tell which > of > > them are important to you. > > > > For instance, do you care if someone enters a time which is skipped by > the > > clocks going forward ? If at 1am your clocks skip straight to 2am, do > you > > care if someone enters a time of 1:30am on that day ? > > > > Or maybe you're in Samoa, which skipped the 30th of December 2011 > > entirely, and may one day want to go the other way, which it would do by > > having a 32nd of December or an unwarranted 29th of February. > > > > You can get endlessly fussy about leap years and leap seconds and such > > things to the point where you know the Time Lords by name. SQLite > > definitely cannot handle that level of detail and it should not be used > for > > timestamp validation. It's best either to find an external library for > > your programming language or tell yourself to relax and stop sweating the > > small stuff. > > > ?I do not like the straw man stuff. > https://en.wikipedia.org/wiki/Straw_man > > I respond to a question and in the response from someone else something is > said that when checked is proved to be not true. I do then some other > checking and find some things that could in my opinion easily be rectified. > I can understand a reaction like: > We do not want to change it because we do not find it important. > What I really not like is: > We do not want to change it, but do not want to acknowledge it. So lets > pretend that what is asked is unreasonable. > > I never was talking about timezones, so the problems about Samao and Summer > Time has nothing to do with what I was talking about. > I was not ?endlessly fussy?, I was only talking about a few simple changes > that would give a much better result with little effort (Pareto Principle). > Again: it is possible that it is something that the maintainers not want to > do, but be honest about that. > > > I like to contribute, but a treatment like this is not encouraging. > > -- > Cecil Westerhof > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- -------------- VerifEye Technologies Inc. 151 Whitehall Dr. Unit 2 Markham, ON L3R 9T1