Aggregations on multiple fields showing datetimes as results with PostgreSQL?

[underyx]

• I am using the Chrome browser.
• My computer's OS is OS X El Capitan.
• I'm running Metabase with the Docker image.
• My database is PostgreSQL.
• My Metabase version is 0.16.0.

Let's say I have the following data

CREATE TABLE sale (
    timestamp TIMESTAMP WITH TIME ZONE,
    region TEXT
);

I would like to get a line chart with time on the X axis, row count on the Y axis, and one line per region.

In the table view I'd expect this to be represented as one row per day and one column per region, with the cells containing the DB row counts. Here, it works perfectly with the included sample data:

But with PostgreSQL, I'm seeing this instead:

Oh dear! 🙈

[tlrobinson]

Hmm I tried reproducing this on Postgres 9.4 with the schema and query you posted and a few data points:

insert into sale values (now() + '-1 day', 'foo'), (now() + '-1 day', 'bar'), (now() + '-2 days', 'foo');

and it seems to work fine:

Did you change anything in the admin metadata page?

[agilliland]

@tlrobinson what happens if the pivoting is flipped so that the timestamps end up as the smaller dimension?

[agilliland]

in fact, it almost looks like we are parsing the cell value as a datetime in that scenario, instead of the header values.

[tlrobinson]

@agilliland Yup, that did it:

[tlrobinson]

It seems like there are two things we should fix:

1. We're not pivoting cols correctly.
2. If there's a date/time column it should probably always use that as the row label.

[tlrobinson]

I fixed # 1 in #2285. Do we also want to do # 2?

[agilliland]

So 1) gets the value displaying properly and 2) formats the column header? if that's the case then i agree that we should do 2) as well.

[underyx]

Oh wow, I can't believe you guys were seriously this quick with the fix. You two are amazing! 😲

[salsakran]

@underyx tell your friends ;)

[tlrobinson]

@agilliland No, I meant # 1 fixes both of things you mentioned, # 2 would also put the date/time column in the first column (my first screenshot), as opposed to across the top (my second screenshot) regardless of the cardinality compared to the other column. Not sure if that's desired or not.

[agilliland]

Ahh, yeah, lets skip that and we can decide a bit later if we think that's really the best thing to do. The fix in the current PR is all we need to close out this issue.