CODEWITHSUNDEEP
mysqlsql
dames
dames

Reputation: 1481

mysql date and time columns

I'm using mysql have two columns in a table one is date_added and the other is date_updated, is it possible to have it that the date when the data was entered is recorded in date_added and the date it was updated recoreded in date_updated without changing the date_added column.

Upvotes: 1

Views: 217

Answers (2)

Ross Smith II
Ross Smith II

Reputation: 12189

Prior to 5.6, MySQL only supported having one TIMESTAMP field that auto-initializes. This can be a 'created' or an 'updated' column, but not both at the same time.

For an auto-initializing 'created' column, use:

CREATE TABLE ...
date_added TIMESTAMP DEFAULT CURRENT_TIMESTAMP

For an auto-initializing 'updated' column, use

CREATE TABLE ...
date_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Unfortunately, you can't have both. If you want to have both auto-initialize, you will need to use triggers:

CREATE TABLE ...
date_added TIMESTAMP, -- doesn't auto-initialize
date_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

and then set up a TRIGGER to update the date_added field:

DROP TRIGGER IF EXISTS tablename_before_insert;

DELIMITER //

CREATE
DEFINER = CURRENT_USER
TRIGGER tablename_before_insert
    BEFORE INSERT ON 
        tablename -- insert your table name here
    FOR EACH ROW
BEGIN
    IF IFNULL(NEW.date_added, 0) <= 0
    THEN
        SET NEW.date_added = NOW();
    END IF;
END;
//

The

 IF IFNULL(NEW.date_added, 0) <= 0

clause is there to allow you to override the date_added value, if you want.

Upvotes: 2

John Conde
John Conde

Reputation: 219894

Of course. Just use the correct column name in your queries. When doing your INSERT populate the date_added field. When doing your UPDATEs just update the date_updated field. The other won't be affected unless you explicitly do so in the query or with a trigger. But that is obviously in your control.

Upvotes: 0

Related Questions