Skip to Content

ToTimeZone() mvSQL database function in Jitterbit App Builder

Summary

Adjusts a date and time in UTC to the given time zone offset.

Syntax

TOTIMEZONE(<datetime>, <timezoneoffset>)

Parameters

datetime

An expression that resolves to a datetime value. The datetime value is assumed to be a UTC timestamp.

timezoneoffset

A string in the ISO offset format ±[hh]:[mm].

Returns

Returns a datetime adjusted to the given time zone.

The datetime data type stores a timestamp without a time zone offset.

Remarks

The TOTIMEZONE() function is only supported on RDBMS data providers. The following data providers have been tested:

  • DB2/LUW
  • DB2/i
  • MySQL
  • Oracle
  • PostgreSQL
  • SQL Server
  • SQLite

Examples

Literal time zone offset

Given:

  • NOW(UTC) evaluates to 2018-08-01 21:00:00.000

The expression:

TOTIMEZONE(NOW(UTC), '-04:00')

Returns 2018-08-01 17:00:00.000

Client time zone offset

Given:

  • NOW(UTC) evaluates to 2018-08-01 21:00:00.000
  • CLIENT(TIMEZONEOFFSET) evaluates to -04:00

The expression:

TOTIMEZONE(NOW(UTC), CLIENT(TIMEZONEOFFSET))

Returns 2018-08-01 17:00:00.000