SQL provides a lot of functions for manipulating dates in a database. You can store values in a date field or a datetime field. If you store the dates as unix_timestamps you can get a lot more flexibility with dates. In fact, this is usually what is done when you are using PHP with the database.
It might even make sense to store them as timestamps anyways. This is because I always convert them to timestamps before I use them.
Creating the Database
We are going to create a test database with a people table. The people table will contain these fields:
First, the code to create the database:
CREATE DATABASE `test` ;
Now, we need to create the table.
CREATE TABLE `test`.`people` ( `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `name` VARCHAR( 100 ) NOT NULL , `date` DATE NOT NULL ) ENGINE = MYISAM ;
This table contains 3 fields just like we said it would. Now let us add some data. The format for the date needs to be yyyy-mm-dd.
The date could be when they registered to a web site or when they first made an order, or whatever.
Populating the table
INSERT INTO `people` (`id`, `name`, `date`) VALUES (1, 'James', '2009-09-16'), (2, 'Joe', '2009-09-30'), (3, 'Jack', '2009-09-18');
We are going to insert 3 rows of data.
Like I said earlier, it is easier to work with dates as numbers. This number is the unix timestamp which is the number of milliseconds that have past since January 1st, 1900.
To see how this works let us select all the UNIX_TIMESTAMPS from the database.
SELECT UNIX_TIMESTAMP(date) AS theDate FROM people;
The resulting output is:
These values are the unix timestamps representing the dates that we inserted above. We can use these values to compare dates. This allows us to select rows that occur between the two days.
A date occurs between two dates if the unix_timestamp is >= the test date and <= the test date.
We use the BETWEEN clause to test if a date occurs between two dates.
Let us return all results that occur between '2009-09-16' and '2009-09-18'.
The SQL query is simply:
SELECT * FROM people WHERE UNIX_TIMESTAMP(date) BETWEEN UNIX_TIMESTAMP('2009-09-16') AND UNIX_TIMESTAMP('2009-09-18')
The result is:
I convert the date field that I am comparing to a unix_timestamp and I also convert the dates that I am testing it to a unix timestamp. This makes comparing easy.