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.
This bug is quite annoying because it can create considerable damage. I hat to correct manually serverel hundreds of data.
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?
(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?).
Adding self to CC if not already on
*** Bug 91249 has been marked as a duplicate of this bug. ***
*** Bug 100623 has been marked as a duplicate of this bug. ***
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.
Is there any workaround for this, so that one does not accidently mess up the database by changing the system time zone?
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.
Typed too quickly. I meant "anyway" instead of "anymore".
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.
See also https://sourceforge.net/p/hsqldb/bugs/1375/
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?
Well, this is a good workaround, but not a little uncomfortable, since it requires macros:
It is uncomfortable, and there is no way to edit the tables via UI afterwards. Is there a better workaround for this?
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.
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  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.
The Firebird integration is much better now, so I am going with that.