Dealing with TimeStamps and UTC/GMT conversions

[Joaquin_Lazaro]

Hi guys:

I've been reading the community for all the morning, and I haven't found what I'm looking for.

Is there any Best Practices, White Paper or Technical Brief about dealing TimeStamp conversions ?

I have information created in New York, Chicago, Los Angeles, Tijuana, Brasil, Argentina, Chile, UK, Spain and other CET countries.

I wish to read a guide or tutorial about how to dealing with time zone conversions, and how to manage different possibilities about show this information. Depending on some app we will need to see the information using the Local Time of creation, for instance we like to see yesterday sales then it doesn't matter the time offset; but if we analyze the investment on the web ads, we would like to use the real time of the headquarter official time.

Each bit of help will be appreciated.

Thank you,

Joaquín

[Gabriel]

Hi,

QlikView has a function call ConvertToLocalTime(), you can read more from the QV help file.

Also read this http://community.qlik.com/thread/37530. It might help you.

[Anonymous · Accepted solution]

I use the QV function ConvertToLocalTime(). 

I have not come across any Best Practices, White Paper or Technical Brief about TimeStamp conversions.

It is not complicated though, and the Help gives a basic outline of the command.

I find it useful is to do time conversions in my Script(s) and store them in both UTC and local timestamps in my QVD's.

[Joaquin_Lazaro]

Thanks Gabsus04

I have read this and other links before.

I'm searching something more in a functional level, rahter than in a technical level.

[Joaquin_Lazaro]

Thanks bill.markham

I think it's an useful idea store both times UTC and local time, but what happens when there are some timestamps per record, will the app response slower than before ?

[Miguel_Angel_Baeyens]

Hi Joaquín,

What I have done is to generate a new field, an index for hours. So if your analysis needs to be done within every day, assign a value from 1 to 23 to all hours, based on the timezone different. For example, 1300 Madrid will have that field value to 12 (if you want it based on UTC). And with the same index 12, the value for New York is 0600 (six hour less).

On the other hand, if you need for example to aggregate with net work time, then using the original hour works best since I can add up hours from 9 to 5 for all different cities (for example).

Once you get that, it's much easier to aggregate based on hours or days for that matter.

Miguel

[Joaquin_Lazaro]

Hi

Creating the index time it's a good idea as well.

If I understand well the index value is the local time less the time offset to UTC

[Anonymous]

Do beware that not all time zones offset from UTC by a whole hour,e.g. India is UTC +5:30.

Your current countries may well be whole hour shifts, but you may / may not need to cater additional timezones later.

[Joaquin_Lazaro]

I've answered your previous message, but it's still under moderation.

That's a good appreciation, I guess mby index is little trick for converting to a false UTC.

Anyway, all this information it is beening very interesting