Bug 102945 - Base table: entering a default value for a date in the design view gives a wrong value in the table view
Summary: Base table: entering a default value for a date in the design view gives a wr...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
3.6.7.2 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
: 122140 152021 (view as bug list)
Depends on:
Blocks: Database-Tables
  Show dependency treegraph
 
Reported: 2016-10-04 01:30 UTC by stroy
Modified: 2023-05-17 15:43 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


Attachments
DateFormatDemo.odb (3.49 KB, application/vnd.oasis.opendocument.database)
2016-10-07 01:26 UTC, stroy
Details
Default-value of a date adds 2 days each time you leave the row of the date (98.23 KB, application/pdf)
2016-10-07 15:49 UTC, Robert Großkopf
Details

Note You need to log in before you can comment on or make changes to this bug.
Description stroy 2016-10-04 01:30:19 UTC
I opened Base table in Edit mode and entered a date format e.g. mm/dd/yy and a default value in the same format e.g. 07/01/16.  Closing the edit mode and opening the table, there was a wrong value for the date.  Someone on the LibreOffice forum suggested leaving the format entry as is, but entering the default value using format yyyy-mm-dd.  This worked, except that (1) opening the table, the default value shown was two days later than what I had entered in the Edit mode; (2) re-opening the Edit mode, each time I clicked on that date field, two additional days had been added to the default value.  I finally entered a default date in yyyy-mm-dd format but two days earlier than what I wanted, and refrained from checking back on that field.
Comment 1 Alex Thurgood 2016-10-04 08:00:12 UTC
@stroy : 

1) what is your locale/language setting ?

2) please provide a sample ODB file where the problem appears and explain how to reproduce the issue.

Setting to NEEDINFO, please set back to UNCONFIRMED once you have done this.
Comment 2 Alex Thurgood 2016-10-04 08:25:31 UTC
Seems similar to the behaviour described in bug 96190, but without a test ODB file I'm just guessing.
Comment 3 stroy 2016-10-07 01:26:43 UTC
Created attachment 127849 [details]
DateFormatDemo.odb
Comment 4 stroy 2016-10-07 01:30:16 UTC
(In reply to Alex Thurgood from comment #1)
> @stroy : 
> 
> 1) what is your locale/language setting ?
> 
> 2) please provide a sample ODB file where the problem appears and explain
> how to reproduce the issue.
> 
> Setting to NEEDINFO, please set back to UNCONFIRMED once you have done this.

1) My locale/language setting is Default-English (USA)
2) I am attaching a demo file.
Comment 5 Alex Thurgood 2016-10-07 08:13:59 UTC
@stroy : when I open your file and then the table in Edit mode, I see that the date field StartDate has been formatted as 01/01/00 and if I click on the ellipsis button next to that formatting window, I get the usual LO number formatter dialog. This dialog indicates that the field is configured as a number and not a date (Category > Number, Format > Standard).

I left the default and then closed table edit mode and opened the Table view/data entry mode where I can see the data already in the table. The first and only line of data gives a displayed date value of 06/10/16.

What date value did you actually enter ?

I entered 07/01/20 as the date in a new entry, and some random text string. The date displayed as 07/01/20, even after closing and re-opening the table.

If I click on the field header of the open table, and choose Column Format, I get a dialog that indicates that the field is configured as a Date, and in the DD/MM/YY format. If I change the language to English(USA), I get the default date format of MM/DD/YY. This setting works for me and survives a shutdown and reload of the ODB file.

Tested against
Version: 5.3.0.0.alpha0+
Build ID: 44170554cc9c0411c2ed6720fd09bedee2a9a162
Threads CPU : 2; Version de l'OS :Mac OS X 10.12; UI Render : par défaut; 
Locale : fr-FR (fr_FR.UTF-8); Calc: group


I don't see a two day difference in date values.


What setting have you put in Calc for the default date under LibreOffice > Preferences > Calc > Calculations > Date ?


Which time zone are you operating in ? The Java date function, which is what is used in hsqldb (the default embedded database engine in ) to manage dates, is known to be dependent on timezone corrections, which can lead to date display offsets in LO Base (and thus incorrect display).
Comment 6 Robert Großkopf 2016-10-07 15:49:00 UTC
Created attachment 127863 [details]
Default-value of a date adds 2 days each time you leave the row of the date

There is a description with screenshots in the attachment.

Add a default-value while editing the table. Change to another row and go back. 2 days have been added. Save the table. Again tow days have been added. Every time you move from the row, which contains the date, to other rows and back there will be added 2 days.

I could confirm this buggy behavior for OpenSUSE 42.1 64bit rpm Linux and LO 5.2.2.2.
Comment 7 Robert Großkopf 2016-10-07 15:56:51 UTC
Seems to be a very old bug: I could also confirm it with LO 3.6.7.2. Could be this bug is inherited from OOo. Could also confirm with version: 5.3.0.0.alpha0+, Time: 2016-08-23_13:10:42
Comment 8 QA Administrators 2018-11-03 03:50:41 UTC Comment hidden (obsolete)
Comment 9 Robert Großkopf 2018-12-16 16:18:50 UTC
*** Bug 122140 has been marked as a duplicate of this bug. ***
Comment 10 Drew Jensen 2018-12-16 16:28:19 UTC
(In reply to Robert Großkopf from comment #9)
> *** Bug 122140 has been marked as a duplicate of this bug. ***

on the duplicate issue it is clear that it is not just a two day change, the year part of the date is messed up also with the Firebird backend. 

I could add the FB example to this issue so that distinction isn't lost in the fix?
Comment 11 Robert Großkopf 2018-12-16 16:38:11 UTC
(In reply to Drew Jensen from comment #10)
> (In reply to Robert Großkopf from comment #9)
> > *** Bug 122140 has been marked as a duplicate of this bug. ***
> 
> on the duplicate issue it is clear that it is not just a two day change, the
> year part of the date is messed up also with the Firebird backend. 
> 
> I could add the FB example to this issue so that distinction isn't lost in
> the fix?

Are you shure the year will also be changed? I have tested it with a Firebird and a HSQLDB on LO 6.1.4.2 with OpenSUSE 15, 64bit rpm Linux. Couldn't find any difference between the behaviour of both internal databases.
Comment 12 Justin L 2019-05-02 13:24:21 UTC
(In reply to Robert Großkopf from comment #7)
> Seems to be a very old bug: I could also confirm it with LO 3.6.7.2.

I believe the change happened from commit fb162871bea92577e72437e59e1dfc1d2e6b3a93 by Author: Ocke Janssen <oj@openoffice.org> on Date: Thu Sep 26 09:49:09 2002 +0000
    #102963# SetEvalDateFormat(NF_EVALDATEFORMAT_FORMAT) to use the format key at the number formatter

specifically with the single use of toNullDate() in
nValue = DBTypeConversion::toNullDate(DBTypeConversion::getNULLDate(xNumberFormatter->getNumberFormatsSupplier()),nValue);
Comment 13 Stéphane Guillou (stragu) 2022-11-14 22:24:47 UTC
I can still reproduce this in:

Version: 7.5.0.0.alpha0+ (X86_64) / LibreOffice Community
Build ID: 6e66b5d75b4cda0314b64f4d12ef9e4350751470
CPU threads: 8; OS: Linux 5.15; UI render: default; VCL: gtk3
Locale: en-AU (en_AU.UTF-8); UI: en-US
Calc: threaded
Comment 14 Stéphane Guillou (stragu) 2022-11-14 22:24:58 UTC
*** Bug 152021 has been marked as a duplicate of this bug. ***
Comment 15 Justin L 2023-05-17 15:43:22 UTC
repro 7.6+
Note: it is in design (editing the table's settings) where this is seen, not in data entry.