Time Conversion in List Box

[#1]

Hi All,

I know this question has been asked multiple times, but I can't seem to get it working.

Let me give you a brief background. I'm pulling data from a Oracle DB. The time fields are in Unix time which I'm able to convert to a readable date/time from within the SQL

i.e:

to_char(to_date('01011970','ddmmyyyy') + 1/24/60/60 * SDATE, 'mm/dd/yyyy HH24:MI:SS')
AS SDATE

Now the date/time being spit out is in UTC time. I've attempted to convert this to CST (my timezone) from within the SQL statement and that did not work. So I've been attempting to do it from the list box itself, but every time I attempt to do it from the expression it loads nothing.

I've also attempted from multiple other places and all fails. Your help is greatly appreciated.

[swuehl · Accepted solution]

What is the expression you've used in the list box?

I suggest that you take a look at the QV date and time functions in the HELP or reference manual, namely

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.

[#3]

Hi swuehl‌

I had to change the SQL to reflect the time as yyyy-mm-dd HH24:MI:SS instead. I did not see where the actual format had to be a specific way!

to_char(to_date('01011970','ddmmyyyy') + 1/24/60/60 * SDATE, 'yyyy-mm-dd HH24:MI:SS')  
AS SDATE

Now, is there a way before saving it to a qvd to convert the time?

[swuehl]

Have a look at

QlikView Date fields

The code might look like

LOAD

     *,

     ConvertToLocalTime( Date, 'Central Time (US & Canada)') as DateConverted;

LOAD *,

     Date#( SDATE, 'YYYY-MM-DD hh:mm:ss') as Date;

//Your SQL code in the load script

SQL SELECT ...

FROM ....;

Instead of 'Central Time (US & Canada)' you may need to input your localized place string taken from your OS time zone control. Or try something like

ConvertToLocalTime( Date, 'GMT-06:00') as DateConverted;

[ramasaisaksoft]

Hi Yusof,

once u selected expression in list box ->EDIT click on edit button and write expression

=ConvertToLocalTime( Date, 'GMT-05:30') as YourFieldName

SAVE the expression

click OK

check the data in List box.

i hope it will help to you.