Date and Time Conversion Functions - MySQL

MySQL also has some handy date and time conversion functions:

FROM_DAYS(days ): Takes a number of days since 0000-00-00 and returns a date in YYYY-MM-DD format. The minimum value accepted by this function is 366, which yields '0001-01-01' ; however, you should not use this function for dates preceding the advent of the Gregorian calendar (1582 in Western Europe; 1917 in Russia) in any case, as it does not take the conversion from Julian to Gregorian reckoning into account.

FROM_UNIXTIME(unix_timestamp[, formatstring]): Takes a Unix timestamp and returns a date/time formatted according to a formatstring using the same format specifiers as the DATE_FORMAT() function (see Table 4-4); the format defaults to standard Unix date/time format.

MAKEDATE(year, day): Takes a year and a day of that year and returns a date in Unix format. Note that day may be greater than 366 and MySQL will calculate the year accordingly in the return value; for example, MAKEDATE(2003, 366) returns '2004-01-01' .

MAKETIME(hours, minutes, seconds): Returns a time in standard format. Unlike MAKEDATE(), passing out-of-range values to this function will result in a NULL value. (Added in MySQL 4.1.1.)

SEC_TO_TIME(seconds): Takes a number of seconds and returns a time in standard Unix (HH:MM:SS) format.

STR_TO_DATE(datestring, formatstring): The inverse of DATE_FORMAT(); takes a formatted datestring and formatstring (using the specifiers for DATE_FORMAT() ) and returns a date or date/time in standard format. (Added in MySQL 4.1.1.)

TIME_TO_SEC(hours, minutes, seconds): Takes a time in HH:MM:SS format and returns a number of seconds. If either the minutes or seconds value is greater than 59, the function returns NULL . The upper limit for the hours argument is at least 10²¹.

TO_DAYS(date): The inverse of FROM_DAYS(); takes a date string in Unix format and returns the number of days since 0000-00-00. Like FROM_DAYS(), this function is not reliable for dates prior to the adoption of the Gregorian calendar in the sixteenth century.

UNIX_TIMESTAMP(date): Returns a Unix timestamp for a DATE or DATETIME value; can also accept an integer representing a date in YYYYMMDD format. Note that the returned value is an unsigned integer. Out-of-range dates will return 0; the year must be between 1970 and 2037 inclusive.

Date Arithmetic

You have two choices when it comes to performing date arithmetic in MySQL:

• Converting dates and times into a common unit before performing the calculation. This isn’t that hard to do, and there are times—for instance, when making comparisons in a WHERE clause—when it’s desirable. However, when you’re interested in obtaining a nicely formatted final result, it can be quite cumbersome.
  
• Using the INTERVAL operator alone or in conjunction with the DATE_ADD() and DATE_SUB() functions. This is generally what you want to do when you need to pass the results of date calculations back to the application. The advantage here is that MySQL automatically returns the results in standard date, time, or datetime format, which can save you a lot of overhead.

Let’s look at an example illustrating the first option. Suppose our firm’s billing department wants a report of accounts that have unpaid orders that are more than three months old. We need to extract this information from an orders table, a partial definition of which might be as follows:

CREATE TABLE orders (
  order_id INT AUTO_INCREMENT PRIMARY KEY,
  acct_id INT UNSIGNED NOT NULL,
  order_date DATE NOT NULL,
  order_amt DECIMAL(8, 2) NOT NULL,
  last_pmt_date DATE NOT NULL,
  order_balance DECIMAL(8, 2) NOT NULL
);

For purposes of this set of examples, we’ll ignore the possibility that any negative amounts might be stored in the order_amt or order_balance columns due to refunds or other adjustments. Also, we won’t worry about creating any indexes or the fact that this does not represent a fully normalized database schema, since we should really have a separate payments table.

---

NOTE  In this first example, we also assume that when the order is placed, the last payment date is set to the same value as the order date. The reason for this is that TO_DAYS('0000-00-00') returns NULL .

---

We need to find all the records for which the last payment date is at least 120 days in the past, and for which there remains an unpaid balance. The unpaid balance part is simple enough—we’ll just need a balance > 0 constraint in the WHERE clause. To determine whether a date is more than 120 days in the past, we can use the TO_DAYS() function to convert both the date column and the current date into numbers of days, subtract, and then compare the difference to 120. The resulting query might look something like this:

SELECT acct_id, SUM(balance) AS total
FROM orders
WHERE balance > 0
AND TO_DAYS(CURRENT_DATE) – TO_DAYS(last_pmt_date) > 120
GROUP BY acct_id;

We’ve used the SUM() function with a GROUP BY clause in order to produce a listing with one entry per delinquent account, with the total past due for all orders made by that account.

---

NOTE  Beginning with MySQL 4.1.1, you can also use the DATE_DIFF() function to rewrite TO_DAYS(CURRENT_DATE) – TO_DAYS(last_pmt_date) > 120 as DATE_DIFF(CURRENT_DATE, last_pmt_date) > 120 .

---

Using data from the same orders table, we want to do a weekly billing for all new orders from the previous week and in each case show a due date 30 days from the date of the order. We won’t worry about whether there’s a balance showing on the account, only whether an order was placed in the last seven days. Using the INTERVAL operator makes this much easier than you might think:

SELECT acct_id, SUM(balance) AS total,
  MAX(order_date) + INTERVAL 30 DAY AS due_date
FROM orders
WHERE order_date + INTERVAL 7 DAY >= CURRENT_DATE
GROUP BY acct_id;

Alternatively, depending on the exact requirements, we might be able to use this:

SELECT acct_id, SUM(balance) AS total,
  MAX(order_date) + INTERVAL 1 MONTH as due_date
FROM orders
WHERE order_date + INTERVAL 1 WEEK >= CURRENT_DATE
GROUP BY acct_id;

As we indicated earlier, we can also use INTERVAL in conjunction with MySQL’s date arithmetic functions:

DATE_ADD(date, INTERVAL expression type) DATE_SUB(date, INTERVAL expression type)

DATE_ADD() returns the date obtained by adding the interval specified by expression type to date. The expression is simply any legal MySQL expression that evaluates to a number. The value used for date can be any valid DATE, TIME, or DATETIME value. DATE_SUB() does the same calculation, except that it returns the date obtained by subtracting the specified interval. In all of these cases, the type argument is any of the values that can be used with EXTRACT() (see Table 4-5). The following are some examples of using the date arithmetic functions.

Synonymous with these functions are ADDDATE() and SUBDATE(), whose arguments follow the same rules.

As you can see from the second query in the preceding examples, there’s nothing wrong with using compound type specifiers with these functions, as long as you observe the rules explained in our earlier discussion of the EXTRACT() function.

Should you choose ADDDATE() and SUBDATE() over DATE_ADD() and DATE_SUB()? In many cases, it doesn’t make any difference; however, beginning with MySQL 4.1.1, ADDDATE() and SUBDATE() have been enhanced somewhat with a simplified alternative syntax when working with numbers of days:

ADDDATE(date, days)
SUBDATE(date, days)

Here are a few examples:

This shorthand notation is not available with DATE_ADD() and DATE_SUB() .

So, as you can see, quite a lot of the work required for date calculations, conversions, and even representations can be handled in queries rather than in application code. Your time spent in learning these and making use of them will generally be well spent.