Solved

Time Zone issue in a view

Posted on 2006-06-19
22
404 Views
Last Modified: 2013-12-18
I have a view that has a column that shows the name of the Day of Week based off of  a date on the form (i.e. Sunday, Monday, etc). My problem is we just rolled this application out to a EST time zone and we are in the CST time zone. Notes seems to be looking at this Date field which has 12:00 AM in there for the time (even though I told it to only use Date when creating the form) and it takes one hour off, which ends up showing Saturday for Sunday etc...

How can I get a view column that uses a date value to ignore Time Zones and just look at that time the field says.... I tried:

@Weekday(@Date(DateField)

And it still changes to the date to the previous day....

Hopefully an easy one but need it quickly...

Thanks
0
Comment
Question by:TheBull369
  • 8
  • 8
  • 5
  • +1
22 Comments
 
LVL 43

Expert Comment

by:Steve Knight
Comment Utility
Hmm, you could always store the @WeekDay as a field in the document which can then just be displayed?

Not really done much with TimeZone conversions as most Notes companies I have dealt with run seperate systems across continents.  If you just wanted to display the date using the normally available format options then you could do it with the "always show timezones" option in the view column.  Perhaps you could use @TextToTimeInZone (I think that is the formula) to convert to original timezone to include TZ then strip off to just the date and convert that to your day :-)

Not by notes at the moment, sure someone else will come up with full solution shortly.

Steve
0
 
LVL 43

Accepted Solution

by:
Steve Knight earned 500 total points
Comment Utility
Hmm, maybe this:

@WeekDay(@TextToTime(@Left(@Text(DateField;"D0S0Z2");10)));

i.e. day, month, year, date only but include original time zone then strip off date (first 10 chars) and convert back...

There must be a proper way of doing this: sjef, marilynng?

Steve
0
 
LVL 46

Expert Comment

by:Sjef Bosman
Comment Utility
Do you have the TimeZone stored in the document? Only then you can do this the proper way. Look in your calendar, any appointment, and check the StartTimeZone item. Using @GetCurrentTimeZone, you can use the timezone to calculate the proper day. As an alternative, if the time is really not important, then store the date as a text string, to make it abundantly clear that it's the day that counts.

It's sad nobody ever seems to appreciate we have only one current time, Universal Time. And that NOW here was the same NOW at your place. That our two clocks tell you something different is apparently very important. The idea "I told it to only use Date when creating the form" is an illusion, since date and time are one. Internally, date/time is stored as a Double value, counting days since somewhere 1899 or so. To get the time, remove the integral part of that value, multiply by 86,400 and you have the number of seconds elapsed since midnight.
0
 
LVL 43

Expert Comment

by:Steve Knight
Comment Utility
Does the time not get stored as UTC anyway, hence the problem he has which is that it is 'correctly' converting it to local time at the other site whereas he wants to show the time as it is at the source time zone.

Steve
0
 
LVL 46

Expert Comment

by:Sjef Bosman
Comment Utility
Time is always stored as UTC indeed, so an additional piece of information is required how to "translate" that time to the local clock. When stored in a separate field, one could combine the UTC date value and the timezone using @TimeToTextInZone( timeDate ; timeZone ; formatString ), explicitly mentioning the timezone of the source.
0
 

Author Comment

by:TheBull369
Comment Utility
The Time Zone gets stored in the field itself.... I use a NoteDateTime function to populate that field and I use the DateOnly method with it but for some reason that field still gets the 12:00 AM EST added to it....
0
 
LVL 46

Expert Comment

by:Sjef Bosman
Comment Utility
If you would ask your colleague in the other timezone to look at the Document Properties of the same document, he'd probably see 11:00 PM CST. Whatever function you use to populate a field, the internal representation is UTC. All the rest is representation and conversion.
0
 

Author Comment

by:TheBull369
Comment Utility
The field itself shows: 6/18/2006 12:00:00 AM EST .... I am in the CST time zone... I look at the field through Doc Properties and that is what I see... When I do something like @Prompt to display the date and time it shows 6/17/2006 11:00:00PM .... When running my calcs in the view I need it to stay at 6/18/2006 (dont event care about the time because it doesnt need to be there and not sure why notes is putting it there)
0
 
LVL 46

Expert Comment

by:Sjef Bosman
Comment Utility
Hm, you got me thinking there. Is your database local or on the server? A view on the server uses the server's timezone, could the server's timezone be different from your local timezone?

By the way: a NotesDateTime object is NOT the the item as stored in your document. It is just a means to make calculations with date/time values easier.
0
 

Author Comment

by:TheBull369
Comment Utility
The user uses Local copies when submitting the data.... the user is locating in the EST timezone and the server is in CST....
0
 
LVL 46

Expert Comment

by:Sjef Bosman
Comment Utility
Additional info:
    http:Q_21280270.html "TIME ZONE Issues"
    http:Q_21443401.html "Canonical Time Zone Conversion using Lotus Script"
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:TheBull369
Comment Utility
Either I am missing it or something I do not see a solution in those as to how to make this column show the correct date (basically I need Notes not to do the timezone conversion on the date)
0
 
LVL 43

Expert Comment

by:Steve Knight
Comment Utility
All considering it would be easiest to include an extra field as text in the document which won't need any conversion at all as has been suggested a few times above.

Steve
0
 

Author Comment

by:TheBull369
Comment Utility
Was trying to do it without adding another field. Figured there would have to be another way from the view level to do it.
0
 
LVL 46

Expert Comment

by:Sjef Bosman
Comment Utility
I'm sorry I have to say that you're missing it. The timezone is NOT in the date/time field. For that, you need additional information in a separate field.

Once again, a field with a date/time value is internally stored as a double value, meaning a long floating point number. That number is the number of days elapsed since 31 December 1899 midnight in Greenwich (or so, if you want to know the exact origin, please look it up in the Designer Help database). As an example, let's assume the date/time field contains the value of 1234.5. That means that 1234 and a half day have elapsed since 13-dec-1899 midnight in Greenwich. The date is therefore day 139 of the year 1903.

The time is stored as a fraction of a day. In the example 1234.5, the .5 means that half a day has passed, so it is 12 o'clock, still in Greenwich. The Greenwich time, or Universal Time (UTC) , is the standard time chosen for the planet Earth. Using this time code, 1600 UTC is the exact same moment for everybody. As a last step you need to apply the timezone. My timezone (in winter) is CET, GMT+1, so for me the meeting starts at 17:00 CET. For you, the same meeting would start at 11:00.

So, you see, the timezone is NOT a property of the internal time, but it is a property of your location. And there is no such thing as just a date value, without a time value. they are one.

Now your case. There are two important moments for the date/time field: A) when the value is stored, and B) when the value is retrieved.
A) When the value is stored, the local time is taken, the timezone-difference is subtracted, and the result is the date/time value in UTC.
B) When retrieving, the internal value in UTC is taken, the local timezone-difference is added, and the result is displayed.

So there are two possibilities to solve your problem:
I) the timezone of the person who saved the value is stored in the document, so you can calculate the date/time in his timezone independent of the current timezone.
II) you don't use date/time values, but you compute the date value as a string when the document is stored.

I) is by far the best solution, it is flexible and always correct, yet more complex.
II) might lead to unexpected situations when the text is converted back to a date/time value and calculations are made using that value.

My advice:
- add a timeZone-field (hidden) to your document, with formula @GetCurrentTimeZone
- use in the view-column a formula like
    @TimeToTextInZone( timeDate ; timeZone ; "D0S0" )
0
 

Author Comment

by:TheBull369
Comment Utility
I must have missed the one I accepted.... it works...
0
 
LVL 46

Expert Comment

by:Sjef Bosman
Comment Utility
Hm, accepted, I was too late... I hope someone understood my story :S
0
 

Author Comment

by:TheBull369
Comment Utility
I did understand it but I already accepted the other....
0
 
LVL 46

Expert Comment

by:Sjef Bosman
Comment Utility
I can only hope you get colleagues in New Zealand or Hawaii some day...
0
 

Author Comment

by:TheBull369
Comment Utility
not sure our trucks could deliver our product that far... hehe
0
 
LVL 43

Expert Comment

by:Steve Knight
Comment Utility
Guys,

On a proper PC connection now, must stop dipping into Q's over PDA...  I appreciate getting the PAQ and points but I'm not sure you've got the best answer there.  Sjef has put a lot of effort into explaining how date/time values work and how you really need to use a seperate timezone field though if all you need is a weekday for real easy simplicity I think I'd just stamp that into a hidden computed field on the form calculated from the date at the point of entry.

As I said at the beginning here I haven't had direct benefit of working with multiple timezones on Notes myself so had only a passing interest in the functions as I've progressed through the Notes versions.

Sjef, don't have MSN but will drop you a note offline as requested.

Steve
0
 
LVL 18

Expert Comment

by:marilyng
Comment Utility
Hee, hee.. I just make everything GMT and convert it locally.  So, the question is if I create an appointment for 1:00 PM Eastern Standard Time and send the invitation to someone in another time zone, do they see the appointment as 1:00 PM Eastern Standard Time or do they see it translated to the time it would have to be in their time zone in order for us to talk on the phone.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

For users on the Lotus Notes 8 Standard client, this article provides information on checking the Java Heap size and adjusting it to half of your system RAM in attempt to get the Lotus Notes 8.x Standard client to run faster.  I've had to exercise t…
Notes Document Link used by IBM Notes is a link file which aids in the sharing of links to documents in email and webpages. The posts describe the importance and steps to create a Lotus Notes NDL file in brief.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now