CODEWITHSUNDEEP
phpmariadb
smith
smith

Reputation: 3

Error on default value with datetime column

I'm trying the following query through phpmyadmin to create a new table:

CREATE TABLE IF NOT EXISTS `app`.`token` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `token` VARCHAR(64) NOT NULL,
  `creation_date` DATETIME NOT NULL DEFAULT NOW(),
  `expiration_date` DATETIME NOT NULL DEFAULT NOW() + INTERVAL 1 HOUR,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;

And I get the following error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '+ INTERVAL 1 HOUR,
  PRIMARY KEY (`id`))
ENGINE = InnoDB' at line 5

If I remove the default value of expiration_date, or change it only to NOW() the query is successful.

Also, the following query is successful too:

INSERT INTO `app`.`token` (`token`,`expiration_date`) VALUES ('b1ae528de7ec8355a655926fcb84397f5d21d604e232b6567471c84610a171a8', NOW() + INTERVAL 1 HOUR);

The NOW() + INTERVAL 1 HOUR works with the INSERT statement but not as the default value. Why is not working as the default value? Do I need a trigger for this?

Upvotes: 0

Views: 204

Answers (3)

pala_
pala_

Reputation: 9010

Here's the trigger you'll need:

DELIMITER //
CREATE TRIGGER expiryDateTrigger BEFORE INSERT on token
FOR EACH ROW
BEGIN
  SET expiration_date = CURRENT_TIMESTAMP + INTERVAL 1 HOUR;
END//
DELIMITER ;

And here's a fiddle showing it in operation: http://sqlfiddle.com/#!9/f8f53/1

It's necessary to change the delimiter when creating a trigger so you can use the semi-colon inside the trigger definition.

Upvotes: 1

George Cummins
George Cummins

Reputation: 28906

Expressions for default values are not supported in MariaDB or MySQL. From the MariaDB documentation:

You must provide a constant default value instead. The one exception is that you may use CURRENT_TIMESTAMP as the default value for a TIMESTAMP column to use the current timestamp at insertion time.

See also the MySQL documentation and a previous StackOverflow question relating to the same topic in MySQL.

Upvotes: 2

MarshallOfSound
MarshallOfSound

Reputation: 2719

You have to use the DATE_ADD mysql function

http://www.w3schools.com/sql/func_date_add.asp

So your query would change to this 

INSERT INTO `app`.`token` (`token`,`expiration_date`) VALUES ('b1ae528de7ec8355a655926fcb84397f5d21d604e232b6567471c84610a171a8', DATE_ADD(CURRENT_TIMESTAMP, INTERVAL 1 HOUR));

Upvotes: 0

Related Questions