Timezone Calculation

[#1]

Hi All,

We have a table with all time zones available (like PST, IST, GMT, EST etc).

We have a fact data with multiple vendors sitting in different places all over the world. And we have a time offset of all vendors in a vendor dimension table. Like +5.30 to IST and -8 hours to PST etc.

Can we create a new field where dates should be populated based on Vendor local time?

Anyway to calculate and add this in QVD with respective to the time offset? And how can we manage timeday saving time?

Is it good if this handle at ETL side? We are thinking, but as of now we need to handle this at QV level.

Thanks.

[avinashelite]

We have a functionConvertToLocalTime using which you can easily convert time in various timezone to your required format

Eg:ConvertToLocalTime(UTC(),'Kolkata')

[#3]

Will it take care of day light saving as well Avinash?

If conversion is based on systime, then i think there will be no problem.

Based on your information, i need to find of one city from each timezone to convert, right?

Any limitation with this function and any issue will occur after few years? I will refer this function anyway.

[avinashelite]

yes , it will take care of the daylight saving time also...you can pass the 3 parameter as true or false in order to make it enable and disabling the daylight savings ...

easy approach would be is to convert all the time to once format like UTC or GMT and then use this fucntion and pass the respective city names as listed in the Qlikview reference manual or QV help ...it will automatically convert the time to your required format

eg

You can use convert to local time function.  This is from Qlikview Help:

ConvertToLocalTime(timestamp [, place [, ignore_dst=false]])

Converts a UTC or GMT timestamp to local time as a dual value. The place can be any of a number of cities, places and time zones around the world.

Valid places and time zones:

Abu Dhabi, Adelaide, Alaska, Almaty, Amsterdam, Arizona, Astana, Athens, Atlantic Time (Canada), Auckland, Azores, Baghdad, Baku, Bangkok, Beijing, Belgrade, Berlin, Bern, Bogota, Brasilia, Bratislava, Brisbane, Brussels, Bucharest, Budapest, Buenos Aires, Cairo, Canberra, Cape Verde Is., Caracas, Casablanca, Central America, Central Time (US & Canada), Chennai, Chihuahua, Chongqing, Copenhagen, Darwin, Dhaka, Eastern Time (US & Canada), Edinburgh, Ekaterinburg, Fiji, Georgetown, Greenland, Greenwich Mean Time : Dublin, Guadalajara, Guam, Hanoi, Harare, Hawaii, Helsinki, Hobart, Hong Kong, Indiana (East), International Date Line West, Irkutsk, Islamabad, Istanbul, Jakarta, Jerusalem, Kabul, Kamchatka, Karachi, Kathmandu, Kolkata, Krasnoyarsk, Kuala Lumpur, Kuwait, Kyiv, La Paz, Lima, Lisbon, Ljubljana, London, Madrid, Magadan, Marshall Is., Mazatlan, Melbourne, Mexico City, Mid-Atlantic, Midway Island, Minsk, Monrovia, Monterrey, Moscow, Mountain Time (US & Canada), Mumbai, Muscat, Nairobi, New Caledonia, New Delhi, Newfoundland, Novosibirsk, Nuku'alofa, Nuku´alofa, Osaka, Pacific Time (US & Canada), Paris, Perth, Port Moresby, Prague, Pretoria,Quito, Rangoon, Riga, Riyadh, Rome, Samoa, Santiago, Sapporo, Sarajevo, Saskatchewan, Seoul, Singapore, Skopje, Sofia, Solomon Is., Sri Jayawardenepura, St. Petersburg, Stockholm, Sydney, Taipei, Tallinn, Tashkent, Tbilisi, Tehran, Tijuana, Tokyo, Ulaan Bataar, Urumqi, Warsaw, Wellington, West Central Africa, Vienna, Vilnius, Vladivostok, Volgograd, Yakutsk, Yerevan or Zagreb.

Also GMT, GMT-01:00, GMT+04:00 etc. are valid places.

The resulting time is adjusted for daylight savings time, unless the third parameter is set to 1 or true().

Examples:

ConvertToLocalTime(’2007-11-10 23:59:00’,’Paris’) returns ’2007-11-11 00:59:00’ and the corresponding internal timestamp representation.

ConvertToLocalTime(UTC(), ’GMT-05:00’) returns the time for the North American east coast, e.g. New York.