-
1. Re: ModifyTimezone - valid time zones - clarification needed
shawkins Apr 2, 2018 9:25 AM (in response to cm.kumar)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.
-
2. Re: ModifyTimezone - valid time zones - clarification needed
cm.kumar Apr 2, 2018 11:07 AM (in response to shawkins)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.
-
3. Re: ModifyTimezone - valid time zones - clarification needed
shawkins Apr 2, 2018 8:36 PM (in response to cm.kumar)> 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').