Should I use a field of type DateTime or TimeStamp in mySQL?
I must save dates and times in different tables of a typical transactional application where I will store varied information, such as for example:
- dates and times at which each transaction occurs (invoice, payment receipt, etc.)
- dates and times of future events, such as scheduled appointments and 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'm not sure what kind of data should I choose in each case.
What is recommended, use a field of type DateTime or type TimeStamp and why?
2 answers
At some point in this MySQL documentation exposes something like this: http://dev.mysql.com/doc/refman/5.7/en/datetime.html (English link)
TIMESTAMP has a range of '1970-01-01 00:00:01' GMT to '01/09/2038 03:14:07' GMT.
DATETIME is used when values containing both the date and time. MySQL retrieves and displays the DATETIME values in 'YYYY-MM-DD HH: MM: SS' format. The supported range is '01/01/1000 00:00: 00 ' a '9999-12-31 23:59:59'.
Based on the above and taking it into account it is possible that the range of data that you could store or be interested in it inclines 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 range of TIMESTAMP today.
TIMESTAMP it is affected by Time Zone settings/settings. Whereas DATETIME is constant.
TIMESTAMP is four bytes and DATETIME of eight bytes, consequently timestamps (TIMESTAMP) are also lighter in the database, with faster indexing.
Considering all of the above if you want to store a specific value "it might be better" to use DATATIME, but if you just want to store to do some tracking of possible changes to the records you might want to use TIMESTAMP when changing a record.
If you are working on MySQL 5 or higher, the values of TIMESTAMP will be they convert from the current time zone to UTC for almacenamiento, and they convert back from UTC to the current time zone for recuperación. only for TIMESTAMP data type.
(note If I find the link of the above I will try to put it here)
Hope to help you.
Should I use a field of type DateTime or TimeStamp in mySQL?
Depending on your requirements, there are certain considerations for each case for example the range of values that can support each.
DATETIME considers date and time values in format:
'YYYY-MM-DD HH:MM:SS', The supported range:'1000-01-01 00:00:00' a '9999-12-31 23:59:59'TIMESTAMP also considers date and time values but has a range of
'1970-01-01 00:00:01' UTC a '2038-01-19 03:14:07' UTC.
Mas information: date, DATETIME, and TIMESTAMP types.
Other perhaps obvious properties for many of us is automatic initialization and update to the current date and time. TIMESTAMP values with default of zero are not allowed in MySql.