SQLite DB - DateTime field values are not consistent

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

SQLite DB - DateTime field values are not consistent

Rajat Pal
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] SQLite DB - DateTime field values are not consistent

Hick Gunter
There is no "date" datatype in SQLite, an das you yourself attest, SQLite is returning exactly whatever was originally inserted.

It is up to your application to define the allowed format for storing datetimes and to provide conversion between the chosen storage format and the presentation to the user.

Hint: You could write a user defined function and use that in a check constraint to enforce your chosen format.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Rajat Pal
Gesendet: Donnerstag, 02. November 2017 13:24
An: [hidden email]
Cc: Joy George Chittilapailly <[hidden email]>; Minghua Feng <[hidden email]>; Rohit Udasi <[hidden email]>
Betreff: [EXTERNAL] [sqlite] SQLite DB - DateTime field values are not consistent

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
[hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite DB - DateTime field values are not consistent

Stephen Chrzanowski
In reply to this post by Rajat Pal
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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] SQLite DB - DateTime field values are not consistent

David Raymond
In reply to this post by Hick Gunter
For basic level you can use a check constraint

create table Table1 (
  TestDate DATETIME
    check (TestDate like '____-__-__ __:__:__'
           and datetime(TestDate) is not null)
);

That should check that it's in the correct format, and the second part should check that there's no garbage in there.

Hmm, if TestDate is nullable I think it needs slightly more...

create table Table2 (
  TestDate DATETIME
    check (TestDate like '____-__-__ __:__:__'
      and not (TestDate is not null and datetime(TestDate) is null))
);

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Hick Gunter
Sent: Thursday, November 02, 2017 8:39 AM
To: 'SQLite mailing list'
Subject: Re: [sqlite] [EXTERNAL] SQLite DB - DateTime field values are not consistent

There is no "date" datatype in SQLite, an das you yourself attest, SQLite is returning exactly whatever was originally inserted.

It is up to your application to define the allowed format for storing datetimes and to provide conversion between the chosen storage format and the presentation to the user.

Hint: You could write a user defined function and use that in a check constraint to enforce your chosen format.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Rajat Pal
Gesendet: Donnerstag, 02. November 2017 13:24
An: [hidden email]
Cc: Joy George Chittilapailly <[hidden email]>; Minghua Feng <[hidden email]>; Rohit Udasi <[hidden email]>
Betreff: [EXTERNAL] [sqlite] SQLite DB - DateTime field values are not consistent

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
[hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] SQLite DB - DateTime field values are not consistent

Richard Hipp-3
On 11/2/17, David Raymond <[hidden email]> wrote:

> For basic level you can use a check constraint
>
> create table Table1 (
>   TestDate DATETIME
>     check (TestDate like '____-__-__ __:__:__'
>            and datetime(TestDate) is not null)
> );
>
> That should check that it's in the correct format, and the second part
> should check that there's no garbage in there.

How about this approach:

  CREATE TABLE table1(
      TestDate DATETIME NOT NULL
          CHECK(julianday(TestDate) IS NOT NULL)
  )

All of the date/time functions return a NULL if you give them an
invalid date/time as input.  So checking for a NULL result is a good
way to validate the date/time.  This goes further than the LIKE
pattern above, as it inhibits nonsense dates such as 'abcd-ef-gh
ij:kl:mn' which the LIKE pattern would apparently accept.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: SQLite DB - DateTime field values are not consistent

Hick Gunter
In reply to this post by Stephen Chrzanowski
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:[hidden email]] Im Auftrag von Stephen Chrzanowski
Gesendet: Donnerstag, 02. November 2017 14:45
An: SQLite mailing list <[hidden email]>
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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] SQLite DB - DateTime field values are not consistent

nomad
In reply to this post by Richard Hipp-3
On Thu Nov 02, 2017 at 10:18:41AM -0400, Richard Hipp wrote:

> On 11/2/17, David Raymond <[hidden email]> wrote:
> > For basic level you can use a check constraint
> >
> > create table Table1 (
> >   TestDate DATETIME
> >     check (TestDate like '____-__-__ __:__:__'
> >            and datetime(TestDate) is not null)
> > );
> >
> > That should check that it's in the correct format, and the second part
> > should check that there's no garbage in there.
>
> How about this approach:
>
>   CREATE TABLE table1(
>       TestDate DATETIME NOT NULL
>           CHECK(julianday(TestDate) IS NOT NULL)
>   )
>
> All of the date/time functions return a NULL if you give them an
> invalid date/time as input.  So checking for a NULL result is a good
> way to validate the date/time.  This goes further than the LIKE
> pattern above, as it inhibits nonsense dates such as 'abcd-ef-gh
> ij:kl:mn' which the LIKE pattern would apparently accept.

That approach is also not quite sufficient to ensure that the input is
actually a yyyy-mm-dd hh:mm::ss format, because the SQLite datetime
functions also accept HH:MM, 'now', integers/float...

    sqlite> select julianday(2017323.32);
    julianday(2017323.32)
    ---------------------
    2017323.32

To be really sure the input conforms to a particular date/time format
you need to round-trip it:

    CREATE TABLE Table1(
        TestDate DATETIME NOT NULL,
        CONSTRAINT valid_datetime CHECK(
            TestDate = COALESCE(
                datetime( julianday(TestDate) ),
                TestDate || x'
            )
        )
    );

--
Mark Lawrence
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] SQLite DB - DateTime field values are not consistent

David Raymond
In reply to this post by Richard Hipp-3
I think the combo is needed as with just the time function not null constraint you can just insert in plain ol' integer values.

sqlite> select julianday(7);
julianday(7)
7.0

sqlite> select datetime(2);
datetime(2)
-4713-11-26 12:00:00

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Richard Hipp
Sent: Thursday, November 02, 2017 10:19 AM
To: SQLite mailing list
Subject: Re: [sqlite] [EXTERNAL] SQLite DB - DateTime field values are not consistent

On 11/2/17, David Raymond <[hidden email]> wrote:

> For basic level you can use a check constraint
>
> create table Table1 (
>   TestDate DATETIME
>     check (TestDate like '____-__-__ __:__:__'
>            and datetime(TestDate) is not null)
> );
>
> That should check that it's in the correct format, and the second part
> should check that there's no garbage in there.

How about this approach:

  CREATE TABLE table1(
      TestDate DATETIME NOT NULL
          CHECK(julianday(TestDate) IS NOT NULL)
  )

All of the date/time functions return a NULL if you give them an
invalid date/time as input.  So checking for a NULL result is a good
way to validate the date/time.  This goes further than the LIKE
pattern above, as it inhibits nonsense dates such as 'abcd-ef-gh
ij:kl:mn' which the LIKE pattern would apparently accept.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite DB - DateTime field values are not consistent

Tim Streater-3
In reply to this post by Rajat Pal
On 02 Nov 2017, at 12:23, Rajat Pal <[hidden email]> wrote:

> 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.

As sqlite has no DATETIME field, what you actually get is a TEXT field in which you can store anything.

> 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.

In whatever language you use to call sqlite, you should write a general function that can take anything that looks like a date/time and convert it to seconds since the epoch (or any other fixed historical moment), then store that value as an integer using sqlite. If you are using PHP, the strtotime() function does a very good job for that purpose.

Then when you retreive it, convert it to a time/date in the form that the local user expects.


--
Cheers  --  Tim
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite DB - DateTime field values are not consistent

Keith Medcalf
In reply to this post by Rajat Pal

Fascinating.  

What is the Operating System and Version (eg Linux <kernel version> <distribution name> <distribution version> or Windows <version> <build>); SQLite version; interface (C or .snot)?  

Does this only affect trips through the platform localtime function within SQLite (that is, if you store GMT/UTC by leaving out the 'localtime' modifier) do you still get regionalized formatting coming back?

Or are you providing a misformed timestamp produced by some regional aware function (ie, not forcing a format/region override when retrieving the timestamp-string -- which is a pretty common application level error in my experience)?

Note that I cannot get the builtin 'now' or 'localtime' modifiers to return an inconsistent (regionalized) format, but I haven't tried real hard either.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Rajat Pal
>Sent: Thursday, 2 November, 2017 06:24
>To: [hidden email]
>Cc: Joy George Chittilapailly; Minghua Feng; Rohit Udasi
>Subject: [sqlite] SQLite DB - DateTime field values are not
>consistent
>
>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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users