Convert_TZ vs ConvertToLocalTime

[Farsana]

Team,

i am looking to handle the script in QlikCloud (Set Analysis),

SELECT CONVERT_TZ('2024-07-08T14:00:00.000000Z', 'America/Chicago','America/Phoenix');

Tried with ConvertToLocalTime but output was not correct. How to hanlde this. Any refernce?

Thanks in advance!!!

[marksouzacosta]

Hi @Farsana,

This is the official reference for the ConvertToLocalTime function in Qlik:
converttolocaltime - script and chart function | Qlik Sense on Windows Help

I did a small example to convert the time you sent to Phoenix Time Zone (UTC-7). Not sure if this is exactly what are you looking for, but it will give you an idea how to use the function.

LOAD
ConvertToLocalTime('2024-07-08T14:00:00.000000Z','UTC-07:00') AS Converted
AutoGenerate(1)
;

Regards,

Mark Costa

[Farsana]

Thanks @marksouzacosta thanks for your response,  i have multiple timezone in table as highlighted in below screen shot,

i implemented the logic

" =ConvertToLocalTime(ConvertToLocalTime('2024-07-08 14:00:00','America/Chicago'),'America/Phoenix')" , in set expression, but the problem is output was not right as expected, its giving different answer, i replaced with 'UTC-07:00' but still same.

"=ConvertToLocalTime(ConvertToLocalTime('2024-07-08 14:00:00','America/Chicago'),'UTC-07:00')" 

Any suggestions

[marksouzacosta]

I think you'll have to replace 'America/Chicago' with 'UTC-05:00'.

I'm curious about your Set Expression. Can you please share the whole Expression here? A Screen shot will also help.

Dealing with date and time fields in Set Analysis are often tricky because of the format of the values.

[Farsana]

please find the screen shot for reference. timezone and starttime_bt are the filedname which refers to timezone and time. Thanks @marksouzacosta 

[marksouzacosta]

Thank you @Farsana.

Ok. So, this is not a Set Analysis expression, this is a regular expression that may or may not work depending where you are using it - which type of chart and which selections you have on your dashboards.

I believe you are trying to convert the Time Zones dynamically, right? Based on user's selections or maybe displaying different times according to the time zone on the charts dimensions. Can you please clarify that? I think we are close to a solution.

[Farsana]

Thanks for clarifying the point on regular expression  @marksouzacosta, currently am working with data Modelling where i am fine tuning the records based on the business need, in the below table i have timezone and start_time_bt, Here the timezone was not constant . Now i need to apply the logic dynamically based on the existing time zone with time  into America/Phoenix.

Thanks again

[marksouzacosta · Accepted solution]

Oh, that is much better now @Farsana.

Ok, ConvertToLocal time expects a UTC value so the first step is to convert your time to UTC. Then, with the converted UTC time you convert to your America/Phoenix (UTC-07:00) value. Is a two steps process.

Follow the code and the result. You can shorter your code but I did in separated steps to make it easier to understand. Please let me know if the results are correct:

TimezoneUTCMapping:
MAPPING LOAD * INLINE [
Timezone, Value
America/Chicago, UTC-05:00
America/Phoenix, UTC-07:00
America/New York, UTC-04:00
];


Times:
LOAD
start_time_bt,
timezone,
timezoneValue,
start_time_bt_UTC,
ConvertToLocalTime(start_time_bt_UTC,'UTC-07:00') AS start_time_bt_Phoenix,
;
LOAD
start_time_bt,
timezone,
timezoneValue,
TimeStamp(ConvertToLocalTime(start_time_bt,'GMT') - ConvertToLocalTime(start_time_bt,timezoneValue) + start_time_bt) AS start_time_bt_UTC,
;
LOAD 
TimeStamp(start_time_bt) AS start_time_bt, 
timezone,
ApplyMap('TimezoneUTCMapping',timezone) AS timezoneValue 
INLINE [
start_time_bt, timezone
2024-07-08T14:00:00.000000Z, America/Chicago
2024-07-09T11:00:00.000000Z, America/Chicago
2024-07-10T17:00:00.000000Z, America/New York
2024-08-06T15:00:00.000000Z, America/New York
];

 

Another possible solution is to simple use math. I haven't fully tested but looks correct too:

TimezoneUTCMapping:
MAPPING LOAD * INLINE [
Timezone, Value
America/Chicago, -5
America/Phoenix, -7
America/New York, -4
];


Times:
LOAD
start_time_bt, 
TimeStamp(start_time_bt + If(timezoneValue > 0, -MakeTime(timezoneValue),MakeTime(fabs(timezoneValue))) - MakeTime(7)) as start_time_bt_Phoenix, 
timezone,
timezoneValue 
;
LOAD 
TimeStamp(start_time_bt) AS start_time_bt, 
timezone,
ApplyMap('TimezoneUTCMapping',timezone) AS timezoneValue 
INLINE [
start_time_bt, timezone
2024-07-08T14:00:00.000000Z, America/Chicago
2024-07-09T11:00:00.000000Z, America/Chicago
2024-07-10T17:00:00.000000Z, America/New York
2024-08-06T15:00:00.000000Z, America/New York
];

Regards,

Mark Costa

[Farsana]

Thanks ton @marksouzacosta for the detailed explanation.