Optimizing Queries with Operators for Date, Time and Other Functions (Page 1 of 5 )
This article will give you a good grounding in operators, branching and functions in MySQL, so you can make the database, instead of your own code, do the bulk of the work. It is the third of three parts, and excerpted from chapter four of
Beginning MySQL Database Design and Optimization: From Novice to Professional, written by Jon Stephens and Chad Russell (Apress; ISBN: 1590593324).
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.
Table 4-3. Current Date and Time Functions Supported by MySQL
SQL STANDARD CURRENT_DATE | DESCRIPTION Current date in YYYY-MM-DD format (server time) | MYSQL ADDITIONS CURRENT_DATE(), CURDATE() |
CURRENT_TIME | Current time in HH:MM:SS format (server time) | CURRENT_TIME(), CURTIME() |
CURRENT_TIMESTAMP | Current timestamp in YYYY-MM-DD HH:MM:SS format (server time) | CURRENT_TIMESTAMP(), NOW(), SYSDATE()* |
| | Current timestamp in Unix format (seconds elapsed since 1970-01-01 00:00:00) (server time) | UNIX_TIMESTAMP() |
| Current UTC date in YYYY-MM-DD format | UTC_DATE**, UTC_DATE()** |
| | Current UTC time in HH:MM:SS format | UTC_TIME**, UTC_TIME()** |
| | Current UTC timestamp in YYYY-MM-DD HH:MM:SS format | UTC_TIMESTAMP**, UTC_TIMESTAMP()** |
* 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
SPECIFIER* | FORMAT |
%a | Three-letter day of week (Sun, Mon, Tue, and so on) |
%b | Three-letter name of month ( Jan, Feb, Mar, and so on) |
%c | Month number (0–12) |
%D | Day of month with ordinal suffix (0th, 1st, 2nd, 3rd, and so on) |
%d | Two-digit day of month (00–31) |
%e | Day of month (0–31) |
%f | Microseconds (000000–999999) |
%H | Two-digit hour (00–23) |
%h or %I | Two-digit hour (01–12) |
%i | Minutes (00–59) |
%j | Day of year (001–366) |
%k | Hour (0–23) |
%l | Hour (1–12) |
%M | Full month name ( January, February, March, and so on) |
%m | Two-digit month number (00–12) |
%p | AM/PM |
%r | 12-hour time in HH:MM:SS XM format |
%S or %s | Seconds (00–59) |
Table 4-4. Some Format Specifiers for DATE_FORMAT() and Date Arithmetic Functions (Continued)
SPECIFIER* %T | FORMAT 24-hour time in HH:MM:SS format |
%U | Week of year (00–53), where Sunday is the first day of the week |
%u | Week of year (00–53), where Monday is the first day of the week |
%W | Full day of week (Sunday, Monday, Tuesday, and so on) |
%w | Day of the week (0–6, where 0=Sunday) |
%Y | Four-digit year |
%y | Two-digit year |
*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
FUNCTION DAYNAME() | VALUE RETURNED Name of the day of the week (English) |
DAYOFMONTH(), DAY() | Day of the month (DAY() was added in MySQL 4.1.1) |
DAYOFWEEK() | Number of the day of the week (1=Sunday, 7=Saturday) |
DAYOFYEAR() | Day of the year as a number |
HOUR() | Hours portion of the time (added in MySQL 4.1.1) |
MINUTE() | Minutes portion of the time |
MONTH() | Month portion of the date (1=January) |
MONTHNAME() | Name of the month (in English); if the date holds a 0 for the month, this function returns NULL |
QUARTER() | Quarter of the year (1–4) |
SECOND() | Seconds from time |
TIME() | Time portion of a date/time (added in MySQL 4..1.1) |
WEEK()* | Week of the year (1–53) |
WEEKDAY() | Day of the week (0=Monday, 6=Sunday) |
WEEKOFYEAR() | Week of the year, the week reckoned as beginning on Monday (added in MySQL 4.1.1) |
YEAR() | Four-digit year (1000–9999) |
* 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.
Next: Date and Time Conversion Functions >>
More MySQL Articles
More By Apress Publishing
|
 This article is excerpted from chapter four of Beginning MySQL Database Design and Optimization: From Novice to Professional, written by Jon Stephens and Chad Russell (Apress; ISBN: 1590593324). Check it out at your favorite bookstore today. Buy this book now.
|
|
/ 6 
