Forum > Database
Max() function on datetime column in SQLite
Чебурашка:
Hello,
In SQLIte I have a table with a datetime field:
--- 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";}};} ---CREATE TABLE ExampleTable( id INTEGER, datetimeField datetime); INSERT INTO ExampleTable (id, datetimeField) VALUES (1, '2022-04-22 10:30:34');INSERT INTO ExampleTable (id, datetimeField) VALUES (2, '2022-04-22 10:30:36');INSERT INTO ExampleTable (id, datetimeField) VALUES (3, '2022-04-22 10:30:39');
I have the following simple query but when I execute the query I get a weird error:
--- 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";}};} ---var Q: TSQLQuery; maxDatetimeField: TDateTimebegin maxDatetimeField := 0; // [connect db omitted] // [open transaction omitted] // [creating Q omitted] Q.SQL.Add('select count(id) count_id, max(datetimeField) max_datetimeField from ExampleTable'); Q.Open(); if (Q.FieldByName('count_id').AsInteger > 0) then begin if (not Q.FieldByName('max_datetimeField').IsNull) then begin // this line throws: "EDatabaseError: error converting field max_datetimeField to TDateTime" maxDatetimeField := q.FieldByName('max_datetimeField').AsDateTime; end; end; Q.Close();end;
My guess is that when doing a aggregation function the database access library looses the information about the field datatype.
Did anybody have similar experiences?
Sieben:
You could try
--- 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";}};} ---Q.SQL.Add('select count(id) count_id, date(max(datetimeField)) max_datetimeField from ExampleTable');
Or you can use the resulting string value (because that's what it is) in max_datetimeField to manually convert it to a TDateTime value with proper format settings.
Zvoni:
1) There is no DataType "datetime" in SQLite. SQLite probably assigned NUMERIC Affinity to that Column
2) You stuff a String into that Column with your INSERTS (And yes, SQLite allows to store Strings in numeric columns), so your "AsDateTime" probably chokes on that
Zvoni:
--- Quote from: Sieben on April 22, 2022, 02:02:32 pm ---You could try
--- 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";}};} ---Q.SQL.Add('select count(id) count_id, date(max(datetimeField)) max_datetimeField from ExampleTable');
Or you can use the resulting string value (because that's what it is) in max_datetimeField to manually convert it to a TDateTime value with proper format settings.
--- End quote ---
rather:
--- 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";}};} ---SELECT ........ DateTime(Max(datetimefield)) max-datetimefield..... Date/DateTime-SQLFunction of SQLite returns UTC without modifier (now there is a hideyhole for a serious bug)
EDIT: Just had a thought: using Max before or after converting to DateTime? Shouldn't make a difference
Чебурашка:
--- Quote from: Zvoni on April 22, 2022, 02:05:38 pm ---1) There is no DataType "datetime" in SQLite. SQLite probably assigned NUMERIC Affinity to that Column
2) You stuff a String into that Column with your INSERTS (And yes, SQLite allows to store Strings in numeric columns), so your "AsDateTime" probably chokes on that
--- End quote ---
In the example I populated the db using TEXT, but in my real program I do this:
write in db:
Q.ParamByName('P').AsDateTime := <somedatetimevariable>;
read from db:
<somedatetimevariable> := Q.FieldByName('F').AsDateTime;
Moreover if I do a direct query in the DB using cli program sqlite3 I see the datetime values as REAL. This means that the values are stored as REAL, but unexpectedly the max operation does something so that the library cannot interpret the resulting REAL correctly.
Navigation
[0] Message Index
[#] Next page