SQLite 3 datetime and timestamp

[Roberto Padovani]

Hi all!

two things: 1) a (possible) improvement to sqlite3ds; 2) the old
problem with dates and time

1) I found a sqlite database that uses the TIMESTAMP data type, but
this is not directly supported by the Tsqlite3Dataset written by Luiz.
I added the if-else checks in sqlite3ds.pas for this type, which
refers to ftTimestamp of TFieldType in db.pas. And I also added
db.DefaultFieldClasses[ftTimeStamp] := TDateTimeField at run-time,
because in db.pas it is defined as nil (and I didn't want to recompile
it).
So, now my app can understand a timestamp field as a TDateTime instead
of a string...

2)and here comes the problem! The code:

var d: TDateTime;
..
d := database.FieldByName('birthday').asDateTime;
...
will always store the zero date: 30 Dec 1899
why ?

Being urgent, at the moment I am reading the dates as strings by using
the "date" or "datetime" function of SQLite in the query, i.e.:

SELECT date(birthday) AS string_birthday FROM people;

and then parsing them with the powerful functions in the freepascal
RTL, but I would like to understand this once for all.

Can anyone help ?
Thanks !!

R#

[Roberto Padovani]

Hi all!

two things: 1) a (possible) improvement to sqlite3ds; 2) the old
problem with dates and time

1) I found a sqlite database that uses the TIMESTAMP data type, but
this is not directly supported by the Tsqlite3Dataset written by Luiz.
I added the if-else checks in sqlite3ds.pas for this type, which
refers to ftTimestamp of TFieldType in db.pas. And I also added
db.DefaultFieldClasses[ftTimeStamp] := TDateTimeField at run-time,
because in db.pas it is defined as nil (and I didn't want to recompile
it).
So, now my app can understand a timestamp field as a TDateTime instead
of a string...

2)and here comes the problem! The code:

var d: TDateTime;
..
d := database.FieldByName('birthday').asDateTime;
...
will always store the zero date: 30 Dec 1899
why ?

Being urgent, at the moment I am reading the dates as strings by using
the "date" or "datetime" function of SQLite in the query, i.e.:

SELECT date(birthday) AS string_birthday FROM people;

and then parsing them with the powerful functions in the freepascal
RTL, but I would like to understand this once for all.

Can anyone help ?
Thanks !!

R#

[Luiz Americo Pereira Camara]

Post by Roberto Padovani
Hi all!
two things: 1) a (possible) improvement to sqlite3ds; 2) the old
problem with dates and time
1) I found a sqlite database that uses the TIMESTAMP data type, but
this is not directly supported by the Tsqlite3Dataset written by Luiz.
I added the if-else checks in sqlite3ds.pas for this type, which
refers to ftTimestamp of TFieldType in db.pas. And I also added
db.DefaultFieldClasses[ftTimeStamp] := TDateTimeField at run-time,
because in db.pas it is defined as nil (and I didn't want to recompile
it).
So, now my app can understand a timestamp field as a TDateTime instead
of a string...
var d: TDateTime;
..
d := database.FieldByName('birthday').asDateTime;
...
will always store the zero date: 30 Dec 1899
why ?

It's necessary to see the changes you made.
To add a new type is necessary to modify InternalInitFieldDefs,
GetFieldData and SetFieldData

Post by Roberto Padovani
Being urgent, at the moment I am reading the dates as strings by using
SELECT date(birthday) AS string_birthday FROM people;
and then parsing them with the powerful functions in the freepascal
RTL, but I would like to understand this once for all.

Here's how sqlite works (for good and bad):

- You can create tables with any "field type": TIMESTAMP, TIME_STAMP,
QWERTY etc
- In any of this "field type" you can store anything: a integer, a
float, a string

Many sqlite managers make assumptions (each one create its own
convention). Examples:
BOOLEAN will store TRUE or FALSE strings,
DATE will store "02-12-2007".
This is completely random.

So program X can store DATE as "02-12-2007" but program Y will store as
"02/12/2007". The program Z, worried with memory stores as "02122007"
but program W is more smart so will store as "021207".

Supporting these conventions it would lead to code bloat and performance
issues and someone would always ask "hey my DATE format is not
supported, please do it".

In other words, sqliteds, as it was designed, has the objective of add a
way to fpc programs to use sqlite as a database backend. So it does. It
was not designed for access data files created with other managers or
frameworks.
See as a balance between feature and code size/performance. Any design
taken has advantages and disadvantages. In the sqliteds case, the
decision was to trend to code simplicity, privilege the most common
cases in detriment of not so common.

If you come until here, don't be pessimist. Some solutions:

- Don't forget is open source. You can modify to fill your needs. I can
help with this. Send what you already did.
- If you don't want to modify sqliteds file directly you can write
descendants.
- Try sqlitepass and sqldb/sqlite3. I vaguely remenber of sqlitepass to
support timestamp. I don't know about sqldb.
- If you just want to import the data of a "strange" db file (you won't
need to access directly this file all time) than convert to the sqliteds
format (Double). I already started a program that does this but not
finished. I can also help you.


Luiz

[Roberto Padovani]

Hi Luiz,

first of all, thanks a lot for the good job you did with sqlite for us all!

Post by Luiz Americo Pereira Camara

Post by Roberto Padovani
So, now my app can understand a timestamp field as a TDateTime instead
of a string...
..
[removed]
...

- You can create tables with any "field type": TIMESTAMP, TIME_STAMP,
QWERTY etc
- In any of this "field type" you can store anything: a integer, a
float, a string
Many sqlite managers make assumptions (each one create its own
..
[removed]
...

I'm perfectly aware of that and I'm living happily with it...it's an
embedded database anyway!

I don't expect a QWERTY type to be understood, of course, but I
thought a timestamp might, because the sqlite.org documentation
http://www.sqlite.org/lang_createtable.html
refers to it. And SQLiteAdmin and SQLite Manager and SQLite Firefox
extension can understand it.
Anyway, as you said, it is opensource and with a very open license, so
I just added the three lines of code I needed: my post number 1) is
just to share it.
The code needed is only:

Index: sqlite3ds.pas
===================================================================
--- sqlite3ds.pas (revision 100)
+++ sqlite3ds.pas (working copy)
@@ -165,6 +165,10 @@
begin
AType:= ftTime;
FieldSize:=SizeOf(TDateTime);
+ end else if (ColumnStr = 'TIMESTAMP') then
+ begin
+ AType:= ftDateTime;
+ FieldSize:=SizeOf(TDateTime);
end else if (ColumnStr = 'TEXT') then
begin
AType:= ftMemo;

It's not expensive from the point of view of the code performance nor
lightweight.
Even better, one could also assign AType := ftTimeStamp (which is
alreay defined in db.pas) and then change also db.pas accordingly:

Index: db.pas
===================================================================
--- db.pas (revision 101)
+++ db.pas (working copy)
@@ -1900,7 +1900,7 @@
{ ftInterface} Nil,
{ ftIDispatch} Nil,
{ ftGuid} TGuidField,
- { ftTimeStamp} Nil,
+ { ftTimeStamp} TDateTimeField,
{ ftFMTBcd} Nil,
{ ftFixedWideString} TWideStringField,
{ ftWideMemo} TWideMemoField


I haven't seen the source code of the tools I mentioned above, but I
guess that sqlite3ds would behave in the same way if you change the
IFs like:
if (ColumnStr = 'TIME')
into
if ( Pos('TIME',ColumnStr) > 0 )
which is similar to what you do with BOOL, except that >0 understands
the CURRENT_TIME type that you can find in the documentation of sqlite

[Roberto Padovani]

Now, about assumptions.

in sqlite3ds there is the assumption that BOOLEAN fields are stored as
1 or 0. In fact, to recover a db that had "TRUE" and "FALSE", I made a
new class where I changed ftBool to fString. In this way I could read
them, parse them and then write them back as 1 or 0.

With the dates, the problem is similar, because sqlite3ds understands
that the field is of type "DATE", and so it makes the following
assumption in customsqliteds.pas line 603:

ftFloat,ftDateTime,ftTime,ftDate,ftCurrency:
begin
Val(StrPas(FieldRow),Double(Buffer^),ValError);
Result:= ValError = 0;
end;

In the database I have, the dates are in the ISO standard form
"2007-12-03 12:11:10.1234"
How do you suggest me to handle with them ?
1) by complicating the SQL queries with SELECT date(field_name) as
field_name FROM... which turns the field_name into a string in the
result set
2) by subclassing or anyway changing the TFieldType ?
3) like with boolean, by making a special temporary class that parses
the whole database (not so big) and changes the dates into
Freepascal-style doubles (are they stored more efficiently ?)
4) other (please specify :-))

My brain-storming last night came up with the idea of adding a flag
that says "force every field as a string": this would let someone who
doesn't know which convention was used in which field to inspect it.
Is it somewhat crazy ?

By the way, I really like sqlite and the unit you made to access it.
When the project I'm working on is finished, I'll strip everything
from the source code and only leave a detailed tutorial for using
sqlite without visual components. Meanwhile, I read somewhere in the
mailing that you are writing a documentation for sqlite3ds; is it
ready or partially ready ?
I'm studying the whole code at the moment and some documentation would
help a lot, especially to see the global structure, to get the large
picture of it. Moreover, I'm going to keep on working a lot with
sqlite, so if some contribution is needed somewhere, let me know.

Thanks a lot,

Roberto

[Luca Olivetti]

Post by Roberto Padovani
When the project I'm working on is finished, I'll strip everything
from the source code and only leave a detailed tutorial for using
sqlite without visual components.

FWIW to do that you can also use sqlite3 directly (or with a thin
wrapper), you don't have the convenience of a full dataset descendant
but you have complete control on what you get from the database.

Bye

[Luiz Americo Pereira Camara]

Post by Luca Olivetti

Post by Roberto Padovani
When the project I'm working on is finished, I'll strip everything
from the source code and only leave a detailed tutorial for using
sqlite without visual components.

FWIW to do that you can also use sqlite3 directly (or with a thin
wrapper), you don't have the convenience of a full dataset descendant
but you have complete control on what you get from the database.

BTW: I wrote a thin wrapper to sqlite3. See
https://luipack.bountysource.com/svn/!tree/185#svn0_4|svn0_4_9

Is not in a release state yet. I'll release together with the documentation.

Luiz

[Luiz Americo Pereira Camara]

Post by Roberto Padovani
Now, about assumptions.
in sqlite3ds there is the assumption that BOOLEAN fields are stored as
1 or 0. In fact, to recover a db that had "TRUE" and "FALSE", I made a
new class where I changed ftBool to fString. In this way I could read
them, parse them and then write them back as 1 or 0.
With the dates, the problem is similar, because sqlite3ds understands
that the field is of type "DATE", and so it makes the following
begin
Val(StrPas(FieldRow),Double(Buffer^),ValError);
Result:= ValError = 0;
end;

True. It stores both in memory and in database the Double value.

Post by Roberto Padovani
In the database I have, the dates are in the ISO standard form
"2007-12-03 12:11:10.1234"
How do you suggest me to handle with them ?
1) by complicating the SQL queries with SELECT date(field_name) as
field_name FROM... which turns the field_name into a string in the
result set

Possible

Post by Roberto Padovani
2) by subclassing or anyway changing the TFieldType ?

Difficult

Post by Roberto Padovani
3) like with boolean, by making a special temporary class that parses
the whole database (not so big) and changes the dates into
Freepascal-style doubles (are they stored more efficiently ?)

I already started such tool. Not ready yet. Good if is one step eg you
won't access that file every time.

Post by Roberto Padovani
4) other (please specify :-))

Modify the sqlite3ds or create a TSqlite3Dataset descendant that:

1) Recognize a TIMESTAMP field
2) Stores the field value as a string in the desired format
3) When the data is loaded convert from string to double
4) When do ApplyUpdates convert from Double to String


I can do that.

Post by Roberto Padovani
My brain-storming last night came up with the idea of adding a flag
that says "force every field as a string": this would let someone who
doesn't know which convention was used in which field to inspect it.
Is it somewhat crazy ?

It's more difficult than above, but possible.

Post by Roberto Padovani
By the way, I really like sqlite and the unit you made to access it.

Good

Post by Roberto Padovani
When the project I'm working on is finished, I'll strip everything
from the source code and only leave a detailed tutorial for using
sqlite without visual components.

Good.

Post by Roberto Padovani
Meanwhile, I read somewhere in the
mailing that you are writing a documentation for sqlite3ds; is it
ready or partially ready ?

Partially ready. This is really needed because there's a lot of
undocumented features and some feature misuses (like using QuickQuery
for execute a SQL)
I'll be busy until 15/12. After that i will work on a solution for this
case, finish the documentation, work in improvements in sqliteds and
release a new sqlite3 wrapper.
I'll contact you.

Post by Roberto Padovani
I'm studying the whole code at the moment and some documentation would
help a lot, especially to see the global structure, to get the large
picture of it. Moreover, I'm going to keep on working a lot with
sqlite, so if some contribution is needed somewhere, let me know.

Fine. The documentation is a place that needs work.

Luiz

[Luca Olivetti]

Post by Luiz Americo Pereira Camara
- Try sqlitepass and sqldb/sqlite3. I vaguely remenber of sqlitepass to
support timestamp. I don't know about sqldb.

Note that sqlitepass is windows only (at least it was last time I looked).
You can also use zeos, it is cross platform but I didn't check if/how it
does support timestamps with sqlite (since, as Luiz explained, you can
put any data type in any sqlite column).

Bye