topic Re: how to convert a local time to utc time when I have the time zone? in QlikView

how to convert a local time to utc time when I have the time zone?

Wed, 19 Oct 2011 17:29:32 GMT

Hi All

I have a table where it's storing the time zone and the local time. How do I convert that information back into UTC time?

For example:

Time Zone, Start Time, End Time

'Paris', 08:00, 17:00

I have used the converttolocaltime function to convert from utc to local time but not sure how to do it the other way around.

Re: how to convert a local time to utc time when I have the time zone?

Miguel_Angel_Baeyens — Wed, 19 Oct 2011 18:36:22 GMT

Hi Jack,

Did you try using?

ConvertToLocalTime([Start Time], 'UTC')

Depending on your version of Windows, you might need to set 'GMT' instead of 'UTC'. You might need as well to set it to 'UTC-01:00' (or 'GMT-01:00') depending on the time zone, so you can generate that format in a different field based on [Time Zone] field.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: how to convert a local time to utc time when I have the time zone?

Wed, 19 Oct 2011 18:55:05 GMT

I think converting to the opposite should work. In another words, if time zone is GMT+05:00, then to get the utc time from the local time, you should be able to do a converttolocaltime(start time, 'GMT-05:00).

However, I can't seem to get converttolocaltime to accept 'GMT-05:00). GMT-05:00 is not a valid place?

In the manual, it has an example of ConvertToLocalTime(UTC(), 'GMT-05:00'). I tried but qlikview doesn't accept it as a valid place.

However, ConvertToLocalTime(UTC(), 'GMT') does work.

Any ideas?

Re: how to convert a local time to utc time when I have the time zone?

Miguel_Angel_Baeyens — Wed, 19 Oct 2011 19:00:13 GMT

Jack,

My Windows 7 doesn't seem to work with GMT at all, but it does if I use 'UTC-01:00' instead. I know that only 'GMT+something' is documented, but that parameter does not depend on QlikView but on your operating system.

Is that your case?

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: how to convert a local time to utc time when I have the time zone?

Wed, 19 Oct 2011 19:08:55 GMT

That did the trick. I'm running Windows 7 also.

Re: how to convert a local time to utc time when I have the time zone?

IAMDV — Fri, 21 Oct 2011 14:17:48 GMT

Miguel - This is very useful. Thanks so much.

Cheers - DV