Converting between MySQL Date and Time Formats
March 9, 2011
All of the larger systems that I've helped develop necessitated storing some combination of date and time fields. Almost universally, these fields presented difficulties of some kind or another. One particularly memorable challenge was converting between internal storage and display formats that featured the month name rather than a numeric one. We were able to overcome it using a combination of database and programming solutions, but it would have been much easier had we been using MySQL. Its thorough and flexible date handling allows you to switch between internal and external date/time formats with relative ease. Now I'd like to show you what we could have done in MySQL...
Some Date/Time BasicsMySQL supports a variety of date/time data type combinations. All you have to do, when creating a date/time column, is choose one of the following types that most closely matches the date/time portion and accuracy that you require:
While it is common to store dates using a dash (-) as the delimiter and a colon (:) as the time delimiter, it is in fact possible to use any character, or even no character at all, between the date and time segments. Hence, the following formats all represent the same date and time:
Input ValuesMySQL will happily accept any of the following statements as date inputs:
The yyyy-mm-dd format used by MySQL is called the International (or ISO) date format. The beauty of using that format is that it prevents the days and months from being mixed up. Depending on the database locale, ambiguous months and days such as 3/4/1999 could be interchanged. I have run into this issue many times in Access, which relied on the dd/mm/yyyy' short date format!
Applications which insert dates must convert them from a pure date data type into a string comprised of some variation of year, month, and day format. The following VBScript code would convert a date and time into a YYYY-M-D h:m:s string such as '2006-3-3 15:2:7':
Note that, without the time formatting on the final line, the time portion would be left out and would be lost forever!
Output ValuesBy default, MySQL also displays dates in the International format:
To display dates and times in a different format, use MySQL's versatile DATE_FORMAT() function:
It accepts a number of specifiers in the format argument. These are the "%" character followed by format specifier characters. For example, %e specifies the numeric month; %h specifies the hour:
Working with Month NamesThe date format standard where I work is 'DD-MMM-YYYY' where MMM is the abbreviated three-letter month name. Having worked with this format for years, I can't think of a more diabolical way to drive developers batty! There have been many trials and tribulations, but we've gotten the hang of it now! Here's what I've learned:
Ideally, you should exert control over the inputs by using some type of GUI widget that limits the month names to valid dates. A dropdown or calendar control works nicely for this purpose. Conversely, accepting date inputs via freeform text fields is asking for trouble.
Other inputs, such as those submitted from a partner via XML data, must be accepted in whatever format is given to you. Working with dates which contain full month names can be especially problematic. In these instances, MySQL's STR_TO_DATE() function can help. It is the inverse of the DATE_FORMAT() function. It takes a value string (str) and a format string (format). The format string can contain literal characters and format specifiers beginning with "%". Literal characters in format must match literally in str. Format specifiers in format must match a date or time part in str. STR_TO_DATE() returns a date/time value if the format string contains both date and time parts, or a date or time value if the string contains only date or time parts. If the date/time value extracted from str is invalid, STR_TO_DATE() returns NULL and produces a warning:
Displaying month names is fairly easy because the DATE_FORMAT() function has specifiers for both full and abbreviated month names ( %M and %b respectively ). The MONTHNAME() function can also be of service there. Just be careful with abbreviated month names because some locales, like French (fr'), use a four letter abbreviation. The problem with French month abbreviations is that three letters is not enough to distinguish between June and July, which are juin and juillet in French.
Beginning with MySQL 5.0.25, the locale indicated by the lc_time_names system variable controls the language used to display day and month names and abbreviations. This variable affects the output from the DATE_FORMAT(), DAYNAME(), and MONTHNAME() functions. Locale names have a language and region portion listed by IANA (http://www.iana.org/assignments/language-subtag-registry) such as 'ja_JP' or 'pt_BR'. The default value is 'en_US'.
The following function reads the language portion of the lc_time_names system variable to return a three letter month name in the current locale. It accepts the month number as the input parameter, ranging from 1 to 12:
The following tests highlight the workaround for the June/July issue in French. Juillet is thus displayed as jul:
ConclusionMySQL's thorough and flexible date handling allows you to switch between internal and external date/time formats with relative ease. Moreover, traditionally challenging three letter month names can be managed using MySQL's lc_time_names system variable and locale-aware DATE_FORMAT(), DAYNAME(), and MONTHNAME() functions.
Explore the following links to learn more about some of today's topics: