Download it now!
Bug 80177 - PIVOTTABLE: Date format PostgreSQL
Summary: PIVOTTABLE: Date format PostgreSQL
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.0.0.3 release
Hardware: x86-64 (AMD64) All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
: 92116 (view as bug list)
Depends on:
Blocks: Pivot-Table
  Show dependency treegraph
 
Reported: 2014-06-18 07:31 UTC by mshiner
Modified: 2020-10-09 08:23 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description mshiner 2014-06-18 07:31:19 UTC
I have a Calc spreadsheet connected to a registered Base database which is in turn a connection to an existing PostgreSQL database.

If I view the data in Base the date is formatted correctly. If I view in calc using F4 the dates are alos formatted correctly.

But when I create a PIVOTTABLE from the registered database then the dates are formatted as numbers. Obviously I can format the column(s) but this is lost on refresh.

Running ubuntu 14.04 and LO Version: 4.2.3.3 Build ID: 420m0(Build:3)
Comment 1 afonit 2014-06-23 19:13:05 UTC
I went through the steps, and have reproduced the results.
When doing the pivot table from a registered database, the dates are in numeric format - not in date format.

I am on
4.2.5.2
on Fedora 20
Comment 2 Anthony ORGER 2014-06-24 10:16:52 UTC
I have the same problem since LO 4.0.0.
It appends when data come from outter file - database or Spreadsheet. I tried with dbf files and MS Excel 5.0.
It's OK when data are in a sheet of the same file.

There's the same "bug" in Apache OpenOffice 4.0.1 but it's fixed on AOO 4.1.0.
Comment 3 Alex Thurgood 2015-01-03 17:38:12 UTC Comment hidden (no-value)
Comment 4 mshiner 2015-04-21 15:29:38 UTC
Hello

I filed this ages ago... its a regression so I'm not sure why it hasn't been assigned yet.
Comment 5 Alex Thurgood 2015-04-21 16:04:23 UTC
(In reply to mshiner from comment #4)
> Hello
> 
> I filed this ages ago... its a regression so I'm not sure why it hasn't been
> assigned yet.

A regression over which previous version. As I don't currently have a postgres db setup to connect to, I can not check whether this still occurs in the latest production release of LO.

I would encourage you to retest this with LO 4412 and report back your findings here, as the bug might have been fixed in the meantime.

Setting to NEEDINFO, please set to UNCONFIRMED once you have re-tested and reported back.
Comment 6 Alex Thurgood 2015-04-21 16:05:28 UTC
Please also indicate how you connect your ODB to your postgres instance - jdbc, native driver, ODBC, etc.
Comment 7 Alex Thurgood 2015-04-21 16:12:04 UTC
Additionally, is the problem only with dates from postgres data sources or any database source ? For example, do you see the same problems with a simple hsqldb embedded database ?

Note that there are currently quite a few date formatting issues with PivotTables in general, and you may just have come across one of them. This would corroborate with the dates looking OK in Base, but screwed up in Calc.
Comment 8 mshiner 2015-04-21 16:59:31 UTC
Its a problem with all data sources I have tested - even spreadsheets.

You can test by setting up a calc sheet then routing through a base database into the pivot table in a second sheet.

The bug is still present in Version: 4.2.7.2 Build ID: 420m0(Build:2)
Comment 9 mshiner 2015-04-21 17:02:34 UTC
(In reply to Alex Thurgood from comment #5)
> (In reply to mshiner from comment #4)
> > Hello
> > 
> > I filed this ages ago... its a regression so I'm not sure why it hasn't been
> > assigned yet.
> 
> A regression over which previous version. As I don't currently have a
> postgres db setup to connect to, I can not check whether this still occurs
> in the latest production release of LO.
> 
> I would encourage you to retest this with LO 4412 and report back your
> findings here, as the bug might have been fixed in the meantime.
> 
> Setting to NEEDINFO, please set to UNCONFIRMED once you have re-tested and
> reported back.

Sorry...can't remember which version I was running before 4.2.3.3 Build ID: 420m0(Build:3)
Comment 10 mshiner 2015-04-21 20:41:34 UTC
Have just updated to Version: 4.4.2.2 Build ID: 40m0(Build:2) and it is still there.
Comment 11 m.a.riosv 2015-06-16 22:15:27 UTC
*** Bug 92116 has been marked as a duplicate of this bug. ***
Comment 12 m.a.riosv 2015-06-16 22:27:02 UTC
Bug is there with:
Win7x64Ultimate
Version: 5.0.0.0.beta3 (x64)Build ID: 96345c15d8ab19c49014f055fe41ba8e1f421e5c

As commented in https://bugs.documentfoundation.org/show_bug.cgi?id=92116 is reproducible with a spreadsheet as registered source.
Comment 13 raal 2015-06-21 16:32:48 UTC
confirmed in comment 12, setting as NEW
Comment 14 mshiner 2016-02-17 15:40:16 UTC Comment hidden (no-value)
Comment 15 Julien Nabet 2016-03-14 21:12:30 UTC
On pc Debian x86-64 with master sources updated today, I could reproduce this.
I create a simple ods sheet, then an odf file from it and finally a second ods with a table pilot from this odf file.
Comment 16 QA Administrators 2017-10-23 14:14:36 UTC Comment hidden (obsolete)
Comment 17 mshiner 2018-07-09 19:20:04 UTC
Chaps

This bug is still present FOUR YEARS AFTER THE ORIGINAL REPORT!!!!

I'm guessing you don't think it important enough to fix even though its a regression from a previous build.

Tested on ubuntu 18.04 
Version: 6.0.3.2
Build ID: 1:6.0.3-0ubuntu1
CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: gtk3; 
Locale: en-GB (en_GB.UTF-8); Calc: group

I used both a connection a postgreSQL database AND a spreadsheet
Comment 18 Julien Nabet 2018-07-23 14:06:23 UTC
On pc Debian x86-64 with master sources updated today, I can still reproduce from comment 15 (A first ods file with a odb wrapper file and a final ods file using the odb file).

Several remarks:
1) In first file, even when typing date with 4 years, Calc uses 2 numbers by default for date.
I must format the column explicitely. (perhaps not related directly to this bugtracker but concerns format problem)
Note: I didn't use column headers in this file. 

2) In odb file, Base uses the format with 4 numbers for years in column name of the table, but uses the by default the format with 2 numbers for years for the values.

3) In third file (so the second ods file), the name of column uses the format with 4 numbers for years and like the bug tracker describes, I got just numbers for values.
But I noticed that when clicking on arrow of the column in pivot table (eg to filter it), values appeared as dates with a format with 2 numbers for years.

Eike: thought you might be interested in this one since it concerns Pivot part. Any thoughts?
Comment 19 QA Administrators 2019-07-30 03:13:32 UTC Comment hidden (obsolete)
Comment 20 mshiner 2019-07-30 16:52:19 UTC
Chaps

Have tested using both and ods and postgreSQL as a source.

Bug is still present.

Concur with comment 18 that the filter section of the report shows the values as dates

Version: 6.0.7.3
Build ID: 1:6.0.7-0ubuntu0.18.04.8
CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: gtk3; 
Locale: en-GB (en_GB.UTF-8); Calc: group

Regs
Martyn
Comment 21 philnsicab 2020-10-04 15:43:29 UTC
Here on windows 7, libreOffice Version : 6.3.5.2 (x64).

The bug is still present :

I have a pivot table in calc based on a request and data from an HSQLBD file. Dates are correctly formatted in my database. I can format them to appear correctly in the database but as soon as I refresh they appear as numbers again.

Because of this format bug, the pivot table is unusable. I hope this gets solved one day...
Comment 22 Julien Nabet 2020-10-04 16:14:19 UTC
(In reply to philnsicab from comment #21)
> Here on windows 7, libreOffice Version : 6.3.5.2 (x64).
> ...
When retesting, it's more relevant to test with not an EOL version. I mean last stable one is 6.4.6 and brand new one is 7.0.1. (Don't misunderstand me, I don't pretend it's already fixed in these last versions, I haven't given a try)
Comment 23 mshiner 2020-10-09 08:23:00 UTC
Bug is still present

Ubuntu 20.04
Version: 6.4.6.2
Build ID: 1:6.4.6-0ubuntu0.20.04.1
CPU threads: 4; OS: Linux 5.4; UI render: default; VCL: gtk3; 
Locale: en-GB (en_GB.UTF-8); UI-Language: en-US
Calc: threaded

I narrowed it down to the way styles are applied to the PT.

Basically on a new PT (with data in a tab in the file or an external database) you get 6 styles

Pivot Table Category
Pivot Table Corner
Pivot Table Field
Pivot Table Result
Pivot Table Title
Pivot Table Value

If you change background colour/font etc on, say, the Category or Value it is applied and survives a refresh. Number format, however is not. Even if you modify the number format in the style it is not applied at all - I assume the default of 'general' is applied. 

If you have your data in a calc tab on the same sheet you can apply the number format there and the PT respects it.

Conclusion, the PT style for number format is always set to 'general' unless the number is 'formatted' in the data - which only works if the data is in the same sheet on another.

I also found this link https://mikekaganski.wordpress.com/2019/04/29/xlsx-interoperability-pivot-tables-related-improvements/ and specifically this

and this fix https://git.libreoffice.org/core/+/26e85974a0287ab5869e7ff0145a66b853d66a02

related to this bug https://bugs.documentfoundation.org/show_bug.cgi?id=124772 report.

Mike notes "Excel relies on per-field format setting in pivot tables, while Calc
takes fields formatting from source." which I think is where the problem resides.

Regards
Martyn