About DATE, DATETIME, and TIMESTAMP

[Liquid Fire]

First of all what is the difference from DATETIME and TIMESTAMP since they both store the data and time?

 

Also i have come use to just when i need to store a date of time to use TIMESTAMP even if i don't need both, i figure i might need it is the future and i figure it really not that much extra data to store.  Is there any reason i should not do this?

[bubblegum.anarchy]

The main difference is the supported date range - TIMESTAMP is more appropriate for storing current values used in all inserts and updates like a creation and modification timestamp, the range is from `1970-01-01 00:00:00` till partway though the yeay 2037 (according to the mysql manual).  DATETIME supports dates ranging from `1000-01-01 00:00:00` to `9999-12-31 23:59:59` and is more appropriate for anniversary dates, especially those prior to 1970.  TIMESTAMP values are also automatically set to the current date and time if no value is provided or a NULL value is assigned.

[bubblegum.anarchy]

This is all straight out of the MySQL manual:

 

DATETIME

 

A date and time combination. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. MySQL displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format, but allows you to assign values to DATETIME columns using either strings or numbers.

 

TIMESTAMP[(M)]

 

A timestamp. The range is '1970-01-01 00:00:00' to partway through the year 2037.

 

A TIMESTAMP column is useful for recording the date and time of an INSERT or UPDATE operation. The first TIMESTAMP column in a table is automatically set to the date and time of the most recent operation if you don't assign it a value yourself. You can also set any TIMESTAMP column to the current date and time by assigning it a NULL value.

 

A TIMESTAMP value is returned as a string in the format 'YYYY-MM-DD HH:MM:SS'whose display width is fixed at 19 characters. If you want to obtain the value as a number, you should add +0 to the timestamp column.

 

Note: The TIMESTAMP format that was used prior to MySQL 4.1 is not supported in MySQL 5.1; see MySQL 4.1 Reference Manual for information regarding the old format.

 

[fenway]

Always use DATETIME -- always... TIMESTAMP is absolutely horrible.

 

First of all what is the difference from DATETIME and TIMESTAMP since they both store the data and time?

 

Also i have come use to just when i need to store a date of time to use TIMESTAMP even if i don't need both, i figure i might need it is the future and i figure it really not that much extra data to store.  Is there any reason i should not do this?