Metabase running as .jar is showing correct result for date filter but in kubernetes it is showing +1 day for the date filter?

Bug reports
1

Hi,

I am facing an issue related to the filters in the metabase using the question option. when i apply filter on the date column, it is fetching the results for the +1 day of given date. I am selecting for 17march, and it is giving me the 18 march results as follow:

image
image1189×613 15.8 KB

image
image1873×829 19.2 KB

the version is : You're on version v0.55.10

But similar steps when i did in the metabase running in the localhost using .jar file, it is giving me accurate results for the 17march.

I am running the metabase in kubernetes when I am facing the this issue, while I have checked the timezone of all components including the host, oracle database, metabase pod etc.

The performed steps includes the following:

changed the timezone of metabase..
changing the timezone of oracle..
changing the timezone of jvm in metabase using value file.
changing the node timezone of running metabase pod.
changing the timezone of postgres
changing the db from postgres to h2.
upgrade to latest version v0.56.1.4

any thoughts on this topic?

Thanks

2

Hi there,

Could you build the same exact question on both and send us the SQL they’re generating (you can do View SQL on the question to do this)? Are they different SQL statements?

3

Hi,

Please find the SQLs from both:

Metabase in kubernetes:
SELECT
*
FROM
(
SELECT
TRUNC("CSBASE"."IC_AGG_DAY"."PERIOD", 'dd') "PERIOD"
FROM
"CSBASE"."IC_AGG_DAY"

WHERE
(
"CSBASE"."IC_AGG_DAY"."PERIOD" >= to_date('2025-03-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
)

AND (
"CSBASE"."IC_AGG_DAY"."PERIOD" < to_date('2025-03-18 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
)

GROUP BY
TRUNC("CSBASE"."IC_AGG_DAY"."PERIOD", 'dd')

ORDER BY
TRUNC("CSBASE"."IC_AGG_DAY"."PERIOD", 'dd') ASC
)
WHERE
rownum <= 1048575

Metabase in localhost:
SELECT
*
FROM
(
SELECT
TRUNC("CSBASE"."IC_AGG_DAY"."PERIOD", 'dd') "PERIOD"
FROM
"CSBASE"."IC_AGG_DAY"

WHERE
(
"CSBASE"."IC_AGG_DAY"."PERIOD" >= to_date('2025-03-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
)

AND (
"CSBASE"."IC_AGG_DAY"."PERIOD" < to_date('2025-03-18 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
)

GROUP BY
TRUNC("CSBASE"."IC_AGG_DAY"."PERIOD", 'dd')

ORDER BY
TRUNC("CSBASE"."IC_AGG_DAY"."PERIOD", 'dd') ASC
)
WHERE
rownum <= 1048575

These are actually same.

4

This query is being run on Oracle? What version of Oracle?

5

Oracle version is 19c

6

I think we’re still trying to determine if the issue is due to timezone weirdness.

What data type is this column?

If you run the following query in a SQL Query question, it will return the timezone offset of the session. How does it compare to the timezone setting in Metabase?

SELECT SESSIONTIMEZONE FROM DUAL;

Have you tried running the SQL in your earlier post in sqlplus or sqldeveloper and seen if it returns a different result?

7

"CSBASE"."IC_AGG_DAY"."PERIOD"
It has data_type = DATE

SELECT SESSIONTIMEZONE FROM DUAL;

If i run the above sql from metabase, it is showing the TZ of metabase pod. And similarly, if I run from the sql developer, it is showing my system TZ.

The above sql when I run from the sql-developer it is showing my accurate date. also accurate date when i use sql query option in the metabase, but +1 day, when i use question option in metabase.

8

It’s only wrong if run from the notebook editor? That’s curious.

I take it that the timezone setting is different between your local install and the k8s pod? How different?

If you look at the table metadata in Admin, what semantic type is assigned to that PERIOD column?

It would be better if Metabase didn’t use a timestamp to create a date from, since that invokes timezone translation. I wonder if its possible to convince it to use bare dates instead.