datetime, date, and timestamp?

Anon

After reading the MySQL manual I get a bit confused about these dateformats. I am new to databases. I want to have a date in a column that I can compare and use for some transactions. I would like to find all columns for a specified year or month. I presume that date is the field type to use. What I really don't understand exactly is what the fields are for. My questions;

Are these fields different from strings in a way that you can more easily compary and extract strings?
Are they different in other ways?
Shall I use date for my purposes?
Do I have to update them manually?

Thanks / Henrik

Anon

You need to update DATE and DATETIME types manually. TIMESTAMP is updated to NOW() whwnever the record is updated (ideal use for a last_updated field). I am under the impression that DATETIME is only a mysql field type. You can achive the same using a DATE and TIME field (if portability is a concern).

You can use many of the built in date functions (look in the manual) to do date manipulations and comparisons.

So, I would just use Date.

toma42

Actually, few databases support a only time or only date datatype. It's better to NOT use Date or Time and just use datetime. It's also easier as you only need to update one column instead of two.

In MySQL, timestamp is updated to now() on any record update where that timestamp field is not set to a specific value, BUT, other databases, such as Postgresql, handle them differently. In pgsql, the timestamp is set on an insert but is not updated on an update automaticly.