Recent

Author Topic: Again on SQLite and date times.  (Read 3821 times)

Чебурашка

  • Hero Member
  • *****
  • Posts: 568
  • СЛАВА УКРАЇНІ! / Slava Ukraïni!
Re: Again on SQLite and date times.
« Reply #15 on: November 16, 2022, 08:33:12 am »
Regarding DateTime data type in SQLite3 look at "2.2. Date and Time Datatype" on https://www.sqlite.org/datatype3.html.
You can use DATETIME as "datatype" in CREATE TABLE statement. SQLite3 will perfectly accept it and Lazarus/FPC will recognize it and will map it to ftDateTime field type.

Thanks, I agree and it was the way I was working originally. Later I needed to reimplement my code/db in order to support SQLite+PostgreSQL+SQLServer+Oracle and I decided to start using liquibase to manage the various details of the db creation step. At this point I noticed that liquibase was mapping DATETIME datatype to REAL and all this story started.
FPC 3.2.0/Lazarus 2.0.10+dfsg-4+b2 on Debian 11.5
FPC 3.2.2/Lazarus 2.2.0 on Windows 10 Pro 21H2

Чебурашка

  • Hero Member
  • *****
  • Posts: 568
  • СЛАВА УКРАЇНІ! / Slava Ukraïni!
Re: Again on SQLite and date times.
« Reply #16 on: November 16, 2022, 08:34:25 am »
Yeah, that about sums it up.
The only thing coming to mind would be subclassing TSQLQuery, and override AsDateTime and the others with your own Getter/Setter‘s

EDIT: or maybe a class-helper.
Would have to chew on it.

I would be glad to be helpful in some way.
FPC 3.2.0/Lazarus 2.0.10+dfsg-4+b2 on Debian 11.5
FPC 3.2.2/Lazarus 2.2.0 on Windows 10 Pro 21H2

Zvoni

  • Hero Member
  • *****
  • Posts: 2319
Re: Again on SQLite and date times.
« Reply #17 on: November 16, 2022, 09:49:45 am »
Yeah, that about sums it up.
The only thing coming to mind would be subclassing TSQLQuery, and override AsDateTime and the others with your own Getter/Setter‘s

EDIT: or maybe a class-helper.
Would have to chew on it.

I would be glad to be helpful in some way.
After thinking about it, i don't think there is an "easy" solution (nevermind that subclassing/extending TSQLQuery would be the wrong class, since AsDateTime is a property of TField)

Bottom Line: Since you have to "support" multiple DBMS (are you using TSQLConnector?), you'll probably have to write individual code for each connection-type
Kind of like (Pseudo-Code)
If ConnType='SQLite' And FieldType='Float' Then ReadAsFloat

Down-Side: You'd have to know if the Field you want to read is a "real" Float-Value (Say, Money-Balance) or a DateTime-Value
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

Чебурашка

  • Hero Member
  • *****
  • Posts: 568
  • СЛАВА УКРАЇНІ! / Slava Ukraïni!
Re: Again on SQLite and date times.
« Reply #18 on: November 16, 2022, 10:48:19 am »
Bottom Line: Since you have to "support" multiple DBMS (are you using TSQLConnector?), you'll probably have to write individual code for each connection-type
Kind of like (Pseudo-Code)
If ConnType='SQLite' And FieldType='Float' Then ReadAsFloat

Down-Side: You'd have to know if the Field you want to read is a "real" Float-Value (Say, Money-Balance) or a DateTime-Value

To support different RDBMS I simply create the right (subclass of) TSQLConnection object according database type (SQLITE -> TSQLite3Connection, MSSQL -> TMSSQLConnection, ...). Once this is done TSQLTransation and TSQLQuery are always the same. And from my persective also TParam and TField are always the same, despite actual ones could be subclasses to adapt to actual connection type.

And for queries: given the SQL query, each parameter/field has a fixed and known datatype. So not need to do "If ConnType='SQLite' And FieldType='Float' Then ReadAsFloat".

For example, with this update query:

Code: Pascal  [Select][+][-]
  1.   query.SQL := 'update T0003_ConfMachines'
  2.               + ' set'
  3.               + '   name = :name'
  4.               + ' , deleted = :deleted'
  5.               + ' , machineType = :machineType'
  6.               + ' , machineCode = :machineCode'
  7.               + ' , machineAcquisitionType = :machineAcquisitionType'
  8.               + ' , machineAcquisitionDirectory = :machineAcquisitionDirectory'
  9.               + ' , machineImportAtStartup = :machineImportAtStartup'
  10.               + ' , machineAcquisitionFileName = :machineAcquisitionFileName'
  11.               + ' , machineAcquisitionSerialIdentifier = :machineAcquisitionSerialIdentifier'
  12.               + ' , machineHasEnergyMeasuringDevice = :machineHasEnergyMeasuringDevice'
  13.               + ' , listeningMode = :listeningMode'
  14.               + ' , listeningPort = :listeningPort'
  15.               + ' where id = :id';
  16.  
  17.  

for each parameter I know what is going to be the datatype (regardless of RDBMS), so I'll write

Code: Pascal  [Select][+][-]
  1.   query.ParamByName('name').AsString := 'value';
  2.   query.ParamByName('listeningMode').AsInteger := 4;
  3.   ...
  4.  

Same applies to select. For each field I know pascal datatype so I will simple ask for it.

Code: Pascal  [Select][+][-]
  1. query.SQL := 'select eventDate from T0041_MachineLog where (refMachine = :refMachine) and (id = :id)';
  2. query.ParamByName('refMachine').AsInteger = 4;
  3. query.ParamByName('id').AsLargeInt= 214;
  4.  
  5. query.Open();
  6.  
  7. eventDate := query.FieldByName('eventDate').AsDateTime;
  8.  

Basically my perspective is: in code each field has its own fixed data type, then in each RDBMS it has to be choosen the "datatype" capable of storing/suppyling the correspondent correct datatype. Always very easy and working, except with SQLite that sees datatypes as something less strict than other RDBMS.
« Last Edit: November 16, 2022, 11:06:34 am by tt »
FPC 3.2.0/Lazarus 2.0.10+dfsg-4+b2 on Debian 11.5
FPC 3.2.2/Lazarus 2.2.0 on Windows 10 Pro 21H2

Zvoni

  • Hero Member
  • *****
  • Posts: 2319
Re: Again on SQLite and date times.
« Reply #19 on: November 16, 2022, 12:36:29 pm »
Just out of curiosity: How do you handle the different SQL-"Dialects"? (Yeah, nothing to do with the Problem of this Thread)
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

Thaddy

  • Hero Member
  • *****
  • Posts: 14214
  • Probably until I exterminate Putin.
Re: Again on SQLite and date times.
« Reply #20 on: November 16, 2022, 12:42:17 pm »
Just make sure you store in either Tdatetime (double) or year-month-date-time order and time in UTC.
Handle local date/time in code. But everybody knows that.
Specialize a type, not a var.

Чебурашка

  • Hero Member
  • *****
  • Posts: 568
  • СЛАВА УКРАЇНІ! / Slava Ukraïni!
Re: Again on SQLite and date times.
« Reply #21 on: November 16, 2022, 01:17:11 pm »
Just out of curiosity: How do you handle the different SQL-"Dialects"? (Yeah, nothing to do with the Problem of this Thread)

So far I didn't encounter situations in which I need it, mostly my programs do quite standard sql queryies and if there is some logic involved I prefer to keep it in code, it makes me do more work in some situations, but reduces need to dive into db specific operations (like stored procedures). 

In case it happens it would be something like

Code: Text  [Select][+][-]
  1. if (connection-database-type = SQLite) then
  2.    query.SQL := 'SQL dialect text';
  3.    SQLLite specific processing
  4. end;
  5. if (connection-database-type = MSSQL) then
  6.    query.SQL := 'MSSQL dialect text';
  7.    MSSQL specific processing
  8. end
  9.  

more or less what you suggested.
FPC 3.2.0/Lazarus 2.0.10+dfsg-4+b2 on Debian 11.5
FPC 3.2.2/Lazarus 2.2.0 on Windows 10 Pro 21H2

Zvoni

  • Hero Member
  • *****
  • Posts: 2319
Re: Again on SQLite and date times.
« Reply #22 on: November 16, 2022, 01:21:53 pm »
*snipp* or year-month-date-time order and time in UTC.
Handle local date/time in code. But everybody knows that.
Whatever for?
SQLite does offer a modifier for localtime, IF you use the Built-in Date(Time)-Functions of SQLite's own SQL-"Dialect".
If you INSERT/UPDATE DateTimes, this information comes from the Frontend in 99% of cases, and then it's usually according to system-Date/Time of the Workstation.

Of course, if you use the builtin Date/Time-Functions of SQLite itself to Insert/Update Date/Times, then there is a hideyhole for a serious Bug
kinda like
Code: SQL  [Select][+][-]
  1. INSERT INTO MyTable (ID, SomeDateTime) VALUES (1, DateTime('now'));  //THIS STORES UTC!!!!
  2. INSERT INTO MyTable (ID, SomeDateTime) VALUES (2, DateTime('now','localtime'));
  3.  
« Last Edit: November 16, 2022, 01:24:26 pm by Zvoni »
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

Чебурашка

  • Hero Member
  • *****
  • Posts: 568
  • СЛАВА УКРАЇНІ! / Slava Ukraïni!
Re: Again on SQLite and date times.
« Reply #23 on: November 16, 2022, 01:22:27 pm »
Just make sure you store in either Tdatetime (double) or year-month-date-time order and time in UTC.
Handle local date/time in code. But everybody knows that.

I agree.
And, extending the reasonment, in the past I was putting in DB only numeric values in SI unit, so that given a variable it was automatic to know the measure unit. But later I changed my mind and created something that allows me to store values in a somewhat more convenient measure unit system, more usefult during debugging, even if the software during it's internal calculations goes to SI. I am a maniac about these things. If my father understood about these topics I am sure he would tell me too.
FPC 3.2.0/Lazarus 2.0.10+dfsg-4+b2 on Debian 11.5
FPC 3.2.2/Lazarus 2.2.0 on Windows 10 Pro 21H2

Zvoni

  • Hero Member
  • *****
  • Posts: 2319
Re: Again on SQLite and date times.
« Reply #24 on: November 16, 2022, 01:25:46 pm »
Just out of curiosity: How do you handle the different SQL-"Dialects"? (Yeah, nothing to do with the Problem of this Thread)

So far I didn't encounter situations in which I need it, mostly my programs do quite standard sql queryies and if there is some logic involved I prefer to keep it in code, it makes me do more work in some situations, but reduces need to dive into db specific operations (like stored procedures). 

In case it happens it would be something like

Code: Text  [Select][+][-]
  1. if (connection-database-type = SQLite) then
  2.    query.SQL := 'SQL dialect text';
  3.    SQLLite specific processing
  4. end;
  5. if (connection-database-type = MSSQL) then
  6.    query.SQL := 'MSSQL dialect text';
  7.    MSSQL specific processing
  8. end
  9.  

more or less what you suggested.
Ah.... what i thought.
I've left that approach, and worked out a "generic" way to address that specific problem.
So no need anymore for DBMS-specific handling
If you're intested, send me a PM
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

Thaddy

  • Hero Member
  • *****
  • Posts: 14214
  • Probably until I exterminate Putin.
Re: Again on SQLite and date times.
« Reply #25 on: November 16, 2022, 01:27:04 pm »
@Zvoni
That is what I mean, but how a programmer handles it is often plain wrong. Of course a decent Database adheres to what I wrote. Programmers often make the wrong assumptions about date and time handling. That is all. Easy for you and me, but not for everyone.
Specialize a type, not a var.

Zvoni

  • Hero Member
  • *****
  • Posts: 2319
Re: Again on SQLite and date times.
« Reply #26 on: November 16, 2022, 02:27:45 pm »
@Zvoni
That is what I mean, but how a programmer handles it is often plain wrong. Of course a decent Database adheres to what I wrote. Programmers often make the wrong assumptions about date and time handling. That is all. Easy for you and me, but not for everyone.
Ah... OK
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

LacaK

  • Hero Member
  • *****
  • Posts: 691
Re: Again on SQLite and date times.
« Reply #27 on: November 17, 2022, 09:05:24 am »
At this point I noticed that liquibase was mapping DATETIME datatype to REAL and all this story started.
It is no surprise. SQLite3 in fact stores datetime as REAL (or STRING) as stated in documentation.
So another library may recognize this data (which have real affinity) as real numbers - these real nubers represents Julian days ...
But when you use SQLite3 built-in date and time functions like datetime() on this "real" column you will see valid datetime values ...

Zvoni

  • Hero Member
  • *****
  • Posts: 2319
Re: Again on SQLite and date times.
« Reply #28 on: November 17, 2022, 09:14:51 am »
At this point I noticed that liquibase was mapping DATETIME datatype to REAL and all this story started.
It is no surprise. SQLite3 in fact stores datetime as REAL (or STRING) as stated in documentation.
So another library may recognize this data (which have real affinity) as real numbers - these real nubers represents Julian days ...
But when you use SQLite3 built-in date and time functions like datetime() on this "real" column you will see valid datetime values ...
We know that.
OP's problem was how TSQLQuery (and TField) determine (and set) the Datatype from the Column-MetaData, because if the Metadata of the Column is Real, he cannot use Qry.FieldbyName('SomeDate').AsDateTime
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

Чебурашка

  • Hero Member
  • *****
  • Posts: 568
  • СЛАВА УКРАЇНІ! / Slava Ukraïni!
Re: Again on SQLite and date times.
« Reply #29 on: November 17, 2022, 09:56:08 am »
Hi guys,
today I made a small test with SQLite related to datatype freedom. Please see what I got.

Code: Bash  [Select][+][-]
  1. user@debian:~/tmp/sqlite-test$ sqlite-cli notypes.db
  2. SQLite version 3.34.1 2021-01-20 14:10:07
  3. Enter ".help" for usage hints.
  4. sqlite> create table NoTypesTable
  5. (
  6.   field_pk integer not null,
  7.   field_int,
  8.   field_string,
  9.   field_datetime,
  10.   field_float
  11. );
  12. sqlite> insert into NoTypesTable (field_pk, field_int, field_string, field_datetime, field_float) values (1, 1, 'hello 1', '2022-11-16 14:51:36.541', 4.32);
  13. sqlite> insert into NoTypesTable (field_pk, field_int, field_string, field_datetime, field_float) values (2, 2, 'hello 2', '2022-11-16 14:52:36.541', 4.36);
  14. sqlite> insert into NoTypesTable (field_pk, field_int, field_string, field_datetime, field_float) values (3, '3', 'hello 3', 'hello 3', '4.36');
  15. sqlite> insert into NoTypesTable (field_pk, field_int, field_string, field_datetime, field_float) values (4, 'hello 4', 'hello 4', 'hello 4', 'hello 4');
  16. sqlite> select * from NoTypesTable;
  17. 1|1|hello 1|2022-11-16 14:51:36.541|4.32
  18. 2|2|hello 2|2022-11-16 14:52:36.541|4.36
  19. 3|3|hello 3|hello 3|4.36
  20. 4|hello 4|hello 4|hello 4|hello 4
  21.  

Sorry if I say a stupid thing, I am old school, but I wonder if we can still call SQLite a database. It seems to me rather a byte array logger.

Apart my uninteresting thoughts, with this example we see that column datatype definition is optional, and even different rows of a table can contain unrelated datatypes.

Is the concept of datatype itself to be senseless here.
FPC 3.2.0/Lazarus 2.0.10+dfsg-4+b2 on Debian 11.5
FPC 3.2.2/Lazarus 2.2.0 on Windows 10 Pro 21H2

 

TinyPortal © 2005-2018