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

Reply via email to