MySQL Create Time and Update Time Timestamp

MySQL create time and update time timestamp

You can have two columns of type timestamp in one table.

The following works for MySQL 5.0


create table t
(
id integer,
created_at timestamp default current_timestamp,
updated_at timestamp
);

I think you are confusing this with SQL Server (where timestamp is not really a "time stamp" and there is indeed a limit on a single "timestamp" column)

Edit: But you will need a trigger to update the update_at column each time the row is changed.

MariaDB/MySql: Setting CURRENT_TIMESTAMP on CREATE and changing noting on UPDATE

you can create trigger for this

DELIMITER //

CREATE TRIGGER user_prefs_before_insert
BEFORE INSERT
ON user_prefs FOR EACH ROW

BEGIN
SET NEW.updated = new.created;
END; //

DELIMITER ;

then another trigger for update

DELIMITER //

CREATE TRIGGER user_prefs_before_update
BEFORE UPDATE
ON user_prefs FOR EACH ROW

BEGIN
SET NEW.updated = CURRENT_TIMESTAMP();
END; //

DELIMITER ;

When is a timestamp (auto) updated?

Give the command SHOW CREATE TABLE whatever

Then look at the table definition.

It probably has a line like this

logtime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

in it. DEFAULT CURRENT_TIMESTAMP means that any INSERT without an explicit time stamp setting uses the current time. Likewise, ON UPDATE CURRENT_TIMESTAMP means that any update without an explicit timestamp results in an update to the current timestamp value.

You can control this default behavior when creating your table.

Or, if the timestamp column wasn't created correctly in the first place, you can change it.

ALTER TABLE whatevertable
CHANGE whatevercolumn
whatevercolumn TIMESTAMP NOT NULL
DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP;

This will cause both INSERT and UPDATE operations on the table automatically to update your timestamp column. If you want to update whatevertable without changing the timestamp, that is,

To prevent the column from updating when other columns change

then you need to issue this kind of update.

UPDATE whatevertable
SET something = 'newvalue',
whatevercolumn = whatevercolumn
WHERE someindex = 'indexvalue'

This works with TIMESTAMP and DATETIME columns. (Prior to MySQL version 5.6.5 it only worked with TIMESTAMPs) When you use TIMESTAMPs, time zones are accounted for: on a correctly configured server machine, those values are always stored in UTC and translated to local time upon retrieval.

MySQL CURRENT_TIMESTAMP on create and on update

Guess this is a old post but actually i guess mysql supports 2 TIMESTAMP in its recent editions mysql 5.6.25 thats what im using as of now.

MySQL timestamp fields - created/modified

Depending on the driver, the 0000-00-00 date might be treated as null, which isn't valid for your column definition. But if that wasn't the case, any date prior to 1970 isn't a valid timestamp either.

I'd try defining "created" as DATETIME instead of TIMESTAMP

alter table users modify `created` DATETIME NOT NULL DEFAULT '1000-01-01 00:00:00'

(That is the lowest date a DATETIME can take). As to why the modified column is not working, it's part of the same problem. Tables are supposed to have only one timestamp column, but if you put two or more, only the first one can have CURRENT_TIMESTAMP. Silly mysql limitations.

This restriction will be lifted in future version, but I guess yours hasn't that yet.

Update only the time stamp in a MySql table

UPDATE last_user_activity 
SET time_stamp = NULL
WHERE token="40aed4d9-c9ac-471e-8d53-b2baa0d72523";

OR

UPDATE last_user_activity 
SET time_stamp = NOW()
WHERE token="40aed4d9-c9ac-471e-8d53-b2baa0d72523";

Having both a Created and Last Updated timestamp columns in MySQL 4.0

From the MySQL 5.5 documentation:

One TIMESTAMP column in a table can have the current timestamp as the default value for initializing the column, as the auto-update value, or both. It is not possible to have the current timestamp be the default value for one column and the auto-update value for another column.

Changes in MySQL 5.6.5:

Previously, at most one TIMESTAMP column per table could be automatically initialized or updated to the current date and time. This restriction has been lifted. Any TIMESTAMP column definition can have any combination of DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses. In addition, these clauses now can be used with DATETIME column definitions. For more information, see Automatic Initialization and Updating for TIMESTAMP and DATETIME.



Related Topics



Leave a reply



Submit