CODEWITHSUNDEEP
mysql
Joe Scotto
Joe Scotto

Reputation: 10877

Auto update DATETIME column on insert with SQL?

I am currently just inserting NOW() into the date field on my database which works just fine but it got me wondering, is there a way to automatically update a DATETIME row upon inserting data?

I found online in some places that I should set the extras to ON UPDATE CURRENT_TIMESTAMP but when doing this I get the following error: 

An error occurred when trying to change the field 'uploaded' via

ALTER TABLE `uploads` CHANGE `uploaded` `uploaded` DATETIME
 NOT NULL
 ON UPDATE CURRENT_TIMESTAMP
 COMMENT 'Upload Datetime'

MySQL said:

Invalid ON UPDATE clause for 'uploaded' column

Upvotes: 9

Views: 55648

Answers (6)

Schwern
Schwern

Reputation: 164829

For most SQL dialects, you can use a DATETIME column with a default of current_timestamp. That handles creation.

create table whatever (
    ...
    updated datetime default current_timestamp
);

Then add an after update trigger.

create trigger foo_updated
after update
on foo
for each row
begin
    update foo set updated = current_timestamp where id = new.id;
end;

MySQL has some special syntax to do this. You can make the trigger in your create table statement with an with an on update.

create table whatever (
    ...
    updated datetime default current_timestamp on update current_timestamp
);

And then there's MySQL's timestamp date type. Timestamp will set itself to the current date and time anytime a row is updated... sorta.

The rules are very complicated which is why it's better to use datetime and on update.

Upvotes: 0

Joe Scotto
Joe Scotto

Reputation: 10877

It seems the issue was that the column needed to be a TIMESTAMP not DATETIME upon changing it, I was able to successfully add the CURRENT_TIMESTAMP argument.

Upvotes: 9

Tony
Tony

Reputation: 2754

Set the default value for the column upon table creation or alter it later.

In MySQL versions 5.6.5 and later:

default CURRENT_TIMESTAMP

In SQL Server:

default getdate()

In PostgreSQL:

default now()

Upvotes: 2

Just_Do_It
Just_Do_It

Reputation: 821

Seems your query is wrong, try this out works for me:

ALTER TABLE `uploads` CHANGE `uploaded` `uploaded` 
DATETIME NOT NULL ON UPDATE CURRENT_TIMESTAMP;

Tested it.

Upvotes: 6

santiago arizti
santiago arizti

Reputation: 4737

This is what I do and it has always worked

create table if not exists my_table (
    index1 char(32) not null primary key,
    title varchar(50),
    my_timestamp timestamp not null default current_timestamp on update current_timestamp
)

This will have the timestamp on inserts and on updates, it also is valid sql, maybe you are missing the default statement in your query?

Upvotes: 13

DannyDSB
DannyDSB

Reputation: 145

Maybe this documentation can help you a bit at understanding how to auto update the datetime in a sql database

http://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html

Hope this helped you :)

Upvotes: 0

Related Questions