CODEWITHSUNDEEP
mysqlsql
Michelignax
Michelignax

Reputation: 69

Having a "Creation Date" column with default value at table creation

I want to have a column that will store the creation date of a row. I'm using php and mysql but I don't think that matters.

I've looked for a series of answers about that but all of them seem to be for updating an existing table. Well surely there's one for what I'm looking for since it's a pretty basic question but I've yet to find it. I've tried things with DEFAULT, CONSTRAINT but none of them allow me to create the table once added to my code. You could have the feeling that I'm not well versed in sql and you would not be wrong.

This creates the table, could you tell me what to add ?

CREATE TABLE IF NOT EXISTS artwork (
  id_artwork int(4) NOT NULL AUTO_INCREMENT,
  title varchar(50) NOT NULL,
  creationDate DateTime(3),
  CONSTRAINT PK_artwork PRIMARY KEY (id_artwork)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

I've tried the following with no success:

creationDate DateTime(3) DEFAULT GETDATE()
creationDate DateTime(3) DEFAULT (GETDATE())
creationDate DATETIME(3) DEFAULT (CURRENT_TIMESTAMP)

MySQL Version: 5.7.23 - MySQL Community Server (GPL)

Upvotes: 1

Views: 1497

Answers (1)

TomNash
TomNash

Reputation: 3288

From the MySQL documentation on initialization using DATETIME:

If a TIMESTAMP or DATETIME column definition includes an explicit fractional seconds precision value anywhere, the same value must be used throughout the column definition.

This means we'll have to carry forward your precision. I was able to get it to work on SQL Fiddle:

CREATE TABLE IF NOT EXISTS artwork (
  id_artwork int(4) NOT NULL AUTO_INCREMENT,
  title varchar(50) NOT NULL,
  creationDate DateTime(3) DEFAULT CURRENT_TIMESTAMP(3),
  CONSTRAINT PK_artwork PRIMARY KEY (id_artwork)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Upvotes: 2

Related Questions