New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Issue with date display in chart with ordinal and timeseries options #5246

Open
HelenMertes opened this Issue Jun 1, 2017 · 22 comments

Comments

Projects
None yet
@HelenMertes
Copy link

HelenMertes commented Jun 1, 2017

Hi,
I am having issues displaying the data axis for bar charts based on SQL queries grouped by month properly. Using "ordinal" it shows the entire date incl time of the 1st day of the respective month, which looks awful and overlaps. With "timeseries" if correctly shows the label "June 2016", but not below the correct data point, i.e. below July (ad the label "July 2017" below the August data point etc). Any idea how to resolve this so that the chart can be displayed properly?
Thanks!

  • Your browser and the version: Version 58.0.3029.110 (64-bit)
  • Your operating system: Windows 10
  • Your databases: BigQuery
  • Metabase version: 24.1
@salsakran

This comment has been minimized.

Copy link
Contributor

salsakran commented Jun 3, 2017

I'd guess this is an incorrect timezone conversion happening somewhere.

Can you share the following (taken from http://www.metabase.com/troubleshooting/timezones) -

What is the time zone of the data you think is being displayed improperly? (I.e., in the database itself.)
Are you using an explicit time zone setting on each timestamp, or are the timestamps being stored without a timestamp? (E.g., Dec 1, 2019 00:00:00Z00 is an explicitly timestamped value, but Dec 1, 2019 has an implied time zone.)
What time zone is the database server set to?
What time zone is the server that is running Metabase set to?
What is your Reporting Time zone setting?
What is your browser time zone setting?

@gilhs

This comment has been minimized.

Copy link

gilhs commented Jun 5, 2017

Not the OP, but also started experiencing this (seems to start with 0.24?), so will give my input - this also happens when visualizing SQL results, and specifically when using dates [i.e. date(<some_timestamp_filed>) which returns 'YYYY-MM-DD']. So this isn't just limited to timestamps.

image

@gseva

This comment has been minimized.

Copy link

gseva commented Jun 26, 2017

We are experiencing the same problem. I will add our case here to not open another issue.

Seems like this happens when we change the timezone setting (via /admin/settings/general).

This is what we see with timezone setting set to Brazil/East:

screen shot 2017-06-26 at 14 11 59

That's the result with timezone set to Database default (UTC):

screen shot 2017-06-26 at 14 12 32

The second chart shows correct data, in the first one all the counts are moved 1 day.

We are using Metabase 23.1, connecting to postgesql database. Created at field in our database is saved as bigint, and is defined as Unix timestamp (seconds) in Metabase's data model.

Hope it's helpful to fix this issue.

@ebalgava

This comment has been minimized.

Copy link

ebalgava commented Oct 13, 2017

I'm having same issue with shifted date labels in charts:
https://bi.ekosystem.slovensko.digital/public/dashboard/86ce303c-05bf-4947-bb39-d2a958208492
@salsakran anounced 'this should be fixed in 0.26' but my issue still persists within v0.26.1:
#3642 (comment)

@andehen

This comment has been minimized.

Copy link

andehen commented Oct 17, 2017

Also having the same issue with version 0.26.1. The tool tip is correct, but axis tick labels are off by one.
wrong-axis-tick-issue

@tobbbles

This comment has been minimized.

Copy link
Contributor

tobbbles commented Oct 24, 2017

Experiencing the same on 0.26.1.

@mazameli

This comment has been minimized.

Copy link
Contributor

mazameli commented Oct 24, 2017

One place to start for everyone who's experiencing this is to first go through the timezone troubleshooting doc here: https://github.com/metabase/metabase/blob/master/docs/troubleshooting-guide/timezones.md

@ebalgava

This comment has been minimized.

Copy link

ebalgava commented Oct 24, 2017

@mazameli this issue definitely isn't timezone related, as the same chart shows correct result in value label, just X-axis label is shifted

@salsakran

This comment has been minimized.

Copy link
Contributor

salsakran commented Oct 24, 2017

@ebalgava the bar charts at that link look like they have matched axis ticks and tool tips.

Can anyone reproduce this with the sample dataset on 0.26.X?

We fixed a number of timezone issues in the most recent release, but it's entirely possible we missed some corner cases. Can you share more information about the underlying query, database timezone, your browser timezone, etc, and help us reproduce your situation?

@salsakran

This comment has been minimized.

Copy link
Contributor

salsakran commented Oct 24, 2017

@ebalgava fyi, there was in fact a class of timezone bugs related to situations where tooltips (or other representations) weren't correctly mapping to a timezone

@salsakran

This comment has been minimized.

Copy link
Contributor

salsakran commented Oct 24, 2017

This is what I see

screen shot 2017-10-24 at 10 03 15 am

Can you point me to where in that dashboard you're seeing a discrepancy?

@ebalgava

This comment has been minimized.

Copy link

ebalgava commented Oct 24, 2017

diff

@calmyournerves

This comment has been minimized.

Copy link

calmyournerves commented Oct 24, 2017

Is this related to the browser time zone? It's wrong for me as well (CEST).

image

@tobbbles

This comment has been minimized.

Copy link
Contributor

tobbbles commented Oct 24, 2017

@salsakran Can you try casting to a DATE type? When grouping a timestamp by day (via casting to date) it appears Metabase interprets this as "Monday, October 23rd, 2017 12:00AM" in the tooltip, but appears as October 22nd on the X axis.

I'm guessing this is an issue surround the "12:00AM" bit.

@restyler

This comment has been minimized.

Copy link

restyler commented Dec 30, 2017

I am having the same issue where x-axis label is wrong, tooltip label is correct.
screenshot at dec 30 19-40-46

All the data is stored in UTC timestamp in mysql database.

@achesnais

This comment has been minimized.

Copy link

achesnais commented Jan 2, 2018

I've had a similar issue and it appears to come down to a discrepancy between the Reporting timezone setting and the browser time zone.

If I set my Reporting timezone to UTC, and leave my browser in UTC+2, I get the above issue

If I set my Reporting timezone to UTC+2 the discrepancy disappears.

If I set my Reporting timezone to UTC, and force my browser to use UTC, the issue disappears

If I set my Reporting timezone to UTC+2 and force my browser to use UTC, I don't get an issue on the chart, but I if I explore the data I find datums from the next month included in the current month (which is expected behaviour here, given the reporting timezone).

Based on the above, it seems the chart uses the browser timezone to render the dates on the x axis, but the value of the hover values of the dates are displayed using the reporting time zone? And I suppose the value shown when hovering over a bar is that of the earliest datum in the group? This would explain all cases above.

@TeamDKS

This comment has been minimized.

Copy link

TeamDKS commented Jan 10, 2018

@salsakran is there any news about this issue? because we have the same problem in our company. Possible workaround for others: if the order in the DB is correct it will work when you use the ordinal x-axis scale in the axis settings on the question.

@tlrobinson

This comment has been minimized.

Copy link
Member

tlrobinson commented Apr 7, 2018

@HelenMertes @gilhs @gseva @ebalgava @calmyournerves @mnzt @restyler @achesnais @TeamDKS I believe we have a fix for this in #7247. Please give it a try and let me know if it does or doesn't fix this for you (and hopefully doesn't make things worse...)

@SetSails

This comment has been minimized.

Copy link

SetSails commented Jun 19, 2018

@tlrobinson any indication on when this will be merged into a stable version? I'm actually experiencing #3642 since over a year now, tried all sorts of timezone settings and am brutally hoping for this issue to be finally fixed.

Thanks & Best Regards
F

@tlrobinson

This comment has been minimized.

Copy link
Member

tlrobinson commented Jun 19, 2018

@SetSails If you could pull down the branch in PR #7247 and validate that it fixes the issue for you that would be helpful.

@MarkRx

This comment has been minimized.

Copy link
Contributor

MarkRx commented Sep 25, 2018

@tlrobinson I can confirm that #7247 fixes the following use case:

select 
    start_month "Month",
    status,
    count(*) as "Deployments"
    from (
    select 
        xldt_task_id,
        to_timestamp(extract(month from xldt_start_dt) || '-' || extract(year from xldt_start_dt) , 'MM-YYYY') as start_month,
        xldt_duration,
        (case
            when xldt_state = 'CANCELLED' then 'ABORTED' 
            when xldt_failure_count > 0 then 'RETRIED'
            when xlddt_type = 'ROLLBACK' then 'ROLLBACK'
            when xldt_state = 'DONE' then 'SUCCESS'
        end) as status
    from mbase.deployments
    . . .
)
where start_month > sysdate - 365
group by start_month, status

Before (x-axis label is wrong):
before

After:
after

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment