Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo

SQL Date


  • Please log in to reply
6 replies to this topic

#1 chili5

chili5

    CC Mentor

  • Expert Member
  • PipPipPipPipPipPipPipPip
  • 3038 posts
  • Programming Language:Java, C#, PHP, JavaScript, Ruby, Transact-SQL
  • Learning:C, Java, C++, C#, PHP, JavaScript, Ruby, Transact-SQL, Assembly, Scheme, Haskell, Others

Posted 01 September 2009 - 09:49 AM

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:

http://forum.codecal...=1&d=1251838133

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:

http://forum.codecal...=1&d=1251838133

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.

Attached Thumbnails

  • dates.JPG
  • between.JPG

  • 2

#2 Guest_Jordan_*

Guest_Jordan_*
  • Guest

Posted 02 September 2009 - 04:01 AM

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

#3 chili5

chili5

    CC Mentor

  • Expert Member
  • PipPipPipPipPipPipPipPip
  • 3038 posts
  • Programming Language:Java, C#, PHP, JavaScript, Ruby, Transact-SQL
  • Learning:C, Java, C++, C#, PHP, JavaScript, Ruby, Transact-SQL, Assembly, Scheme, Haskell, Others

Posted 02 September 2009 - 04:03 AM

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.
  • 0

#4 WingedPanther73

WingedPanther73

    A spammer's worst nightmare

  • Moderator
  • 17757 posts
  • Location:Upstate, South Carolina
  • Programming Language:C, C++, PL/SQL, Delphi/Object Pascal, Pascal, Transact-SQL, Others
  • Learning:Java, C#, PHP, JavaScript, Lisp, Fortran, Haskell, Others

Posted 02 September 2009 - 01:09 PM

Oracle + dates = headache.
MySQL + dates = nice :)
+rep
  • 0

Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog

My MineCraft server site: http://banishedwings.enjin.com/


#5 chili5

chili5

    CC Mentor

  • Expert Member
  • PipPipPipPipPipPipPipPip
  • 3038 posts
  • Programming Language:Java, C#, PHP, JavaScript, Ruby, Transact-SQL
  • Learning:C, Java, C++, C#, PHP, JavaScript, Ruby, Transact-SQL, Assembly, Scheme, Haskell, Others

Posted 02 September 2009 - 01:11 PM

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. :)
  • 0

#6 WingedPanther73

WingedPanther73

    A spammer's worst nightmare

  • Moderator
  • 17757 posts
  • Location:Upstate, South Carolina
  • Programming Language:C, C++, PL/SQL, Delphi/Object Pascal, Pascal, Transact-SQL, Others
  • Learning:Java, C#, PHP, JavaScript, Lisp, Fortran, Haskell, Others

Posted 02 September 2009 - 01:32 PM

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.
  • 0

Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog

My MineCraft server site: http://banishedwings.enjin.com/


#7 BlaineSch

BlaineSch

    CC Leader

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1559 posts

Posted 02 September 2009 - 02:21 PM

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.
  • 0