CODEWITHSUNDEEP
mysqldatetimetimestamp
Michael Plautz
Michael Plautz

Reputation: 3778

MySQL is not allowing ON UPDATE CURRENT_TIMESTAMP for a DATETIME field

I have seen a lot of related questions, but I cannot place my finger on this specific question:

I have a MySQL table with both a TIMESTAMP (for when the field was created) and a DATETIME (for each time the field gets updated). It looks like this:

CREATE TABLE 'vis' (
ID BIGINT PRIMARY KEY AUTO_INCREMENT NOT NULL,
ENTRY VARCHAR(255),
AUTHOR VARCHAR(255),
CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UPDATED_AT DATETIME ON UPDATE CURRENT_TIMESTAMP,
UPDATED_BY VARCHAR(255)
)

When I try this though, the error I am getting is: (SQL Error: 1294 SQL State: HY000) - Invalid ON UPDATE clause for 'updated_at' field

Everywhere I have read (even on Stack Overflow) suggests I should be able to do this, yet I am getting this error. Perhaps there is another way to have a field that automatically updates the time each time I update it?

I am using MySQL Server 5.5.

Upvotes: 23

Views: 64654

Answers (7)

SandroMarques
SandroMarques

Reputation: 6534

INSERT and UPDATE date/time automatically

Works with data type: DATETIME or TIMESTAMP

Tested on: MySQL 5.6.27-2 and MariaDB 10.1.10

Stores the current date and time on INSERT

CREATE TABLE table_demo (
    ...
    `CreatedAt` datetime DEFAULT CURRENT_TIMESTAMP
    ...
);

Stores the current date and time on INSERT and UPDATE

CREATE TABLE table_demo (
    ...
    `UpdatedAt` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ... 
);

Stores the current date and time only on UPDATE

NOTE: when INSERT, the default value is '0000-00-00 00:00:00'

CREATE TABLE table_demo (
    ...
    `UpdatedAt` datetime DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP
    ...
);

Upvotes: 1

slugonamission
slugonamission

Reputation: 9642

DATETIME cannot use CURRENT_TIMESTAMP on update. Instead, change it to a TIMESTAMP.

Or, consider using a trigger for this situation: http://dev.mysql.com/doc/refman/5.0/en/trigger-syntax.html

EDIT: As in the comments (thanks @АлександрФишер!), this is no longer the case since MySQL 5.6.5, so another solution is to upgrade your server if possible.

Upvotes: 37

Shihab Uddin
Shihab Uddin

Reputation: 6931

Look my picture.

Set column Filed Type timestamp, Attributes on Update CURRENT_TIMESTAMP & Extra also . No problem after changed the filed type in Database, in my case . But not sure for all case. enter image description here

Upvotes: 0

原习斌
原习斌

Reputation: 1

The TIMESTAMP and (as of MySQL 5.6.5) DATETIME data types offer automatic initialization and updating to the current date and time. For more information, see Section 11.3.5, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”.

Upvotes: 0

rich remer
rich remer

Reputation: 3577

That feature appears to have been introduced in 5.6. Works as expected on my default OS X install.

Reference: Automatic Timestamp Properties Before MySQL 5.6.5

Upvotes: 5

Anshu
Anshu

Reputation: 7853

MySQL does not allow functions to be used for default DateTime values. (See MySQL Data Type Defaults.)

Upvotes: 2

FSP
FSP

Reputation: 4827

Yeah, and if you change it to timestamp , with neither DEFAULT CURRENT_TIMESTAMP nor ON UPDATE CURRENT_TIMESTAMP, it is the same as specifying both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP.

Upvotes: 0

Related Questions