ModifyTimezone - valid time zones - clarification needed

[cm.kumar]

Hello,

I'm trying to utilize the ModifyTimeZone function from teiid. I'm trying to pass the input from UI generated to get the date time converted to local time, like say for example,

India Time zone (IST - GMT+5:30). The data in the DB is in UTC.

So I provide like MODIFYTIMEZONE(datetime, 'UTC', 'GMT+5:30') , but the dates converted was not as expected say, for UTC date, 2018-03-26 00:00:30, it provides like 2018-03-25 18:30:30.

But actually i was expecting something like 2018-03-26 05:30:30.

If i provide as GMT-5:30 as input like MODIFYTIMEZONE(datetime, 'UTC', 'GMT-5:30'), the result obtained is perfect.

Is it something i'm missing or have to be handled from my side to have - instead of + and vice-versa when mentioning the GMT?

[shawkins]

You must be careful as to where the timestamp values are rendering and whether any calendar differences have already been accounted for.

For example if you have MODIFYTIMEZONE(parseTimestamp('2018-03-26 00:00:30 UTC', 'yyyy-MM-dd hh:mm:ss Z'), 'UTC', 'GMT+5:30'), that logically results in "2018-03-26 00:00:30 GMT+5:30".  It matters where that is being rendered - if you are on the server with timezone GMT-5, then "2018-03-26 00:00:30 GMT+5:30" would be displayed as "2018-03-25 13:30:30.0" as that is matching calendar display in GMT-5.

[cm.kumar]

Thanks Steven.

Three questions to get myself cleared, I'm not totally understanding this,

1. Does it mean, i have to find the server timezone and apply that in from time zone to get my desired timezone?

          a. Is there a function in teiid which returns server time zone?

2. My requirement is that my data in database (server) is already in UTC time zone. My Teiid driver or my VDB server as well is in, UTC time zone. So if I use MODIFYTIMEZONE(timedata, 'UTC', 'GMT+5:30') should return time with respect to IST date time, right?

3. Actually if I use GMT-5:30, instead of GMT+5:30 it is returning what I'm expecting or date in IST timezone.

[shawkins]

> Does it mean, i have to find the server timezone and apply that in from time zone to get my desired timezone?

No, but there is a two argument form modifytimezone that behaves that way.  The function is just looking at the relative difference between the start and end time zones.

> Is there a function in teiid which returns server time zone?

Not currently.

> My requirement is that my data in database (server) is already in UTC time zone. My Teiid driver or my VDB server as well is in, UTC time zone. So if I use MODIFYTIMEZONE(timedata, 'UTC', 'GMT+5:30') should return time with respect to IST date time, right?

Teiid treats the timestamp type similar to a timestamp without time zone type.  When you create a timestamp value, it is effectively in the server time zone - both the Teiid client and the fetches from the source can account for discrepancies in the respective time zones.

You are not actually creating a timestamp with time zone value - note that no timezone is indicated with the calendar fields when you render the timestamp.  When you see the result '2018-03-25 18:30:30' with the server timezone of UTC, that is actually '2018-03-25 18:30:30 UTC'.

> Actually if I use GMT-5:30, instead of GMT+5:30 it is returning what I'm expecting or date in IST timezone.

You are starting with '2018-03-26 00:00:30 UTC' and using modifytimezone to create '2018-03-26 00:00:30 GMT+5:30'. '2018-03-26 00:00:30 GMT+5:30' displayed in the server UTC time zone is ''2018-03-25 18:30:30''.  Since it seems like you want to end up with '2018-03-26 05:30:30 UTC', you should instead reverse the time zone arguments - modifytimezone(datetime, 'GMT+5:30', 'UTC').