SQL makes using dates easy. My favorite being the Unix Timestamp. +rep!
Thread: SQL Date |
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:
Now, we need to create the table.Code:CREATE DATABASE `test` ;
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.Code:CREATE TABLE `test`.`people` ( `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `name` VARCHAR( 100 ) NOT NULL , `date` DATE NOT NULL ) ENGINE = MYISAM ;
The date could be when they registered to a web site or when they first made an order, or whatever.
Populating the table
We are going to insert 3 rows of data.Code:INSERT INTO `people` (`id`, `name`, `date`) VALUES (1, 'James', '2009-09-16'), (2, 'Joe', '2009-09-30'), (3, 'Jack', '2009-09-18');
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.
The resulting output is:Code:SELECT UNIX_TIMESTAMP(date) AS theDate FROM people;
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:
The result is:Code:SELECT * FROM people WHERE UNIX_TIMESTAMP(date) BETWEEN UNIX_TIMESTAMP('2009-09-16') AND UNIX_TIMESTAMP('2009-09-18')
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.
SQL makes using dates easy. My favorite being the Unix Timestamp. +rep!
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.
Oracle + dates = headache.
MySQL + dates = nice
+rep
CodeCall Blog | CodeCall Wiki
Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog
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.![]()
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.
CodeCall Blog | CodeCall Wiki
Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)