Forum > Databases
Again on SQLite and date times.
Чебурашка:
Hello,
I have a table like this in SQlite
--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---CREATE TABLE ExampleT( id integer not null, fieldDate datetime);
If I do a TSQLQuery and I try to read the field fieldDate .AsDateTime it succeds.
Instead if I define the table like this:
--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---CREATE TABLE ExampleT( id integer not null, fieldDate NUMERIC);
(should be equivalent to the previous as DATETIME is treated as NUMERIC in SQlite - https://www.sqlite.org/datatype3.html, also I can see that the data stored in doth cases is the same, using the sqlite3 cli program)
If I do a TSQLQuery and I try to read the field fieldDate .AsDateTime it fails telling: Invalid type conversion to TDateTime in field fieldDate.
I guess that the free pascal code determines the field type using the table field definition.
Is it possible to force a TField to be what one wants without relying only on the table defintion?
Zvoni:
And as i told you in your other thread(s): Don't use those DataTypes!
There is no DateTime DataType, and NUMERIC is not a DataType but an Affinity.
Use the proper Storage-Classes.
And TDateTime wants a double.
"Spot the mistake"
Чебурашка:
--- Quote from: Zvoni on November 14, 2022, 02:56:13 pm ---And as i told you in your other thread(s): Don't use those DataTypes!
There is no DateTime DataType, and NUMERIC is not a DataType but an Affinity.
Use the proper Storage-Classes.
And TDateTime wants a double.
"Spot the mistake"
--- End quote ---
After your reply, I recreated the DB with DATETIME mapped to DOUBLE, in order to follow strictly your indications (unless I did not undestrand them fully).
--- Code: Bash [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---SQLite version 3.34.1 2021-01-20 14:10:07Enter ".help" for usage hints.sqlite> .schema 'T0041%'CREATE TABLE T0041_MachineLog (refMachine INTEGER NOT NULL, id UNSIGNED BIG INT NOT NULL, deleted INTEGER NOT NULL, refImport INTEGER NOT NULL, eventDate DOUBLE NOT NULL, eventType INTEGER NOT NULL, eventVersion INTEGER NOT NULL, eventDataType INTEGER, eventData BLOB, CONSTRAINT T0041_MachineLog_PK PRIMARY KEY (refMachine, id), CONSTRAINT T0041_FK_T0003 FOREIGN KEY (refMachine) REFERENCES T0003_ConfMachines(id));CREATE INDEX T0041_MachineLog_FKI ON T0041_MachineLog(refMachine);
The field in question now is:
--- Code: SQL [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---eventDate DOUBLE NOT NULL,
Hereafter it is a query made on that table, where you can see that it does contain data (=writing seems to work, probably writing doe not guess datatype from db structure)
--- Code: SQL [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---sqlite> SELECT * FROM T0041_MachineLog LIMIT 5;refMachine|id|deleted|refImport|eventDate|eventType|eventVersion|eventDataType|eventData1|1|0|1|2455615.89571759|1|1|0|1|2|0|1|2455615.89572917|11|1|0|1|3|0|1|2455615.89574074|11|1|0|1|4|0|1|2455615.89574074|11|1|0|1|5|0|1|2455615.89586806|2|1|0|
And here are the errors related to the reading of this field (and all others that should be read AsDateTime):
--- Code: Text [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---Localization system auto-analysis disabled. To set the compiler directive DEBUG_LOCALIZATION and execute the programProcessing machine "New machine1" with code "" ...Found 37 source prodlog filesProcessed 37 new files and 0 changed filedUnexpected error while LoadEvents on eventId 1, eventType 1/version 1 form machine 1 (exception: Invalid type conversion to TDateTime in field eventDate)Unexpected error while ProcessEvents (exception: Invalid type conversion to TDateTime in field eventDate)Unexpected error while recaching data of machine 1 (exception: Invalid type conversion to TDateTime in field eventDate)Unexpected error while compiling daily summaries of machine 1 (exception: Invalid type conversion to TDateTime in field eventDate)Processing machines completedUnexpected error while loading products in interval (exception: Invalid type conversion to TDateTime in field eventDate)Unexpected error while loading products in interval (exception: Invalid type conversion to TDateTime in field eventDate)
Any ideas at this point?
Zvoni:
--- Quote from: tt on November 14, 2022, 03:25:49 pm ---
After your reply, I recreated the DB with DATETIME mapped to DOUBLE, in order to follow strictly your indications (unless I did not undestrand them fully).
--- Code: Bash [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---SQLite version 3.34.1 2021-01-20 14:10:07Enter ".help" for usage hints.sqlite> .schema 'T0041%'CREATE TABLE T0041_MachineLog (refMachine INTEGER NOT NULL, id UNSIGNED BIG INT NOT NULL, deleted INTEGER NOT NULL, refImport INTEGER NOT NULL, eventDate DOUBLE NOT NULL, eventType INTEGER NOT NULL, eventVersion INTEGER NOT NULL, eventDataType INTEGER, eventData BLOB, CONSTRAINT T0041_MachineLog_PK PRIMARY KEY (refMachine, id), CONSTRAINT T0041_FK_T0003 FOREIGN KEY (refMachine) REFERENCES T0003_ConfMachines(id));CREATE INDEX T0041_MachineLog_FKI ON T0041_MachineLog(refMachine);
The field in question now is:
--- Code: SQL [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---eventDate DOUBLE NOT NULL,
--- End quote ---
Close. Use REAL instead of Double (but SQLite will map Double to Real in 99.9999% of cases)
--- Quote ---Hereafter it is a query made on that table, where you can see that it does contain data (=writing seems to work, probably writing doe not guess datatype from db structure)
--- Code: SQL [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---sqlite> SELECT * FROM T0041_MachineLog LIMIT 5;refMachine|id|deleted|refImport|eventDate|eventType|eventVersion|eventDataType|eventData1|1|0|1|2455615.89571759|1|1|0|1|2|0|1|2455615.89572917|11|1|0|1|3|0|1|2455615.89574074|11|1|0|1|4|0|1|2455615.89574074|11|1|0|1|5|0|1|2455615.89586806|2|1|0|
--- End quote ---
Remove the LIMIT 5
I want to see ALL those Dates
Чебурашка:
--- Quote from: Zvoni on November 14, 2022, 03:32:21 pm ---Remove the LIMIT 5
I want to see ALL those Dates
--- End quote ---
Attached
Navigation
[0] Message Index
[#] Next page