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

At some point in this MySQL documentation it states 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 '09/01/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'.

Starting from the above and taking it into account, it is possible that the range of data that you could store or be interested in might tilt you in favor of one or the other, for example, if you work with mortgages, to say the least, it is easy for you to exceed the range TIMESTAMPtoday.

TIMESTAMPis affected by time zone settings/adjustments. As long as it DATETIMEis constant.

TIMESTAMPis four bytes and DATETIMEeight bytes, therefore timestamps ( TIMESTAMP) are also lighter in the database, with faster indexing.

With all of the above in mind if you want to store a specific value "it might be better" to use DATATIME, but if you just want to store to keep track of possible record changes you might want to use TIMESTAMPwhen a record changes.

If you are working on MySQL 5or above, the values ​​of TIMESTAMPare converted from the current time zone to UTC for the almacenamiento, and are converted back from UTC to the current time zone for the 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.

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

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

• DATETIME considers datetime 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.

more information: DATE, DATETIME, and TIMESTAMP types.

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

I only see the point of using TimeStampit when you want to make sure that date is set automatically when the record is inserted or updated.

For example, if you want to save the date and time of an event, you define it like this:

CREATE TABLE `eventos` 
(
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Evento` VarChar(50) NOT NULL DEFAULT '', 
  `FechaHora` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 
  PRIMARY KEY (`Id`)
);

Note that in the INSERT you do not include the field FechaHora.

INSERT INTO eventos(Evento) VALUES('Evento 0001');

SELECT * FROM eventos; 

Another example with UPDATE:

CREATE TABLE `productos` 
(
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Nombre` VarChar(50) NOT NULL DEFAULT '', 
  `Marca` VarChar(50) NOT NULL DEFAULT '', 
  `Referencia` VarChar(50) NOT NULL DEFAULT '', 
  `FechaModif` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, 
  PRIMARY KEY (`Id`)
);


INSERT INTO productos (Nombre, Marca, Referencia, FechaModif) VALUES ('MOUSE', 'GENIUS', 'X105', NOW());

SELECT * FROM productos; 

UPDATE productos p SET p.Referencia = 'X106' WHERE p.Id = 1;

SELECT * FROM productos;