When filter date field using specific date, data filter for a day before

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.

1 Like

@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 :+1: on the first post of an issue).

2 Likes

@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

1 Like

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.