Max() over datetime field

(1) By anonymous on 2022-04-26 06:37:32 [link] [source]

Hello,
I have a table like this on "SQLite version 3.27.2 2019-02-25 16:06:06":

CREATE TABLE ExampleTable ( id integer, datetimeField datetime );

Populated with these records:

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:39');
INSERT INTO ExampleTable (id, datetimeField) VALUES (3, 478694.2 );
INSERT INTO ExampleTable (id, datetimeField) VALUES (4, 578694.2 );
INSERT INTO ExampleTable (id, datetimeField) VALUES (5, 5758694.2 );
INSERT INTO ExampleTable (id, datetimeField) VALUES (6, 478694.2 );
INSERT INTO ExampleTable (id, datetimeField) VALUES (7, 778694.2 );
INSERT INTO ExampleTable (id, datetimeField) VALUES (8, 1778694.2 );
INSERT INTO ExampleTable (id, datetimeField) VALUES (9, 2778694.2 );

If I do the query: sqlite> select max(datetimeField) from ExampleTable; 2022-04-22 10:30:39

But if I do the query: sqlite> select max(julianday(datetimeField)) from ExampleTable; 2778694.2

And if I write in a human format: sqlite> select strftime('%Y-%m-%d %H:%M:%f', max(julianday(datetimeField))) from ExampleTable; 2895-09-14 16:48:00.000

Is this meaningful?

Thanks
Tomaso Tosolini

(2) By Keith Medcalf (kmedcalf) on 2022-04-26 17:00:33 in reply to 1 [link] [source]

Is this meaningful?

It is as meaningful as the question. Did you ask a question for which the result is meaningless?

Notwithstanding, the result is correct.

(3) By David Raymond (dvdraymond) on 2022-04-26 17:24:56 in reply to 1 [link] [source]

Remember, max(datetimeField) is not taking the values and turning them into a standardized datetime format, it's just taking the "maximum" raw value. Since real numbers are "less than" strings Data Type Sort Order then you're gonna get one of the strings.

The julianday function is smart enough to see if its argument is a string, a float or an integer and converts them all to the same format. And in your second query you're correctly doing that before max does its comparison, so everything will be of a similar format when it gets to the max function. Date And Time Functions

(4) By David Raymond (dvdraymond) on 2022-04-26 17:54:05 in reply to 3 [link] [source]

You should, of course, really not be mixing ISO-8601, julian day, and unix epoch times in the same field. Convert them all to the same standard before storing, and then things like max and min will work fine on their own.

(6) By Keith Medcalf (kmedcalf) on 2022-04-26 18:09:17 in reply to 4 [link] [source]

The values also all have to be in the same localization zone -- that is, use the same Arbitrary Meridian -- and that same Arbitrary Meridian must have consistent spelling.

(5) By Tim Streater (Clothears) on 2022-04-26 18:05:05 in reply to 1 [link] [source]

Defining datetimeField as a datetime is certainly not particularly meaningful.

(7) By tomasotosolini on 2022-04-27 07:08:29 in reply to 5 [link] [source]

I thought is was meaningful by reading the following pages.

https://www.sqlite.org/datatype3.html

3.1. Determination Of Column Affinity

3.1.1. Affinity Name Examples

(8.1) By tomasotosolini on 2022-04-27 08:32:55 edited from 8.0 in reply to 1 [source]

Thanks for the answers.

I am working on a free pascal project that uses also SQLite as db.

I was inserting the values using the .AsDateTime and the values visible in database were REAL numbers (with sqlite3 client). Therefore the Max() operation was for sure performing right on the database.

I was then doing a Max() over such a "datetime" column.

Once trying to read "Max()'ed" column using the .AsDateTime, I got an error "Impossible to cast the value to TDateTime".

The reason I guess is that since there is no "traditional" datatype bound with the column, therefore there cannot be any datatype after the Max operation. And therefore the free pascal driver fails to interpret the Max'ed column as a datetime.

This of course is a "limitation" on the free pascal driver that is not able to figure out the datatype resulting from the dyntyipng of SQLite, not a SQLite problem.

Tomaso Tosolini