What is the best data type for DATE and TIME

Question

I have an application that saves the date and time of a transaction. My initial database design is to create separate fields for DATE as date and TIME as varchar. My second option is to have a single field with DATETIME.

What is the difference of these two??

Answer 1

I would recommend to use TIMESTAMP as this will help you to track every change made to your database. You shou;d use DateTime datatype if you want to store specific value of date and time in your column. But if you want to track the changes made in your values then I would recommend to use TIMESTAMP. From the MYSQL Docs:

The DATETIME type is used when you need values that contain both date and time information. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

...

The TIMESTAMP data type has a range of '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC. It has varying properties, depending on the MySQL version and the SQL mode the server is running in.

Answer 2

Use timestamp. I would advise against storing date and time separately. The reason is that when you want to use them in a where clause you can find yourself adding them together.