In the dashboards the dates are always displayed as the default date format (eg. October 1, 2019) even if I change the format to for example YYYY-MM-DD, and the time is always in AM/PM even though I've set it to 24h. In the visualization editor, I only get the option to display currency as USD/$, even though SEK is set in formatting options. Am I missing something?
Metabase version 0.33.4 (.jar version running on a windows 10 machine)
MySQL database
Chrome browser (version 78.0)
I've set and reset Formatting options in the Admin panel multiple times, and I've tried restarting the application and the computer multiple times, however, I can't get any formatting option set in the admin panel to affect any cards or dashboards.
Formatting options does not match dashboards and visualization settings:
I can not reproduce the date/time formatting problem on latest version 0.33.5.1
But it could be that you have formatting defined on the dashboard card, so go to the dashboard and click (edit dashboard), hover the card and click (settings), click “Reset to defaults” and “Done”, and “Save” dashboard.
There’s 3 levels of formatting. Global (in Admin > Settings > Formatting), questions (lower left Settings) and dashboard cards (hover card and ).
The date format is only editable in the Admin > Settings > Formatting, it is not possible to modify how to visualize the date in neither the question nor the dashboard card setting. Resetting the settings did not help sadly.
However, it appears to be related to the data source. I downloaded 0.33.5 and for my MySQL data source the date is still displayed according to the default Metabase settings (January 1, 2018), but using the Sample data source the date is formatted as expected as 2018-1-1.
In the MySQL database the datetimes are stored as 2019-09-17 14:25:01.097
Also the 24h formatting doesn’t work properly for either data source.
The behaviour of the time formatting is different based on the Summarize by setting.
Using formatting YYYY-MM-DD and 24h clock.
For Sample data source: Summarize by ‘Hour’ will display the datetime as expected as 2018-1-1, 22:30. However setting ‘Hour of day’ will display the time of day as AM/PM
For MySQL datasource: Summarize by ‘Hour’ will display datetime as January 1, 2018, 10:30 PM, and ‘Hour of day’ is in AM/PM.
@mrmiffo
Please use 0.33.5.1 instead of 0.33.5 - there was a build problem, so just want to make sure everyone is using the same/correct version.
As for date formatting, it should be correct if you assign the Field Type as Creation/Join date/timestamp, not does not work correctly for Native/SQL questions unless manually defined: https://github.com/metabase/metabase/issues/9079
I haven’t re-tested on 0.33.5.1, so please let me know if I need to do more testing on different databases.
As the formatting works for others, and for me on the sample database, it appears to be some issue related to my databases. Currently I’ve not tried date formatting in native queries, but I’ll keep it in mind.
I’ve noticed that the databases I use are missing the Timezone table (which is messing up timezone reporting). Maybe the formatting is related to this issue…? In any case I’m in contact with my DB management team to have the table imported.
I’ll try to do some testing, thank you for your help! I’ll update this issue if I find anything.
I found a solution to my date formatting issues. There were multiple levels to this:
When creating and visualizing a question using a MySQL database, the Admin > Settings > Formatting options are not applied by default. However, the settings are applied automatically when using the Sample database. (This applies to both version 33.4 and 33.5)
I, at first, did not realize the formatting could also be set in the visualization of the question by pressing of the X-axis. Even though you literally told me.
However, setting the display option in the question in version 33.4 (which I initially used) had no effect, while this has been resolved in 33.5.
So what I had to do was upgrade to 33.5 (33.5.1 is not available as .jar) and for all my questions change the x-axis formatting.
@mrmiffo Okay, I’ve just tested with a couple of MySQL/MariaDB test databases on the upcoming 0.33.6 (there’s very few changes compared to 0.33.5.1), and global formatting is applied - date, time and currency - so maybe I’m misunderstanding you or this is specific to your setup?
There’s actually 4 levels of formatting - so make sure one of the lower ones isn’t overruling the upper ones:
Global: Admin > Settings > Formatting
Model: Admin > Data Model > (database) > (table) > (column) > Formatting
Question: visualization Settings > Data > (column) > Formatting
Card: hover dashboard card and click > Formatting
Please make sure that you use 0.33.5.1 and not 0.33.5, since there was a build issue, so 0.33.5 might actually be a broken version of 0.33.4 https://www.metabase.com/start/jar.html - 0.33.5.1 has been available as download since release.
Ah, the Model formatting is what is causing the defaults not to apply in the Question. My Global settings are not the same as the Model Column formatting. In Model my date columns use the AM/PM time settings and default date formatting, which then overrides the Global settings when setting the defaults in the Question. How are the Model formatting options initially set?
EDIT: I've now updated to 0.33.6.
@mrmiffo The Data Model should use the Global formatting, when it hasn't been manually specified in the Data Model. It should automatically use the formatting from upper level.
So if you add a card on a dashboard, but haven't defined formatting anywhere but Global, then it'll check the card, question, Data Model and then Global for which formatting to set.
I cannot replicate your problem. When I change my Global formatting, then Data Model automatically changes as well (might require browser refresh), since I haven't defined that.
If you change options of any level below Global, then you should see a little reload-icon next to the option name - when you click that, it will reset the option, so it uses whatever value is defined from upper levels.
That is weird, I’ve not touched the Data Model at any time so I have definitely not changed it manually. I have noticed the reload function, however it has never had any effect for me after changing the Global settings as the Data Model has been messing with me .
I just tried adding a new database through the UI and checked the Data Model. The formatting in the Data Model is automatically set to “January 7, 2018” and AM/PM, even though the Global settings is “2018-1-7” and 24h.
So I tried recreating the issue on a fresh install of Metabase (0.33.6), and the results are the same.
Steps to recreate:
Start fresh instance of Metabase
Enter setup information/user details
Add new MySQL Database
Set formatting in Admin > Settings > Formatting to 24h and 2018-1-7
Create Question.
Result Question is NOT formatted with global settings. Inspecting Data Model for the Date fields will show Formatting as “January 7, 2018” and AM/PM.
@mrmiffo Can you post Diagnostic Info from Admin > Troubleshooting, and then I’ll try to create similar setup to reproduce the issue. Also, which version of MySQL.
@mrmiffo Thanks for the info - it’s great, when I can try to reproduce as close a possible.
I did a completely fresh setup. Adding the database, then checked Data Model, and it showed US-style formatting (the default) as expected.
Then I changed Global formatting to more Scandinavian-style, then checked Data Model of the same columns I checked before, and they still show US-style formatting. But when I refresh the browser window, then it shows Scandinavian-style as expected.
Does a simple browser refresh solve it for you? I think you use CTRL+SHIFT+R on Windows to also clear the cache.
There’s several issues open about cache not being flushed automatically.
Nope, refresh doesn’t help. Tried again with CTRL+SHIFT+R, and I’ve also rebooted my computer and the browser/Metabase server application multiple times, does not help. I’ve also checked the Model Data of fields I know I’ve never even seen before in the database, and they also use the US formatting. So it does not appear to be a caching issue.
@mrmiffo Dang it. I’ll do some more testing, but maybe one of the issues I’ve linked to previous is triggering some strange behavior. At least you have a couple of workarounds (though time consuming) by changing Data Model and/or the question formatting.
For my use case, the workaround with changing the formatting in questions will work just fine. Its more of a slight annoyance than an issue. The AM/PM issue for Hour of Day bucketing is worse as there is no workaround
@mrmiffo I’ve just been scanning through the thread, but I cannot see you posted the actual MySQL column type.
Can you get that? Something like SHOW COLUMNS FROM order_lines.
There’s a few issues with formatting right now, but might be the root cause of some of the other issues, so I’ll really like to nail it if I could. Still haven’t been able to reproduce.
By the way, the only workaround for the AM/PM thing would be to use SQL and simply output 24h-style text string instead.
When the Field Type (Data Model > database > table > column ) is not set to Creation/Cancellation/Deletion/Join timestamp, then Global formatting is not respected (it falls back to Metabase default, which is US-style). When the correct type is defined, then no need to apply specific formatting for that column.
on the first post of each:
(edit dashboard), hover the card and click 
(settings), click “Reset to defaults” and “Done”, and “Save” dashboard.
