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.
MH Software, Inc.
Voice: 303 438 9585
> -----Original Message-----
> From: Jay Blanchard [mailto:jay.blanchard@stripped]
> Sent: Thursday, June 09, 2005 5:56 AM
> To: George Sexton; mysql@stripped
> Subject: RE: Seriously.. When are we going to get subqueries?!
> I think MySQL has a little ways to go yet before I would subjectively
> it best.
> I posted twice to the list with questions about porting my application
> runs on (SQL Server, Oracle, PostgreSQL, Sybase SQL Anywhere,
> MS Access,
> DB2) to MySQL. No one on the mysql list, or the internals
> list responded
> 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
> 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.
> 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
> 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
> 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
> 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: