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

The database servers covered in this book have a different range of possible DATETIME (or the equivalent type) values, summarized here:

Server
Range of Values for DATETIME
MySQL
1000-01-01 00:00:00 - 9999-12-31 23:59:59
PostgreSQL (timestamp)
Jan-01-4713 BC 00:00:00 - 1465001-12-31 23:59:59.99
Oracle 10 (DATE)
Jan-01-4712 BC 00:00:00 - 9999-12-31 23:59:59
SQL Server 2000
1753-01-01 00:00:00 - 9999-12-31 23:59:59

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.

For example, in MySQL, you can use the DATE_ADD or DATE_SUB functions with the INTERVAL keyword to specify new dates, as follows:

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.

The STR_TO_DATE function operates almost as the reverse of the DATE_FORMAT functionby using a format string, you tell MySQL how to interpret the values given to it in an input string, as follows:

INSERT INTO users (name, birthdate)
  VALUES('Luigi the Lemur',
         STR_TO_DATE('%d.%m.%y', '02/06/80'));

This example tells MySQL that the date was being input in a common European format of dd/mm/yy (that is, June 2, 1980) rather than the yyyy-mm-dd format to which the server is most accustomed.

MySQL and Timestamps

MySQL also includes a function called FROM_UNIXTIME, which takes a 32-bit timestamp value (assuming 0 is January 1, 1970) and converts it into a MySQL DATETIME value, as follows:

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

The only caveat regarding this function is that it returns 0 for those dates that fall outside the possible range of values for Unix timestamps.

Table of Contents