When using DataModeler, if I create a field with type Datetime, it creates the field in SQLite as REAL and not DATETIME as I would have expected. This all works fine in delphi, but external to delphi, the dates are represented as reals instead of dates.
There must be something different I'm doing to you. My table was created by using the generated file from the data modeler when my delphi program 1st runs. Here are some screen shots of what I have
and from the preview of the Export option
The delphi generated code is correct. but the final SQLite field gets a type of REAL.
If it helps, here is the data modeler file I'm using TMSSample.zip (7.0 KB)
This image is not from Data Modeler. What is the script generated by the Data Modeler.
That screenshot is from SQLite displaying the schema of the table created with the DateTime type.
Where do I see the script from datamodeler ? I have never seen one created. I use the pascal file created from the export option then create an instance of the Manager and access the table, then add a record with the date. The table is created by Aurelius from the pascal file generated by the DataModeler.
If you got a script created by the DataModeler, then perhaps try using the pascal file generated by DataModeler. Maybe the problem is there.
I have included all source of an example that when run creates an SQLite table with a Date, Time, and DateTime field in it. All 3 get created as REAL.
Sample.zip (13.3 KB)
Well, you mentioned Data Modeler so I expected you had created the database using Data Modeler SQL script. Which is correct:
Everything is correct, actually. SQLite doesn't have strong data types. DATETIME type doesn't exist, it's just an alias for type affinity. More info here: https://www.sqlite.org/datatype3.html
Hi Wagner,
I did some more hunting and this is what I found.
Executing the SQL you listed (which is correct from the DataModeler) does generate the correct SQLite schema with the correct DATE, TIME, and DATETIME types. Adding an entry to the table and doing a SELECT * FROM SampleData; displays the dates and times as you would expect in human readable form.
However, stepping through the Aurelius code, in Aurelius.Engine.DatabaseManager.pas in the following procedure
the SQL it's actually executing is
which is not the same as the SQL from the script.
Hmmm... the end result is still correct. Even with the type specified as REAL, performing a SELECT * FROM DateTest; displays the data in human readable form and not as a REAL.
Interesting. Using SQLite to add a date works as expected and displays the date in human readable form despite it being declared as REAL, however, adding a date using Aurelius in the following code
ends up inserting the dates as REAL. Displaying the result in SQLite now displays the dates as REAL and not in human readable form.
If I then perform this
insert into DateTest(adate,atime,adatetime) values(date(),time(),datetime());
from within SQLite, the perform a SELECT ALL, I get 1 record with REAL's for the dates and another record with human readable data.
If I then perform
select * from DateTest where aDate > '01/01/1990';
from SQLite, I only get the human readable record and NOT the one inserted by Aurelius which has the REAL's. Both records were inserted with todays date and so both should have been displayed.
It appears that as Delphi stores date and times in a double, that Aurelius chose to save the data in a double rather than the appropriate SQLite date/time format.
Manually (through the debugger) I changed Aurelius to create the table with the correct DATE, TIME, and DATETIME types and it did indeed create the data types with the correct type name, but Aurelius still saved the data as REALs.
I'm guessing the problem may be in Aurelius.Drivers.SQLite.pas in the method function TSQLiteNativeStatementAdapter.PrepareStatement: TSQLiteStatement; thich doesnt have a type for any of the date types Delphi has and uses ftFloat, which is what Delphi uses for date/time types but it seems SQLite is instructed to save this as a REAL.
When the dates are stored by Aurelius as a REAL, then you cannot use SQLite or any other tools that use SQLite to execute statements like
select * from DateTest where aDate > '01/01/1990';
as all the REALS are filtered out. I suspect it would work in Aurelius to perform this type of select, but if I was to try that in SQLite or in a 3rd party tool (like SQLiteStudio which I use) then to perform the above select I'd have to replace the date with whatever number is for the date 1/1/1990. I can see from my table that todays date 17/02/2021 is the number 44244.0
I'd see this as am impediment to anyone trying to use the database outside of Aurelius.
SQLite doesn't have strict date types. You can save whatever you want in any field. If you save a date in REAL (double) format, it will save it. If you save a date in string format, it will save it. In the same table.
By default, Aurelius saves date values in SQLite as native Delphi TDateTime values, i.e., as double (float) values. This works fine if you are only using Aurelius to access the SQLite database. But if you have a legacy SQLite database or want other applications to read the database directly, you might need to use a different format. Aurelius offers two other alternative formats, which is Julian (saves the date values as Julian date times) or Text (which saves in text format “yyyy-mm-dd”).
To do that, add this code to the beginning of your application (choose the date time you want and uncomment the correct line).
Uses
Aurelius.Sql.SQLite, Aurelius.Sql.Register;
Var
SQLiteGenerator: TSQLiteSQLGenerator;
begin
SQLiteGenerator := TSQLiteSQLGenerator.Create;
// SQLiteGenerator.DateType := TSQLiteSQLGenerator.TDateType.Delphi;
// SQLiteGenerator.DateType := TSQLiteSQLGenerator.TDateType.Julian;
SQLiteGenerator.DateType := TSQLiteSQLGenerator.TDateType.Text;
TSQLGeneratorRegister.GetInstance.RegisterGenerator(SQLiteGenerator);
Perfect !
That did the trick Wagner.
Of the 3 options there, the text one was the only one that seems to work with the selects so I'll use that one.
Thanks very much for your assistance !
1 Like
This topic was automatically closed 60 minutes after the last reply. New replies are no longer allowed.