Convert Dates

[anuradhaa]

HI,

I have two users using two time zones (EST) and (CST)

So when a user form CST enter a record we enter it as comman time in our data base.

Then when he search records we convert in and display according to users time zone.

So when we use this colum in qlikview i can do it.

So think the CST user enter a record at 00.02 AM  20 th and we record it as 09.00 PM 19th in our data base.

So after we create reports that data display under 19th and that is wrong in users view.

Is there a way in qlikview to prevent this issue.

Thanks

[Gysbert_Wassenaar]

Use the ConvertToLocalTime function to change a datetime to the timezone of your choice. The Timezone() function returns the timezone of the local user. By combining them you can display times in the timezone of the local user.

[anuradhaa]

Hi Thanks for you reply.

Could you please explain a bit ,

say i have two users,

User1  -   eneter a record at   02.00 AM 19th EST and record it as 09.00 PM 18th MYtime in our database - Record 1

User2 - enter a record at  05.00 AM 19th GMT and record it as 04.00 PM 18th MYtime in ourdata base.

So in our application side we hand this looking at users time zones when we search those records.

I need to do it in qlikview as well.

When User1 serach his data,

i want to display Record 1 under 19th which is belogs to users  timezone.

but in my database that record is in MYtime.

Please explain bit how can i use above those two functions

Thanks

[Qvmaster2019]

I think this is a case of normalizing the data. This is just a theory but the plan is to convert both timestamp in to a GMT.

i.e.

If a user from Ohio, USA have a time stamp of "X", it would be normalized into converttolocaltime("X", 'UTC +05:00') since if it's GMT, it would be 'UTC -05:00.

If a user from Berlin, Germany have a timestamp of "Y", it would be normalized into convertolocaltime("Y", 'UTC -01:00') since if it's GMT, it would be 'UTC +01:00'.

Then when pulling the data, one can easily pull as based on his/her local time or the based the record's timezone.

More info here:

http://community.qlik.com/message/159101#159101

[Qvmaster2019]

What I have is call records with timestamp from various regions but it was recorded on EST. What I did is normalized the data (convert all recorded timestamp into GMT). When I pull the record for a region, I would then use ConverntToLocalTime(OriginalTimeStamp, if(IsDST=1, OffSetNonDST, OffSetDST))

IsDST is a calculated field that would check if a date falls under DST or not. I based the calculation in this one:

http://stackoverflow.com/questions/5590429/calculating-daylight-savings-time-from-only-date

My example, I have this control table

Region	GMT	OffsetDST	OffsetNonDST
North America	UTC-05:00	UTC+04:00	UTC+05:00
Europe	Berlin	UTC+04:00	UTC+05:00

since all my recordings are based on the current time in Ohio.

To pull the converted date/time for Europe for the original time "OriginalTimeStamp",

I wil check first if "OriginalTimeStamp" falls under DST or not then apply the ConvertToLocalTime which will then give me the normalized GMT.

so, I have this:

ConverntToLocalTime(OriginalTimeStamp, if(IsDST=1, OffSetNonDST, OffSetDST)) as [Normalized GMT],

ConvertToLocalTie(ConverntToLocalTime(OriginalTimeStamp, if(IsDST=1, OffSetNonDST, OffSetDST)), GMT) as [Regional Date/Time]

Hope this helps.