Question:
I must save dates and times in different tables of a typical transactional application where I am going to store various information, such as:
- Dates and hours at which each transaction occurs (Invoice, Payment Receipt, etc.)
- Dates and times of future events, such as appointments and scheduled deliveries
- Past dates, such as the date of birth of the employee, or the employee's children.
I have little experience with MySQL and I am not sure which data type to choose in each case.
What is recommended, use a field of type DateTime or type TimeStamp and why?
Answer:
At some point in this MySQL documentation it exposes something like this: http://dev.mysql.com/doc/refman/5.7/en/datetime.html (English link)
TIMESTAMP has a range from '1970-01-01 00:00:01' GMT to '01 / 09/2038 03:14:07 'GMT.
DATETIME is used when you need values that contain both the date and the time. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH: MM: SS' format. The supported range is '01 / 01/1000 00:00:00 'to' 9999-12-31 23:59:59 '.
Based on the above and taking it into account, it is possible that the range of data that could be stored or be interested in it will incline you in favor of one or the other, for example if you work with mortgages for saying something it is easy for you to exceed the TIMESTAMP range as of today.
TIMESTAMP is affected by the time zone settings / adjustments. Whereas DATETIME is constant.
TIMESTAMP is four bytes and DATETIME eight bytes, consequently the TIMESTAMP ( TIMESTAMP ) are also lighter in the database, with faster indexing.
Taking into account all the above, if you want to store a specific value "it might be better" to use DATATIME , but if you want to store to keep track of possible changes in the records, you may want to use TIMESTAMP when you change a record.
If you are working on MySQL 5 or higher, TIMESTAMP values are converted from the current time zone to UTC for almacenamiento , and converted back from UTC to the current time zone for recuperación . only for the TIMESTAMP data type.
(NOTE if I find the link of the above I will try to put it here)
I hope it helps you.