Forum > Database
Max() function on datetime column in SQLite
Чебурашка:
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 [+][-]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";}};} ---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 [+][-]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";}};} ---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.23|778694.23|1778694.23|2778694.2
The result of the max operation in the cli is:
--- Code: Bash [+][-]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";}};} ---sqlite> select max(datetimeField) from ExampleTable;2022-04-22 10:30:39
--- Code: Bash [+][-]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";}};} ---sqlite> select max(julianday(datetimeField)) from ExampleTable;2778694.2
--- Code: Bash [+][-]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";}};} ---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:
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.
Чебурашка:
--- Quote from: SymbolicFrank 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.
--- End quote ---
INTEGER is not enough for me, I will use REAL.
Zvoni:
--- Quote from: tt on April 22, 2022, 03:44:17 pm ---
--- Quote from: SymbolicFrank 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.
--- End quote ---
INTEGER is not enough for me, I will use REAL.
--- End quote ---
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
Чебурашка:
--- Quote from: tt 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 [+][-]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";}};} ---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 [+][-]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";}};} ---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.23|778694.23|1778694.23|2778694.2
The result of the max operation in the cli is:
--- Code: Bash [+][-]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";}};} ---sqlite> select max(datetimeField) from ExampleTable;2022-04-22 10:30:39
--- Code: Bash [+][-]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";}};} ---sqlite> select max(julianday(datetimeField)) from ExampleTable;2778694.2
--- Code: Bash [+][-]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";}};} ---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.
--- End quote ---
I posed the question to the SQLite forum.
https://www.sqlite.org/forum/forumpost/e856cc26d5
Hope this might be useful to somebody.
Navigation
[0] Message Index
[#] Next page
[*] Previous page