time filter applied twice when applying filter from dashboard

[Keveen-ghori]

Bug description

Dataset:

SELECT mcdd.meter_id,
       mcdd.bucket,
       mcdd.meter_channel_id, mcdd.status,
                                    mcdd.nem12_status,
                                    mcdd.meter_type,
                                    mcdd.channel_type,
                                    mcdd.unit_of_measurement,
                                    mcdd.meter_property_name,
                                    mcdd.customer_name,
                                    mcdd.site_name,
                                    mcdd.physical_time_zone,
                                    mcdd.channel_name,
                                    CASE
									    WHEN mcdd.channel_type = 'Interval' THEN mcdd.total_value
									    WHEN mcdd.channel_type = 'Cumulative' THEN
									    COALESCE(
									    (mcdd.total_value - COALESCE(
										    LAG(mcdd.total_value) OVER (PARTITION BY mcdd.meter_id, mcdd.meter_channel_id ORDER BY mcdd.bucket),
										    0
									    ))::double precision, 0)
									    ELSE mcdd.total_value
									    END as calculated_value,
									    mcdd.average_value,
									    mcdd.minimum_value,
									    mcdd.maximum_value
FROM west_Side_place_meter_channel_data_daily as mcdd
JOIN Dashboard_Meter_Type_Channel_Configuration as dmtcc ON dmtcc.channel_name = mcdd.channel_name AND dmtcc.meter_type = mcdd.meter_type AND dmtcc.unit_of_measurement = mcdd.unit_of_measurement
WHERE mcdd.bucket >= '{{ from_dttm }}' at time zone'Australia/Brisbane' AND mcdd.bucket < '{{ to_dttm }}' at time zone'Australia/Brisbane' AND  dmtcc.meter_type = 'Electricity';

Filter:

Actual Query:

I don't want to apply date filter two times. There should be only one date filter inside virtual table. How can i achieve that kind of functionality?

Screenshots/recordings

No response

Superset version

master / latest-dev

Python version

3.9

Node version

16

Browser

Chrome

Additional context

No response

Checklist

• I have searched Superset docs and Slack and didn't find a solution to my problem.
• I have searched the GitHub issue tracker and didn't find a similar bug report.
• I have checked Superset's logs for errors and if I found a relevant Python stacktrace, I included it here as text in the "additional context" section.

[dosubot]

A similar issue was previously reported and resolved. The solution involves using the get_time_filter macro with the remove_filter parameter set to True. This approach ensures that the time filter is applied only within the virtual dataset and not on the outer query. Here's an example of how you can implement this:

{% set time_filter = get_time_filter("bucket", remove_filter=True) %}
{% set from_expr = time_filter.from_expr %}
{% set to_expr = time_filter.to_expr %}
{% set time_range = time_filter.time_range %}
SELECT
  *,
  '{{ time_range }}' as time_range
FROM west_Side_place_meter_channel_data_daily as mcdd
JOIN Dashboard_Meter_Type_Channel_Configuration as dmtcc 
ON dmtcc.channel_name = mcdd.channel_name 
AND dmtcc.meter_type = mcdd.meter_type 
AND dmtcc.unit_of_measurement = mcdd.unit_of_measurement
WHERE dmtcc.meter_type = 'Electricity'
{% if from_expr or to_expr %}AND 1 = 1
{% if from_expr %}AND mcdd.bucket >= {{ from_expr }}{% endif %}
{% if to_expr %}AND mcdd.bucket < {{ to_expr }}{% endif %}
{% endif %}

This configuration will apply the time filter only within the virtual dataset and prevent it from being applied again in the outer query ^[1].

To continue talking to Dosu, mention @dosu.

---

^{Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Other}

[Keveen-ghori]

@dosu What is bucket in your provided query?

[dosubot]

In the provided query, bucket refers to a column in the west_Side_place_meter_channel_data_daily table. It likely represents a timestamp or a time period used for filtering data based on the specified time range. This column is used to apply the time filter conditions, such as mcdd.bucket >= {{ from_expr }} and mcdd.bucket < {{ to_expr }}, to ensure that the data is filtered according to the desired date range.

To continue talking to Dosu, mention @dosu.

---

^{Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Other}

[villebro]

@Keveen-ghori do note that get_time_filter is not yet available in any released version of Superset (it will be available in 5.0).

[sonfire186]

any released version of Superset

The documentation says that it has already been implemented

[saurabhsatra]

@Keveen-ghori were you able to find any workaround for this? I am also facing the same issue.

[rusackas]

The documentation says that it has already been implemented

Our documentation is not yet versioned, but it will be before long (it's something I'm working on).

Are you able to test the 5.0.0 release candidate(s) to see if they do indeed resolve this?