ShowTable of Contents
Date/time values are complicated. This document discusses these complexities and ways to deal with them. The following specific issues are covered:
- Storage of dates in a document.
- Conversion of dates to text in a view (why it's a bad idea)
- Other effects of differences in date formatting preferences between workstations and between the workstation and server.
- The mysterious "off by one day" issue.
- Time zones.
- Comparing date/time values.
- Daylight Savings Time.
- Working with dates in LotusScript, which has two ways of representing date/times.
Summary of Recommendations
- Don't store date/times as text values if it can be avoided.
- If you do store date/times as text, specify how you want it formatted -- don't accept the default.
- Don't use @Text function in a view column to convert dates to text; rather, let the user's workstation do this conversion.
General Theory of Date/Times
Notes' native way of storing a date, is as a numeric value which is an offset from a baseline date/time. Extra information appended when the value is stored in a field, to specify the timezone and whether DST is supported for that time.
As such, since they do not contain a sequence of characters, dates are not stored in a particular format MM/DD/YYYY or whatever. The conversion to a string of characters takes place when the value must be displayed to a user, so different users will see the parts of the date arranged in different ways, based on their date formatting settings. Also, since the timezone is recorded, the date/time value is usually adjusted to the local time in the user's timezone. Of course, if this happens, the date part may not be the same for users in different timezones, since (say) 12:30 AM in New York is 11:30 PM on the previous day in Chicago.
Date/Time field values do not store formatting information (MM/DD/YYYY vs DD/MM/YYYY, etc).
|
When a date is stored without a time, the timezone is not recorded. So if you store a date alone, all users see the same date, regardless of their timezone.
It's important to be aware that when you see a date displayed without a time, that doesn't necessarily mean that a time is not being stored. View column formatting options and field formatting options can be used to strip out the time and display the date only, but you may still be working with the full value.
The potential problems you might run into if you don't handle date/time values correctly in your application are:
- User confusion because dates are not displayed appropriately for their regional settings, e.g. they see 04/09/2009 and don't know whether it means April 9 or September 4.
- Data integrity issues because dates are stored as text without the context to determine how they should be converted back to dates. E.g. a field contains the value "04/09/2009" and it could mean April 9 or September 4, depending on the date formatting preferences of the user who last edited the document; so, your data are ambiguous and there's no way to be sure what date was intended. This is a real problem for view displays, sorting, and agent processing of the documents.
- Documents showing up on the wrong dates in a view (or the form showing a wrong date), because of timezone considerations when a time is stored along with the date. In particular, different users may see different dates because the users, or their servers, are in different timezones. Sometimes this is what you want to happen; sometimes not. We'll explain what's going on so you can know how to get the behavior you intend.
Some developers are particularly enthusiastic to catch date errors in other people's applications, and will deliberately set their date formatting preferences in an unusual way (e.g. MM%YYYY%DD) to see whether this messes anything up. Of course, it
can mess things up if the application was poorly designed; you can edit documents and the application might store invalid data in them. You might try this as a short-term measure as you test each view and form in an application, to evaluate whether all screens handle dates properly.
Date/Times in Documents
The requirements for your application may call for date values to be formatted in a specific way, e.g. DD/MM/YYYY. However, we suggest you avoid this if possible, because it's unfriendly to users in other countries who are used to formatting dates differently. By default, Notes will display dates in the order based on the user's own date formatting preferences, and we believe this helps to avoid confusion. If for whatever reason you can't manage that, at least consider using an unambiguous format, e.g. "4 August 2009" (though it is well to bear in mind that users in different countries also have different names for their months).
When a user opens a document, the server sends them the the contents of the document. If a date/time value is stored in the document, the server sends the numeric date/time value and timezone information -- it does not send a string in the form MM/DD/YYYY or whatever. The user's workstation takes that date value and converts it to a string of characters for display, based on the user's preferences and the formatting options of the field.
However, if a field contains a text value, the server sends the text value to the client, and the client displays that exact string. In that case, all users of the document would see the same thing, regardless of their date preferences.
Likewise, when saving the document, the user's workstation will send a date/time value to the server in numeric form, but if a field's value is of the "text" datatype, the client sends a series of characters, which the server stores, not knowing that the string is supposed to represent a date value.
As an example, suppose a form has a computed Text field with the formula "Edited by " + @Name([CN]; @Username) + " on " + @Text(@Today). Dan saves the document, and the string "Edited by Dan Theman on 9/12/2008" is stored in the document. When Hiro opens the document, stored fields are not recalculated, so Hiro sees that exact string, which for him is wrong. Hiro edits the document, and now the field says "Edited by Hiro Tsukemono on 2009/01/02".
It would be better to store the editor's name and date in two separate fields, so that the date can be stored in a date field. When the user opens a document, the date is formatted based on the user's preferences -- or, if you prefer, it's formatted the same for everyone, but at least then the users can know what to expect.
It may often be the case that you're looking at someone else's application -- or you're not very sure what's going on in your own application -- and you might not know whether a field value contains text or a date. To find out, use the document properties infobox from a view to see the value and datatype of the field.
In this example, the field "Enrolled" has been selected. On the right, look for the "Data Type:" line and observe it says "Text". Also look at the value, which has the date/time inside of quotes. This is what a date looks like when it has been incorrectly stored as a string.
You might be thinking, why take the trouble to use the infobox when we can just look at the form design and see what the datatype of the field is? The answer is, this is not a reliable guide, as we describe in the next section.
Field Datatype Is Only A Suggestion
Suppose you use the document properties as described above and discovered that a date is being stored as text. You might then look at the form and find that the field is defined as type date/time. What went wrong?
The first thing you have to realize, is that in many cases the field's definition on the form doesn't matter. A Notes document is a generalized container of information. Any document can contain items with any name, any datatype, any value. The form only applies when you are editing and saving the document -- and even then, the datatype you select for the field is not a guarantee, depending what else you do on the form.
What are the different ways in which you can get a mismatch between the datatype on the form and the value stored in the document -- and what do you do to correct them?
Incorrect Form Design
Even though the datatype of a field is selected as date/time, there are other things you can do on the form that can result in the value still being stored as text.
When you save or refresh a document, the order of operations is as follows:
1. Fields are converted to the datatype defined in the form design.
2. Computed field formulas and input translation formulas are evaluated and the result assigned to the field.
3. Input validation formulas are evaluated.
Notice that the fields are converted to the appropriate datatype only once, and if they have a computed field or translation formula, they are assigned a second time after that. If these formulas return a value of the wrong datatype, it is not corrected to the defined datatype of the field -- that conversion already occurred. So if an input translation formula returns a text value, a text value is stored in the field, even if the field was defined as type date/time.
For instance, a form has a computed date/time field with the formula @Text(@Modified; "D0S0"). Hiro saves the document, with the field showing 2009/03/05. When Dan opens the document, or sees it in a view, he also sees "2009/03/05" -- the text value stored in the document.
This sort of error most often occurs with date/time fields when you think you need to store a value in a particular format -- say, with date only, or forcing display of a leading 0 on a one-digit month or day despite the user's preferences. Remember, though, real date/time values do not contain formatting information. They are stored as a number and formatted by the user's workstation. If you need to control the display, use the field formatting properties, as shown on the right -- not the translation formula.
Also, however, please consider just letting the user's preferences control formatting. It will in some cases avoid confusion. If you do use custom formatting, we suggest using an unambiguous format such as the one shown in the screenshot. Note that month and weekday names are translated to the local language when the workstation does the formatting.
Form Was Modified
A common cause of datatype inconsistency, is that the form previously had a different datatype defined for the field. Documents created with the earlier version of the form, are not automatically corrected when the form is modified. This is a separate step you must do.
For instance, you might write a fixup agent to run on all documents, as follows:
SELECT DateNeeded != "" & @IsText(DateNeeded);
_tmp := @ToTime(DateNeeded);
@If(@IsError(_tmp);
"";
FIELD DateNeeded := _tmp
);
The first line limits the execution to documents that need attention, the second line calculates the new value, and the rest reassigns the field, provided conversion to a date was possible. Please note that this is dangerous, however. Your application may contain date/time strings that are formatted according to different preferences than yours. You might end up with the wrong date because your computer interprets the string differently from the person who created the document. In general, there's no way for the agent to tell whether it has converted the date correctly. This is an excellent reason to take care to avoid getting into this mess.
This agent might fail to update a document because the string couldn't be translated to a date (not even the wrong date). In that case, it's better to leave the document unchanged than to overwrite the information you do have with an error value. But you're left with the problem of what to do with the remaining documents. One possibility is to create a view using the above SELECT statement as a selection formula, so that you can locate and manually correct the erroneous documents.
Note: If the old documents contain stored forms that use the wrong datatype for the field, even if you fix the data, it will revert to text the next time someone edits the document. To fix this, you may have to remove the stored forms from the documents. All right-thinking people hate stored forms. To learn how to remove stored forms from documents, see the Designer help document "Using agents to update documents affected by form changes"
Back-End Code
You can write code to create or modify documents through the "back end" -- similar to the fixup agent in the previous section -- and this code is not constrained by the form in any way. For instance, you might have an agent that assigns the field:
FIELD DateNeeded := @Left(DateTimeNeeded; 10); REM {Amazingly bad -- don't do this!};
@Left always returns a string -- never a date/time value. So the value stored in DateNeeded will be a string, regardless how the field might be defined on some form.
Even worse than this, the formula is assuming that DateTimeNeeded is also a string -- which might be a correct assumption in this case, but is a bad idea -- and also that it can get the date part of a date/time text by reading off the first ten characters. That's often true, but not always.
The only fix for this, is to make sure to write code that assigns a value of the correct datatype. Use the document properties dialog as shown above, to validate that the value assigned is of the right datatype.
The same thing can happen when items are assigned through LotusScript. You must read the documentation of the functions you're using to see what their return type is. For instance:
Dim whenDue As New NotesDateTime("")
whenDue.SetNow
whenDue.AdjustDay 7
Call doc.ReplaceItemValue("DueDate", whenDue.DateOnly) ' wrong way!!!
What's the problem with this code? We wanted to assign the date field with just the date part of the date/time value. But the NotesDateTime.DateOnly property returns a string value, so you actually stored text in the field. Here's the corrected code.
{code}Dim whenDue As New NotesDateTime("")
whenDue.SetNow
whenDue.SetAnyTime
whenDue.AdjustDay 7
Call doc.ReplaceItemValue("DueDate", whenDue){code}
Likewise, if you try to affect the format of a stored date by using the Format function:
Call doc.ReplaceItemValue("WhenRequested", Format(Today, "yyyy/mm/dd") ' can't be done!
There is no correct way to do this. As we already covered, date/time fields cannot store formatting information. Attempting to store a date with a particular format, can only result in storing text instead.
Code to assign fields doesn't have to be in an agent, of course; it might be anywhere. You have a little more leeway when working with fields on a document you're editing, because when you save your changes, Notes will try to convert the string to a value of the datatype specified for the field; so it might correct your error. For instance, if you have a form action that uses this statement in edit mode:
FIELD DateDue := @Text(@Adjust(@Now; 0; 0; 7; 0; 0; 0); "S0");
you have assigned a text value to the field, but it usually won't matter because when you save (or refresh) the document, DateDue will be converted to a real date/time value (assuming the DateDue is defined as date/time, and you haven't made an error such as described in the previous section). However, even if it saves correctly, it may cause problems in the meantime, for instance if there are other formulas on the form that use DateDue and assume it's a date value; when you refresh the form the first time, you may get a datatype error in those formulas. Then you refresh it again and the error goes away, because the first refresh has changed DateDue to a date value.
Date/Times in Views
One thing you might find surprising in the discussion below, is the idea of "storing" information in a view index. Documents are normally considered storage, while one thinks of a view as just a way to display documents. However, the server pre-calculates the view index and stores the column data on disk. Just like a field in a document, a view column can store either a date/time value, or a date alone, or a text value that contains a date/time string, and if you use a formula, the datatype may be different from the datatype of the field from which the information was read.
For instance, if a view column has the formula @Modified, it stores a full date/time value including timezone. If you use the date/time style settings of the column to tell it to display only the date, you'll only see the date, but the column value still stores a date and time, which you could read using @DbLookup, for instance. If you were to sort the column, the entries would show in the exact order the documents were last modified. If you categorized the column, you would see many categories for the same day, each containing one document. That's because the category is grouped based on the exact value of what's in the column, down to the 100th of a second, even though the precision of the display is truncated. Also, users will see different dates on the category headings depending on their timezone.
If you change the column formula to @Date(@Modified), then the value stored in the column contains only the date. You would only have one category per day, and all users of the same replica would see documents listed under the same date regardless of the user's timezone. The question then becomes, whose timezone is it using to figure out the date?
The hint is in the words "the same replica." You have to think about which computer will evaluate the formula @Date(@Modified). In a server replica, the server builds the index, so it will be based on the server's timezone. In a local replica (or a "desktop local" view of a server database) the user's workstation calculates the column formula, so it would be based on that workstation's timezone.
Now consider the effect of the user's and server's date formatting preferences. Say one user, Dan, has his workstation set to format dates as MM/DD/YYYY. Another user, Hiro, uses YYYY/MM/DD. Hiro is Japanese. They're both using the same server in Europe, which is set to format dates in DD/MM/YYYY order.
When Dan opens an application on the European server, he expects to see dates in MM/DD/YYYY order. That's the way his computer always displays dates. For instance, if Dan sees "04/08/2009", to him that means April 8th -- but maybe it really means August 4th. If Hiro sees "04/08/2009", he might not know what to think. What determines whether a user sees a date/time in their preferred format, or whether all users see the same sequence of characters?
When the user opens a view, the server sends them the column values that are stored in the index. If the index contains a date/time value, the server sends the date/time numeric value and timezone information to the user's workstation, and the workstation converts it to text based on the user's settings and the view column's settings -- the developer can deliberately override the user's preferences (though we don't generally advise this).
However, suppose the view column formula were @Text(@Date(@Modified)). Now, both the timezone conversion and the conversion to a text string happens on the server, using the server's settings. When a user opens the view, the server sends them a text string for that column value. The workstation doesn't convert a date to text, because the value is already text -- there's nothing to convert. So all users will see the same text. As we saw in the above example, the European server's default way of formatting dates, isn't correct for all users; neither Dan nor Hiro is happy with what they see.
Furthermore, think about what happens if the view column is sorted. Dates and date/time values sort chronologically. Text values, however, sort alphabetically, so that "03/06/2008" would appear between "03/05/2009" and "03/07/2009".
Recommendations about views
Avoid use of @Text to convert dates in view column formulas.
If you don't want to conform to the user's date/time preferences, use the date formatting settings in the column properties. If you do this, consider making it an unambiguous format, such as YYYY/MM/DD or DD monthname YYYY.
There are some situations where you're forced to use @Text; e.g. in response columns, where you have to get multiple fields of information into a single column. Please note there is an optional second argument to @Text, and also you can control the formatting exactly by converting the components of the date yourself, e.g. @Text(@Year(Posted)) + "/" + @Text(@Month(Posted)) + "/" + @Text(@Day(Posted)).
If you use @Date in a column formula, be aware how this ties in with timezones.
The date/time will be stripped down to a date based on the server's timezone in a server replica, or based on the user's timezone for a local replica. This might be what you want; but we suggest you think about it and work out what different users will see in their different situations. Don't assume your application will only ever be used in one timezone.
Be aware that supposed date fields in documents may contain text values.
Just because a field name contains the word "Date", don't assume it's really a date/time field. As described above, all too often it happens that a text string is used to store date/time values in documents, even in some cases where the field is specified as a date/time field on a form (as discussed below).
Date/Times and Full-text Search
When Notes constructs a full-text index of an application, it notices the datatype of each field. Date/time fields support a different query syntax than text fields -- for instance, with a date/time field you can test for before or after a particular date:
([Form] = "Something") and ([ReqNeeded] < 4/4/2008)
whereas with text values, you can only test for whether the string contains a particular word or phrase -- there's no way to do a range comparison. Since searches by date range are very useful, this is another strong reason to store date values as date/times, not as text.
The determination of the datatype is made based on the data in the documents. The first document that the indexer comes across containing a particular field, determines the datatype for that field. So if your application contains some documents with a date value, and some with a text value, the indexer might decide either way, essentially by chance.
If the datatype was determined in error, you should:
- Correct your design to avoid creating more incorrect documents.
- Use an agent (such as the one above) to correct all existing documents.
- Delete the full-text index.
- Copy-compact the application.
- Re-create the full-text index.
The Midnight Problem / Off By One Day
A common problem we see in applications that work with dates in LotusScript, is that an item gets assigned a value of 12:00:00 AM on some date, when you intended to assign it a date/only value. This results in different users seeing the same document show different days, based on what time zone they're in. For instance, 04/04/2009 12:00:00 AM for someone in New York, is 04/03/2009 11:00:00 PM for someone in Chicago.
Often the problem is not obvious because the view column and/or field settings are set to display the date only, so it's not evident that the field contains a time also. However, you can see this by using the document properties dialog from a view, as shown here.
This problem occurs when you use the LotusScript "date variant" datatype to assign a field. Behind the scenes, this datatype is just a floating-point number. The integer part represents the day (an offset from a base date) and the fractional part represents time of day (e.g. 0.5 means noon). This doesn't include information about timezone, DST, and whether the number contains a date only vs. date and time. The variant representation for just a date, is the same as the variant representation for midnight on that date. This problem is hard to see when you're in LotusScript, because when you convert a date/time to text, it assumes the time should be excluded if it's midnight. E.g. the statement:
Print Today
Just prints the date. But when you copy this value into an item, it assumes you meant to copy the date and time, even if the time was midnight. For instance, this line would cause the problem:
doc.ReplaceItemValue "WhenRequested", Today ' do not do!
The correct way to assign a date-only value in LotusScript is by using the alternate date/time representation, the NotesDateTime class.
Dim ntoday As New NotesDateTime("")
ntoday.SetNow
ntoday.SetAnyTime
doc.ReplaceItemValue "WhenRequested", ntoday
Notice we're assigning the item the value of the NotesDateTime object -- not the value of some property returned by the object. E.g. this assignment:
doc.ReplaceItemValue "WhenRequested", ntoday.ZoneTime ' do not do!
does not yield the desired result, because ZoneTime property returns a string.
If programming in Java, a DateTime class exists which is similar to NotesDateTime; this class is a better match with the properties of a Notes date/time item value, than other date representations Java might offer.
Comparing Date/Time Values
In formulas
In formula language, date/times can be compared using the normal comparison operators <, = and so on. A later date/time is considered greater. If two times are in different timezones, or one uses DST and one doesn't, the same rule applies; the later one is greater (you can think of them as being converted to a common timezone for comparison).
If comparing a date-only value to a date/time value, the time value is discarded, so [3/4/2008] is considered equal to [3/4/2008 3:04 AM]. Of course, the date taken from the date/time is the date in the timezone of the computer evaluating the formula, which may not be the same for all users.
You can also find the difference between two date/time values using the subtraction operator. E.g. A-B is the difference between time A and time B in seconds. Once again, they are converted to a common timezone for comparison, and the difference between a date-only value and a date-and-time value is the difference between the days (in seconds).
Because of Daylight Savings Time, the difference between the same time on two different dates is not necessarily evenly divisible by 86,400 (the number of seconds in a day). However, the difference between two date-only values is a multiple of 86,400.
In LotusScript
As mentioned before, LotusScript has two ways of representing date/time values; the date/time variant and the NotesDateTime class.
The date/time variant is basically just a floating-point number with the date as the integer part and the time as the fractional part. It can be freely converted back and forth to a Double, without losing information.
The comparison operators <, =, etc, work with dates as you would expect.
Subtracting one date from another yields a difference in days.
The NotesDateTime datatype is a class, so access to it is via its properties and methods -- not the relational operators. To determine which of two NotesDateTime objects is later, use the TimeDifference or TimeDifferenceDouble method, e.g.:
If ndt1.TimeDifference(ndt2) > 0 Then
Print "ndt1 is later."
End If
One can also use properties that convert the date/time to a variant, e.g.:
If ndt1.LSLocalTime > ndt2.LSLocalTime Then
Print "ndt1 is later."
End If
In Java
TODO :-)
"Business Days"
To find the number of business days between two dates in a formula, use @BusinessDays -- read about it in the Designer help. Remember the number of business days include both endpoints, so usually @BusinessDays(@Today; @Tomorrow) = 2.
Working the other way, to find what date is n business days after a given date, is more complicated; you would have to use a loop to calculate and adjust your initial guesses. For example:
REM {Find the date N business days after StartDate};
_holidays := [03/06/2009]:[03/09/2009]:[03/13/2009]:...; REM {or look them up from somewhere, whatever};
_ans := @Adjust(StartDate; 0; 0; (N/5)*7; 0; 0; 0); REM {Should get us reasonably close};
@While(
0 != (_diff := N - @BusinessDays(_base; _ans; 1:7; _holidays) );
_ans := @Adjust(_guess; 0; 0; _diff; 0; 0; 0)
);
REM {Final answer...};
_ans
The @BusinessDays function is also available in LotusScript by means of the Evaluate function, but see the references for an alternate implementation.
Daylight Saving Time
DST is a consideration mainly when adjusting date/time values by a given number of days or hours.
The LotusScript date/time variant datatype, doesn't manage DST at all. Its value is just a number; if you add 1 to it, you'll get the same time on the next day, assuming that's possible.
Every other way of representing date/times does take DST into account, however. These representations generally let you separately adjust days and hours. For instance:
@Adjust(SomeTime; 0; 0; 1; 0; 0; 0)
and
@Adjust(SomeTime; 0; 0; 0; 24; 0; 0)
do not necessarily give the same result. In the first case, if SomeTime was 2:00 PM Sunday, @Adjust always returns 2:00 PM Monday. But in the second example, if we had a DST change overnight, then the second @Adjust would return 1:00 PM or 3:00 PM, depending which way the time shifted. And of course, DST has some oddities that hopefully will never be an issue for you, such as the fact that on some days 2:30 AM comes twice, on other days not at all.
DST might be a consideration in cases of a repeating meeting, where you're trying to come up with the list of date/times. If the meeting involves people in different timezones and with different DST settings, as increasingly many meetings do these days, it isn't possible to hold it at the same time of day every time for all participants; it's going to shift for somebody, and if you care who, then you have to decide whether to do your adjusting with or without DST. Some date/time representations let you specify this; in other cases it depends what timezone is associated with the value.
All right-thinking people hate DST.
References