Some may have noticed that I've generously lifted part of my introduction from the title one of my favorite childhood books - A Wrinkle In Time by Madeleine L'Engle - learn more here.
Entering Date and DateTime values into software applications is something most of us don't put too much thought into, which as end users of well designed software, we really shouldn't have to.
As application administrators, consultant developers, and analysts we do need to put a bit more thought into the configuration and design of Date/DateTime fields and it helps to understand a bit more about what goes on inside the application.
For many Date values it is important to know not only the Date (i.e. Day, Month, and Year) but also the precise time. Herein lies the challenge of course when you work in an organization where the users are not all in the same timezone.
For example - If someone in New York City creates a new Contact record on Oct 23, 2011 at 4:35pm - what should a user in Seattle see as the Create DateTime if they were to view that Account record?
Seattle is 3 hours behind NYC, so at the precise moment that the record was created in NYC, it was 1:35pm in Seattle. We could add a timezone code to the display value and assume that the end users know enough about time zones and are quick and accurate enough with their math to work it out in their heads, but of course that isn't really practical and would not be a particularly good user experience.
So, we want the user in Seattle to see the value as Oct 23, 2011 at 1:35pm ... and if another user in London viewed the record, it should be expressed in that persons timezone as well. Some timezones are so many hours away that not only the time value will change, but also the Date (day number) itself - if you are in the US and have scheduled conference calls with India or Australia you have experienced the impact of large TimeZone differences first hand.
To rip-off a phrase from Prof. Einstein - it (Time) really is all relative...
Sage SalesLogix accomplishes this by converting DateTime values entered by the users (which are in the users local time zone) into a single common standard called UTC, Coordinated Universal Time, often referred to a Greenwich Mean Time due to the fact that when world timezones were originally established the starting point (i.e. 0 hour) was set at a meridian that passes through Greenwich, England.
DateTime values are converted to UTC and stored in the database. When a client application retrieves the values from the database and wishes to display the DateTime value to the end user, the client application converts the values from UTC to the users local timezone using the timezone defined by ther operating system on the users computer.
If the client application is using the Sage SalesLogix OleDB provider, the DateTime conversions are handled transparently by the OleDB provider. If the application uses the web architecture (the WebClient or any SData webservices based application), then the application itself is responsible for converting to/from UTC and LocalTime. The reason for this is that in the web architecture Sage SalesLogix leverages server based in-memory data caches to enhance performance and minimize trips to the database. This means that the DateTime values must be stored in the cache in UTC because the consumers of that data may be coming from a variety of timezones.
OK - so now we can see how we can accurately store a DateTime value and we can display that to the end user in a manner that reflects the time it would have been in their timezone relieving them from having to work out the details and doing manual conversions.
What about Date (only)? There are occasions where we don't care to be as precise about the time and only wish to store and display the Date value with no time value. Sage SalesLogix can accomodate this as well by configuring the database field to be of type Date rather than DateTime. Note for the more technically inclined, this is a Sage SalesLogix data type concept - in the actually Database we use the same db datatype for both.
If a DB field has been configured as Date (only) then the Sage SalesLogix OleDB provider will not perform UTC/LocalTime conversion. Web architecture based applications need to either dynamically determine the Date vs DateTime configuration or, commonly enough - the developer/customizer configures the application at design time to behave appropriately for that DB field.
Can I store a DateTime value, but have Sage SalesLogix NOT perform UTC/LocalTime? In fact, you can! This is an unusual case, but does sometimes occur. For example - if we were to design a <space> solution that allowed us to schedule live presentations (seminars, etc.), we might wish to define an agenda for various parts of the day. Let's say that the seminar is occuring in Los Angeles, we don't want a user in NYC to view the adgenda with the times converted to their timezone - imagine their surprise when after flying to Los Angeles they arrive early for various sessions.
As it turns out, when you configure a DB field as a "Date" (vs. DateTime) field in SalesLogix, you are still able to store whatever Time value you wish - the "Date" datatype merely tells SalesLogix to NOT perform UTC/LocalTime converstions.
Is there another wrinkle to this DateTime stuff ? Well, in fact there is. In the real world we sometimes need to put tasks and tentative meetings or phone calls on our calendar, but we do not always know precisely what time those activities should start or will end. Sage SalesLogix accomodates this by allowing the user to indicate that an Activity is "timeless", in which case Sage SalesLogix does NOT perform UTC/LocalTime conversion on that exact record.
Internally, this gets tricky as it means we cannot rely on ALL records being UTC converted or not converted, instead we have to determine this on a record by record basis. How Sage SalesLogix accomplishes this is that there is a special third data type for DateTime fields, the dynamic DataTime data type. If a DB field has been configured to be this dynamic DateTime data type, then the secret is to set the time portion to 00:00:05 (5 seconds after midnight) as the indicator that this record is "timeless" and should NOT be converted to/from UTC.
I hope this blog post has shed additional light on how SalesLogix keeps all system users stay on the same (relative) page with respect to DateTime values.
Additional Details for the technical crowd: The Database Manager utility, found in both the Win32 Administrator application and the Win32 Architect application provides facilities for creating custom tables and database fields. This feature will allow you to choose "Date" or "DateTime" as a datatype - but it does not offer a "Dynamic DateTime", so how can you leverage that if you needed to?
The answer can be found in one of the Sage SalesLogix database schema "meta data" tables - SECTABLEDEFS. select DATETIMETYPE, * from sectabledefs where DATETIMETYPE is not null and DATETIMETYPE <> '' order by DATETIMETYPE asc
D = Date (only) U = DateTime (i.e. perform UTC conversion) Z = Dynamic DateTime
If you change these values directly in the database, be sure to restart all relevant Sage SalesLogix services in case any meta-data has been cached by running services or applications.