Database data is UTC and need data to be presented with +8 hours

[pvanthony]

We have booking datetime data that is recorded in the database as UTC.
We need the booking datetime data to show at local time of +8, Singapore.
The data is visualized in table mode.

Unfortunately the data presented is of by 8 hours which is the UTC. Need datetime data to be presented in +8 Singapore time.

In the admin > settings > localization > report timezone has been set to Asia/Singapore.
The centos 8 server is set to Asia/Singapore.

Is there some other settings to be done?

Metabase version 0.39.3

[flamber]

Hi @pvanthony
Post "Diagnostic Info" from Admin > Troubleshooting.
And which database you are querying.
And what the actual database column type.

Timezone issues are some of the most complicated, so you need to provide very specific information - helped with actual queries+results or screenshots showing the problem.

[pvanthony]

Thank you very much for the quick reply. Here are the data requested.

The database is postgresql96-server-9.6.21-1PGDG
The database column type is timestamp.

Here is the sql created by metabase from the sql button.

SELECT "public"."sale_order_line"."id" AS "id", "public"."sale_order_line"."product_uom_qty" AS "product_uom_qty", "public"."sale_order_line"."company_id" AS "company_id", "public"."sale_order_line"."name" AS "name", "public"."sale_order_line"."product_id" AS "product_id", "public"."sale_order_line"."booking_end" AS "booking_end", "public"."sale_order_line"."booking_start" AS "booking_start", "public"."sale_order_line"."booking_state" AS "booking_state", "public"."sale_order_line"."price_subtotal_company_currency" AS "price_subtotal_company_currency"
FROM "public"."sale_order_line"
LEFT JOIN "public"."product_product" "product_product__via__product_" ON "public"."sale_order_line"."product_id" = "product_product__via__product_"."id"
WHERE ("public"."sale_order_line"."company_id" = 4
   AND "public"."sale_order_line"."booking_state" = 'consumed' AND "public"."sale_order_line"."booking_start" >= date_trunc('week', CAST((CAST(now() AS timestamp) + (INTERVAL '-1 week')) AS timestamp)) AND "public"."sale_order_line"."booking_start" < date_trunc('week', CAST(now() AS timestamp)) AND (NOT (lower("product_product__via__product_"."name_template") like '%coach%')
    OR "product_product__via__product_"."name_template" IS NULL) AND (NOT (lower("product_product__via__product_"."name_template") like '%ice%') OR "product_product__via__product_"."name_template" IS NULL) AND (NOT (lower("product_product__via__product_"."name_template") like '%ball%') OR "product_product__via__product_"."name_template" IS NULL) AND (NOT (lower("product_product__via__product_"."name_template") like '%cleaning%') OR "product_product__via__product_"."name_template" IS NULL) AND (NOT (lower("product_product__via__product_"."name_template") like '%maintenance%') OR "product_product__via__product_"."name_template" IS NULL))
LIMIT 1048575 

Here is the screenshot of the question.

Here is the screenshot of the result. The datetime is in UTC. Need it to be +8

Here is the diagnostic info.

{
  "browser-info": {
    "language": "en-US",
    "platform": "Linux x86_64",
    "userAgent": "Mozilla/5.0 (X11; Fedora; Linux x86_64; rv:88.0) Gecko/20100101 Firefox/88.0",
    "vendor": ""
  },
  "system-info": {
    "file.encoding": "UTF-8",
    "java.runtime.name": "OpenJDK Runtime Environment",
    "java.runtime.version": "11.0.11+9-LTS",
    "java.vendor": "Red Hat, Inc.",
    "java.vendor.url": "https://www.redhat.com/",
    "java.version": "11.0.11",
    "java.vm.name": "OpenJDK 64-Bit Server VM",
    "java.vm.version": "11.0.11+9-LTS",
    "os.name": "Linux",
    "os.version": "4.18.0-305.3.1.el8.x86_64",
    "user.language": "en",
    "user.timezone": "Asia/Singapore"
  },
  "metabase-info": {
    "databases": [
      "postgres",
      "h2"
    ],
    "hosting-env": "unknown",
    "application-database": "h2",
    "application-database-details": {
      "database": {
        "name": "H2",
        "version": "1.4.197 (2018-03-18)"
      },
      "jdbc-driver": {
        "name": "H2 JDBC Driver",
        "version": "1.4.197 (2018-03-18)"
      }
    },
    "run-mode": "prod",
    "version": {
      "date": "2021-05-27",
      "tag": "v0.39.3",
      "branch": "release-x.39.x",
      "hash": "598a112"
    },
    "settings": {
      "report-timezone": "Asia/Singapore"
    }
  }
}

[flamber]

@pvanthony Okay, so when columns are timestamp (withOUT time zone), then Metabase sees them as UTC (since that's what Postgres reports), but if they are timestamptz (with timezone), then they're handled accordingly.
See more in these two issues:
https://github.com/metabase/metabase/issues/6259
https://github.com/metabase/metabase/issues/13400

You would have to create a View on Postgres, which does the conversion, so Metabase just has that "table" to work with.

[pvanthony]

Thank you very much for the advice. I do not know how to do views but I will learn. Will search internet on postgresql views.

Thank you again for helping. I do appreciate it.

[pvanthony]

Just for others like me(newbie) here's how the view was created for my use case.

For my case, the database column for the timestamp did not say which timezone it was set in. So I am guessing metabase did not want to assume that it was UTC. So we have to create view with those columns timezone set to UTC. Then metabase will know the timezone of the data. Metabase will use the localization setting for reports in metabase to convert the timestamp to the correct needed timezone. Which in my case was +8 Singapore.

CREATE VIEW name_of_the_view AS SELECT product_uom_qty,invoiced,company_id,name,product_id,booking_end AT TIME ZONE 'UTC' AS booking_end,booking_start AT TIME ZONE 'UTC' AS booking_start,pitch_id,booking_state,price_subtotal_company_currency FROM sale_order_line;

Then in metabase use the new view like a table in the database.

Wonder if there is a feature in metabase where we can set the timezone for table columns.

Here are the links that I learned about creating views and about timezone setting in postgresql.

https://www.guru99.com/postgresql-view.html

PostgreSQL date() with timezone

Once again thank you to @flamber for the advice. It was really helpful in solving the problem.

[flamber]

@pvanthony Awesome that you did a quick write-up for others who sees this - sharing is caring

We are planning working on much more granular timezone handling than the current Report Timezone, which isn't helpful in situations where databases are might be in uncontrollable difference timezones, or some columns have different timezones, or different users needs different timezones.
This is a very complicated change, so it will take time, but it would open up a lot of possibilities.
More technical info if you are interested - upvote by clicking on the first post
https://github.com/metabase/metabase/issues/14056
https://github.com/metabase/metabase/issues/6439

[pvanthony]

Thank you the reply with the plans for the future. Looks great.