Should I use a DateTime or TimeStamp type field in mySQL?

Question

Should I use a DateTime or TimeStamp type field in mySQL?

Navigation

#1 by (31 votes)
#2 by (7 votes)

27

I must save dates and times in different tables of a typical transactional application where I will store varied information, such as:

Dates and times 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 employee's date of birth, or the employee's children.

I have little experience with MySQL and I'm not sure what kind of data I should choose in each case.

What is advisable, use a field of type DateTime or type < a href="https://dev.mysql.com/doc/refman/5.0/en/datetime.html"> TimeStamp and why?

mysql datetime timestamp

asked by jachguate 02.11.2015 в 16:27

source

2 answers

7

Should I use a DateTime or TimeStamp type field in mySQL?

Depends on your requirements, there are certain considerations for each case for example the range of values that each can support.

DATETIME considers date and time values in the following format: 'YYYY-MM-DD HH:MM:SS' , the supported range: '1000-01-01 00:00:00' a '9999-12-31 23:59:59'



TIMESTAMP It 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.

more information: Types DATE, DATETIME, and TIMESTAMP.

Other properties perhaps obvious to many of us is automatic initialization and updating to the current date and time. TIMESTAMP values with default of zero are not allowed in MySql.

answered by 01.12.2015 в 18:35

Execute subqueries in materialized view Copy the contents of an element to the clipboard using JavaScript

score 31 · Accepted Answer

At some point in this documentation MySQL exposes something like this: link (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 you need values that contain both the   date and time MySQL retrieves and displays the DATETIME values in   'YYYY-MM-DD HH: MM: SS' format. The admitted range is '01 / 01/1000   00:00:00 'a' 9999-12-31 23:59:59 '.

Starting from the above and keeping this in mind, it is possible that the range of data that you could store or be interested in 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 exceed the TIMESTAMP range today.

TIMESTAMP is affected by the settings / settings of the time zone. While DATETIME is constant.

TIMESTAMP is four bytes and DATETIME of eight bytes, consequently timestamps ( 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 what you want is to store to make a follow-up of possible changes in the records, you may want to use TIMESTAMP when a record changes.

If you are working on MySQL 5 or higher, the values of TIMESTAMP 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 I help you.