Home arrow MySQL arrow Optimizing Queries with Operators for Date, Time and Other Functions

Optimizing Queries with Operators for Date, Time and Other Functions

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

  1. Optimizing Queries with Operators for Date, Time and Other Functions
  2. Date and Time Conversion Functions
  3. Other MySQL Functions
  4. Branching: Making Choices in Queries
  5. Our Demonstration Revisited
By: Apress Publishing
Rating: starstarstarstarstar / 6
April 13, 2006

print this article



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


DESCRIPTION Current date in YYYY-MM-DD format (server time)



Current time in HH:MM:SS format (server time)



Current timestamp in YYYY-MM-DD HH:MM:SS format (server time)



Current timestamp in Unix format (seconds elapsed since 1970-01-01 00:00:00) (server time)



Current UTC date in YYYY-MM-DD format



Current UTC time in HH:MM:SS format



Current UTC timestamp in YYYY-MM-DD HH:MM:SS format


* 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




Three-letter day of week (Sun, Mon, Tue, and so on)


Three-letter name of month ( Jan, Feb, Mar, and so on)


Month number (0–12)


Day of month with ordinal suffix (0th, 1st, 2nd, 3rd, and so on)


Two-digit day of month (00–31)


Day of month (0–31)


Microseconds (000000–999999)


Two-digit hour (00–23)

%h or %I

Two-digit hour (01–12)


Minutes (00–59)


Day of year (001–366)


Hour (0–23)


Hour (1–12)


Full month name ( January, February, March, and so on)


Two-digit month number (00–12)




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)


FORMAT 24-hour time in HH:MM:SS format


Week of year (00–53), where Sunday is the first day of the week


Week of year (00–53), where Monday is the first day of the week


Full day of week (Sunday, Monday, Tuesday, and so on)


Day of the week (0–6, where 0=Sunday)


Four-digit year


Two-digit year

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

The following are a few examples of formatting dates.

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:


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


VALUE RETURNED Name of the day of the week (English)


Day of the month (DAY() was added in MySQL 4.1.1)


Number of the day of the week (1=Sunday, 7=Saturday)


Day of the year as a number


Hours portion of the time (added in MySQL 4.1.1)


Minutes portion of the time


Month portion of the date (1=January)


Name of the month (in English); if the date holds a 0 for the month, this function returns NULL


Quarter of the year (1–4)


Seconds from time


Time portion of a date/time (added in MySQL 4..1.1)


Week of the year (1–53)


Day of the week (0=Monday, 6=Sunday)


Week of the year, the week reckoned as beginning on Monday (added in MySQL 4.1.1)


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.



>>> More MySQL Articles          >>> More By Apress Publishing

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates


Dev Shed Tutorial Topics: