Bug 63566 - HSQLDB: Date will be changed when timezone of OS is changed
Summary: HSQLDB: Date will be changed when timezone of OS is changed
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
(earliest affected)
3.3.0 release
Hardware: Other All
: medium critical
Assignee: Not Assigned
: 91249 100623 (view as bug list)
Depends on:
Blocks: Database-Tables
  Show dependency treegraph
Reported: 2013-04-15 19:21 UTC by Robert Großkopf
Modified: 2018-04-07 13:30 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:

Testdatabase with dates in a date-field and dates in a varchar-field, formatted as dd.mm.yyyy (5.42 KB, application/vnd.oasis.opendocument.base)
2013-04-15 19:21 UTC, Robert Großkopf

Note You need to log in before you can comment on or make changes to this bug.
Description Robert Großkopf 2013-04-15 19:21:04 UTC
Created attachment 78013 [details]
Testdatabase with dates in a date-field and dates in a varchar-field, formatted as dd.mm.yyyy

1. Create a table with date-fields.
2. Put some date into this table.
3. Close LO
4. Change the timezone of your OS. For example from Europe/Berlin to Europe/Zürich. (with Linux/KDE in system-settings)
5. Open the database again. 
6. Have a look at the dates. 
7. Could be some dates have changed. (1 day lost or added)
8. Close LO.
9. Change timezone again to the first value, your default.
10. Open the database again and have a look at the table. The changed dates of No. 7 are also changed in the other timezone.

Could be this is a problem of the internal HSQLDB and couldn't be solved by LO. But the field "Date" in internal HSQLDB isn't able to save, for example, a birthday.
Comment 1 Ulrich Knoepfel 2013-04-16 09:45:03 UTC
This bug is quite annoying because it can create considerable damage. I hat to correct manually serverel hundreds of data.
Comment 2 Robert Großkopf 2013-04-29 19:27:50 UTC
The package "timezone" and "timezone-java" have had an update at my OpenSuSE 12.3 32bit rpm-System. So I tested this bug again. Changing from timezone "Berlin" to "Zürich" works now; changing from "Zürich" to "Vaduz" set 3 dates one day earlier.
Could be this isn't a bug of LO/HSQLDB but of the timezone currently working in Unix/Linux-systems?
Comment 3 Lionel Elie Mamane 2013-08-07 14:26:06 UTC
(In reply to comment #0)

> Could be this is a problem of the internal HSQLDB

That's most probably the case. I didn't find out in what
format rows (and data columns) are saved in the "data" file,
but it is plausible that the confusion in the standard Java
Date classes between the date "1 January 2012" and the timestamp
"1 January 2012 00:00:00" lead to some bogus timezone conversions
that leads to "1 January 2012 00:00:00" becoming e.g.
"31 December 2011 23:00:00", which would then be truncated
to "31 December 2011".

*But* the big hole in that theory is that example timezones
that you mention have AFAIK the same time (unless maybe that
was not the case in the early twentieth century, which is where
the dates of your examples are?).
Comment 4 Alex Thurgood 2015-01-03 17:40:27 UTC Comment hidden (no-value)
Comment 5 Alex Thurgood 2015-05-29 14:56:17 UTC
*** Bug 91249 has been marked as a duplicate of this bug. ***
Comment 6 Gerhard Schaber 2016-06-26 18:06:48 UTC
*** Bug 100623 has been marked as a duplicate of this bug. ***
Comment 7 Gerhard Schaber 2016-06-26 18:09:53 UTC
I also stumbled across this in 5.1.4. This is annoying and actually leads to data loss. There is no way to find out how often the date values have changed dpending on time zone changes on the system. Naturally you do not change the time zone too often, but if you sometimes have to, this can mess up your whole database.
Comment 8 Gerhard Schaber 2016-06-26 18:20:54 UTC
Is there any workaround for this, so that one does not accidently mess up the database by changing the system time zone?
Comment 9 Gerhard Schaber 2016-06-28 09:19:48 UTC
This does not happen when using the experimental embedded Firebird SQL database system. This is supposed to be much faster than HSQLDB anymore, but it has implications for me. Copy and paste of tables from an HQSQL database to a new Firebird based one always gives an error "SQL-Status: IM001 Der Treiber unterstützt die Funktion 'XParameters::setByte' nicht.". This is probably because of the missing support of VARCHAR_IGNORECASE in Firebird, but this is actually not related to the Date issue here.
Comment 10 Gerhard Schaber 2016-06-28 09:21:38 UTC
Typed too quickly. I meant "anyway" instead of "anymore".
Comment 11 Gerhard Schaber 2017-05-17 11:10:39 UTC
I am aware of the work on Firebird, but can HSQLDB be updated in parallel to a version that does not have this issue, or provide a workaround for this? It might take a while until the Firebird integration is where the HSQLDB integration is.
Comment 12 Gerhard Schaber 2017-05-17 11:17:07 UTC
See also https://sourceforge.net/p/hsqldb/bugs/1375/
Comment 13 Gerhard Schaber 2017-11-22 12:20:20 UTC
Firebird still has a couple of open issues, so I cannot really use that. Is there a workaround for the time zone issue with HSQLDB?
Comment 14 Gerhard Schaber 2017-11-23 11:51:23 UTC
Well, this is a good workaround, but not a little uncomfortable, since it requires macros:
Comment 15 Gerhard Schaber 2017-12-12 11:22:21 UTC
It is uncomfortable, and there is no way to edit the tables via UI afterwards. Is there a better workaround for this?
Comment 16 Gerhard Schaber 2017-12-13 15:24:31 UTC
I cannot use VARCHAR, because then there is no localization of the date format. I cannot use DATE, because that corrupts the data once the time zone is changed towards the west. I cannot use TIMESTAMP, because when using input dialogs for queries, it asks for a time as well as the date, which is impractical. I cannot use HSQLDB 2.3.2 with a split database, because this is not supported by the LO table editing tools (everything greyed out). And I cannot use Firebird, because there are still a couple of serious open issues that still makes it unusable for me (although I have prepared everything for a transition, the last 10% are not working because of the open issues).

So that means risking data corruption as soon as I change the time zone when I am travelling.

It would be great to have some practical workaround for this (other than having to write a macro that goes through all date fields of all tables and fix the dates). Any suggestions are welcome.
Comment 17 Tamas Bunth 2018-04-07 11:50:12 UTC
It is caused by the time zone handling of HSQLDB. Local time is taken, but the "data" file stores it as UTC+0 (in form of elapsed milliseconds - for TIME, DATE and TIMESTAMP columns).

See [1] or src/org/hsqldb/HsqlDateTime.java in HSQLDB source.

@Gerhard: you may consider using Firebird. It is less buggy than before. Also, please report a (new) bug here in Bugzilla if you have further problems.

[1] https://wastack.wordpress.com/2018/04/07/final-steps-of-libreoffice-database-migration/
Comment 18 Gerhard Schaber 2018-04-07 13:30:20 UTC
The Firebird integration is much better now, so I am going with that.