Timestamp vs date

A complete newbie question.

What is the difference between timestamp and date data type?

What are the best practices for timestamp and date?

When should one use timestamp? same for date?

A DATE is just a date: year, month, and day. A TIMESTAMP includes both date and time information: year, month, day, hour, minute, second (and fractions of seconds down to microsecond precision).

If you need the time, then of course you should use TIMESTAMP. If not, then you have a choice: you can use DATE, or you can use a TIMESTAMP with zeros in the hour/minute/second fields (but be careful of time zones: which time zone’s zero hour do you use?). DATE is usually simpler if that’s all you need.