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 1021.
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.
You have two choices when it comes to performing date arithmetic in MySQL:
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 (
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.
SELECT acct_id, SUM(balance) AS total
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.
SELECT acct_id, SUM(balance) AS total,
Alternatively, depending on the exact requirements, we might be able to use this:
SELECT acct_id, SUM(balance) AS total,
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:
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.
blog comments powered by Disqus