convert_timezone_explanation.md

Here is an example of the query and the expected output of convert-timezone

(mt/with-report-timezone-id "UTC"
  (mt/with-driver :postgres
    (mt/dataset test-data
      (->> (mt/run-mbql-query
             users
             {:expressions {"login_in_HCM"      [:convert-timezone [:field (mt/id :users :last_login) nil] "Asia/Ho_Chi_Minh" "Europe/Rome"]
                            "hour_login_in_HCM" [:get-hour [:expression "login_in_HCM"]]}
              :fields      [[:expression "login_in_HCM"]
                            [:expression "hour_login_in_HCM"]]
              :filter      [:= (mt/id :users :id) 1]})
           mt/rows))))


;; => ["2014-04-01T13:30:00+07:00" 13]
;; keep in mind `last_login` is a `timestamp without time zone` column
;; and the 1st row is `2014-04-01 08:30:00`

The SQL generated:

SELECT timezone('Europe/Rome', "last_login") AS "login_in_HCM",
       CAST(extract(hour
                        from timezone('Asia/Ho_Chi_Minh', 
                                      timezone('Europe/Rome', "last_login"))
                    ) AS integer
            ) AS "hour_login_in_HCM"
FROM "users"
WHERE id = 1

The query is querying 2 things:

• login_in_HCM: we wants to view the last_login column in Asia/Ho_Chi_Minh(UTC+7) and we knew before hands the column was created in Europe/Rome(UTC+2)
• hour_login_in_HCM: the hour part of login_in_HCM

Let's go into how we got the result for 2 columns?

login_in_HCM

Step	Value	Stage	Data type
1	2014-04-01 08:30:00	Stored in DB	timestamp without time zone
2	2014-04-01 06:30:00+00	After called timezone('Europe/Rome', "last_login")	timestamp with time zone
3	2014-04-01 06:30:00+00	Coming out of JDBC	OffsetDateTime
4	2014-04-01 13:30:00+70	Coming out of format-rows middleware	String
5	2014-04-01 13:30:00+70	Final result	String

At step 2, the hour shifted from 08:30:00+00:00 -> 06:30:00+00:00 because 06:30:00+00:00 = 08:30:00+02:00

At step 3, the values does not changed, because fetching timestamp without timezone columsn from JDBC will always return in report-tz.

At step 4, the hour shifted from 6 -> 13, this is where the actual timezone conversion from Europe/Rome(UTC+2) to Asia/Ho_Chi_Minh(UTC+7) is done

hour_login_in_HCM

In postgres extract(hour from timestamp with time zone) => returns the hour in the report-tz.

And remember the column type of login_in_HCM is timestamp with time zone, so if we naively extract(hour from login_in_HCM), the returned value will be 6. But we want a 13, because that is the hour in Asia/Ho_Chi_Minh.

So that why in the MBQL, we need to do another call timezone before extraction, by doing this:

• the hour will be shifted to target timezone(Asia/Ho_Chi_Minh)e
• and column type is timestamp

Step	Value	Stage	Data type
1	2014-04-01 08:30:00	Stored in DB	timestamp without time zone
2	2014-04-01 06:30:00+00	After called timezone("Europe/Rome", "last_login")	timestamp with time zone
3	2014-04-01 13:30:00	After called timezone("Asia/Ho_Chi_Minh", result-from-step2)	timestamp without time zone
4	13	After called extract hour	int
5	13	Coming out of JDBC	int
6	13	Final result	Int