One thing you can do with dates is use the UNIX_TIMESTAMP function and the BETWEEN clause to determine if a date lies between two dates. However, there is a lot more that you can do.
Getting Current Date and Time
SQL provides us with a function NOW that returns the date and time.
You could easily store this value in a datetime field. You might use this in a registration script to record the date and time that someone registered on a website.
Getting Current Date
You might just be interested in the current date. SQL provides us with a function CURDATE that returns the current date.
Getting Current Time
The function CURTIME returns the current time. However, this might not be as accurate as you want because you are using the time on the database server. If getting the exact time the user did something is important, then the program should get the time value and pass that to the database.
One thing that is really interesting about dates, is we can do all sorts of math to manipulate them. Consider the ADDDATE function which takes a date and adds x days to it.
This code is going to add 30 days to the current date.
You might use these functions in correlation with the IF function to determine if the user is allowed to do something. Say you only want people to post to your bulletin board if they have been registered for 30 days. Granted, this is something that the application should do. Not, something the database should do, but the database could do this for you.
If the date we passed in is greater than or equal to the registration date + 30 dates then the user is allowed to do something, and we return "yes". Otherwise, we return no.
Date Diff - Subtracting Dates
We can use the DATEDIFF function to subtract two dates. This function returns the number of days between the two dates.
I chose this example because it is easy to determine what the result is. Since I am finding the number of days between two dates, I chose two dates that are 30 days apart.
This SQL function is going to evaluate:
Thus the result should be -30. Now, let us run our code and see what happens.
This function is a monster, it is very similar to the date function in PHP and works much in the same way.
The function prototype is:
The format parameter is made up of different specifies that indicate how you want the date to be displayed.
SELECT DATE_FORMAT(NOW(),'%W %M %e, %Y %T');
This function is going to return the string formatted with a date and a time.
Friday September 4, 2009 06:50:01
Instead of memorizing all the specificiers, you are going to save a lot more time if you just look them up as you need them.
Make Time Function
This function returns a time value that is calculated from the parameters. It accepts three parameters, hour, minute and second.
Formatting Time Values
There is a function TIME_FORMAT that works much like DATE_FORMAT. It takes two parameters time and format. However the only format specifies you can use are ones for hours, minutes, seconds and microseconds. If you use any other specifies the return value will either be NULL or 0.
SELECT TIME_FORMAT(CURTIME(),'%I:%i:%S %p');