In the previous chapter, you saw how MySQL’s date and time datatypes can save storage space—50% or more over storing dates as strings—but that’s not the only reason for using them. One aspect of applications programming that’s often troublesome is working with dates. Different programming languages have radically different ways of representing dates and performing date calculations; users and clients have different requirements for how they’re displayed. By leveraging MySQL functions that are associated with DATE, TIME, and DATETIME values, you can minimize and sometimes even eliminate many of these problems.
First, let’s look at how to get the current date and time. MySQL supports all of the standard SQL functions for this, as well as several others, as shown in Table 4-3.
Table 4-3. Current Date and Time Functions Supported by MySQL
* SYSDATE() is supported to provide compatibility with Oracle.
** The UTC functions were added in MySQL 4.1.1.
Note that functions expecting a TIME value will generally accept DATETIME values while ignoring the date part. Functions that expect a DATE value will generally accept a DATETIME value and ignore the time portion of the value.
It’s somewhat customary in MySQL to use NOW(), CURDATE() , and CURTIME() , since these are short and convenient. However, if compatibility with other databases is an issue, you should use the standard SQL functions instead. To convert from a Unix-style timestamp to date/time format, use the FROM_UNIXTIME() function.
If you call any functions returning the current date and/or time in the same query, they will always return the same date and/or time.
Date and Time Formatting and Extraction
If your application doesn’t need to support multiple locales or languages, you can perform most, if not all, of your date formatting in your queries using the DATE_FORMAT() function, which takes this form:
where date is a date, and format is a string containing one or more format specifiers and optional additional punctuation marks. Table 4-4 shows a partial listing of the available format specifiers.
Table 4-4. Some Format Specifiers for DATE_FORMAT() and Date Arithmetic Functions
Table 4-4. Some Format Specifiers for DATE_FORMAT() and Date Arithmetic Functions (Continued)
*These specifiers return the names of days and months in English.
The following are a few examples of formatting dates.
Formatting dates can be even easier in MySQL 4.1.1 and above, using the GET_FORMAT() function. This function returns format strings for a number of locales:
The locale argument can take one of several predefined values: 'EUR' , 'USA' , 'JIS' , 'ISO' , and 'INTERNAL' . GET_FORMAT() can be used in place of a format string wherever one is applicable. Here are some examples:
The SUBDATE() function employed in the second example is used to subtract dates, as discussed in the “Date Arithmetic” section later in this chapter.
You can also obtain the various parts of a date, time, or datetime as a number using the functions shown in Table 4-5.
* The WEEK() function takes an optional second mode argument. The behavior of this function also changed significantly in MySQL 4.0; see the MySQL Manual for details.
Another means of extracting portions of dates and times is to use the EXTRACT() function, which takes this form:
EXTRACT(type FROM date)
It returns a number corresponding to the part of the date argument specified by type. The type argument is the name of one of the following units of time: MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR. MICROSECOND was added in MySQL 4.1.1; WEEK and QUARTER were added in MySQL 5.0.0. (The date argument is a date or date/time value in standard format.) Here are some examples:
If a time value is missing when you use EXTRACT() , it returns 0.
In addition, the following compound types are permitted: MINUTE_SECOND, HOUR_SECOND, HOUR_MINUTE, DAY_SECOND, DAY_MINUTE, DAY_HOUR, and YEAR_MONTH. Each of these actually specifies a complete set of values; that is, any values that would normally be expected are filled in. For example, if you use HOUR_SECOND for the type, MySQL returns the hours, minutes, and seconds as a number.
MySQL 4.1.1 and above also supports these compound types, which include microseconds: SECOND_MICROSECOND, MINUTE_MICROSECOND, HOUR_MICROSECOND , and DAY_MICROSECOND. These also fill in the missing units from the range, as in this example:
EXTRACT(DAY_MICROSECOND FROM '2004-05-15 15:35:25.104528')
This returns the value 151535250104528.
blog comments powered by Disqus