List:General Discussion« Previous MessageNext Message »
From:George Sexton Date:June 9 2005 12:47pm
Subject:RE: Seriously.. When are we going to get subqueries?!
View as plain text  
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: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?!
> 
> [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/mysql?unsub=1
> 
> 

Thread
Seriously.. When are we going to get subqueries?!Kevin Burton7 Jun
  • Re: Seriously.. When are we going to get subqueries?!Greg Whalin7 Jun
    • Re: Seriously.. When are we going to get subqueries?!Kevin Burton8 Jun
  • Re: Seriously.. When are we going to get subqueries?!DBA8 Jun
    • Re: Seriously.. When are we going to get subqueries?!Kevin Burton8 Jun
  • Re: Seriously.. When are we going to get subqueries?!Jeff Smelser8 Jun
    • Re: Seriously.. When are we going to get subqueries?!Dan Nelson8 Jun
      • Re: Seriously.. When are we going to get subqueries?!Jeff Smelser8 Jun
        • Re: Seriously.. When are we going to get subqueries?!Kevin Burton9 Jun
          • Re: Seriously.. When are we going to get subqueries?!Jochem van Dieten9 Jun
            • Re: Seriously.. When are we going to get subqueries?!Kevin Burton10 Jun
RE: Seriously.. When are we going to get subqueries?!Jay Blanchard8 Jun
  • Re: Seriously.. When are we going to get subqueries?!Jeff Smelser8 Jun
  • Re: Seriously.. When are we going to get subqueries?!Martijn Tonies8 Jun
  • RE: Seriously.. When are we going to get subqueries?!George Sexton9 Jun
    • Re: Seriously.. When are we going to get subqueries?!Jeff Smelser9 Jun
    • Re: Seriously.. When are we going to get subqueries?!Josh Trutwin9 Jun
    • Re: Seriously.. When are we going to get subqueries?!Keith Ivey9 Jun
      • Re: Seriously.. When are we going to get subqueries?!Jochem van Dieten9 Jun
      • Re: Seriously.. When are we going to get subqueries?!George L. Sexton9 Jun
RE: Seriously.. When are we going to get subqueries?!Jay Blanchard8 Jun
  • Re: Seriously.. When are we going to get subqueries?!Jeff Smelser8 Jun
  • Re: Seriously.. When are we going to get subqueries?!Greg Whalin8 Jun
    • Re: Seriously.. When are we going to get subqueries?!Kevin Burton9 Jun
RE: Seriously.. When are we going to get subqueries?!Jay Blanchard8 Jun
RE: Seriously.. When are we going to get subqueries?!PMilanese8 Jun
Re: Seriously.. When are we going to get subqueries?!Bob_Savard8 Jun
  • Re: Seriously.. When are we going to get subqueries?!Boyd Lynn Gerber9 Jun
RE: Seriously.. When are we going to get subqueries?!Jay Blanchard9 Jun
  • Re: Seriously.. When are we going to get subqueries?!Gordan Bobic9 Jun
  • RE: Seriously.. When are we going to get subqueries?!George Sexton9 Jun
RE: Seriously.. When are we going to get subqueries?!Jay Blanchard9 Jun
  • Re: Seriously.. When are we going to get subqueries?!Gordan Bobic9 Jun
    • Re: Seriously.. When are we going to get subqueries?!Josh Trutwin9 Jun
RE: Seriously.. When are we going to get subqueries?!Jay Blanchard9 Jun
RE: Seriously.. When are we going to get subqueries?!Jay Blanchard9 Jun
  • Re: Seriously.. When are we going to get subqueries?!Gordan Bobic9 Jun
RE: Seriously.. When are we going to get subqueries?!Jay Blanchard9 Jun
RE: Seriously.. When are we going to get subqueries?!Jay Blanchard9 Jun
  • RE: Seriously.. When are we going to get subqueries?!George Sexton9 Jun
RE: Seriously.. When are we going to get subqueries?!Jay Blanchard9 Jun
  • RE: Seriously.. When are we going to get subqueries?!George Sexton9 Jun
    • Re: Seriously.. When are we going to get subqueries?!Jeff Smelser9 Jun
      • Re: Seriously.. When are we going to get subqueries?!George L. Sexton9 Jun
        • Re: Seriously.. When are we going to get subqueries?!Roger B.A. Klorese 9 Jun
          • Re: Seriously.. When are we going to get subqueries?!Jochem van Dieten9 Jun
        • Re: Seriously.. When are we going to get subqueries?!Jeff Smelser9 Jun
          • Re: Seriously.. When are we going to get subqueries?!Greg Whalin9 Jun
            • Re: Seriously.. When are we going to get subqueries?!daniel9 Jun
              • Re: Seriously.. When are we going to get subqueries?!Jochem van Dieten9 Jun
  • Re: Seriously.. When are we going to get subqueries?!David Griffiths9 Jun
    • RE: Seriously.. When are we going to get subqueries?!J.R. Bullington9 Jun
Re: Seriously.. When are we going to get subqueries?!George L. Sexton9 Jun
RE: Seriously.. When are we going to get subqueries?!Bob)9 Jun
  • Re: Seriously.. When are we going to get subqueries?!Jochem van Dieten9 Jun
RE: Seriously.. When are we going to get subqueries?!Jay Blanchard9 Jun
RE: Seriously.. When are we going to get subqueries?!Gordon Bruce9 Jun
  • Timestamp and it's usage (Re: Seriously.. When are we going to get subqueries?!)Martijn Tonies9 Jun
    • Re: Timestamp and it's usage (Re: Seriously.. When are we going to get subqueries?!)Jochem van Dieten9 Jun
  • Re: Timestamp and it's usage (Re: Seriously.. When are we going to get subqueries?!)Martijn Tonies9 Jun