Difference Between Time, Datetime and Timestamp

Timestamp is a SQL field that basically is used to record when an object is changed. If you have a Timestamp field in MySQL and you have an insert or update script with a NULL value in this column, then it will use the current time. Depending upon the length of the field, will determine the format. A timestamp(14) produces a value like so: YYYYMMDDHHMMSS
Datetime is quite similar to Timestamp, it just stores the data as YYYY-MM-DD- HH:MM:SS as a more formated string. I don't think that the NULL value will give anything but NULL.
Time is also quite similar to the above except that it only records the time as HH:MM:SS, no dates at all.

Of course, in MySQL, there is also the DATE field which can also be made to default to the current date time. It is a little tricky to use sometimes since MySQL stores the date in the following format: YY-MM-DD. Which looks a little weird when used as a date. Using PHP, you can get MySQL to format the date properly by using a query string like so:
"SELECT *, DATE_FORMAT(datefield, '%W, %M %D') as datefield . In MySQL, todays date would be output as 20010317. Using the above select statement, the date would be output as Sunday, March 17. I left the year off but could quite easily put that on as well.

Timestamp is often used as kind of an audit trail to see when a database record was last updated. It would also be used to generate a customer order date from an order placed. Anything like that.