Re: Does Update allow for aliases?
Hi Reina, Try like: mysql > UPDATE maindb o,altdb ao set o.price =ao.price where o.id=ao.id; This will do good. Thanks ViSolve DB Team - Original Message - From: "Richard Reina" <[EMAIL PROTECTED]> To: Sent: Wednesday, January 10, 2007 10:08 PM Subject: Does Update allow for aliases? I am trying to update from one table to another but I get a syntax error when I try: UPDATE from maindb.orders o, altdb.orders ao SET o.price=ao.price WHERE o.ID=a.ID; If update does not support aliases, is there another way to do this query? I am usin V3.23.54. Any help would be greatly appreciated. Thanks, Richard Your beliefs become your thoughts. Your thoughts become your words. Your words become your actions. Your actions become your habits. Your habits become your values. Your values become your destiny. -- Mahatma Gandhi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to take advantage of STR_TO_DATE
Hi,
STR_TO_DATE() simply converts the given format string to datetime value. So
to change the format of the date dispaly, go for DATE_FORMAT().
For Instance,
mysql> select DATE_FORMAT('2007/10/01','%d/%m/%Y');
or
mysql> select DATE_FORMAT(datecolumn,'%d/%m/%Y') from table1;
Thanks
ViSolve DB Team
- Original Message -
From: "Gilles MISSONNIER" <[EMAIL PROTECTED]>
To:
Sent: Thursday, January 11, 2007 12:49 AM
Subject: how to take advantage of STR_TO_DATE
Hello the list
I have a bunch of data that I load in the base through
the "load data infile" procedure.
These data contain date with the following date format :
%d/%m/%Y [ that is day/month/year_4digit ]
I could rewrite the date with a script (perl, shell,)
to convert "day/month/year_4digit" into the standard MySQL format
that is "year_4digit-month-day", then load data in the base.
but I think I could take advantage of the STR_TO_DATE feature :
mysql> SELECT STR_TO_DATE('15/10/1999', '%d/%m/%Y');
+---+
| STR_TO_DATE('15/10/1999', '%d/%m/%Y') |
+---+
| 1999-10-15|
+---+
I don't know how to do it on the fly :
should I create an string colum, in which I put the date like
"15/10/1999"
then run a mysql procedure that use STR_TO_DATE to fill a date column ?
how to do this ?
regards,
_-¯-_-¯-_-¯-_-¯-_
Gilles Missonnier
IAP - [EMAIL PROTECTED]
01 44 32 81 36
--
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]
RE: Help me to understand multiple locking the same tables (lock; lock; unlock)
Yes, the two examples are equivalent. "UNLOCK TABLES releases any locks held by the current thread. All tables that are locked by the current thread are implicitly unlocked when the thread issues another LOCK TABLES..." So there is a hole there with either example. In order to keep others from changing your data under you, you'll need a single (WRITE) lock around your entire operations. Sad, but true. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 > -Original Message- > From: Denis Solovyov [mailto:[EMAIL PROTECTED] > Sent: Wednesday, January 10, 2007 2:29 PM > To: mysql@lists.mysql.com > Subject: Help me to understand multiple locking the same > tables (lock; lock; unlock) > > Dear friends, > > Please help me to understand several LOCKing the same > tables without > unlocking them between "LOCKs". Imagine the following code: > > LOCK TABLES t1 READ, t2 READ; > -- some hard select queries which need that other threads do > not update tables > LOCK TABLES t1 WRITE, t2 WRITE; > -- some easy update queries > UNLOCK TABLES; > > Is this code equal to the following: > > LOCK TABLES t1 READ, t2 READ; > -- some hard select queries which need that other threads do > not update tables > UNLOCK TABLES; > -- here other threads have a moment to update these tables! > LOCK TABLES t1 WRITE, t2 WRITE; > -- some easy update queries > UNLOCK TABLES; > > or t1 and t2 will not be unlocked even for a moment before > the second > lock? > > Really, I don't want to have a single WRITE LOCK here > and freeze > everything for some time, but I can't understand if here is a > chance for > other threads to update tables between two lockings or not... > > MySQL 4.1, myisam tables (if it is important). > > Best regards, > Denis Solovyov > > > -- > 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]
Log Warnings Level
The manual indicates that you can specify a specific level to control what types of warnings are logged: http://dev.mysql.com/doc/refman/5.0/en/server-options.html (See section on log-warnings.) But all I really get from this reading is 0 turns it off, 1 prints some warnings, and 2 prints level 1 warnings plus aborted connections warnings. I have not been able to find any additional information in my search. Am I missing something, or is this all the documentation there is on this? Thanks, Kristen G. Thorson Programmer (804) 553-1130, Ext. 204 www.AllegroConsultants.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Does Update allow for aliases?
Hi all, Multi-table updates are not possible for versions older than 4.0.4. (http://dev.mysql.com/doc/refman/4.1/en/update.html) so the operation is not possible with your current version. To be complete, though, each of you missed the second syntax error in his statement Jonathan Langevin wrote: The proper syntax would need to be: UPDATE maindb.orders o, altdb.orders ao SET o.price=ao.price WHERE o.ID=a.ID; The second table is aliased to 'ao' not 'a': UPDATE maindb.orders o, altdb.orders ao SET o.price=ao.price WHERE o.ID=ao.ID; ^^ Look here :) An alternative form is: UPDATE maindb.orders o INNER JOIN altdb.orders ao ON o.ID=ao.ID SET o.price=ao.price; The portion of the mulitple-table UPDATE command will accept any valid JOIN syntax, not just the implied INNER JOIN of a comma separated table list. Yours, -- Shawn Green, Support Engineer MySQL Inc., USA, www.mysql.com Office: Blountville, TN Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange query.
[EMAIL PROTECTED] ("Paul Halliday") wrote in
news:[EMAIL PROTECTED]:
> I am trying to deal with empty values so that I can graph data over a
> 24 hour period without gaps.
Have a look here:
http://forums.mysql.com/read.php?10,133603,133607#msg-133607
--
felix
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help me to understand multiple locking the same tables (lock; lock; unlock)
Dear friends, Please help me to understand several LOCKing the same tables without unlocking them between "LOCKs". Imagine the following code: LOCK TABLES t1 READ, t2 READ; -- some hard select queries which need that other threads do not update tables LOCK TABLES t1 WRITE, t2 WRITE; -- some easy update queries UNLOCK TABLES; Is this code equal to the following: LOCK TABLES t1 READ, t2 READ; -- some hard select queries which need that other threads do not update tables UNLOCK TABLES; -- here other threads have a moment to update these tables! LOCK TABLES t1 WRITE, t2 WRITE; -- some easy update queries UNLOCK TABLES; or t1 and t2 will not be unlocked even for a moment before the second lock? Really, I don't want to have a single WRITE LOCK here and freeze everything for some time, but I can't understand if here is a chance for other threads to update tables between two lockings or not... MySQL 4.1, myisam tables (if it is important). Best regards, Denis Solovyov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to take advantage of STR_TO_DATE
Hello the list
I have a bunch of data that I load in the base through
the "load data infile" procedure.
These data contain date with the following date format :
%d/%m/%Y [ that is day/month/year_4digit ]
I could rewrite the date with a script (perl, shell,)
to convert "day/month/year_4digit" into the standard MySQL format
that is "year_4digit-month-day", then load data in the base.
but I think I could take advantage of the STR_TO_DATE feature :
mysql> SELECT STR_TO_DATE('15/10/1999', '%d/%m/%Y');
+---+
| STR_TO_DATE('15/10/1999', '%d/%m/%Y') |
+---+
| 1999-10-15|
+---+
I don't know how to do it on the fly :
should I create an string colum, in which I put the date like
"15/10/1999"
then run a mysql procedure that use STR_TO_DATE to fill a date column ?
how to do this ?
regards,
_-¯-_-¯-_-¯-_-¯-_
Gilles Missonnier
IAP - [EMAIL PROTECTED]
01 44 32 81 36
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Does Update allow for aliases?
The proper syntax would need to be: UPDATE maindb.orders o, altdb.orders ao SET o.price=ao.price WHERE o.ID=a.ID; The only problem is the existence of the "from". That being said, an UPDATE ... JOIN likely doesn't work under MySQL 3 -Original Message- From: Chris White [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 10, 2007 12:10 PM To: Richard Reina Cc: mysql@lists.mysql.com Subject: Re: Does Update allow for aliases? Richard Reina wrote: > I am trying to update from one table to another but I get a syntax error when I try: > > UPDATE from maindb.orders o, altdb.orders ao SET o.price=ao.price WHERE o.ID=a.ID; > First off, it'd be best if possible (I know some cases prevent it) to upgrade your server. The latest stable is 5 and you're on 3, so a lot of people aren't going to be able to vouch that much for any sort of issues regarding it. Also, UPDATE FROM seems to be a non standard SQL extension, and I haven't been able to find anything on MySQL supporting it (Only MsSQL). Feel free to prove me wrong though (in fact I'd love to be proven wrong so I know I'm not going completely crazy ;) ). -- 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]
RE: automated character set conversions for tables
Columns can have character set definitions, also. In this case, I hope not. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 > -Original Message- > From: Chris White [mailto:[EMAIL PROTECTED] > Sent: Wednesday, January 10, 2007 10:59 AM > To: [EMAIL PROTECTED] > Cc: mysql@lists.mysql.com > Subject: Re: automated character set conversions for tables > > > > I did a DB conversion before that with "ALTER DATABASE db_name > > CHARACTER SET utf8" > > That worked wonderfully, except not as expected. ;-) > > It basically converted only the database itself. so I had to do a > > separate "ALTER TABLE ..." for each table. > > The database encoding more establishes the default to use > when creating > new tables. As far as adjusting every single table, you can > work with > your Favorite Scripting Program (tm) and run the query: > > `SHOW TABLES` > > to get a list of all tables for that database (the column you want is > called Tables_in_[database name here]), which you can get the exact > column by running it in console or your Favorite SQL Program > (tm). Then > simply loop over the result set and run the alter table > command on each > table. > > -- > 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]
Multiple table updates (Was: Does Update allow for aliases)
Reading the noted previous thread, I was curious as to updating multiple tables. I read the MySQL docs, which mentions that you can do it: Multiple-table syntax: UPDATE [LOW_PRIORITY] [IGNORE] /|table_references|/ SET /|col_name1|/=/|expr1|/ [, /|col_name2|/=/|expr2|/ ...] [WHERE /|where_condition|/] However, I didn't see any sort of example for achieving this (that wasn't somewhat complicated). Does anyone have a base example (preferably with table structure) that can show how this works?
Re: Does Update allow for aliases?
Hi Richard, Richard Reina wrote: I am trying to update from one table to another but I get a syntax error when I try: UPDATE from maindb.orders o, altdb.orders ao SET o.price=ao.price WHERE o.ID=a.ID; If update does not support aliases, is there another way to do this query? I am usin V3.23.54. Any help would be greatly appreciated. I think multi-table update or delete operations are impossible in MySQL 3.23. You should really consider upgrading to a more recent version as the mysql 3 lifecycle ended long ago. You will need a script to do that, it can't be done in pure SQL. regards Nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Does Update allow for aliases?
Richard Reina wrote: I am trying to update from one table to another but I get a syntax error when I try: UPDATE from maindb.orders o, altdb.orders ao SET o.price=ao.price WHERE o.ID=a.ID; First off, it'd be best if possible (I know some cases prevent it) to upgrade your server. The latest stable is 5 and you're on 3, so a lot of people aren't going to be able to vouch that much for any sort of issues regarding it. Also, UPDATE FROM seems to be a non standard SQL extension, and I haven't been able to find anything on MySQL supporting it (Only MsSQL). Feel free to prove me wrong though (in fact I'd love to be proven wrong so I know I'm not going completely crazy ;) ). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date v. DateTime index performance
Yes, of course. Thank you! - Anders Brent Baisley wrote: Splitting out your values will cause problems where doing greater than/less than searching. If you search on year_number>=2000 and month_number>=6, that's not going to give you everything from 6/2000 on. It will return really only the second half of each year from 2000 on. To include 2/2002, you'll need to add an OR statement, which will slow things down. If you want to search on just year and month for a date field, just add the first day of the month. If you want an entire month, search on >= first day of the month and < the first day of the next month. That will use an index. - Original Message - From: "Anders Lundgren" <[EMAIL PROTECTED]> To: "Dan Buettner" <[EMAIL PROTECTED]> Cc: "Thomas Bolioli" <[EMAIL PROTECTED]>; Sent: Tuesday, January 09, 2007 8:34 PM Subject: Re: Date v. DateTime index performance > One potential solution might be to use an extra column that tracks > month_number, and populate it with a trigger on insert or update. > Index that field and then use it in your WHERE clause. One > possibility anyway. Resulting question, what if I have three colums named year_number, month_number and day_number. How should I create the keys on these columns? I. (year_number, month_number, day_number) - or - II. (year_number) (month_number) (day_number) If I create the key as of I. above and in the Where clause I just compare year and month, can the index still be used? Thanks, Anders Dan Buettner wrote: Thomas, I do not think in this case that one is better than the other, for the most part, because both require using a value computed from the column. Computing month from a DATE field should be just as fast as computing from a DATETIME column I would think. Also splitting into DATE and TIME columns can make your SQL a bit trickier depending on your needs. That being said, one difference that might come up in extreme cases is that the size of an index on a DATE column will be smaller than on a DATETIME (fewer unique values, less cardinality) so if you have a lot of records you might be able to keep all or more of the index in memory. One potential solution might be to use an extra column that tracks month_number, and populate it with a trigger on insert or update. Index that field and then use it in your WHERE clause. One possibility anyway. HTH, Dan On 12/4/06, Thomas Bolioli <[EMAIL PROTECTED]> wrote: If one has a large number of records per month and normally searches for things by month, yet needs to keep things time coded, does anyone know if it make sense to use datetime or separate date and a time columns? Thanks, Tom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Does Update allow for aliases?
I am trying to update from one table to another but I get a syntax error when I try: UPDATE from maindb.orders o, altdb.orders ao SET o.price=ao.price WHERE o.ID=a.ID; If update does not support aliases, is there another way to do this query? I am usin V3.23.54. Any help would be greatly appreciated. Thanks, Richard Your beliefs become your thoughts. Your thoughts become your words. Your words become your actions. Your actions become your habits. Your habits become your values. Your values become your destiny. -- Mahatma Gandhi
MySQL Community Server 5.0.33 have been released
Dear MySQL users, MySQL Community Server 5.0.33, a new version of the popular Open Source Database Management System, has been released. The release is now available in source form from our download pages at http://dev.mysql.com/downloads/ and mirror sites. Note that not all mirror sites may be up to date at this point in time, so if you can't find this version on some mirror, please try again later or choose another download site. MySQL Community Server 5.0.33 is a source-only release, which means that we will not provide binary builds. We welcome and appreciate your feedback, bug reports, bug fixes, patches etc.: http://forge.mysql.com/wiki/Contributing The following section lists the changes from version to version in the MySQL source code through the latest released version of MySQL Community Server, the MySQL Community Server 5.0.27 release. It can also be viewed online at http://dev.mysql.com/doc/refman/5.0/en/releasenotes-cs-5-0-33.html Functionality added or changed: * NDB Cluster: Setting the configuration parameter LockPagesInMainMemory had no effect. (Bug#24461: http://bugs.mysql.com/24461) * NDB Cluster: It is now possible to create a unique hashed index on a column that is not defined as NOT NULL. Note that this change applies only to tables using the NDB storage engine. Unique indexes on columns in NDB tables do not store null values because they are mapped to primary keys in an internal index table (and primary keys cannot contain nulls). Normally, an additional ordered index is created when one creates unique indexes on NDB table columns; this can be used to search for NULL values. However, if USING HASH is specified when such an index is created, no ordered index is created. The reason for permitting unique hash indexes with null values is that, in some cases, the user wants to save space if a large number of records are pre-allocated but not fully initialized. This also assumes that the user will not try to search for null values. Since MySQL does not support indexes that are not allowed to be searched in some cases, the NDB storage engine uses a full table scan with pushed conditions for the referenced index columns to return the correct result. Note that a warning is returned if one creates a unique nullable hash index, since the query optimizer should be provided a hint not to use it with NULL values if this can be avoided. (Bug#21507: http://bugs.mysql.com/21507) * DROP TRIGGER now supports an IF EXISTS clause. (Bug#23703: http://bugs.mysql.com/23703) * The Com_create_user status variable was added (for counting CREATE USER statements). (Bug#22958: http://bugs.mysql.com/22958) * The --memlock option relies on system calls that are unreliable on some operating systems. If a crash occurs, the server now checks whether --memlock was specified and if so issues some information about possible workarounds. (Bug#22860: http://bugs.mysql.com/22860) * The bundled yaSSL library was upgraded to version 1.5.0. * If the user specified the server options --max-connections=N or --table-open-cache=M, a warning would be given in some cases that some values were recalculated, with the result that --table-open-cache could be assigned greater value. It should be noted that, in such cases, both the warning and the increase in the --table-open-cache value were completely harmless. Note also that it is not possible for the MySQL Server to predict or to control limitations on the maximum number of open files, since this is determined by the operating system. The recalculation code has now been fixed to ensure that the value of --table-open-cache is no longer increased automatically, and that a warning is now given only if some values had to be decreased due to operating system limits. (Bug#21915: http://bugs.mysql.com/21915) * NDB Cluster: The HELP command in the Cluster management client now provides command-specific help. For example, HELP RESTART in ndb_mgm provides detailed information about the START command. (Bug#19620: http://bugs.mysql.com/19620) * NDB Cluster: Added the --bind-address option for ndbd. This allows a data node process to be bound to a specific network interface. (Bug#22195: http://bugs.mysql.com/22195) * NDB Cluster: The Ndb_number_of_storage_nodes system variable was renamed to Ndb_number_of_data_nodes. (Bug#20848: http://bugs.mysql.com/20848) * NDB Cluster: The ndb_config utility now accepts -c as a short form of the --ndb-connectstring option. (Bug#22295: http://bugs.mysql.com/22295) * SHOW STATUS is no longer logged to the slow query log. (Bug#19764: http://bugs.mysql.com/19764) * mysqldump --single-transaction now uses START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */ rather than
Re: Strange query.
Oh, sorry. I set up a test table and then to send the query to the list, I changed the table names and column names to match yours...but I missed some. I think this one will work. SELECT SUM(CASE when e.sid is null then 0 else 1 end) as counts, HOURS.hour FROM HOURS LEFT OUTER JOIN (SELECT sid, date_format(timestamp, '%H')as hr FROM event) as e ON HOURS.hour =e.hr WHERE timestamp between '2007-01-10 04:00:00' and '2007-01-11 04:00:00' AND sid=1 group by HOURS.hour Donna "Paul Halliday" <[EMAIL PROTECTED]> 01/10/2007 10:36 AM To "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> cc Subject Re: Strange query. e.c1? Giving me errors.. On 1/10/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > Try something like this: > > SELECT > SUM(CASE when e.c1 is null then 0 else 1 end) as counts, HOURS.hour > FROM HOUR > LEFT OUTER JOIN (SELET sid, date_format(timestamp, '%H')as hr FROM > event) as e on HOURS.hour =e.hr > WHERE timestamp between '2007-01-10 04:00:00' and '2007-01-11 > 04:00:00' AND sid=1 > group by HOURS.hour > > Donna > > > > "Paul Halliday" <[EMAIL PROTECTED]> > 01/10/2007 09:48 AM > > To > "Brent Baisley" <[EMAIL PROTECTED]> > cc > mysql@lists.mysql.com > Subject > Re: Strange query. > > > > > > > That query doesn't return empty values. Just to clarify what I want as > the result: > > My initial query was this, > > mysql> select count(*),DATE_FORMAT(timestamp,'%H') AS hour FROM event > WHERE timestamp between '2007-01-10 04:00:00' and '2007-01-11 > 04:00:00' AND sid=1 GROUP BY hour; > +--+--+ > | count(*) | hour | > +--+--+ > |4 | 04 | > |5 | 06 | > |5 | 07 | > |1 | 08 | > |7 | 09 | > | 12 | 10 | > | 73 | 12 | > | 31 | 13 | > | 50 | 14 | > +--+--+ > 9 rows in set (0.03 sec) > > What I am looking for is 0's for every empty result and up to the end > of the day. > > Thanks. > > On 1/10/07, Brent Baisley <[EMAIL PROTECTED]> wrote: > > You can't join on the result of calculations in the field selection. The > result is not associated with any table. So the problem > > isn't so much with the date_format statement, but that you are joining > on HOURS.hour the timestamp, not HOURS.hour the DATE_FORMAT. > > I would think you would be getting an error when you run your SELECT. > > Your group by can use the result of a calculation. So you may actually > have two problems, since you are grouping on HOURS.hour, the > > timestamp, the 'hour' the alias name for the calculation result. > > I'm not sure why you don't just pull the hour from the timestamp either. > > > > SELECT COUNT(*), HOUR(timestamp) AS hour FOM HOURS > > LEFT JOIN event ON HOURS.hour=HOUR(timestamp) > > WHERE timestamp BETWEEN '2007-01-09 04:00:00' > > AND '2007-01-10 04:00:00' AND sid=2 GROUP BY hour > > > > - Original Message - > > From: "Paul Halliday" <[EMAIL PROTECTED]> > > To: > > Sent: Wednesday, January 10, 2007 8:39 AM > > Subject: Strange query. > > > > > > > Hi, > > > > > > I am trying to deal with empty values so that I can graph data over a > > > 24 hour period without gaps. > > > > > > I created a table called HOURS which simply has 0->23 and I am trying > > > to do a join on this to produce the desired results. I think that the > > > DATE_FORMAT in the query is screwing things up. > > > > > > The query looks something like this: > > > > > > SELECT COUNT(*),DATE_FORMAT(timestamp,'%H') AS hour FROM HOURS LEFT > > > JOIN event ON HOURS.hour=hour WHERE timestamp BETWEEN '2007-01-09 > > > 04:00:00' AND '2007-01-10 04:00:00' AND sid=2 GROUP BY HOURS.hour; > > > > > > Any help would be appreciated. > > > > > > Thanks. > > > > > > -- > > > 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] > > > -- > This message has been scanned for viruses and > dangerous content by MailScanner, and is > believed to be clean. > > > > CONFIDENTIALITY NOTICE:This email is intended solely for the person or > entity to which it is addressed and may contain confidential and/or > protected health information. Any duplication, dissemination, action > taken in reliance upon, or other use of this information by persons or > entities other than the intended recipient is prohibited and may violate > applicable laws. If this email has been received in error, please notify > the sender and delete the information from your system. The views > expressed in this email are those of the sender and may not necessarily > represent the views of IntelliCare. > > > -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. CONFIDENTIALITY NOTICE:This email is intended solely for the person or entity to which it is
Re: automated character set conversions for tables
I did a DB conversion before that with "ALTER DATABASE db_name CHARACTER SET utf8" That worked wonderfully, except not as expected. ;-) It basically converted only the database itself. so I had to do a separate "ALTER TABLE ..." for each table. The database encoding more establishes the default to use when creating new tables. As far as adjusting every single table, you can work with your Favorite Scripting Program (tm) and run the query: `SHOW TABLES` to get a list of all tables for that database (the column you want is called Tables_in_[database name here]), which you can get the exact column by running it in console or your Favorite SQL Program (tm). Then simply loop over the result set and run the alter table command on each table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange query.
Try something like this: SELECT SUM(CASE when e.c1 is null then 0 else 1 end) as counts, HOURS.hour FROM HOUR LEFT OUTER JOIN (SELET sid, date_format(timestamp, '%H')as hr FROM event) as e on HOURS.hour =e.hr WHERE timestamp between '2007-01-10 04:00:00' and '2007-01-11 04:00:00' AND sid=1 group by HOURS.hour Donna "Paul Halliday" <[EMAIL PROTECTED]> 01/10/2007 09:48 AM To "Brent Baisley" <[EMAIL PROTECTED]> cc mysql@lists.mysql.com Subject Re: Strange query. That query doesn't return empty values. Just to clarify what I want as the result: My initial query was this, mysql> select count(*),DATE_FORMAT(timestamp,'%H') AS hour FROM event WHERE timestamp between '2007-01-10 04:00:00' and '2007-01-11 04:00:00' AND sid=1 GROUP BY hour; +--+--+ | count(*) | hour | +--+--+ |4 | 04 | |5 | 06 | |5 | 07 | |1 | 08 | |7 | 09 | | 12 | 10 | | 73 | 12 | | 31 | 13 | | 50 | 14 | +--+--+ 9 rows in set (0.03 sec) What I am looking for is 0's for every empty result and up to the end of the day. Thanks. On 1/10/07, Brent Baisley <[EMAIL PROTECTED]> wrote: > You can't join on the result of calculations in the field selection. The result is not associated with any table. So the problem > isn't so much with the date_format statement, but that you are joining on HOURS.hour the timestamp, not HOURS.hour the DATE_FORMAT. > I would think you would be getting an error when you run your SELECT. > Your group by can use the result of a calculation. So you may actually have two problems, since you are grouping on HOURS.hour, the > timestamp, the 'hour' the alias name for the calculation result. > I'm not sure why you don't just pull the hour from the timestamp either. > > SELECT COUNT(*), HOUR(timestamp) AS hour FOM HOURS > LEFT JOIN event ON HOURS.hour=HOUR(timestamp) > WHERE timestamp BETWEEN '2007-01-09 04:00:00' > AND '2007-01-10 04:00:00' AND sid=2 GROUP BY hour > > - Original Message - > From: "Paul Halliday" <[EMAIL PROTECTED]> > To: > Sent: Wednesday, January 10, 2007 8:39 AM > Subject: Strange query. > > > > Hi, > > > > I am trying to deal with empty values so that I can graph data over a > > 24 hour period without gaps. > > > > I created a table called HOURS which simply has 0->23 and I am trying > > to do a join on this to produce the desired results. I think that the > > DATE_FORMAT in the query is screwing things up. > > > > The query looks something like this: > > > > SELECT COUNT(*),DATE_FORMAT(timestamp,'%H') AS hour FROM HOURS LEFT > > JOIN event ON HOURS.hour=hour WHERE timestamp BETWEEN '2007-01-09 > > 04:00:00' AND '2007-01-10 04:00:00' AND sid=2 GROUP BY HOURS.hour; > > > > Any help would be appreciated. > > > > Thanks. > > > > -- > > 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] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. CONFIDENTIALITY NOTICE:This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or protected health information. Any duplication, dissemination, action taken in reliance upon, or other use of this information by persons or entities other than the intended recipient is prohibited and may violate applicable laws. If this email has been received in error, please notify the sender and delete the information from your system. The views expressed in this email are those of the sender and may not necessarily represent the views of IntelliCare.
Re: Strange query.
Ugh. My perl isn't quite right there. Here's a bit better (e.g.
working) example:
If you create the table, then put this in "populate_hours.pl":
BEGIN
#!/usr/bin/perl
$counter = 0;
while ($counter < 100) {
print "INSERT INTO all_hours (date_hour) VALUES (
DATE_ADD('2000-01-01 00:00:00', INTERVAL $counter HOUR) );\n";
$counter++;
}
END
then run
./populate_hours.pl | mysql -h host -u user -ppassword -D database
you'll have a table full of hours.
Dan
On 1/10/07, Dan Buettner <[EMAIL PROTECTED]> wrote:
One of the most puzzling and challenging things to do with SQL can be
to show what's NOT there, as you're trying to do.
Many people opt to do such a report in their favorite scripting
language for this reason, as one can easily increment timestamps by a
given amount and re-do the query. Can be resource intensive to re-do
the queries for each hour or whatever, but it's often pretty easy.
Another option is to create a table used specifically for joining to
get units of time with no corresponding entries in the other table.
You could create a table like so:
CREATE TABLE all_hours (
date_hour DATETIME,
KEY (date_hour) );
then populate it like so, with perl:
$counter = 0;
while $counter < 100 {
print "INSERT INTO all_hours (date_hour) VALUES (
DATE_ADD("2000-01-01 00:00:00", INTERVAL $counter HOUR) );"
$counter++;
}
Then you can join on that table.
A million hour entries would be good for 114 years or so. Fewer would
likely give somewhat better performance.
Dan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange query.
One of the most puzzling and challenging things to do with SQL can be
to show what's NOT there, as you're trying to do.
Many people opt to do such a report in their favorite scripting
language for this reason, as one can easily increment timestamps by a
given amount and re-do the query. Can be resource intensive to re-do
the queries for each hour or whatever, but it's often pretty easy.
Another option is to create a table used specifically for joining to
get units of time with no corresponding entries in the other table.
You could create a table like so:
CREATE TABLE all_hours (
date_hour DATETIME,
KEY (date_hour) );
then populate it like so, with perl:
$counter = 0;
while $counter < 100 {
print "INSERT INTO all_hours (date_hour) VALUES (
DATE_ADD("2000-01-01 00:00:00", INTERVAL $counter HOUR) );"
$counter++;
}
Then you can join on that table.
A million hour entries would be good for 114 years or so. Fewer would
likely give somewhat better performance.
Dan
On 1/10/07, Paul Halliday <[EMAIL PROTECTED]> wrote:
That query doesn't return empty values. Just to clarify what I want as
the result:
My initial query was this,
mysql> select count(*),DATE_FORMAT(timestamp,'%H') AS hour FROM event
WHERE timestamp between '2007-01-10 04:00:00' and '2007-01-11
04:00:00' AND sid=1 GROUP BY hour;
+--+--+
| count(*) | hour |
+--+--+
|4 | 04 |
|5 | 06 |
|5 | 07 |
|1 | 08 |
|7 | 09 |
| 12 | 10 |
| 73 | 12 |
| 31 | 13 |
| 50 | 14 |
+--+--+
9 rows in set (0.03 sec)
What I am looking for is 0's for every empty result and up to the end
of the day.
Thanks.
On 1/10/07, Brent Baisley <[EMAIL PROTECTED]> wrote:
> You can't join on the result of calculations in the field selection. The
result is not associated with any table. So the problem
> isn't so much with the date_format statement, but that you are joining on
HOURS.hour the timestamp, not HOURS.hour the DATE_FORMAT.
> I would think you would be getting an error when you run your SELECT.
> Your group by can use the result of a calculation. So you may actually have
two problems, since you are grouping on HOURS.hour, the
> timestamp, the 'hour' the alias name for the calculation result.
> I'm not sure why you don't just pull the hour from the timestamp either.
>
> SELECT COUNT(*), HOUR(timestamp) AS hour FOM HOURS
> LEFT JOIN event ON HOURS.hour=HOUR(timestamp)
> WHERE timestamp BETWEEN '2007-01-09 04:00:00'
> AND '2007-01-10 04:00:00' AND sid=2 GROUP BY hour
>
> - Original Message -
> From: "Paul Halliday" <[EMAIL PROTECTED]>
> To:
> Sent: Wednesday, January 10, 2007 8:39 AM
> Subject: Strange query.
>
>
> > Hi,
> >
> > I am trying to deal with empty values so that I can graph data over a
> > 24 hour period without gaps.
> >
> > I created a table called HOURS which simply has 0->23 and I am trying
> > to do a join on this to produce the desired results. I think that the
> > DATE_FORMAT in the query is screwing things up.
> >
> > The query looks something like this:
> >
> > SELECT COUNT(*),DATE_FORMAT(timestamp,'%H') AS hour FROM HOURS LEFT
> > JOIN event ON HOURS.hour=hour WHERE timestamp BETWEEN '2007-01-09
> > 04:00:00' AND '2007-01-10 04:00:00' AND sid=2 GROUP BY HOURS.hour;
> >
> > Any help would be appreciated.
> >
> > Thanks.
> >
> > --
> > 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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange query.
That query doesn't return empty values. Just to clarify what I want as the result: My initial query was this, mysql> select count(*),DATE_FORMAT(timestamp,'%H') AS hour FROM event WHERE timestamp between '2007-01-10 04:00:00' and '2007-01-11 04:00:00' AND sid=1 GROUP BY hour; +--+--+ | count(*) | hour | +--+--+ |4 | 04 | |5 | 06 | |5 | 07 | |1 | 08 | |7 | 09 | | 12 | 10 | | 73 | 12 | | 31 | 13 | | 50 | 14 | +--+--+ 9 rows in set (0.03 sec) What I am looking for is 0's for every empty result and up to the end of the day. Thanks. On 1/10/07, Brent Baisley <[EMAIL PROTECTED]> wrote: You can't join on the result of calculations in the field selection. The result is not associated with any table. So the problem isn't so much with the date_format statement, but that you are joining on HOURS.hour the timestamp, not HOURS.hour the DATE_FORMAT. I would think you would be getting an error when you run your SELECT. Your group by can use the result of a calculation. So you may actually have two problems, since you are grouping on HOURS.hour, the timestamp, the 'hour' the alias name for the calculation result. I'm not sure why you don't just pull the hour from the timestamp either. SELECT COUNT(*), HOUR(timestamp) AS hour FOM HOURS LEFT JOIN event ON HOURS.hour=HOUR(timestamp) WHERE timestamp BETWEEN '2007-01-09 04:00:00' AND '2007-01-10 04:00:00' AND sid=2 GROUP BY hour - Original Message - From: "Paul Halliday" <[EMAIL PROTECTED]> To: Sent: Wednesday, January 10, 2007 8:39 AM Subject: Strange query. > Hi, > > I am trying to deal with empty values so that I can graph data over a > 24 hour period without gaps. > > I created a table called HOURS which simply has 0->23 and I am trying > to do a join on this to produce the desired results. I think that the > DATE_FORMAT in the query is screwing things up. > > The query looks something like this: > > SELECT COUNT(*),DATE_FORMAT(timestamp,'%H') AS hour FROM HOURS LEFT > JOIN event ON HOURS.hour=hour WHERE timestamp BETWEEN '2007-01-09 > 04:00:00' AND '2007-01-10 04:00:00' AND sid=2 GROUP BY HOURS.hour; > > Any help would be appreciated. > > Thanks. > > -- > 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]
Re: Strange query.
You can't join on the result of calculations in the field selection. The result is not associated with any table. So the problem isn't so much with the date_format statement, but that you are joining on HOURS.hour the timestamp, not HOURS.hour the DATE_FORMAT. I would think you would be getting an error when you run your SELECT. Your group by can use the result of a calculation. So you may actually have two problems, since you are grouping on HOURS.hour, the timestamp, the 'hour' the alias name for the calculation result. I'm not sure why you don't just pull the hour from the timestamp either. SELECT COUNT(*), HOUR(timestamp) AS hour FOM HOURS LEFT JOIN event ON HOURS.hour=HOUR(timestamp) WHERE timestamp BETWEEN '2007-01-09 04:00:00' AND '2007-01-10 04:00:00' AND sid=2 GROUP BY hour - Original Message - From: "Paul Halliday" <[EMAIL PROTECTED]> To: Sent: Wednesday, January 10, 2007 8:39 AM Subject: Strange query. Hi, I am trying to deal with empty values so that I can graph data over a 24 hour period without gaps. I created a table called HOURS which simply has 0->23 and I am trying to do a join on this to produce the desired results. I think that the DATE_FORMAT in the query is screwing things up. The query looks something like this: SELECT COUNT(*),DATE_FORMAT(timestamp,'%H') AS hour FROM HOURS LEFT JOIN event ON HOURS.hour=hour WHERE timestamp BETWEEN '2007-01-09 04:00:00' AND '2007-01-10 04:00:00' AND sid=2 GROUP BY HOURS.hour; Any help would be appreciated. Thanks. -- 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]
Re: Date v. DateTime index performance
Splitting out your values will cause problems where doing greater than/less than searching. If you search on year_number>=2000 and month_number>=6, that's not going to give you everything from 6/2000 on. It will return really only the second half of each year from 2000 on. To include 2/2002, you'll need to add an OR statement, which will slow things down. If you want to search on just year and month for a date field, just add the first day of the month. If you want an entire month, search on >= first day of the month and < the first day of the next month. That will use an index. - Original Message - From: "Anders Lundgren" <[EMAIL PROTECTED]> To: "Dan Buettner" <[EMAIL PROTECTED]> Cc: "Thomas Bolioli" <[EMAIL PROTECTED]>; Sent: Tuesday, January 09, 2007 8:34 PM Subject: Re: Date v. DateTime index performance > One potential solution might be to use an extra column that tracks > month_number, and populate it with a trigger on insert or update. > Index that field and then use it in your WHERE clause. One > possibility anyway. Resulting question, what if I have three colums named year_number, month_number and day_number. How should I create the keys on these columns? I. (year_number, month_number, day_number) - or - II. (year_number) (month_number) (day_number) If I create the key as of I. above and in the Where clause I just compare year and month, can the index still be used? Thanks, Anders Dan Buettner wrote: Thomas, I do not think in this case that one is better than the other, for the most part, because both require using a value computed from the column. Computing month from a DATE field should be just as fast as computing from a DATETIME column I would think. Also splitting into DATE and TIME columns can make your SQL a bit trickier depending on your needs. That being said, one difference that might come up in extreme cases is that the size of an index on a DATE column will be smaller than on a DATETIME (fewer unique values, less cardinality) so if you have a lot of records you might be able to keep all or more of the index in memory. One potential solution might be to use an extra column that tracks month_number, and populate it with a trigger on insert or update. Index that field and then use it in your WHERE clause. One possibility anyway. HTH, Dan On 12/4/06, Thomas Bolioli <[EMAIL PROTECTED]> wrote: If one has a large number of records per month and normally searches for things by month, yet needs to keep things time coded, does anyone know if it make sense to use datetime or separate date and a time columns? Thanks, Tom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Anders Lundgren Viba IT Handelsbolag Webb: http://www.vibait.se E-post: [EMAIL PROTECTED] Mobil: 070-55 99 589 -- 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]
Strange query.
Hi, I am trying to deal with empty values so that I can graph data over a 24 hour period without gaps. I created a table called HOURS which simply has 0->23 and I am trying to do a join on this to produce the desired results. I think that the DATE_FORMAT in the query is screwing things up. The query looks something like this: SELECT COUNT(*),DATE_FORMAT(timestamp,'%H') AS hour FROM HOURS LEFT JOIN event ON HOURS.hour=hour WHERE timestamp BETWEEN '2007-01-09 04:00:00' AND '2007-01-10 04:00:00' AND sid=2 GROUP BY HOURS.hour; Any help would be appreciated. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Enum issue
Hi Olaf, all !
Olaf Stein wrote:
Hi All
If I have a column
`consent` enum('Y','N','P') default NULL,
And I try to insert 'NULL' I get this error:
Warning: Data truncated for column 'consent' at row 1
What is the problem there?
Double-check your command:
'NULL' is a string of four (4) letters, NULL is a keyword denoting the
unknown value.
I suspect MySQL receives a character string of 'N', 'U', 'L', 'L' (in
one string) and truncates this to the first character, because this
matches the column definition.
What I am doing is moving data from one table to another with a python
script so I have to assign 'NULL' to the variable in the insert string (at
least to my knowledge) because python retrieves "None" (type ) when querying a NULL value.
I cannot comment on that.
HTH,
Jörg
--
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date v. DateTime index performance
OK, thank you. How is the speed of this index compared with an indexed date column if I do: year_number='x' and month_number='y' and day_number='z'; They should have about the same cardinality, right? Thanks, Anders Chris wrote: Anders Lundgren wrote: > One potential solution might be to use an extra column that tracks > month_number, and populate it with a trigger on insert or update. > Index that field and then use it in your WHERE clause. One > possibility anyway. Resulting question, what if I have three colums named year_number, month_number and day_number. How should I create the keys on these columns? I. (year_number, month_number, day_number) - or - II. (year_number) (month_number) (day_number) If I create the key as of I. above and in the Where clause I just compare year and month, can the index still be used? Depends on your queries. If your clause is: year_number='x' and month_number='y' and day_number='z'; then create the index as #1. If your query is in a different order (month first for example), adjust the index accordingly. Multiple key indexes go left to right, so if the index is (year_number,month_number,day_number) then queries using year_number='a' and month_number='b' will be able to use that index. But year_number='a' and day_number='b' will only be able to use it for the year_number part, not the other. -- Anders Lundgren Viba IT Handelsbolag Web: http://www.vibait.se E-mail: [EMAIL PROTECTED] Cell: +46 (0)70-55 99 589 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
