MySQL: should I use "date" and "time" fields when I already have a timestamp field?

Question

Hi,

I have a MySQL table where there is a 'date_added' (date) field, 'time_added' (time) field and 'timestamp' (int) field.

I found out afterwards that I can convert timestamp to a date or a time anyway, so so does that mean I could get rid of the other date/time fields and keep the timestamp only?

Thanks in advance

Answer 1

Yes! Convert as needed using a query.

Answer 2

Yes, just remember the 32 bit timestamp will run out of values in 20 something years.

Answer 3

In the layout you describe I see no reason to have more than one field. You can use a DATETIME column or, if you are fond of DB magic, a TIMESTAMP column (don't confuse it with unix timestamps). The first TIMESTAMP in a table can be configured to autoupdate itself.

It's also worth noting that some platforms allow very narrow ranges of dates when using Unix timestamps (it can be problematic to store a date before 1970 or after 2038) while MySQL's DATETIME type ranges from year 1000 to 9999.

http://dev.mysql.com/doc/refman/5.1/en/datetime.html

Answer 4

Could you get rid of those fields? Yes.

Should you get rid of those fields? Maybe not.

If you find yourself querying specifically by date or time, it is much more efficient to store and index them seperately than it is to do something like WHERE DATE(timestamp) = '01-01-2001' which is un-indexable and inefficient.

Depends on the queries for that table.