https://bugs.freedesktop.org/show_bug.cgi?id=50575
--- Comment #6 from Lionel Elie Mamane <lio...@mamane.lu> 2012-06-07 12:58:19 PDT --- (In reply to comment #5) > (In reply to comment #4) > > But sqlite 3 *does* support fractional seconds in at least some > > contexts. From a terminal session, first the command interpreter that > > comes with sqlite3 ... > > > > $ sqlite3 ../bug_047520/bug_047520.db > > SQLite version 3.7.4 > > Enter ".help" for instructions > > Enter SQL statements terminated with a ";" > > sqlite> .mode columns > > sqlite> select * from byTs ; > > 1 2012-04-06 12:34:56.654321 Friday > > 2 2012-04-05 13:45:57.123456 Thursday > > sqlite> > > 1) When putting a timestamp through a parameter of a parametric query (as > LibreOffice does), sqliteodbc will use exactly three positions after the > decimal dot, so will issue something like "SELECT * from byTs WHERE > ts='2012-04-05 13:45:57.123'". So it won't find your data because of > truncation > issues. That's a bug in sqliteodbc. Even if this bug were corrected in > sqliteodbc, it still wouldn't work, because LibreOffice truncates data to > *two* > positions after decimal dot. That's an imperfection in LibreOffice. > > 2) Now, try this in sqlite3 prompt: > > SELECT * FROM byTs WHERE ts='2012-04-05 13:45:57.1234560' > > ---> no row found, while it should find the second row. > > INSERT INTO byTs VALUES (3, '2012-04-04 15:20:22.980', 'Wednesday'); > SELECT * FROM ByTs WHERE ts='2012-04-04 15:20:22.98'; > > ---> no row found, while it should find the just inserted row, as 22.980 == > 22.98 when understood as numbers!!! > > Similarly > > INSERT INTO byTs VALUES (3, '2012-04-04 15:20:22.98', 'Wednesday'); > SELECT * FROM ByTs WHERE ts='2012-04-04 15:20:22.980'; > > That's a bug in sqlite3. Compare with: SELECT * FROM ByTs WHERE nr=2.0; Which correctly finds the second row. Also consider: INSERT INTO byTs VALUES (4, '2012-04-04 15:20:22.98', 'Wednesday'); This should fail, since there is already a row with ts value "4 april 2012, three pm, twenty minutes, twenty-two seconds and 98 hundredths of a second" which is the same as "4 april 2012, three pm, twenty minutes, twenty-two seconds and 980 thousandths of a second" . > > (*) It is possible to retrieve the data successfully by disguising the > > key column: > > > > select nr, cast( "ts" as char(26) ), word from byTs Yes, because then you tell LibreOffice and sqliteodbc not to treat data from this column as a timestamp, but as a string, whithout normalising it. For *strings*, '2012-04-04 15:20:22.98' is not the same string as '2012-04-04 15:20:22.980', but for *timestamps* it is the *same* timestamp. sqlite3 seems to treat them more as strings than as timestamps. Ah yes, look: sqlite> INSERT INTO byTs VALUES (5, 'not a timestamp', 'Wednesday'); sqlite> SELECT * FROM ByTs WHERE nr=5; nr ts word ---- ------------- ---- 5 not a timestamp Wednesday The insert should have failed, because the value given to ts column is not a valid timestamp! So as far as I see sqlite DOES NOT CORRECTLY SUPPORT TIMESTAMPS, and sqliteodbc/sqlite ARE LYING to LibreOffice when saying this column is a timestamp. They should just declare it as (unbounded) string, because that's how sqlite treats it... -- Configure bugmail: https://bugs.freedesktop.org/userprefs.cgi?tab=email ------- You are receiving this mail because: ------- You are the assignee for the bug. _______________________________________________ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
