MorkaLork Development

Interesting stuff I've picked up over the years...

Mysql 16: Using DATETIME

2009-11-01 10:02:49 | 187 views | select sql datetime table

Using the Datetime type is quite hard if you haven't used the various methods that comes with it. You can read about them all here, and in this article which will show some simple examples for all of them.

Let's take the following table:

id entry date
1 I am a happy camper 2009-11-01 09:35:35
2 I am a capped hampster 2009-11-02 09:35:49
3 I am a wrapped tramp 2009-11-03 09:36:10
4 I am a crappy camper 2009-11-04 09:36:27
5 I am a lappy santa 2009-11-05 09:36:43
6 I am a snappy tamper 2009-11-06 09:37:03


Year(), Day() and Month()


Now, what we want to do is to select all entries ranging from the 1st of november to the 3rd of november. To do this, we could do something clumpsy like:



SELECT date, entry
FROM entries
WHERE date >= '2009-11-01'
AND date <= '2009-11-03'


But a much better way to do this is to this is to use the datetime functions at hand:



SELECT date, entry
FROM entries
WHERE DAY(date) >= 1
AND DAY(date) <= 3


This will give the same effect, but is alot easier to read and handle. The Year() and Month() functions works the same way.

The above will yield:

entry date
I am a happy camper 2009-11-01 09:35:35
I am a capped hampster 2009-11-02 09:35:49
I am a wrapped tramp 2009-11-03 09:36:10



DATE_ADD


If you want to add an interval to a date, use the DATE_ADD function like this:



SELECT DATE_ADD(date, INTERVAL 10 DAY), entry
FROM entries
WHERE DAY(date) = 1


This will yield:

entry date
I am a happy camper 2009-11-11 09:35:35


Other useful intervals are:


There are more, like microseconds etc, but these are the basic ones.


DATE_SUB


If you want to subtract an interval from a date, use the DATE_SUB function, like this:



SELECT DATE_SUB(date, INTERVAL 10 DAY), entry
FROM entries
WHERE DAY(date) = 1


This will yield:

entry date
I am a happy camper 2009-10-22 09:35:35



DATEDIFF


With the DATEDIFF() function you can get the difference between two dates, like this:



SELECT DATEDIFF(date, '2009-10-01') as difference, date, entry
FROM entries
WHERE DAY(date) = 1


This will yield:

difference date entry
31 2009-10-22 09:35:35 I am a happy camper



Article comments

Feel free to comment this article using a facebook profile.

I'm using facebook accounts for identification since even akismet couldn't handle all the spam I receive every day.