|Working with UTC and local date time conversions.|
| 10:16 pm on Aug 28, 2012 (gmt 0)|
I'm hoping i can get some help figuring out the simplest methods for the storage and display of date & time under the following situation.
The web app is a internal company app that will only be used by franchise employees in various states including corporate office employees currently based in Michigan. While most franchisees will operate their own local call centers, some franchisees will have the franchisor (corporate) manage customer calls for them. So, as an example, the corporate call center in Michigan may field customer calls for the California franchise.
In the case of this web app, there are two date time events that need to be stored, the audit trail and work order schedule date times. An audit trail date time is created every time a user of the web app creates, updates or deletes work order records. A work order schedule date time is created or updated every time a work order is added to or moved on the dispatch board.
Obviously, for local franchise call centers, they would need to display audit and schedule date and times local to there city and state. However, with regard to the corporate call center fielding calls for customers of franchisees in different time zones, i think you'd want the audit trail date times to be local to the corporate call center city and state and schedule date times to be local to the time zone the franchisee the customer is requesting services from.
Possible truths so far... (tell me if i'm wrong)
1. From what i have read, it's best to store and work with date time in UTC format throughout the web app and only convert on the client side.
2. Also, converting on the client side appears to insure simple and proper conversion to and from the call center's local date time.
4. With MySQL, it appears that setting the UTC data type to DATETIME is better than the TIMESTAMP data type. While MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format, The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59' where as TIMESTAMP only has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC. Also, it appears the MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. This does not occur for other types such as DATETIME allowing me to convert on the client side.
the questions are...
C. What would be the best method for converting a client side jquery datepicker date to UTC? I'm thinking UTCDateObject = Date.UTC(datepicker[yy], datepicker[mm], datepicker[dd]).
D. How would i handle the schedule data time situation where the Michigan corporate call center fields customer calls for the California franchise? If the Michigan call center schedules a California customer's work order for Tuesday at 4pm on that franchisee's dispatch board, then the work order as seen by an employee at the California franchise needs to show up on the Tuesday 4pm slot on their dispatch board and not 1pm Michigan time.