Timezone setting is not applied on filters

Report Bugs
1

Hi,

Our database is set in a different timezone than Holistics. We used the datasource timezone setting in Holistics to deal with this but apparently the timezone setting is correctly applied on the data shown in the charts but not on the filters.

Holistics produced this query that shows the problem :

SELECT
  TO_CHAR((CONVERT_TIMEZONE('Europe/Paris', CAST ( CAST ( CONVERT_TIMEZONE('Europe/Paris', 'America/Los_Angeles', DATE_TRUNC ( 'day', CONVERT_TIMEZONE('America/Los_Angeles', 'Europe/Paris', CAST ( "models.fct_tra_visites"."TVS_DEBUT_VISITE_DT" AS TIMESTAMP_LTZ )) )) AS TIMESTAMP_LTZ ) AS TIMESTAMP_LTZ ))), 'YYYY-MM-DD HH24:MI:SS.FF6') AS "dd_mtv_tdvd_841121__0",
  COUNT(DISTINCT "models.fct_tra_visites"."TVS_VISITEUR_ID") AS "c_mtv_nvu_8e95f3__1",
  COUNT(DISTINCT "models.fct_tra_visites"."TVS_UTILISATEUR_ID") AS "c_mtv_nuu_34be9d__2",
  CAST ( NULL AS TEXT ) AS "h__placeholder_marker_0",
  0 AS "h__model_level"
FROM
  "models.fct_tra_visites"
  LEFT JOIN "models.dim_tra_infos_sites" ON "models.fct_tra_visites"."TVS_SITE_ID" = "models.dim_tra_infos_sites"."TIS_SITE_ID"
WHERE
  (
    ("models.fct_tra_visites"."TVS_DEBUT_VISITE_DT" >= CAST ( '2022-11-01T00:00:00.000+01:00' AS TIMESTAMP_LTZ )) AND
    ("models.fct_tra_visites"."TVS_DEBUT_VISITE_DT" < CAST ( '2022-12-02T00:00:00.000+01:00' AS TIMESTAMP_LTZ ))
  )
GROUP BY
  1
ORDER BY
  5 DESC

We can see that the convert_timezone function is used in the SELECT part of the query but not in the WHERE part.

Is it normal ?

2

Hi @dacou,

Thank you for your reporting.

In the SELECT part, your data need to be in the desired timezone to make sure the result is correct after some processing, like date trunc. Thus, the query needs to use CONVERT_TIMEZONE function to convert your datetime column.
But in the WHERE part, the filter values are already in Europe/Paris (UTC+1), e.g., 2022-11-01T00:00:00.000+01:00 and 2022-12-02T00:00:00.000+01:00. So your database will implicitly convert it to the database timezone to compare with your column values.
If we use the CONVERT_TIMEZONE on your column "models.fct_tra_visites"."TVS_DEBUT_VISITE_DT" to Europe/Paris, the cut-off time remains the same, the behavior will be the same, but the performance might be worse.

To sum up, this behavior is normal when processing the timezone in the filter.

Hope this explains your concerns.

1 Like