MySQL Forums
Forum List  »  Newbie

Re: date format and sort questions
Posted by: Peter Brawley
Date: June 08, 2009 11:59AM

Ryan,

First, decide whether the date column is to store date or datetime, and if the latter, whether it can be a timestamp or, if you must store datetimes before 1970 or after 2038, a datetime type. Read the manual section on date and time data types. If it is to be a timestamp, decide which default you want.

There are obvious advantages to storing dates in one column. Convenience, compactness, efficiency. There are also disadvantages. For example, MySQL does not support function-based indexes, so you may not maintain an index on, say, MONTH(datevalue). Usually that's not a problem, but it can be. Depends on your requirement.

As a beginner you need to get familiar with standard methods of database design. See http://www.artfulsoftware.com/dbresources.html. Adopt a consistent, thorough method, eg http://www.artfulsoftware.com/dbdesignbasics.html, and stick to it.

You can make one date column (named, say, dt) from three (named, say, y, m and d) with something like this:

ALTER TABLE tbl add column dt datetime;
UPDATE tbl SET dt=DATE(CONCAT(y,'-',m,'-',d));
-- check results
SELECT * FROM tbl;
ALTER TABLE tbl DROP COLUMN d;
ALTER TABLE tbl DROP COLUMN m;
ALTER TABLE tbl DROP COLUMN y;

PB
http://www.artfulsoftware.com

Options: ReplyQuote


Subject
Written By
Posted
Re: date format and sort questions
June 08, 2009 11:59AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.