topic Re: QVDCreateTime Default Time Zone in QlikView

Convert Local Time to UTC with Daylight Saving Handled Automatically

Thu, 18 Feb 2016 05:06:00 GMT

I was trying to capture the create time of a QVD file and convert it from local time to UTC.

But I found that QVDCreateTime function will return a timestamp with timezone UTC rather than my local time zone.

Can we control the timezone of the return from QVDCreateTime function?

Thank you

Paco

Re: QVDCreateTime Default Time Zone

Thu, 18 Feb 2016 05:07:47 GMT

I should have said that I think the return of QVDCreateTime function default the time zone to be 'UTC' while the time value is in local time.

Hope that will make it clear.

Re: QVDCreateTime Default Time Zone

tresB — Thu, 18 Feb 2016 05:18:58 GMT

It returns the time as in your local system.

Re: QVDCreateTime Default Time Zone

Thu, 18 Feb 2016 05:23:12 GMT

Hi tresesco:

Thank you for your reply. But I don't think what you are saying is the case.

Can you try the following two lines of script?

LET v1 = TIMESTAMP(CONVERTTOLOCALTIME(QVDCREATETIME('[Your QVD File]'),'UTC'), 'YYYY-MM-DD hh:mm:ss');

LET v2 = TIMESTAMP(QVDCREATETIME('[Your QVD File]'), 'YYYY-MM-DD hh:mm:ss');

I am not in UTC, but the above script will result in v1 equal to v2.

Let me know what do you get from the above script.

Cheers,

Re: QVDCreateTime Default Time Zone

jagan — Thu, 18 Feb 2016 05:26:13 GMT

Hi,

It will return the local system time, check your system is in UTC zone.

Regards,

Jagan.

Re: QVDCreateTime Default Time Zone

Thu, 18 Feb 2016 05:30:41 GMT

Hi Jagan:

Where can I check my system timezone.

If I click on the clock on the bottom right and click 'Change date and time settings...', then it is showing the following:

(UTC+10:00) Canberra, Melbourne, Sydney

Please let me know if I am looking at the wrong place.

Thank you.

Re: QVDCreateTime Default Time Zone

tresB — Thu, 18 Feb 2016 05:32:06 GMT

Let's not make it complicated. Why don't we try simply

Let vQVDtime = QvdCreateTime('[Your QVD File]');

Don't you get the time of your Windows time in the variable? I get it so.

Re: QVDCreateTime Default Time Zone

tresB — Thu, 18 Feb 2016 05:37:03 GMT

To answer your ConvertToLocaTime() - it takes input as UTC/GMT and if you convert it to UTC again it will always return the same output. Hope this explains your query.

So it's about understanding the ConvertToLocalTime() function right.

Re: QVDCreateTime Default Time Zone

Thu, 18 Feb 2016 05:40:32 GMT

Hi tresesco:

Thanks for your clarification. That make sense now.

Then I have another questions. How can I convert a local time to UTC with Daylight Saving handled automatically?

Thank you.

Re: QVDCreateTime Default Time Zone

tresB — Thu, 18 Feb 2016 05:48:47 GMT

ConvertToLocalTime(YourTime, 'UTC-10:00'); ?

Re: QVDCreateTime Default Time Zone

jagan — Thu, 18 Feb 2016 05:52:36 GMT

=ConvertToLocalTime(Now(), 'UTC-08:00')

Re: QVDCreateTime Default Time Zone

Thu, 18 Feb 2016 05:53:40 GMT

Hi tresesco:

I am not sure that works.

LET v2 = TIMESTAMP(QVDCREATETIME('[Your QVD File]'), 'YYYY-MM-DD hh:mm:ss');

LET v3 = TIMESTAMP(CONVERTTOLOCALTIME(CONVERTTOLOCALTIME(QVDCREATETIME('[Your QVD File]'),'UTC-10:00'), 'Sydney'), 'YYYY-MM-DD hh:mm:ss');

v3 convert QVD time from local to UTC and then back to local. And v3 is different from v2.

Correct me if I am wrong.

Thank you.

Re: QVDCreateTime Default Time Zone

tresB — Thu, 18 Feb 2016 06:04:07 GMT

I see Sydney -('GMT+11')

try with:

ConvertToLocalTime(YourTime, 'UTC-11:00');

Re: QVDCreateTime Default Time Zone

Thu, 18 Feb 2016 06:09:28 GMT

Hi tresesco:

I think the reason why it is showing UTC 11 is because of the Daylight Saving.

So specifying time zone rather than city will not handle Daylight Saving automatically.