Convert time bucket to Local time

[Anonymous]

Hi All,

I have a date&time field in my "Activity table" (Eloqua database)

Date

3/24/2017 1:05

2/23/2017 11:30

and I have converted it to TimeBucket field:

Dual(Time([Date],'h tt')&' - '&Time([Date]+'01:00','h tt'), Hour([Date])) as TimeBucket,

I also have a Country and Region field in my "Contacts" Table:

Country	Region
Spain	EMEA
United States	NA
South Africa	APAC
Australia	APAC
etc..	etc…

I have filters in my dashboard for regions -> APAC, EMEA, NA. I would like to know when the end user selects a APAC region filter he gets to see the data in the APAC local time?.

[avinashelite]

Use Convert to local time function

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.

[Anonymous]

Hi Avinash,

Thank you for your post. Can you help me understand as to how should I be using this function in

Dual(Time([Date],'h tt')&' - '&Time([Date]+'01:00','h tt'), Hour([Date])) as TimeBucket

[avinashelite]

Using this function we could convert the time to their respective time zone

for e.g.

Region , Time

Paris,2007-11-10 23:59:00

New Delhi,2007-11-10 23:59:00

if you use

ConvertToLocalTime(Time,Region)  as Local_time this will convert the time to their respective region time so for Paris it will set the Paris time and similar for Delhi

then you could

Dual(Time([Local_time ],'h tt')&' - '&Time([Local_time ]+'01:00','h tt'), Hour([Local_time ])) as TimeBucket

so based the region time will be shown

if you need all the time to be converted to one time zone specify only the time zone so all the time will be in one region format

Hope this helps you 

[Anonymous]

Hi Avinash,

I did try the above two scripts.

It gives me the below error:

The following error occurred:

Field not found - <Country>

[avinashelite]

Do you the Country column ? to which the time needs to be converted ?

Local_Time is the field you have created inside table and you cannot refer same in immediate load

LOAD *,

Dual(Time([Local_time ],'h tt')&' - '&Time([Local_time ]+'01:00','h tt'), Hour([Local_time ])) as TimeBucket;

LOAD

other columns,

ConvertToLocalTime(Time,Region)  as Local_time

...

[Anonymous]

Yes. I do have a Country column. And I have made sure that it is spelled correctly with C caps.

So you are saying the variable I created "Local_Time" cannot be used in - Dual(Time([Local_time ],'h tt')&' - '&Time([Local_time ]+'01:00','h tt'), Hour([Local_time ])) as TimeBucket;?

I have Date in my Activity table and Country in Contacts Table.

And the TimeBucket Field is in my Activity Table.

My Final output is something like this. So when the user selects a Country field he should get the data according to the local time of that selected country,

[d_prashanthredd]

Hi Ajinkya,

I think this is not the right place to ask but I'm posting here as I see it is relevant to my problem or scenario.

By looking at your post, I assume that you have connected Qlik and Eloqua Database. If yes, will you help me in connecting? I tried through REST but I'm not getting proper parameters to pass.

I have posted different thread and also seen other threads from different users but no responses over there. So thought to ask you here.