Adjust by Timezone | TIMEZONE

Syntax

TIMEZONE( [value = CURRENT_DATE()], [timezone = "UTC"], [invert = false] )

Parameters

value
A DATE value. If the value is not a DATE, it will be converted to one with the DATE operation.

timezone
A timezone value as a TEXT. A list of valid timezone values may be found at https://en.wikipedia.org/wiki/List_of_tz_database_time_zones.

invert
A BOOLEAN value representing whether the offset is going from UTC to the provided timezone or in reverse. Alternatively, the alias TEXT values "FROM_UTC" and "TO_UTC" may be used for FALSE and TRUE, respectively.

Return Value

Returns the UTC month value of the VALUE as an INTEGER

Description

Adjusts the value of a DATE using a timezone. InstaLink stores all date values as UTC DATETIME values. However, many external systems record date time values in some local time rather than in UTC time. This operation enables a local date value to be reconciled with InstaLink's date format. For example, an external system in Utah (America/Denver timezone) may store their date values in local time. If InstaLink were to receive the value "2020-08-15T12:00:00", it would assume the value to be UTC which would be six or seven hours different than the actual time (depending on whether daylight savings was in effect). The TIMEZONE operation can be used to correct this by adjusting the value of the DATE based on the actual timezone of the received date.

Examples

TIMEZONE("December 1, 2018 00:00:00 GMT+00:00", "America/Denver")
# returns DATE("November 30, 2018 17:00:00 GMT+00:00")

TIMEZONE("July 1, 2019 00:00:00 GMT+00:00", "America/Denver")
# returns DATE("June 30, 2019 18:00:00 GMT+00:00")

TIMEZONE("December 1, 2018 00:00:00 GMT+00:00", "America/Denver", true)
# returns DATE("December 1, 2018 07:00:00 GMT+00:00")