CODEWITHSUNDEEP
mysqldatetime
beginner
beginner

Reputation: 2032

What is the best data type for DATE and TIME

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??

Upvotes: 5

Views: 15029

Answers (3)

Rahul Tripathi
Rahul Tripathi

Reputation: 172418

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.

Upvotes: 5

Ashim Sinha
Ashim Sinha

Reputation: 577

datetime will give the date and the time on which the transcation was made like 12-10-2012 12:00:00 So, instead of using two seperate field we can use one ie datetime

Upvotes: 0

Rohit Gupta
Rohit Gupta

Reputation: 4191

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.

Upvotes: 2

Related Questions