When filter date field using specific date, data filter for a day before.
Itâs timezones. Plenty of information in the forum, but there are some outstanding issues.
One of the main bug themes weâve been working on the last two weeks is time zones. Look for a number of fixes in this area in our next update.
@himanshu84
That looks similar to this issue: https://github.com/metabase/metabase/issues/10824
But without knowing the answer to the 6 points in the timezone troubleshooting guide, then itâs difficult to know for sure:
https://www.metabase.com/docs/latest/troubleshooting-guide/timezones.html
Also, could you post the Diagnostic Info from Admin > Troubleshooting?
Hello,
We also have this problem with 0.33.4 on SQL Server. We use DateTime field on our database. We donât use DateTime2 type which includes TimeZone info.
Metabase generates query like this when we use âSingle Dateâ as parameter on our dashboard.
"RECORD_ADD_DATE" >= @P1 AND "dbo"."LD_BI_215_HB_QC_RESULT"."RECORD_ADD_DATE" < @P2
And generated parameters are (when I select 2019.11.06)
@P1 datetime2,@P2 datetime2â,â2019-11-05 03:00:00â,â2019-11-06 03:00:00â
It should be something like 2019-11-06 00:00:00 AND 2019-11-07 00:00:00
Also MB casts our datetime field as datetime2.
Iâve tried to change the Report Timezone in the settings as âDefault Databaseâ, âEuropa/Istanbulâ, âEurope/Moscowâ, âGMTâ, âUTCâ, âEurope/Londonâ but we canât get expected results. Sometimes it generates query as
â2019-11-05 03:00:00â,â2019-11-06 03:00:00â
and some other times as
â2019-11-06 03:00:00â,â2019-11-07 03:00:00â
Weâve also started the MB with -Duser.timezone=Asia/Istanbul parameter. Weâve tried it with -Duser.language=en_US and -Duser.language=tr_TR parameters but nothing changed on each test.
We have solved the problem by
DATEADD(HOUR, -21, RECORD_ADD_DATE) AS RECORD_ADD_DATE
for the time being.
Our diagnostic info is here https://hastebin.com/fojipoxehu.json
{
âbrowser-infoâ: {
âlanguageâ: âen-USâ,
âplatformâ: âWin32â,
âuserAgentâ: âMozilla/5.0 (Windows NT 6.1; Win64; x64; rv:70.0) Gecko/20100101 Firefox/70.0â,
âvendorâ: ââ
},
âsystem-infoâ: {
âjava.runtime.nameâ: âJava⢠SE Runtime Environmentâ,
âjava.runtime.versionâ: â1.8.0_161-b12â,
âjava.vendorâ: âOracle Corporationâ,
âjava.vendor.urlâ: âhttp://java.oracle.com/â,
âjava.versionâ: â1.8.0_161â,
âjava.vm.nameâ: âJava HotSpot⢠64-Bit Server VMâ,
âjava.vm.versionâ: â25.161-b12â,
âos.nameâ: âWindows Server 2012 R2â,
âos.versionâ: â6.3â,
âuser.languageâ: âtr_TRâ,
âuser.timezoneâ: âAsia/Istanbulâ
},
âmetabase-infoâ: {
âdatabasesâ: [
âsqlserverâ
],
âhosting-envâ: âunknownâ,
âapplication-databaseâ: âh2â,
ârun-modeâ: âprodâ,
âversionâ: {
âtagâ: âv0.33.4â,
âdateâ: â2019-10-07â,
âbranchâ: ârelease-0.33.xâ,
âhashâ: â9559406â
},
âsettingsâ: {
âreport-timezoneâ: âEurope/Istanbulâ
}
}
}
PS: Java has a time zone id as Asia/Istanbul but MB has a Europe/Istanbul.
PS2: When we use Date filter with âAll Optionsâ, MB generates this query for âCurrent Dayâ
CAST(CAST("dbo"."LD_BI_215_HB_QC_RESULT"."RECORD_ADD_DATE" AS date) AS datetime) = CAST(CAST(getdate() AS date) AS datetime))
which generates the correct result but any other option still doesnât work as expected.
I'm facing the same issue here with 0.33.4
version.
But we noticed that the error occurs only when the date filtered is after november 4th. Curiously this date should be the first day of the summertime in Brazil, that was cancelled. If it was not cancelled, now hour time would be 1 hour increased.
The behaviour is: when choosing a specif date or a date range as parameter, the front-end submit it correctly.
But when this parameter came to the database to execute the query, it's 1 hour decreased. So, following the examples above, 2019-11-04 00:00:00
becomes 2019-03-03 23:00:00
.
The error occurs event with different databases: SQL Server, Postgres and MySQL.
I tried to change Report Timezone, but the results are the same.
The metabase server timezone is correct: Thu Nov 7 11:13:29 -03 2019
.
Does anyone have any idea about what is causing this problem?
Curiously when selecting november 3th, the paramater is set to NULL at the query. The summertime would start at midnight from november 3th to 4th.
Another curious fact: when running local metabase docker instance, the problem does't occurs.
Should we open a github issue? Or upvote an issue?
@turker.tunali Iâm fairly sure youâre seeing issue #10824, but thereâs several other timezone issues, so you might want to look through some of them too (upvote by clicking on the first post of an issue).
@jnascimento If the problem just started, then make sure that all software is updated. Metabase does not contain timezone information, but your OS, Java, database usually holds this information.
Seems like thereâs an issue open on this: https://github.com/metabase/metabase/issues/11261
The problem was solved after set the Metabase server timezone from âAmerica/Sao_Pauloâ to UTC.
@jnascimento Okay, then it very well could be that the tzdata
of your OS/Java/etc has not been updated to the latest version.
This has been resolved after changing the keeping same timezone in Metabase Server machine as well as Metabase settings.