Can a date change in a date field?

Creating tables and queries
Post Reply
User avatar
charlie.it
Volunteer
Posts: 417
Joined: Wed Aug 21, 2013 2:12 pm
Location: Italy

Can a date change in a date field?

Post by charlie.it »

In this Italian forum: https://forum.openoffice.org/it/forum/v ... =13&t=8809
it is said that a date stored in a data type field changes from day to day by opening the database once with Linux system and once with Windows system. In your opinion, is it possible?
charlie
Italian AOO Admin
macOS 14 Sonoma M1: Open Office 4.1.15 - LibreOffice 7.5.7.1

http://www.charlieopenoffice.altervista.org
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Can a date change in a date field?

Post by Villeroy »

There is an off-by-one error in "T_Persone"."Nato il" ?
The person named "QUESTA" (ID #83) was born 12/06/1966 which is shown as 11/06/1966 ?
When I open the table on Linux with LibreOffice 5.4 I see 10/06/1966. Same with OpenOffice on Linux. Same with LibreOffice 5.2 on Windows. Same when I switch between Oracle Java and OpenJDK.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Can a date change in a date field?

Post by Villeroy »

May be there is a "special entry" in registrymodifications.xml
viewtopic.php?f=9&t=72644
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
charlie.it
Volunteer
Posts: 417
Joined: Wed Aug 21, 2013 2:12 pm
Location: Italy

Re: Can a date change in a date field?

Post by charlie.it »

Thank you for your always ready and competent answers.
But in the file registrymodifications.xcu (not .xml) is no longer found the section neither in LO Linux nor in LO and OO Mac OSX

Code: Select all

<item oor:path="/org.openoffice.Office.Calc/Calculate/Other/Date">
<prop oor:name="DD" oor:op="fuse"><value>1</value></prop>
</item><item oor:path="/org.openoffice.Office.Calc/Calculate/Other/Date">
<prop oor:name="MM" oor:op="fuse"><value>1</value></prop></item>
<item oor:path="/org.openoffice.Office.Calc/Calculate/Other/Date">
<prop oor:name="YY" oor:op="fuse"><value>1900</value></prop></item>
It's been a long time since that thread and, perhaps, things have changed.
Even if the problem has returned.
charlie
Italian AOO Admin
macOS 14 Sonoma M1: Open Office 4.1.15 - LibreOffice 7.5.7.1

http://www.charlieopenoffice.altervista.org
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Can a date change in a date field?

Post by RoryOF »

I confirm: a quick test shows that there is currently no Date [sic] in registrymodifications.xcu on my current computer (Xubuntu).

Caveat: I do not use Calc very much, so there may have been no need for it to be entered.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Can a date change in a date field?

Post by Villeroy »

The xml snippet seems to have no effect on Base dates even when you "inject" it into registrymodifications.xcu
<digression but not off topic>
After injecting 1904-01-01, I opened a csv file with recent dates in order to reproduce the old issue. A csv file does not contain any meta information. The old problem used to be that such files with no meta information about the null date inherit any existing null date from registrymodifications.xcu. Tools>Options>Calc>Calculation shows null date 1904-01-01 for the csv file, therefore a 0 formatted as date shows 1904-01-01.
Then I copied a cell with 2018-01-15 (value 41653) from the csv to another spreadsheet with null date 1899-12-30 and it comes out as 2014-01-14 (same value 41653) which causes a lot of confusion.
I think there is no reason to store any null date in registrymodification.xcu. The null date of a spreadsheet is either given by the document's meta information or by the detected document type (Lotus, old Mac sheets, old Star Calc). I can not see any reason why the program applies a null date to a plain text file. All the text dates are "hard coded" anyway and after importing the text data, 1899-12-30 would be much better calculation base than anything else because it is much more likely to be compatible with any other document you have loaded, including MS Excel files.
To make the problem worse, new ods documents inherit the non-standard null date and store it in their content.xml under <office:body><office:spreadsheet><table:calculation-settings><table:null-date table:date-value="1904-01-01"/>
https://bz.apache.org/ooo/show_bug.cgi?id=97669
</digression but not off topic>
However, the Base document from the Italian topic is not affected by this problem, even when I copy records from the database to a 1904-01-01 sheet and back to database. Base seems to deal with the displayed date strings rather than the numeric cell values. Sometimes this may lead to import problems when the date format is unusual (e.g. in conflict with the locale setting). It is a good idea to apply plain ISO format to date cells before we copy them into a database. Base will even import string dates as long as they are ISO strings.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
unlucky83
Posts: 3
Joined: Sun Mar 27, 2016 2:27 am

Re: Can a date change in a date field?

Post by unlucky83 »

I downloaded the db and I did these tests:
1) I started Ubuntu> I opened the database with Libreoffice> Tables> T_Persone and I filtered these IDs
Schermata del 2018-01-28 14-48-29.png
Schermata del 2018-01-28 14-48-29.png (9.26 KiB) Viewed 7440 times
2) I closed without saving and restarted the laptop
3) I started Win7> I opened the database with Libreoffice> Tables> T_Persone and I filtered these IDs
Immagine.png
Immagine.png (3.78 KiB) Viewed 7440 times
The dates of "ID_Persona" = 83, 167, 169, 201, 214, 221 are changed, but the dates of the other records are not (see "ID_Persona" = 0)
4) I closed without saving and restarted the laptop
5) I started Ubuntu> I opened the database with Libreoffice> Tables> T_Persone and I filtered these IDs
Schermata del 2018-01-28 15-05-13.png
Schermata del 2018-01-28 15-05-13.png (7.69 KiB) Viewed 7440 times
Data values ​​do not change from Windows to Ubuntu, but from Ubuntu to Windows some of them change.
Why? Is this a Windows-LibreOffice problem ? With Calc nothing like that..for both the calculate date option is set to 12/30/1899

Ubuntu 16.04 ( uname output: Linux 4.4.0-112-generic #135-Ubuntu i686 i686 i686 GNU/Linux)
Libreoffice (Versione: 5.1.6.2 Build ID: 1:5.1.6~rc2-0ubuntu1~xenial2 Thread CPU: 4; Versione SO: Linux 4.4; Resa interfaccia: predefinito;Versione locale: it-IT (it_IT.UTF-8))

Windows7 64bit
Libreoffice (Versione: 5.4.4.2 (x64) Build ID: 2524958677847fb3bb44820e40380acbe820f960 Thread CPU: 4; SO: Windows 6.1; Resa interfaccia: GL; Versione locale: it-IT (it_IT))
Ubuntu 16.04 32bit-Libreoffice 5.1.6.2
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Can a date change in a date field?

Post by Sliderule »

I too downloaded the database file, and, to summarize what I found:
  1. I extracted the database from the .odb file . . . and . . . used HSQL utilities ( both SQLTool.jar and HSQL Database Manager ) and for record "ID_Persona" of 83, the database is reporting the date field "Nato il" as 2066-10-06 ( this is in YYYY-MM-DD format ).
  2. The above answer was the same with different versions of JAVA ( 1.6, 1.7, 1.8 ) . . . so . . . not a JAVA version issue.
  3. Using Windows 10, I Opened the database with OpenOffice 4.1.5 using both JAVA 1.8 and JAVA 1.7 and the date also displays correctly as above.
Just thought I would add what I saw, but, I know this does not offer a solution.

Sliderule
oquintoo
Posts: 7
Joined: Sun Jan 28, 2018 5:01 pm

Re: Can a date change in a date field?

Post by oquintoo »

Hi, I'm the one who opened the post in the Italian forum,

thank you charlie and unlucky83 for the support,

I would like to clarify that on Calc there are no problems, I exported the table on Calc and opening it on Win7Pro and various Linux systems there were no variations.

The problem exists within the base table, and does not depend on a specific operating system because I tested it on Ubuntu, Xubuntu, Manjaro, Antix, PcLinuxOs, Debian and also this problem happened to me even between two Linux systems (unfortunately I do not remember which .... definitely between Win7Pro and Linux)

As regards the answer of Villeroy I clarify that it is not a problem of departure date (as unlucky83 has clarified), because if I export the table to Calc and transform the DATA column into NUMBER the value described is different each time, so the variation is of the numerical value

I admit I do not know what it is
Base will even import string dates as long as they are ISO strings
I remember that these dates change daily and so they were easy to spot (from unlucky83), but unfortunately they also change during the year, for example, after one year I no longer have a valid data ... !! and I manage 2 sports clubs with more than 200 members ...

so the error seems to have no identical value on the cells, it seems that somewhere there is an active function .... :crazy:
OpenOffice_4.1.5 Manjaro
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Can a date change in a date field?

Post by Villeroy »

Searching for date issues with Base in the LO issue tracker, this is the closest I can get: https://bugs.documentfoundation.org/sho ... i?id=63566

To my surprise, I can not reproduce anything like that with a recent LO 5.4 on Linux-x64 with Oracle Java or OpenJDK and Italian locale.
Base_2018-01-28 17-09-23.png
Last edited by Villeroy on Sun Jan 28, 2018 6:10 pm, edited 1 time in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Can a date change in a date field?

Post by RoryOF »

Might this be a leap year problem? It wouldn't be the first!
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
charlie.it
Volunteer
Posts: 417
Joined: Wed Aug 21, 2013 2:12 pm
Location: Italy

Re: Can a date change in a date field?

Post by charlie.it »

Among all, only 76 was a leap year.
charlie
Italian AOO Admin
macOS 14 Sonoma M1: Open Office 4.1.15 - LibreOffice 7.5.7.1

http://www.charlieopenoffice.altervista.org
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Can a date change in a date field?

Post by RoryOF »

Try the following
28/02/68
01/03/68
28/02/72
01/03/72
28/02/76
01/03/76
28/02/80
01/03/80.

This should tie the problem to one mishandled leap year.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
oquintoo
Posts: 7
Joined: Sun Jan 28, 2018 5:01 pm

Re: Can a date change in a date field?

Post by oquintoo »

@Villeroy

To reproduce the error, on my system, it occurs if I download the file on Linux (for example Manjaro, but on any system is the same), then I open it and display the date, then I turn off the PC and restart on Win7Pro, I go back on the same Base file, I visualize the date and discover the change.
In this regard I made a video on youtube at this link https://youtu.be/kYE5aLokfNA

I clarify that if you stay on the same system, there is no change
OpenOffice_4.1.5 Manjaro
unlucky83
Posts: 3
Joined: Sun Mar 27, 2016 2:27 am

Re: Can a date change in a date field?

Post by unlucky83 »

i tried to add a timestamp field to the table with same dates of "Nato il" field and time "12:00 AM" ( in Ubuntu)
Opening the file on windows7 I have these changes
Immagine3.png
Immagine3.png (6.04 KiB) Viewed 7402 times
The timestamp field lost 1 hour!
Ubuntu 16.04 32bit-Libreoffice 5.1.6.2
oquintoo
Posts: 7
Joined: Sun Jan 28, 2018 5:01 pm

Re: Can a date change in a date field?

Post by oquintoo »

I read the post https://bugs.documentfoundation.org/sho ... i? Id = 63566 and it seems to me a difficult problem, as well as being unsolved ..., for sure solving this problem goes beyond my capabilities.
The only thing I can do is describe the malfunctions
OpenOffice_4.1.5 Manjaro
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Can a date change in a date field?

Post by Villeroy »

I have no dual boot system but when I close the office suite on Linux, copy the file to a shared Windows drive and open this file on a Windows box the date remains the same 10/06/1966
I've never seen any birthday other than 10/06/1966 (and I will always remember that day).
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
oquintoo
Posts: 7
Joined: Sun Jan 28, 2018 5:01 pm

Re: Can a date change in a date field?

Post by oquintoo »

@ Villeroy
The date has changed, because I posted the date 11/06/1966 .......

try to double check the date from the Italian forum, https://forum.openoffice.org/it/forum/v ... =13&t=8809
OpenOffice_4.1.5 Manjaro
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Can a date change in a date field?

Post by Villeroy »

I change the birth date in record #83 to 11/06/1966, close LO, copy file to Win share. Open it on the Win box and see 11/06/1966
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
oquintoo
Posts: 7
Joined: Sun Jan 28, 2018 5:01 pm

Re: Can a date change in a date field?

Post by oquintoo »

This problem exists on dualboot systems, ....
OpenOffice_4.1.5 Manjaro
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Can a date change in a date field?

Post by robleyd »

The one hour difference might be related - somehow - to a Daylight Savings anomaly, or clock handling differences between Linux and Windows??
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
oquintoo
Posts: 7
Joined: Sun Jan 28, 2018 5:01 pm

Re: Can a date change in a date field?

Post by oquintoo »

@Robleyd

apart from the fact that there are people who for work change daily time zone and should not have the data corrupted for this reason, .... but this does not explain why some dates are changed in a day and others after months. ..
OpenOffice_4.1.5 Manjaro
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Can a date change in a date field?

Post by robleyd »

apart from the fact that there are people who for work change daily time zone and should not have the data corrupted for this reason
I don't suggest that ti is a good thing that this happens; just a guess based on the hour difference and possibility that there perhaps may be an anomaly (possibly in some setting) between Windows and Linux. I may of course be incorrect....
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
unlucky83
Posts: 3
Joined: Sun Mar 27, 2016 2:27 am

Re: Can a date change in a date field?

Post by unlucky83 »

Well. I took a step forward.
Before I lost an hour every time I went from ubuntu to windows 7, now I lose an hour that I recover when I go back to Ubuntu in TimeStamp
The problem was the Ubuntu datetime set to UTC while windows, which is in dualboot, is set to RTC. I had not noticed it before because both Ubuntu and windows updated the time from the web. The time of the bios was changed with each restart.
Now Ubuntu is set to RTC.
The results for ID_Persona = 83 are:
in Ubuntu, Timestamp is always 11/06/66 0:00 (DD / MM / YY h:mm) or 1966/06/11 0:00 (YYYY / MM / DD h:mm)
Schermata del 2018-01-29 01-07-38.png

in Windows7, Timestamp is always 10/06/66 23:00 (DD / MM / YY h:mm) or 1966/06/10 23:00 (YYYY / MM / DD h:mm)
Immagine4.png
Immagine4.png (8.63 KiB) Viewed 7321 times
In "Nato il" I lost an hour every time I went from ubuntu to windows 7 as before,which causes the loss of a day every time.

I hope I have explained enough, sorry for my English and thank you all for your suggestions :knock:
Ubuntu 16.04 32bit-Libreoffice 5.1.6.2
oquintoo
Posts: 7
Joined: Sun Jan 28, 2018 5:01 pm

Re: Can a date change in a date field?

Post by oquintoo »

Update the situation, we can exclude the problem of 32 or 64 bit systems,
I installed Manjaro 64 bit on my son's PC which also has WIN8 64 bit, and after downloading the file from the forum on Manjaro I modified the correct date on 11/06/1966 then I restarted and reopened the same file on WIN8 and I found the date 10/06/1966, then remain suspected of Java and the HSQLDB engine.

The interesting thing is that I downloaded the file on a windows system and I saw the date 10/06/1966, if I do the download with a linux system I see the date (correct) 11/06/1966
OpenOffice_4.1.5 Manjaro
Post Reply