My point is that there is no datetime magic performed for TEXT fields. If you feel the database should handle it, go ahead and write triggers/check constraints.
If you feel the application should handle it, make it convert to and from just one single format (we use 64bit numerical timestamps in UTC). In either case, it is not a "bug" in SQlite. -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Stephen Chrzanowski Gesendet: Donnerstag, 02. November 2017 14:45 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: [EXTERNAL] Re: [sqlite] SQLite DB - DateTime field values are not consistent As Hick mentioned, you could get your application to deal with this, or, if you want it at the SQLite level, you could get into triggers to analyze the data before its being inserted or updated. For me, it depends on how you want to look at the issue as far as what part of the entire application scope should be defending the data. IMO, it should be the database that defends its data that it holds, while others state that the database should be just raw data. Both aspects (IMO) are absolutely valid, but its a matter of thought and how you (And your team?) decide to ensure your data remains safe. On Thu, Nov 2, 2017 at 8:23 AM, Rajat Pal <rajat....@oracle.com> wrote: > Hi Team, > > > > We have identified an issue with SQLite database which seems to be a > potential bug. > > > > In SQLite database, All the values of datetime fields are saved as > text without any format check. We can save any text value in the > datetime column. This behavior is creating problem for us because for > different regional settings, datetime values are getting saved in different > format. > > > > For example, if the regional format is English(United States), the > value is saved as "2017-09-21 00:00:00" but if the regional format is > Finnish(Finland), the value is saved as "2017-10-27 00.00.00". So some > dates have colon(:) as time separator and some dates have dot (.) as > time separator. > > > > Below query can illustrate the problem: > > > > Create Table Table1(TestDate DATETIME); > > > > Insert Into Table1 values (datetime('now','localtime')); > > > > insert into Table1 values ("2017-11-01 17:31:41"); > > > > insert into Table1 values ("2017-11-01 17.32.41"); > > > > insert into Table1 values ("Testing"); > > > > select * from Table1; > > > > Can you please let us know if there is any way we can force the > datetime field to have the datetime values in same format only. > > > > Regards, > > Rajat Pal > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
