Date/hour with timezone not changed using localisation from admin panel

Bug reports
1

Hello,
We convert our data from timestamp to timestampz (with timezone) in order to be able to use the localisation parameter and to have the hour displayed in the required timezone.
It is working well except for the timestamp stored inside views.
In our database, we have tables in public schema and we also have views in the public schema. The views contains deserialized data from some tables. One of the view contain timestamp data with column type timestampz.
When I do a question and display data from this view, my timestamp data are always in UTC even when I set my local to europe/paris.

Is there a problem with timezone and views ? Are you aware of such a problem ?
How can I solve it on my side ? Do you plan to fix it ?

Thanks for the help.

2

There shouldn’t be any problem, please post diagnostic info

3

Hi,

OK, below is my diagnostic info:

{
  "browser-info": {
    "language": "fr-FR",
    "platform": "Win32",
    "userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36",
    "vendor": "Google Inc."
  },
  "system-info": {
    "file.encoding": "UTF-8",
    "java.runtime.name": "OpenJDK Runtime Environment",
    "java.runtime.version": "11.0.18+10-LTS",
    "java.vendor": "Red Hat, Inc.",
    "java.vendor.url": "https://www.redhat.com/",
    "java.version": "11.0.18",
    "java.vm.name": "OpenJDK 64-Bit Server VM",
    "java.vm.version": "11.0.18+10-LTS",
    "os.name": "Linux",
    "os.version": "4.18.0-425.3.1.el8.x86_64",
    "user.language": "fr",
    "user.timezone": "Europe/Paris"
  },
  "metabase-info": {
    "databases": [
      "postgres"
    ],
    "hosting-env": "unknown",
    "application-database": "postgres",
    "application-database-details": {
      "database": {
        "name": "PostgreSQL",
        "version": "11.19"
      },
      "jdbc-driver": {
        "name": "PostgreSQL JDBC Driver",
        "version": "42.5.0"
      }
    },
    "run-mode": "prod",
    "version": {
      "date": "2023-02-19",
      "tag": "v0.45.3",
      "branch": "release-x.45.x",
      "hash": "070f57b"
    },
    "settings": {
      "report-timezone": "Europe/Moscow"
    }
  }
}

In the database, I have schema public with tables and view like below:

image

All timestamp from tables are timestamp with timezone (timestampz).
In the view surgery_person_attendance_time, I have a column timestamp with timezone (timestampz).

I do a question requesting data from tables and from view. And only timestamp from tables changes depending on the local parameter from admin:

image

Thanks for the help.
Regards

4

is it possible you're seeing Postgres not showing correct time in native queries due to timezone adjustments · Issue #31518 · metabase/metabase · GitHub?

5

Hi,

Unfortunately, it is not. My parameter is not a date but already a timestampz.
Moreover the issue is only in views and not in the tables.
Finally when a I do a question I have the hour from the data in the view but always in UTC (As the DB is in utc and we register hour in utc). It never changes to the local timezone chosen in database admin parameter.

6

Hello,

Any update on my issue about the timezone not take into account when I query data into views ?

7

Please post the ddl of the view

8

Hello,

Below is the definition of the table from schema public of our database and also the view inherited from the table:

create table public.sp
(
id uuid default uuid_generate_v4() not null primary key,
provid uuid not null,
surgid uuid not null,
role_group_type varchar(255) not null,
role_group_name varchar(255) not null,
first_name varchar(255),
last_name varchar(255),
gef_code varchar(255),
role varchar(255),
overridden_role varchar(255),
gam_code varchar(255),
adeli_code varchar(9),
rpps_code varchar(11),
billing_identifier varchar(255),
is_main boolean not null,
created timestamp not null,
modified timestamp not null,
attendance_times jsonb
);

create index ix_sp_surgid
on public.sp (surgid);

create index ix_sp_provid
on public.sp (provid);

create index ix_sp_provid_surgid
on public.sp (provid, surgid);

create index ix_sp_firstname
on public.sp (first_name);

create index ix_sp_lastname
on public.sp (last_name);

create index ix_sp_adelicode
on public.sp (adeli_code);

create index ix_sp_rppscode
on public.sp (rpps_code);

create index ix_sp_isoperator
on public.sp (role_group_type)
where ((role_group_type)::text = 'OPERATOR'::text);

create index ix_sp_ismain
on public.sp (is_main)
where (is_main = true);

create index ix_sp_isanesthetist
on public.sp (role_group_type)
where ((role_group_type)::text = 'ANESTHETIST'::text);

create index ix_sp_iscoordinator
on public.sp (role_group_type)
where ((role_group_type)::text = 'COORDINATOR'::text);

create index ix_sp_attendancetimes
on public.sp using gin (attendance_times);

create index ix_sp_rolegrouptype
on public.sp (role_group_type);

create view public.sp_attendance_time
(provid, surgid, sp_attendance_time_id, begin_date_time, end_date_time) as
SELECT sp.provid,
sp.surgid,
(spat.value ->> 'resourceId'::text)::uuid AS sp_attendance_time_id,
(spat.value ->> 'beginDateTime'::text)::timestamp WITH TIME ZONE AS begin_date_time,
(spat.value ->> 'endDateTime'::text)::timestamp WITH TIME ZONE AS end_date_time
FROM sp sp,
LATERAL JSONB_ARRAY_ELEMENTS(sp.attendance_times) spat(value);

Thanks for the help