SQL Date

[chili5]

SQL Date

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:

• id
• name
• date

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.


UNIX_TIMESTAMP function

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.

BETWEEN clause


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.

[Jordan]

SQL makes using dates easy. My favorite being the Unix Timestamp. +rep!

[chili5]

I like the UNIX_TIMESTAMP function also. You showed me that. You have no idea how useful it was.

I am very thankful that these functions are so easy. My last project used SQL dates a lot. That function made it so much easier for me.

[WingedPanther]

Oracle + dates = headache.
MySQL + dates = nice
+rep

[chili5]

I don't know anything about oracle. Why do you say that?

I probably should mention that I'm using MySQL for all my tutorials.

[WingedPanther]

Oracle has a default date/time string format. If you don't pass it a properly formatted string, it rejects it. You can alter it for your session, or use the to_date function, but if your app is out of sync with the db, you can have major headaches.

[BlaineSch]

I have a book on Oracle, its on my todo list, currently reading database systems (A practical approach to design, implementation, and management).

Databases are always fun to play with. I would rep you but I have no rep for you lol.