Max() function on datetime column in SQLite

[Чебурашка]

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?

[Sieben]

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.

[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]

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

[Чебурашка]

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.

[Чебурашка]

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.

[SymbolicFrank]

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.

[Чебурашка]

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.

[Zvoni]

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

[Чебурашка]

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.

[Zvoni]

*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