Erm's I.T. Girl – Zelna Ellis

Don't fear when Zel is near…

MySQL Date Issues

Oh what a week…
MySQL have a restriction towards dates.
I have 2 columns creationDate and lastUpdate in some tables but:

  • There can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause.
  • You cannot use the function NOW() as a default value for a DATETIME variable.

I wanted both values in my table what now?

SOLUTION:
Set the variable type for creationDate to DATETIME variable type and lastUpdate to TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.
Create a trigger to update the field creationDate.

CODE EXAMPLE:

DROP SCHEMA IF EXISTS TESTIT;
CREATE SCHEMA TESTIT;

USE TESTIT;

DROP TABLE IF EXISTS customer;

CREATE TABLE customer(
custID SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
firstName VARCHAR(50) NOT NULL,
creationDATE DATETIME,
lastUpdate TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (custID)
);


ALTER TABLE customer AUTO_INCREMENT = 1001;

CREATE TRIGGER setMyCreationDate BEFORE INSERT ON customer FOR
EACH ROW SET NEW.creationDate = NOW();

INSERT INTO customer (firstName)
VALUES ('Angel');
INSERT INTO customer (firstName)
VALUES ('Bennie');

SELECT * FROM customer;

UPDATE customer
SET firstName='Christo'
WHERE firstName='Bennie';

SELECT * FROM customer;

Click to view full picture in new tab page:
Use creation date and last update together

CONCLUSION:
Personally I would rather see that MySQL can support the function NOW() as a default value for date variables.

8 April 2009 - Posted by | .NET & MySQL | , , , ,

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: