RE: Time Zones in Searches

Let me suggest a basic approach to time searching that captures all of the
information that is available at data storage time and search time and makes
simplifying assumptions to cover those cases where some data is not known.
Judge for yourself if this set of assumptions can be used as a basis for
your search implementation.
 
1. A full time stamp is stored in the database for each event, even if just
a date is known.  Let's call this the "stored date/time value".  It is
always specified in terms of UTC.
 
2. You may, per your larger set of time/date requirements, also have a
secondary data element in the database showing the time delta from UTC
and/or time zone of the originating data.  Let's call this the "stored time
zone value".  Ignore any references below to this value if you decide you
don't need it.
 
3. If only a date is known, the stored date/time value is set to 12:00 noon
UTC, even if the storage event can be pegged to another time zone.  You may
set a special stored time zone value which reflects this condition but
otherwise behaves like UTC.  A full time stamp for the storage event is
assumed to be available in all the cases which follow.
 
4. If the originating time zone is know or can be calculated reliably, put
this in the stored time zone value.  Adjust the given date/time as necessary
from the given time zone to UTC and put it in the stored date/time value.
 
5. If the originating time zone is not known, but can be inferred or guessed
within your comfort level, then use that as the basis for setting your
values.  You may set a special stored time zone value which reflects this
condition but otherwise behaves like the inferred time zone.  Adjust the
given date/time as necessary from the inferred time zone to UTC and put it
in the stored date/time value.
 
Those are the rules for setting stored date/time values and stored time zone
values.  The rules for setting search parameters are similar.  Note that the
algorithm is no more complicated, but the reliability of the search data may
be lower depending on how users interact with the system in specifying their
searches.  If searches are based on times (as opposed to dates), then it is
critical that users understand (or be able to control) whether their time
parameters will be interpreted according to their own time zone, according
to UTC, or according to the local time at the point of origin of the data.
To support the third option, an originating time/stamp will either have to
be stored in the record or calculated from the stored date/time value and
the stored time zone value as each record is examined.
 
Assume that all search parameters are specified either in terms of the local
time of the person making the search or in terms of UTC.  If searches can be
specified in terms of the local time at the point of origin of the data (or
some other point of reference), then the necessary modifications to this
algorithm will be left as an exercise to the reader.  :-) 
 
1. A full time stamp is generated for each search, even if just a date is
specified.  Let's call this the "search date/time value".  It is always
specified in terms of UTC.
 
2. If only a date is specified in the search, then the search date/time
value is set to 12:00 noon UTC, even if the search can be pegged to another
time zone.  A full time stamp is assumed to be the basis for searches in all
the cases which follow.
 
3. If the search parameter is known to be specified in terms of UTC, then it
is put in the search date/time value without change.
 
4. If the search parameter is known to be specified in terms of the local
time of the person making the search and that user's time zone is known or
can be calculated reliably, then the specified time is adjusted from the
given time zone to UTC and placed in the search date/time value.
 
5. If the search parameter is known to be specified in terms of the local
time of the person making the search, and the user's time zone is not known
but it can be inferred or guessed within your comfort level, then the
specified time is adjusted from the inferred time zone to UTC and placed in
the search date/time value.
 
6. If the search parameter is known to be specified in terms of the local
time of the person making the search, but the user's time zone is not known
and cannot be inferred, then the specified time is put in the search
date/time value without change.
 
7. If you do not know whether the search parameter has been specified in
terms of UTC or the local time of the person making the search, then the
specified time is put in the search date/time value without change.
 
Now you have your best approximations for stored date/time values and search
date/time values, and you can conduct your search.  If all you are searching
on is the date, then you can ignore the time portions of the timestamps.
 
Given the rolling midnight problem, you can have difficulty knowing exactly
what UTC time spans are intended to be included if only a date is specified
in a search.  For this reason, you might want date-based searches to match
stored dates from a day before to a day after the search date. Similarly you
might want time-based searches to match stored times within a reasonable
range on either side of the specified time.
 
I hope these rules help a bit to rationalize an inherently fuzzy problem.
 
Merle

-----Original Message-----
From: Eric Kreiser [mailto:ekreiser@knowledgeplanet.com]
Sent: Friday, October 26, 2001 12:30 PM
To: 'Thierry Sourbier'; Www-International-w3c-org (E-mail)
Subject: RE: Time Zones in Searches


--Yes, I think I need to convert to UTC.  I have data being entered for time
zones all around the world, some for location based events and others for
virtual event not tied to any specific location.  I need to be able to do
resource checking across all these events to make sure I have all the
resources available in that timeframe.
--I have no way currently to infer a users time zone.  In my case the
ultimate would be if somehow I could get that information from an HTTP
request header in a JAVA servlet.  But, I don't know of any way to do this.
This out asking the user, how else could I know what time zone they are in?
We have discussed storing this information in the database relative to each
user...
--Not sure the accuracy is completely necessary, but I am not sure how to
avoid it from a technical architecture perspective.  How would I totally
ignore time zones during searches without storing every date/time in my
system twice??  The value once converted to UTC, and once in the local
date/time.
 
Yes, I am using JAVA 1.3 to do my date/time conversions to UTC, which thru
our testing seems to be accurate.
 

-----Original Message-----
From: Thierry Sourbier [mailto:webmaster@i18ngurus.com]
Sent: Friday, October 26, 2001 10:19 AM
To: Www-International-w3c-org (E-mail)
Subject: Re: Time Zones in Searches


> Is there a standard/commonly accepted way to search dates with time zones.
 
A certainly common approach is, as you did, to convert all date/time to UTC
and do the comparaison all comparaison in UTC. Now as you point out this
raises the question of converting to UTC when the user time zone may not be
known. More insights on your application and the use of the dates you are
manipulating would be very usefull to determine if:
 
- The conversion to UTC is indeed necessary (e.g. if you are storing local
event date and time this may not be necessary).
- There are ways to infer the user time zone (e.g. user login information).
- Your user base can understand the need for time zone (e.g. scientifics).
- Great accuracy is needed (what the system should cover to be useful to the
users).
 
Also I like to point out that accurate convertion to UTC is not an easy
thing as it is complicated by the fact that many places use daylight saving
making convertion of a time/date in the past or future much more complicated
than having a single offset. Java has greatly enhanced its TimeZone class to
make this sort of calculation much easier, the referring source in the
subject is probably TZ ( http://www.twinsun.com/tz/tz-link.htm
<http://www.twinsun.com/tz/tz-link.htm> ). You'll find may time zone
information related links there.
 
Regards,
 
Thierry
<><><><><><><><><><><><><><><><><><><><><><>
www.i18ngurus.com <http://www.i18ngurus.com>  - Open Internationalization
Resources Directory

----- Original Message ----- 
From: Eric Kreiser <mailto:ekreiser@knowledgeplanet.com>  
To: Www-International-w3c-org  <mailto:www-international@w3.org> (E-mail) 
Sent: Friday, October 26, 2001 3:12 PM
Subject: Time Zones in Searches

Is there a standard/commonly accepted way to search dates with time zones.

 
I am developing an application that I need to store all dates relative to
time zone.  (Using Oracle 8i as a database)
When the user enters the information, before storing it to the database, I
convert all date/times to UTC (I need a consistent/comparable date/time).
 
Now the problem, when the user is searching based on these dates
----Is it common to have the user specify a time zone relative to the
date/times on the search screen???
----Or, is it common to have the user just enter a date/time on the search
screen and return items that occurred on that date/time irregardless of time
zone???
 
Is anyone doing anything similar?  Any suggestions/comments,etc... are
welcome.