Single day filter includes results for previous day - how to fix?

Get Help
1

Good day,
A couple of weeks into Metabase research and testing, and busy putting together a demo for the physicians in our hospital. Must say, I’m amazed at the features and also the active community :+1:
On to my problem - I just ran into an issue now where I want to know the # of visits for a specific day, so I apply a filter for that day but the output chart shows me TWO bars - one for the day I selected in the filter, and another bar showing a small total for the PREVIOUS day as well.
Looking closer at the filtered data, it seems that I get records for the first approx 20 hours of the selected date, and records for the last approx 4 hours of the previous date. So, an offset of 4 hours, made me think of an issue with time zone? Perhaps a time zone mismatch somewhere in the system?
I checked the timezones as follows:

  1. server which is hosting the report data in SQL: Windows timezone is GMT+4
  2. SQL installation on that server: SYSDATETIMEOFFSET() ends with ‘+4’, so looks like also GMT+4
  3. Metabase server which is running Metabase + mySQL: Windows timezone is GMT+4
  4. mySQL installation on that server: ‘SELECT @@global.time_zone’ returns SYSTEM, so should be same as GMT+4
  5. report timezone in Metabase Admin is not selected, so should default to system time zone which is also GMT+4
  6. my workstation: Windows timezone is GMT+4
    … (yes, I’m in the UAE :grin: )
    So, I can’t find a time zone discrepancy anywhere. Unless I’m checking wrong and/or there is another place where I didn’t look.
    FWIW, I tried the same thing with Metabase running on the native H2 database and I get the same result.
    Please help.
    Regards,
    chrisKH
{
  "browser-info": {
    "language": "en-US",
    "platform": "Win32",
    "userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/83.0.4103.61 Safari/537.36",
    "vendor": "Google Inc."
  }
}
2

Hi @chrisKH
Please post the full “Diagnostic Info”.
And where are you running the queries? You need to run them through Metabase, so it’s using the session information from that connection.

3

Hi flamber,
Pls see below diagnostic info.
Not sure about ‘where’ the queries are running. I mean I’m just building and running dashboards as an admin user on Metabase. Data source is a SQL2016 server. :man_shrugging:

{
“browser-info”: {
“language”: “en-US”,
“platform”: “Win32”,
“userAgent”: “Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/83.0.4103.116 Safari/537.36”,
“vendor”: “Google Inc.”
},
“system-info”: {
“file.encoding”: “Cp1252”,
“java.runtime.name”: “OpenJDK Runtime Environment”,
“java.runtime.version”: “11.0.7+10”,
“java.vendor”: “AdoptOpenJDK”,
“java.vendor.url”: “https://adoptopenjdk.net/”,
“java.version”: “11.0.7”,
“java.vm.name”: “OpenJDK 64-Bit Server VM”,
“java.vm.version”: “11.0.7+10”,
“os.name”: “Windows Server 2012 R2”,
“os.version”: “6.3”,
“user.language”: “en”,
“user.timezone”: “Asia/Muscat”
},
“metabase-info”: {
“databases”: [
“h2”,
“sqlserver”
],
“hosting-env”: “unknown”,
“application-database”: “mysql”,
“application-database-details”: {
“database”: {
“name”: “MySQL”,
“version”: “8.0.18”
},
“jdbc-driver”: {
“name”: “MariaDB Connector/J”,
“version”: “2.5.1”
}
},
“run-mode”: “prod”,
“version”: {
“date”: “2020-05-28”,
“tag”: “v0.35.4”,
“branch”: “release-0.35.x”,
“hash”: “b3080fa”
},
“settings”: {
“report-timezone”: null
}
}
}

4

@chrisKH I have a feeling that you’re seeing this issue:
https://github.com/metabase/metabase/issues/10824 - upvote by clicking :+1: on the first post

5

Hello crisKH,
Show the screen with step-to-step to see if we have like understand better.
I guess that there is no problem with timezone from your server.

Best regards,

6

Thank you edimar,
Thank you flamber,
Screenshot of what I'm seeing is below:

metabase%20visits%20per%20day
metabase%20visits%20per%20day1218×473 25.8 KB

The visits that are showing for 31 May are taking place after 8PM i.e. last 4 hours. Visits counted for 1 June are only up to 8PM.
My time zone is GMT+4 and so I'm sure that '+4' factor is screwing it up somewhere.
FWIW, I found another place to edit time zone and that is in php itself (I'm running mySQL using WAMP). In the php.ini the time zone was set to "UTC". Changed it to "Asia/Dubai" and restarted WAMP services but no difference.
Are there any workarounds? E.g. suppose I separate the visit date and the visit time, and then filter on the date part only?
Regards,
chrisKH

7

@chrisKH
It has nothing to do with PHP, and probably nothing to do with MySQL.
Have you tried to define the Reporting Timezone in Metabase?
Is this a Native question or Simple/Custom question?
What is the database column type of visit_start?

8

@chrisKH see the questions from @flamber and if possible check also the date_format, you can exclude the time stay just the date.

Best regards,

9

Hi again,
Was able to get around it. Your questions pointed me in the right direction.
Turns out the date's format in the original SQL database was 'datetime'. Nothing wrong with that, but there is a better one to use which is 'datetimeoffset' which is the exact same but with the # of hours offset from UTC at the end. To get it to this format use AT TIME ZONE on the the original visit_start field and 'Arabian Standard Time' in my case,

ALTER TABLE [dbo].[visits_table] ADD [visit_start_offset] AS ([visit_start] AT TIME ZONE 'Arabian Standard Time')

This had the following effect,

image

And Metabase seems to know what to do with it,

metabase%20visits%20per%20day%20-%20fixed
metabase%20visits%20per%20day%20-%20fixed1057×508 25.8 KB

:+1::+1::+1::sweat_smile:

Thanks once again and regards,
chrisKH

2 Likes