|date display and entry fields to be in mm/dd/yyyy |
Good luck with that when your European users type in 31/12/2012.
The YYYY-MM-DD format exisits for a reason. It is the ONLY worldwide non-ambiguous format. See ISO 8601, EN 28601, ANSI X3.30, RFC 3339, etc.
If you really want to proceed with this, use the YYYY-MM-DD format on all internal stuff and normalise it to the UTC time zone too. Use other formats only on display and input.
That is what i'm saying... users see and enter in mm/dd/yyy. 31/21/2012 is the mm/dd/yyy date format!
|Good luck with that when your European users type in 31/12/2012. |
Ok... so what i'm hearing is is that it's better to keep the date format yyyy-mm-dd throughout the backend and frontend (the entire system). Convert to mm/dd/yyyy only when it needs to be displayed and convert back to yyyy-mm-dd after user input.
Also, the site is only for internal use for a U.S. company.
|31/21/2012 is the mm/dd/yyy date format! |
Yeah, the 12th day of the 31st month, one example of the dd/mm/yyyy vs. mm/dd/yyyy ambiguity: which can't be detected for the first 12 days of each month.
Sorry, i meant to say 31/12/2012 not 31/21/2012.
In any case, i currently use the MySQL yyyy-mm-dd date format throughout the system and convert to mm/dd/yyyy (the format the client wants) only on display or input.
I just thought it would reduce the amount of conversion throughout the system if i did the yyyy-mm-dd <--> mm/dd/yyyy conversion on database table i/o.
If you don't normalise the data to one time zone, it becomes very difficult to sort it into real time order after the event.
New York enters data at 2012-08-20 14:00 New York time.
Two hours later, L.A. enters data at 12:00 L.A. time.
Sort this by time and it will be sorted in wrong order if only the "numbers" are used without reference to time zone. If you do store timezone data, the algorithm to sort all of the data into order is horrendous.
Normalise it to UTC and it becomes a straight numerical sort again. For display you merely need to ask the user to enter their UTC offset to see the data presented as their own local time.
Even if you're only interested in days:
Let's say New York enters data at 2012-08-20 01:30 New York time.
Two hours later, L.A. enters data at 2012-07-19 23:30 L.A. time.
It gets worse if you have to deal with DST changes etc.
(a day that does not have from 02:00 till 03:00 and even worse a day that has 02:00 -> 03:00 TWICE!).
Do yourself a favor and exclusively use UTC internally, and just display it in local time. Similar: where you do input validation: you convert it all to UTC.
Changing that timezone means you store it as UTC timestamps, even if you only care about displaying the date ... It's the only way to foolproof it into the future.
Ok, i understand and except your recommendation for storing date/time in UTC, converting it only for display and converting it back to UTC after user input.
All dates or dates & times are input by the user using the jquery datepicker in mm/dd/yyyy format.
So, my questions are:
1. Using MySQL 5.5.20, what is the best data type to store the UTC timestamp?
2. It seems that storing the full datetime is better even if you don't always need the time part?
5. 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])
I'd go for DATETIME or TIMESTAMP:
I guess DATETIME would be the better choice for the following reasons:
1. 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.) I prefer to convert on the client side.
2. 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'.
3. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.
I assume your lack of response to items 2 thru 5 means that i'm correct?
|I assume your lack of response to items 2 thru 5 means that i'm correct? |
No it means I'm lazy ;-) and/or don't know enough about jquery to answer.
2: I'd state it's better to keep too much - it's just my opinion: but it can depend on what you need (e.g.: if you want to know when something happens: keep track of too much - can't hurt you. But let's say you keep a date till when a device is under warranty: well that's trickier: you need to define the "when" correctly , and define what you do with borderline cases - depending on that having too much data can;t hurt you again - but you might end up that the date is to be interpreted as in local time.
- the timezone settings might well be off. - somebody might be teleworking from an other timezone ...
Thanks for getting out of bed to respond - wink wink!
Also there are two type of date times i need to store. One is for auditing employee input and the other is for scheduling customers.