Saving timestamps in mySQL

Question

Remember, I read somewhere that the data type timestamp mySQL is used to automatically update when updating a row, is that true?

Do I need to store timestamps with the timestamp data type or with bigint, for example?

NB: the timestamp that should be saved is not generated by mySQL, this is a predefined value that will be generated using PHP.

Thanks.

Answer 1

A column of type TIMESTAMP can be automatically updated using ON UPDATE CURRENT_TIMESTAMP in the column definition.

However, this will not fill out when inserting a record. As you said, you can create a timestamp in PHP (format: YYYY-MM-DD HH:MM:SS ) and insert it, or you can set another attribute of the DEFAULT CURRENT_TIMESTAMP column in the column definition.

For more information: MySQL: TIMESTAMP Properties

UPDATE: If the unix timetstamp value is the desired output, save the value with TIMESTAMP and then get the unix value using:

 SELECT UNIX_TIMESTAMP(your_column_name) FROM your_table_name 

To insert using a unix timestamp, you can use FROM_UNIXTIME()

 INSERT INTO (your_table_name) (your_column_name) VALUES (FROM_UNIXTIME(your_bigint_value)); 

Answer 2

ok I think this is allowed.

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-types.html

a timestamp with a zero value looks like this: 00:00:00 '0000-00-00, so it cannot be used to store a unix timestamp as follows: 1309347278, so it must be BIGINT.

Thanks.

Answer 3

 CREATE TABLE `db1`.`sms_queue` ( `Id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, `Message` VARCHAR(160) NOT NULL DEFAULT 'Unknown Message Error', `CurrentState` VARCHAR(10) NOT NULL DEFAULT 'None', `Phone` VARCHAR(14) DEFAULT NULL, `LastUpdated` TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP, `TriesLeft` tinyint NOT NULL DEFAULT 3, PRIMARY KEY (`Id`) ) ENGINE = InnoDB; 

Answer 4

Remember, I read somewhere that the data type timestamp mySQL is used to automatically update when updating a row, is that true?

Yes.

Do I need to store timestamps with the timestamp data type or with bigint, for example?

Timestamp is a data type. You use it as latest_update TIMESTAMP(8) when creating the table.

Answer 5

from the MySQL manual:

By default, the first TIMESTAMP column in the table is automatically set to the date and time of the last operation, unless you assign it a value yourself. You can also set any TIMESTAMP column to the current date and time by setting it to NULL.

Answer 6

The correct answer is int(11) , since the timestamp is a large integer .