Date format requirements between user and database question.

[nelsonm]

Hi all,

I apologize if this sounds like a stupid question, but...

The user wants all date display and entry fields to be in mm/dd/yyyy format. However, a MySQL date field is in yyy-mm-dd format.

So, the question is...

Is converting mm/dd/yyy to yyy-mm-dd using the STR_TO_DATE() function on insert and update statements and converting yyyy-mm-dd to mm/dd/yyy using the DATE_FORMAT() function on select statements the best place to convert or is it better to keep the date format yyy/mm/dd throughout the web application until it needs to be displayed and then converted to mm/dd/yyyy using javascript conversion functions?

It would seem to be more efficient (with less required conversions) to do it at the backend on the MySQL query statments than at the frontend.

thanks.

[g1smd]

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.

[nelsonm]

Good luck with that when your European users type in 31/12/2012.

That is what i'm saying... users see and enter in mm/dd/yyy. 31/21/2012 is the mm/dd/yyy date format!

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.

[g1smd]

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.

[nelsonm]

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.

I'm not doing any normalizing to the UTC time zone. That brings up a possible problem since the site will be used across the country by various franchises. When the user needs the current date, the page gets it using javascript "new date()".

[g1smd]

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.

[swa66]

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.

[nelsonm]

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.

Also, from what i've read so far... it appears that it's better to do any conversion on the front end or client side. Use JavaScript or jquery to convert the UTC timestamp and display the local time based on the user's computer settings. Likewise, use javascript or jquery to convert the local date/time back to a UTC timestamp. The users timezone should already be set by the OS so i should not have to worry about it.

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?

3. Assuming var UTCDateObject contains the UTC time stored in a database table, Would using javascript var LocalDateTime = UTCDateObject.toLocaleString() or some jQuery equivalent be the best method for converting the UTC time object to a displayable local datetime?

4. Would using javascript var LocalDate = UTCDateObject.toDateString() or some jQuery equivalent be the best method for converting the UTC time object to a displayable local date?

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])

thanks

[swa66]

I'd go for DATETIME or TIMESTAMP:
Ref: [dev.mysql.com...]

[nelsonm]

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?

[swa66]

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.

Javascript doing the local time conversion: I'd be cautious:
- the timezone settings might well be off. - somebody might be teleworking from an other timezone ...
- also javascript might be turned off

[nelsonm]

Thanks for getting out of bed to respond - wink wink!

As i previously said, this web app is for internal company use by call center personnel, so javascript is required. However, you have brought up a good point. While most franchisees will operate their own local call centers, some franchisees will have the franchisor (corporate) manage customer calls for them. The corporate call center may or may not be in the same state or time zone.

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.

Since i'm pretty sure any conversion (if any) should be handled on the clients, i think i should post further questions in the "JavaScript and Ajax" forum catagory. What do you think?