More Dates and Times in Database Servers
Chapter 11, "Using Databases: Advanced Data Access," discussed the various date and time types for databases and showed a number of functions for formatting them and extracting portions of the values in SQL queries. This section examines a few other areas of functionality available in many database servers, showing the MySQL version when there is more than one possible way to do things.
Date and Time Ranges in Common Database Servers
Obviously, you are less likely to have to worry about range violations with database servers than with PHP scripts.
Adding or Subtracting Intervals
On many occasions, you might want to add or subtract some interval from a date stored in a database table. For example, a dental office might want to know which customers have appointments coming up in the next week; an online merchant might concern itself with which orders older than 12 days have not yet been filled.
Many modern database servers support the concept of a date or time interval and can use these in conjunction with other date/time manipulations in queries.
SELECT * FROM hygienist_appts WHERE appt_date >= CURDATE() AND appt_date <= DATE_ADD(CURDATE(), INTERVAL 1 WEEK); SELECT * FROM orders WHERE order_status = 'open' AND order_date <= DATE_SUB(CURDATE(), INTERVAL 12 DAYS);
The range of possible values you can associate with the INTERVAL keyword is truly remarkable:
INTERVAL 30 YEAR INTERVAL 4 WEEK INTERVAL 2 QUARTER (2 three month periods) INTERVAL '1:30' HOUR_MINUTE (1.5 hours) INTERVAL '1:2' YEAR_MONTH (14 months)
The CURDATE function in MySQL functions much like the NOW function, except that it returns a DATE rather than a TIMESTAMP with both time and date.
Parsing Dates in the Database
MySQL supports the parsing of dates, with a high degree of control over how fields are interpreted. Recall the DATE_FORMAT function from Chapter 11. Table 11-2 showed a whole range of fields that you can specify as items to include in the output string.
INSERT INTO users (name, birthdate) VALUES('Luigi the Lemur', STR_TO_DATE('%d.%m.%y', '02/06/80'));
MySQL and Timestamps
<?php $ts = time(); $query = <<<EOQUERY INSERT INTO Users(name, date_added) VALUES('$safe_name', FROM_UNIXTIME($ts)) EOQUERY; $results = @$conn->query($query); // etc. ?>
The converse of this is the UNIX_TIMESTAMP function in MySQL, which returns the given DATETIME value as a Unix 32-bit timestamp:
SELECT name, UNIX_TIMESTAMP(date_added) FROM Users;