CODEWITHSUNDEEP
mysqlsql
Ricky
Ricky

Reputation: 383

Is it possible to auto initialize a date field in MySQL?

So I would like to auto initialize a date field (without the time), and then auto initialize a time field (without the date) in 2 separate columns (I don't want them in the same column)...

Is this possible?

I thought this would work... but it doesn't.

CREATE TABLE `account_ip_history` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `uid` int(10) unsigned NOT NULL,
  `ip_address` varchar(39) NOT NULL,
  `created_date` date NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_time` time NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8;

Upvotes: 0

Views: 477

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270653

The documentation is quite clear:

TIMESTAMP and DATETIME columns can be automatically initializated and updated to the current date and time (that is, the current timestamp).

If DATE could be initialized it would be included in the documentation.

So, simply do:

CREATE TABLE `account_ip_history` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `uid` int(10) unsigned NOT NULL,
  `ip_address` varchar(39) NOT NULL,
  `created_datetime` datetim NOT NULL DEFAULT CURRENT_TIMESTAMP
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8;

You can extract the date and time components when you query the table.

Upvotes: 1

Related Questions