Hi,
Using 0.33.0 production.
I am building query with SQL and the date column does not include time elements. It is resultant from a date() function. tabular view shows date only but chart include the time as 12 midnight. Can the time be suppressed?
Chuck
…furthermore, can the date string be abbreviated such as Jan 04 19?
Hi @crwheelr
Yes, in the Data-tab of the Visualization settings, that’s where you change formatting.
But depending on how you’re returning data from your query, some options might only be available in certain cases.
EDIT: I would recommend that you upgrade to 0.33.2 - several issues were fixed since 0.33.0
Loaded 0.33.2 same behavior
on thedata tab, at the gear icon there is nothing there
here is a sample of the table data
@crwheelr
I didn’t say your specific issue was fixed in 0.33.2 - I just said that many issues has been fixed, so an upgrade was recommended.
Looking at the raw data, it looks like you are returning a string and not a date type.
So with a string, Metabase has limited options what to do with the data - make it return a date or format the string how you want the data.
No its resultant of SQLite Date() function I believe that SQLite stores dates as text files even though the the format of the text conforms to date format.
The column data is represented correctly. i thing the graph should follow the column format.
chuck
Here’s the code
SELECT date(“orders_tbl”.“order_entry_date”,’-6 days’, ‘weekday 0’) AS “Week”
order_entry_date is of type DATE also.
@crwheelr
Okay, I think it’s a combination of using Native query with SQLite and setting the axis to Ordinal.
You can make the query return all the dates, and then make a new question using that Saved Question and summaries by week with the query browser.
Or convert it into a different string like strftime('%d-%m-%Y', date(...)) or strftime('%Y-W%W', date(...))
SQLite doesn’t have built-in functions for returning month names, so you would be doing a lot of case-when to return a formatted string as MMM dd YYYY
Time series displays correctly, but ordinal only does not.
@crwheelr Yes, that’s what I wrote.
You’re welcome to create an issue on this, since the only issue that is similar was closed.
I’m not sure if this can be fixed easily, since SQLite is a very limited database and I think the way it returns dates as string messes with a lot of things in Metabase.
I got around it using a formatted text field and referencing that in the graph. This was your suggestion
Thanks for looking into it though
Chuck
