Recent

Author Topic: Max() function on datetime column in SQLite  (Read 1696 times)

Чебурашка

  • Hero Member
  • *****
  • Posts: 568
  • СЛАВА УКРАЇНІ! / Slava Ukraïni!
Max() function on datetime column in SQLite
« on: April 22, 2022, 01:34:11 pm »
Hello,
In SQLIte I have a table with a datetime field:

Code: SQL  [Select][+][-]
  1. CREATE TABLE ExampleTable
  2. (
  3.     id INTEGER,
  4.  
  5.     datetimeField datetime
  6. );
  7.  
  8. INSERT INTO ExampleTable (id, datetimeField) VALUES (1, '2022-04-22 10:30:34');
  9. INSERT INTO ExampleTable (id, datetimeField) VALUES (2, '2022-04-22 10:30:36');
  10. INSERT INTO ExampleTable (id, datetimeField) VALUES (3, '2022-04-22 10:30:39');
  11.  

I have the following simple query but when I execute the query I get a weird error:

Code: Pascal  [Select][+][-]
  1. var
  2.   Q: TSQLQuery;
  3.   maxDatetimeField: TDateTime
  4. begin
  5.   maxDatetimeField := 0;
  6.  
  7.   // [connect db omitted]  
  8.   // [open transaction omitted]  
  9.   // [creating Q omitted]  
  10.  
  11.   Q.SQL.Add('select count(id) count_id, max(datetimeField) max_datetimeField from ExampleTable');
  12.  
  13.   Q.Open();
  14.   if (Q.FieldByName('count_id').AsInteger > 0) then
  15.   begin
  16.     if (not Q.FieldByName('max_datetimeField').IsNull) then
  17.     begin
  18.        // this line throws: "EDatabaseError: error converting field max_datetimeField to TDateTime"
  19.        maxDatetimeField := q.FieldByName('max_datetimeField').AsDateTime;
  20.     end;
  21.   end;
  22.   Q.Close();
  23. end;
  24.  

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?



« Last Edit: April 22, 2022, 01:52:54 pm 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

Sieben

  • Sr. Member
  • ****
  • Posts: 310
Re: Max() function on datetime column in SQLite
« Reply #1 on: April 22, 2022, 02:02:32 pm »
You could try

Code: Pascal  [Select][+][-]
  1. 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.
Lazarus 2.2.0, FPC 3.2.2, .deb install on Ubuntu Xenial 32 / Gtk2 / Unity7

Zvoni

  • Hero Member
  • *****
  • Posts: 2319
Re: Max() function on datetime column in SQLite
« Reply #2 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
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

Zvoni

  • Hero Member
  • *****
  • Posts: 2319
Re: Max() function on datetime column in SQLite
« Reply #3 on: April 22, 2022, 02:07:25 pm »
You could try

Code: Pascal  [Select][+][-]
  1. 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][+][-]
  1. SELECT ........ DateTime(Max(datetimefield)) max-datetimefield.....
  2.  
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
« Last Edit: April 22, 2022, 02:13:39 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: Max() function on datetime column in SQLite
« Reply #4 on: April 22, 2022, 02:09:43 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

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.

« Last Edit: April 22, 2022, 02:37:50 pm 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

Чебурашка

  • Hero Member
  • *****
  • Posts: 568
  • СЛАВА УКРАЇНІ! / Slava Ukraïni!
Re: Max() function on datetime column in SQLite
« Reply #5 on: April 22, 2022, 03:04:07 pm »
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][+][-]
  1. sqlite> select strftime('%Y-%m-%d %H:%M:%f', datetimeField) from ExampleTable;
  2. 2022-04-22 10:30:34.000
  3. 2022-04-22 10:30:36.000
  4. 2022-04-22 10:30:39.000
  5. -340-07-08 16:48:00.000
  6. -312-04-22 16:48:00.000
  7.  
  8. -340-07-08 16:48:00.000
  9. -258-11-22 16:48:00.000
  10. 0157-10-18 16:48:00.000
  11. 2895-09-14 16:48:00.000
  12.  

Code: Bash  [Select][+][-]
  1. sqlite> select * from ExampleTable;
  2. 1|2022-04-22 10:30:34
  3. 2|2022-04-22 10:30:36
  4. 3|2022-04-22 10:30:39
  5. 3|478694.2
  6. 3|578694.2
  7. 3|5758694.2
  8. 3|478694.2
  9. 3|778694.2
  10. 3|1778694.2
  11. 3|2778694.2
  12.  

The result of the max operation in the cli is:

Code: Bash  [Select][+][-]
  1. sqlite> select max(datetimeField) from ExampleTable;
  2. 2022-04-22 10:30:39
  3.  

Code: Bash  [Select][+][-]
  1. sqlite> select  max(julianday(datetimeField)) from ExampleTable;
  2. 2778694.2
  3.  

Code: Bash  [Select][+][-]
  1. sqlite> select strftime('%Y-%m-%d %H:%M:%f', max(julianday(datetimeField))) from ExampleTable;
  2. 2895-09-14 16:48:00.000
  3.  

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.
« Last Edit: April 22, 2022, 03:07:43 pm 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

SymbolicFrank

  • Hero Member
  • *****
  • Posts: 1313
Re: Max() function on datetime column in SQLite
« Reply #6 on: April 22, 2022, 03:23:55 pm »
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.

Чебурашка

  • Hero Member
  • *****
  • Posts: 568
  • СЛАВА УКРАЇНІ! / Slava Ukraïni!
Re: Max() function on datetime column in SQLite
« Reply #7 on: April 22, 2022, 03:44:17 pm »
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.

INTEGER is not enough for me, I will use REAL.
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: Max() function on datetime column in SQLite
« Reply #8 on: April 22, 2022, 08:20:44 pm »
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.

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
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: Max() function on datetime column in SQLite
« Reply #9 on: April 27, 2022, 11:34:51 am »
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][+][-]
  1. sqlite> select strftime('%Y-%m-%d %H:%M:%f', datetimeField) from ExampleTable;
  2. 2022-04-22 10:30:34.000
  3. 2022-04-22 10:30:36.000
  4. 2022-04-22 10:30:39.000
  5. -340-07-08 16:48:00.000
  6. -312-04-22 16:48:00.000
  7.  
  8. -340-07-08 16:48:00.000
  9. -258-11-22 16:48:00.000
  10. 0157-10-18 16:48:00.000
  11. 2895-09-14 16:48:00.000
  12.  

Code: Bash  [Select][+][-]
  1. sqlite> select * from ExampleTable;
  2. 1|2022-04-22 10:30:34
  3. 2|2022-04-22 10:30:36
  4. 3|2022-04-22 10:30:39
  5. 3|478694.2
  6. 3|578694.2
  7. 3|5758694.2
  8. 3|478694.2
  9. 3|778694.2
  10. 3|1778694.2
  11. 3|2778694.2
  12.  

The result of the max operation in the cli is:

Code: Bash  [Select][+][-]
  1. sqlite> select max(datetimeField) from ExampleTable;
  2. 2022-04-22 10:30:39
  3.  

Code: Bash  [Select][+][-]
  1. sqlite> select  max(julianday(datetimeField)) from ExampleTable;
  2. 2778694.2
  3.  

Code: Bash  [Select][+][-]
  1. sqlite> select strftime('%Y-%m-%d %H:%M:%f', max(julianday(datetimeField))) from ExampleTable;
  2. 2895-09-14 16:48:00.000
  3.  

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.


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: Max() function on datetime column in SQLite
« Reply #10 on: April 27, 2022, 11:58:59 am »

*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
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

 

TinyPortal © 2005-2018