Monday, November 25, 2013

Workaround for CURRENT_TIMESTAMP as default for a DATETIME value

Currently you cannot specify the current time as a default value for a DATETIME column. It works for TIMESTAMP but not for DATETIME,and even with TIMESTAMP this only work for the one timestamp column in the table, if you have more than 1 TIMESTAMP column you have to specify a default for all but the first.

This is fixed in MySQL 5.6 and MariaDB 10 which allows you to use a construct like this:
mysql> CREATE TABLE td1(
  c1 INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  c2 DATETIME DEFAULT NULL());

So what can you do if you are on MariaDB? Well, you can use a trigger to achieve a similar result:
mysql> CREATE TABLE td1(
  c1 INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  c2 DATETIME DEFAULT NULL());
delimiter //
mysql> CREATE TRIGGER td1_ins BEFORE INSERT ON td1 FOR EACH ROW
BEGIN
  IF new.c2 IS NULL THEN
    SET new.c2 = now();
  END IF;
END// 
delimiter ;

And then this works:
mysql> INSERT INTO td1 VALUES(NULL, '1960-04-10 16:45');
mysql> INSERT INTO td1 VALUES(NULL, NULL);
mysql> INSERT INTO td1(c1) VALUES(NULL);
mysql> SELECT * FROM td1;
+----+---------------------+
| c1 | c2                  |
+----+---------------------+
|  1 | 1960-01-04 16:45:00 |
|  2 | 2013-11-23 15:57:51 |
|  3 | 2013-11-23 16:00:55 |
+----+---------------------+
4 rows in set (0.00 sec)



Is this perfect? Nope, but it works while we wait for MariaDB 10.

/Karlsson

3 comments:

Paulo said...

Business land credits are impressively extraordinary when contrasted with private advances. They really are considerably more confounded as they convey terms and conditions that are altogether different when contrasted with that of private advances. cash advance chicago

Paulo said...

Credit unions will as a rule utilize credit reports from outsider sellers. Credit unions are non-benefit associations. This implies individuals who are in a similar industry get together and advance cash to different individuals. This is the reason the interests are low. check cashing san-diego

Paulo said...

Numerous independent ventures have been set up with subsidizing accessible from individual Visas. This is on the grounds that it is promptly open money, and in addition, individual charge cards are less demanding to get hold of than a business credit from a bank or somewhere else. This does not infer that it is the best decision for your startup or business. Payday Loans Corona