The issue with timestamp is this: You can only have one timestamp with a default of the current date.
My app has two fields in one table Created_date datetime default now() Last_update datetime default now() This doesn't work with timestamp because timestamp doesn't support two columns with default current_timestamp. Additionally, it looks like if you default to current_timestamp then any time the record is modified, that value is changed. While tinker-toys were wildly popular (I had them), they are wholly unsuited for large scale building projects. George Sexton MH Software, Inc. http://www.mhsoftware.com/ Voice: 303 438 9585 > -----Original Message----- > From: Jay Blanchard [mailto:[EMAIL PROTECTED] > Sent: Thursday, June 09, 2005 5:56 AM > To: George Sexton; mysql@lists.mysql.com > Subject: RE: Seriously.. When are we going to get subqueries?! > > [snip] > I think MySQL has a little ways to go yet before I would subjectively > call > it best. > > I posted twice to the list with questions about porting my application > that > runs on (SQL Server, Oracle, PostgreSQL, Sybase SQL Anywhere, > MS Access, > and > DB2) to MySQL. No one on the mysql list, or the internals > list responded > to > my pretty basic issues: > > 1) Why can't I declare a datetime field with DEFAULT NOW() > 2) Since the SQL standard states that identifiers are not case > sensitive, how can I use the DB without case sensitivity, when I don't > have > authority to change the system wide lowercase setting? I wouldn't have > authority to change the setting in a hosted environment. > > I have to say, MySQL still looks like a tinker-toy to me. > [/snip] > > Hi George, > > Allow me to reiterate that I was trying to inject some humor into what > was fast becoming a rude situation. Having said that.... > > The list(s) where you post is not comprised of MySQL employees, it is > made up of other users/volunteers who seek help and/or can be > of help to > others using the product. The list is often graced by several authors > and people who are intimately invoved with MySQL development and we > appreciate what little time they do have to offer for free. Sometimes > these folks do not have the answers you are seeking. Let me > see if I can > help some with your issues. > > 1. I am supposing that with DEFAULT NOW() you are wanting the datetime > field to be populated with the current datetime when the tuple is > populated. (This is what I read from your question.) From > http://dev.mysql.com/doc/mysql/en/create-table.html "For date and time > types other than TIMESTAMP, the default is the appropriate ``zero'' > value for the type. For the first TIMESTAMP column in a table, the > default value is the current date and time. See Section 11.3, > "Date and > Time Types"." Declaring the column type to be TIMESTAMP may > be all that > you need to do. I'll test... > > CREATE TABLE `tblTimeDate` ( > `id` int(11) NOT NULL auto_increment, > `theDate` date NOT NULL default '0000-00-00', > `theTime` time NOT NULL default '00:00:00', > `theDateStamp` datetime NOT NULL default '0000-00-00 00:00:00', > `theTimeStamp` timestamp(14) NOT NULL, > PRIMARY KEY (`id`) > ) TYPE=MyISAM > > After doing a couple of inserts... > > INSERT INTO tblTimeDate(theDate) VALUES ('2005-06-09'); > > The table returns.... > > +----+------------+----------+---------------------+----------------+ > | id | theDate | theTime | theDateStamp | theTimeStamp | > +----+------------+----------+---------------------+----------------+ > | 1 | 2005-06-09 | 00:00:00 | 0000-00-00 00:00:00 | 20050609063428 | > | 2 | 2005-06-09 | 00:00:00 | 0000-00-00 00:00:00 | 20050609063438 | > +----+------------+----------+---------------------+----------------+ > > You'll note that theTimeStamp column has data inserted into it without > my prodding. I got this information by searching the online manual, I > have never used this MySQL feature. I hope that this is what you were > looking for. > > On case sensitivity (I found this in the online manual after two > clicks), http://dev.mysql.com/doc/mysql/en/name-case-sensitivity.html > > Here is the first paragraph and subsequent note.... > > "In MySQL, databases correspond to directories within the data > directory. Tables within a database correspond to at least one file > within the database directory (and possibly more, depending on the > storage engine). Consequently, the case sensitivity of the underlying > operating system determines the case sensitivity of database and table > names. This means database and table names are not case sensitive in > Windows, and case sensitive in most varieties of Unix. One notable > exception is Mac OS X, which is Unix-based but uses a default > filesystem > type (HFS+) that is not case sensitive. However, Mac OS X > also supports > UFS volumes, which are case sensitive just as on any Unix. See Section > 1.7.4, "MySQL Extensions to Standard SQL". > > Note: Although database and table names are not case sensitive on some > platforms, you should not refer to a given database or table using > different cases within the same query. The following query would not > work because it refers to a table both as my_table and as MY_TABLE: > > mysql> SELECT * FROM my_table WHERE MY_TABLE.col=1;" > > There is a lot more on the issue of identifier case sensitivity. > > Since I have never been concerned with either of these issues I had to > rely on a couple of things to come up with these answers. One, my past > database experience with MySQL and other databases. And B, my > ability to > type some simple search terms into my browser address bar. (For > instance, when searching for answers about identifiers I first typed > http://www.mysql.com/identifiers into the address bar. When the page > appeared one of the first links that I saw concerned case > sensitivity.) > > One final note concerning your comments. Tinker-Toys were > among the most > popular toys of the mid to late 20th century and have made a > resurgence > early in this century. Your comparison of MySQL to Tinker-Toys in this > light is spot on as MySQL has become one of the most popular databases > this century. It is still a growing, evolving, maturing product whose > feature list, it has been widely acknowledged, is not the same as some > other database products. But MySQL is catching up fast! > > I sent this back to the list as well, in hopes that others may benefit > from the questions and answers. Please let me know if I can be of any > further assistance. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
