Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

show data in correct time zone, can I do UTC+10 ?

Hi all,

is there a way to show the hours of a graph in our time zone?

I am querying a remote database and in the SELECT statement importing sales data for a single day.

In this graph I want to show at what times during the day we sell more of this product.

I get a bar chart showing the sales/hour, but the hours are displayed in UTC time zone as this is how it is recorded in the remote time zone

Rather than in "correct" time zone for Sydney which is UTC+10

is it possible to do this in the "Edit Expression" area?

Any advice appreciated, thanks!

Bjorn

2-10-2014 9-37-05 AM.jpg

2-10-2014 9-38-15 AM.jpg

1 Solution

Accepted Solutions
MVP
MVP

Re: show data in correct time zone, can I do UTC+10 ?

Hi

Not sure why you need maketime(); this should work:

=Hour(ConvertToLocalTime(sales_date , 'Sydney'))

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
5 Replies

Re: show data in correct time zone, can I do UTC+10 ?

You can use the ConvertToLocalTime function to convert a UTC timestamp to a local timestamp. From the help file:

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.


talk is cheap, supply exceeds demand
Not applicable

Re: show data in correct time zone, can I do UTC+10 ?

Hi Gysbert,

thank you for taking the time to help, appreciate it.

I tried the ConvertToLocalTime version but could not get it to work together with the maketime (hour) command.

What we are after is the Hour from the sales date only, which works in my formula above.

But it is shown in UTC time while I was hoping to show it in our time zone which is UTC+10.

thanks

Bjorn

MVP
MVP

Re: show data in correct time zone, can I do UTC+10 ?

Hi

Not sure why you need maketime(); this should work:

=Hour(ConvertToLocalTime(sales_date , 'Sydney'))

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: show data in correct time zone, can I do UTC+10 ?

haha, that worked beautifully!!

thanks a lot for taking the time to help, really appreciate it.

Not applicable

Re: show data in correct time zone, can I do UTC+10 ?

I kept the maketime get AM and PM times, rather than just hours with 0,1,2,3,4,5,6,7,8,9,10,11,12,13....

=maketime(Hour(ConvertToLocalTime(sales_date , 'Sydney')))

thanks again!

7-10-2014 9-58-21 AM.jpg

Community Browser