Max() function on datetime column in SQLite
lazarus / 63-Database

----------------------------------------------------------------------
Чебурашка · 2022-04-22 11:34

Hello,
In SQLIte I have a table with a datetime field:

Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i 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  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i 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 · 2022-04-22 12:02

You could try

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
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 · 2022-04-22 12:05

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 · 2022-04-22 12:07

Quote from: Sieben on April 22, 2022, 02:02:32 pmYou could try

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
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.
rather:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i 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

----------------------------------------------------------------------
Чебурашка · 2022-04-22 12:09

Quote from: Zvoni on April 22, 2022, 02:05:38 pm1) 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

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.

----------------------------------------------------------------------
Чебурашка · 2022-04-22 13:04

I made some tests with the cli. I think the problem is also somehow at
SQLite level.

The table is as the one described before. I inserted some records
using REAL numbers.

Code: Bash  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i sqlite>
select strftime('%Y-%m-%d %H:%M:%f', datetimeField) from
ExampleTable;2022-04-22 10:30:34.0002022-04-22 10:30:36.0002022-04-22
10:30:39.000-340-07-08 16:48:00.000-312-04-22 16:48:00.000 -340-07-08
16:48:00.000-258-11-22 16:48:00.0000157-10-18 16:48:00.0002895-09-14
16:48:00.000 
Code: Bash  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i sqlite>
select * from ExampleTable;1|2022-04-22 10:30:342|2022-04-22
10:30:363|2022-04-22 10:30:393|478694.23|578694.23|5758694.23|478694.2
3|778694.23|1778694.23|2778694.2 
The result of the max operation in the cli is:

Code: Bash  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i sqlite>
select max(datetimeField) from ExampleTable;2022-04-22 10:30:39 
Code: Bash  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i sqlite>
select  max(julianday(datetimeField)) from ExampleTable;2778694.2 
Code: Bash  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i sqlite>
select strftime('%Y-%m-%d %H:%M:%f', max(julianday(datetimeField)))
from ExampleTable;2895-09-14 16:48:00.000 
So the problem is (also) in SQLite, I guess it's due to the fact that
SQLite does not have an explicit datatype.

I will do as suggested by ZVoni, even if this will make the program
not database independent (postgres does not have a julianday()
function. I see no other solution now.

----------------------------------------------------------------------
SymbolicFrank · 2022-04-22 13:23

Even with STRICT tables, the SQLite date-time functions are
unreliable, mostly because there isn't a real DateTime field type (as
Zvoni said). Store the date-time values either as ISO8601 strings
(TEXT) or unix time (INTEGER) and process them yourself in that type.
If you want to do that in queries, unix time is easiest and things
like Max() work. For exactness and readability ISO8601 is better.

----------------------------------------------------------------------
Чебурашка · 2022-04-22 13:44

Quote from: SymbolicFrank on April 22, 2022, 03:23:55 pmEven with
STRICT tables, the SQLite date-time functions are unreliable, mostly
because there isn't a real DateTime field type (as Zvoni said). Store
the date-time values either as ISO8601 strings (TEXT) or unix time
(INTEGER) and process them yourself in that type. If you want to do
that in queries, unix time is easiest and things like Max() work. For
exactness and readability ISO8601 is better.

INTEGER is not enough for me, I will use REAL.

----------------------------------------------------------------------
Zvoni · 2022-04-22 18:20

Quote from: tt on April 22, 2022, 03:44:17 pmQuote from: SymbolicFrank
on April 22, 2022, 03:23:55 pmEven with STRICT tables, the SQLite
date-time functions are unreliable, mostly because there isn't a real
DateTime field type (as Zvoni said). Store the date-time values either
as ISO8601 strings (TEXT) or unix time (INTEGER) and process them
yourself in that type. If you want to do that in queries, unix time is
easiest and things like Max() work. For exactness and readability
ISO8601 is better.

INTEGER is not enough for me, I will use REAL.
You misunderstood: INTEGER in that case as Unixepoch is in seconds
since Jan 1st 1970
You would still be able to convert to a full date with time down to
seconds

----------------------------------------------------------------------
Чебурашка · 2022-04-27 09:34

Quote from: tt on April 22, 2022, 03:04:07 pmI made some tests with
the cli. I think the problem is also somehow at SQLite level.

The table is as the one described before. I inserted some records
using REAL numbers.

Code: Bash  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i sqlite>
select strftime('%Y-%m-%d %H:%M:%f', datetimeField) from
ExampleTable;2022-04-22 10:30:34.0002022-04-22 10:30:36.0002022-04-22
10:30:39.000-340-07-08 16:48:00.000-312-04-22 16:48:00.000 -340-07-08
16:48:00.000-258-11-22 16:48:00.0000157-10-18 16:48:00.0002895-09-14
16:48:00.000 
Code: Bash  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i sqlite>
select * from ExampleTable;1|2022-04-22 10:30:342|2022-04-22
10:30:363|2022-04-22 10:30:393|478694.23|578694.23|5758694.23|478694.2
3|778694.23|1778694.23|2778694.2 
The result of the max operation in the cli is:

Code: Bash  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i sqlite>
select max(datetimeField) from ExampleTable;2022-04-22 10:30:39 
Code: Bash  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i sqlite>
select  max(julianday(datetimeField)) from ExampleTable;2778694.2 
Code: Bash  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i sqlite>
select strftime('%Y-%m-%d %H:%M:%f', max(julianday(datetimeField)))
from ExampleTable;2895-09-14 16:48:00.000 
So the problem is (also) in SQLite, I guess it's due to the fact that
SQLite does not have an explicit datatype.

I will do as suggested by ZVoni, even if this will make the program
not database independent (postgres does not have a julianday()
function. I see no other solution now.

I posed the question to the SQLite forum.

https://www.sqlite.org/forum/forumpost/e856cc26d5

Hope this might be useful to somebody.

----------------------------------------------------------------------
Zvoni · 2022-04-27 09:58

Quote from: tt on April 22, 2022, 03:04:07 pm
*snip* even if this will make the program not database independent
(postgres does not have a julianday() function. I see no other
solution now.
What's that got to do with Database-independence?
You would only need such a function in PostGres (or any other DBMS),
if you MIX the Format of your entries.
And PostGres definitely won't allow you to stuff a String into a Real-
Column.
Setup the DataType correctly in SQLite (as described in the
documentation), and do any conversion from your Frontend-Code.
Important: Stay consistent. Don't mix ISO-Format with Unixepoch and
Julian-Format