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 Basics
MySQL 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:
- DATE: Stores a date value in the form YYYY-MM-DD. For example 2008-10-23.
- TIME: values in ‘HH:MM:SS’ format (or ‘HHH:MM:SS’ format for large hours values). TIME values may range from ‘-838:59:59’ to ‘838:59:59’.
- DATETIME: Stores a date and time value of the form YYYY-MM-DD HH:MM:SS. For example 2008-10-23 10:37:22. The supported range of dates and times is 1000-01-01 00:00:00 all the way through to 9999-12-31 23:59:59
- TIMESTAMP: Similar to DATETIME but more precise, as the TIMESTAMP includes milliseconds. There are a few other differences as well, depending on the version of MySQL and the mode in which the server is running.
- YEAR: The YEAR type is a one-byte type used for representing years. It can be declared as YEAR(2) or YEAR(4) to specify a display width of two or four characters. The default is four characters if no width is given. For four-digit format, MySQL displays YEAR values in YYYY format, with a range of 1901 to 2155, or 0000.
A table containing DATE and DATETIME columns is created much the same way as any other column in a table. For example, we can create a new table called orders that contains order number, order item, order date and order deliverycolumns as follows:
CREATE TABLE `MyDB`.`orders` (
`order_no` INT NOT NULL AUTO_INCREMENT,
`order_item` TEXT NOT NULL,
`order_date` DATETIME NOT NULL,
`order_delivery` DATE NOT NULL,
PRIMARY KEY (`order_no`)
)
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:
2009-10-20 10:39:11
20091020103911
2009/10/20 10.39.11
2009*10*20*10*39*11
Input Values
MySQL will happily accept any of the following statements as date inputs:
INSERT INTO tbl_name (idate) VALUES (19970505);
INSERT INTO tbl_name (idate) VALUES ('19970505');
INSERT INTO tbl_name (idate) VALUES ('97-05-05');
INSERT INTO tbl_name (idate) VALUES ('1997.05.05');
INSERT INTO tbl_name (idate) VALUES ('1997 05 05');
INSERT INTO tbl_name (idate) VALUES ('0000-00-00');
INSERT INTO tbl_name (idate) VALUES (CURDATE());
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’:
dt = CDate( dt )
ISODateTime = "'" & Year(dt) & "-" & Month(dt) & "-" & Day(dt) _
& " " & Hour(dt) & ":" & Minute(dt) & ":" & Second(dt) & "'"
Note that, without the time formatting on the final line, the time portion would be left out and would be lost forever!
Output Values
By default, MySQL also displays dates in the International format:
SELECT idate FROM tbl_name WHERE idate = CURDATE();
--returns 2011-01-21
To display dates and times in a different format, use MySQL’s versatile DATE_FORMAT() function:
DATE_FORMAT(date,format)
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:
SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
--returns something like 'Sunday October 2009'
mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
--returns something like '22:23:00'
Working with Month Names
The 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 stris invalid, STR_TO_DATE() returns NULL and produces a warning:
SELECT STR_TO_DATE('01,5,2013','%d,%m,%Y');
'2013-05-01'
SELECT STR_TO_DATE('May 1, 2013','%M %d,%Y');
'2013-05-01'
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_namessystem 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_namessystem 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:
CREATE FUNCTION `GetThreeLetterMonthName`(`month` SMALLINT)
RETURNS char(3)
CHARSET latin1
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
RETURN CASE WHEN LEFT(@@lc_time_names, 2) = 'fr' AND month = 7
THEN 'jul'
ELSE LEFT(MONTHNAME(STR_TO_DATE(month,'%c')), 3)
END;
END;
The following tests highlight the workaround for the June/July issue in French. Juillet is thus displayed as jul:
SELECT @@lc_time_names;
-->returns 'en_US'
SELECT GetThreeLetterMonthName(6);
-->returns 'Jun'
SELECT GetThreeLetterMonthName(7);
-->returns 'Jul'
SET lc_time_names = 'fr_CA';
SELECT GetThreeLetterMonthName(6);
-->returns 'jui'
SELECT GetThreeLetterMonthName(7);
-->returns 'jul'
Conclusion
MySQL’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_namessystem variable and locale-aware DATE_FORMAT(), DAYNAME(), and MONTHNAME() functions.
Explore the following links to learn more about some of today’s topics:
