Date format pivot table import from base

fifou64 · August 8, 2020, 8:46am

Hello,
I have a pivot table in calc which points on data from a Libreoffice database.
It takes the data with an sql request like this :

SELECT "myTable"."date", "OtherTables"."otherData"...
FROM "myTable", "otherTables"...
WHERE conditions...

But in the pivot table, dates appear as numeric values like “40800”, I would like them to appear as “14/09/2011”.
I can’t figure out how to do this. Can someone help me?

The libreoffice manual says that pivot tables use the formatting of the data source. In my database, the dates are formatted as date of course.
I tried to use sql convert functions like TO_DATE() but I had no success Chapter 4. Built In Functions

Thanks.
I guess I must use some sql function like CONVERT or TO_DATE but I can figure out If I am right,

mariosv · August 8, 2020, 12:17pm

There is an option for the Database range in Menu/Data/Define database range, select the range and expand options, there is a ‘keep formatting’ option, test if changing it has same effect.

fifou64 · August 8, 2020, 2:38pm

Hello m. a. riosv, I tried your solution but my range doesn’t not appear in the menu you described (maybe because it is not a range, but a request from a database).

I tried to select the pivot table to create a range and select the option you described but it didn’t help. I guess your solution works when the data is taken from a range into the sheets.

mariosv · August 9, 2020, 11:06am

Another to try, verify if the default style has ‘Standard’ for number.

fifou64 · August 9, 2020, 4:04pm

Yes, the default style has ‘Standard’ for number.

mariosv · August 10, 2020, 7:12am

Looks like an old bug PIVOTTABLE: Date format PostgreSQL that affects no only PostgreSQL, see comment #8.
Please add there your comment.

fifou64 · October 4, 2020, 4:35pm

Thank you. I added my comment.

Shiney · October 12, 2020, 3:21pm

Hi

I submitted the bug mentioned… have done some more digging and posted this

https://bugs.documentfoundation.org/show_bug.cgi?id=80177#c23

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.

M