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:
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
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?
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?
"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.
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.
Revisiting this older post. I was able to stand up a VM with Oracle Database 23ai and do some testing.
I haven’t figured out how to make the day move forward, but I did come up with an example to show what happens if the timezone is changed and how that could affect a DATE column and make it go backwards.
Here is an example of the problem. Copy the following into sqldeveloper and run it and observe the output. (Caution: will change the session time zone. Reset it by running alter session set time_zone = local):
alter session set time_zone = 'UTC';
select orig_date,
cast(orig_date as timestamp with time zone) as converted_to_timestamptz,
cast(orig_date as timestamp with time zone) at time zone 'America/Los_Angeles' as timestamptz_pacific,
cast((cast(orig_date as timestamp with time zone) at time zone 'America/Los_Angeles') as date) as date_pacific
from (
select to_date('2015-09-21 00:00:00', 'YYYY-MM-DD HH24:MI:SS') as orig_date from dual
) t;
What this does is change the input timezone, create a date value, translate it to another timezone, then cast it back to a date. You'll see that ORIG_DATE starts out as 21-SEP-15 but ends up in DATE_PACIFIC as 20-SEP-15.
This doesn't answer the original question, but it shows a situation where a date isn't the same date everywhere, and can change if you're not careful about type conversions. My line of questioning was trying to determine if this was the case for the OP's issue. I have a hunch that CSBASE.IC_AGG_DAY is a view that manipulates TIMESTAMP WITH TIME ZONE values, but ultimately casts its output to DATE, and that can run into problems if the time zone isn't what the code assumes it is.
