topic Re: time zone conversion SGT to UTC **** in QlikView

time zone conversion SGT to UTC ****

Wed, 12 Mar 2014 02:54:46 GMT

HI all,

I Have a time field Its contains time in SGT zone I want to convert it into UTC format. So that I used the below code

Timestamp(field - time#(8,'hh.mm.ss'))

But it give the result wit date and the time format is like AM/ PM

1. I dont want the date in my resultant set

2. I want the result in 24 hrs format not in AM/PM

Please do me the needful ASAP

thnx

Re: time zone conversion SGT to UTC ****

Anonymous — Wed, 12 Mar 2014 03:04:52 GMT

Hi,

The Timestamp() function takes a second parameter where you can define how you want to format the value.

Timestamp(TimestampValue, 'YYYY-MM-DD hh:mm:ss') for example gives you a value without the AM/PM part. If you hit F1 in QlikView and search for the timestamp function you can see the full syntax with examples.

Re: time zone conversion SGT to UTC ****

Wed, 12 Mar 2014 03:09:58 GMT

Hi thnx fr ur reply.

But I tried those also.

If u have script fr this scenario plz share.

Even I seen UTC() but if I tried its nt reloading

Re: time zone conversion SGT to UTC ****

jagan — Wed, 12 Mar 2014 04:15:08 GMT

Hi,

Try like this

=Timestamp('3/12/2014 12:11:53 PM' - 0.33333, 'hh:mm:ss')

0.33333 is used to convert SGT to UTC

Regards,

Jagan.

Re: time zone conversion SGT to UTC ****

Wed, 12 Mar 2014 06:35:06 GMT

Hi jagan

This one gives me wrong result. Mins and seconds are not correct

Re: time zone conversion SGT to UTC ****

tresB — Wed, 12 Mar 2014 06:54:32 GMT

Try like this:

=Time(yourtimetime - time#(8,'h'), 'hh:mm:ss')

Re: time zone conversion SGT to UTC ****

Wed, 12 Mar 2014 07:01:18 GMT

I tried all these type of combinations but tht mins s not correct

U cab also test by hard coding some time manually

Re: time zone conversion SGT to UTC ****

tresB — Wed, 12 Mar 2014 07:05:53 GMT

With me it works fine!

Re: time zone conversion SGT to UTC ****

jagan — Wed, 12 Mar 2014 08:40:16 GMT

Hi,

For me it is working correctly

=Timestamp('3/12/2014 12:11:53 PM' - 0.33333, 'hh:mm:ss')

I am getting 4:11:53

Regards,

Jagan.

Re: time zone conversion SGT to UTC ****

Anonymous — Wed, 16 Jul 2014 02:55:11 GMT

Hi,

Have you tried ConvertToLocalTime function? Accounding to the reference manual:

"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"

For example if you want to converto from UTC to your local time you have to type:

=ConvertToLocalTime([timestamp UTC Time], 'GMT+08:00')

But, It also works backwards, if you want to calculate UTC time instead of adding 8 hours, subtract them:

=ConvertToLocalTime([timestamp Local Time], 'GMT-08:00')

Then just format the result using timestamp function. I have use this function before, and it worked for me.

Regards

Re: time zone conversion SGT to UTC ****

Anonymous — Wed, 16 Jul 2014 03:08:02 GMT

If Karla's suggestion does not work for you, in the load script you could use DATEADD(hh,+/-X,<timestamp>)