Optimizing Queries with Operators for Date, Time and Other Functions

Date and Time Functions

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.

* 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:

DATE_FORMAT(date, format)

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

*These specifiers return the names of days and months in English.

The following are a few examples of formatting dates.

---

TIP  If you need to return only a formatted time, you can also use the TIME_FORMAT() function. It accepts any of the time-related format specifiers shown in Table 4-4.

---

You can use practically any punctuation you like in formatting dates: commas, dashes, slashes, spaces, and so on. However, you cannot use alphanumeric characters except as part of a format specifier. In addition, you can return a date, time, or date/time value as a number with no formatting whatsoever, simply by forcing it to be evaluated in a numeric context. This will work with any of the functions shown in Table 4-3, as shown in the following example.

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:

GET_FORMAT(DATE|TIME|DATETIME, locale)

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.

---

CAUTION  The WEEK() function exhibits incorrect behavior in MySQL versions previous to 4.0, where the mode argument is the default (0). See the MySQL documentation for details. We suggest that you use WEEKOFYEAR() instead if it’s available to you (MySQL 4.1.1 and above).

---

Table 4-5. Functions Returning Portions of Date, Time, or Date/Time Values

* 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.

 

---

 

NOTE  All of the type specifiers mentioned in this section can also be used with date arithmetic functions such as DATE_ADD(). See the “Date Arithmetic” section later in this chapter.

 

---