RE: [MYSQL]time of elapsed time
Baron, thank you for your response. I did get a different result for the query: 18184639,12544976,0,0,0,0,0,0,0,0,0,0,0,0 But it still is all the data for the ticket in the row. If I group by the ticket (key) number and the status, I get one line per status with the right numbers. 18184639,240,0,0,0,0,0,0,0,0,0,0,0,0 18184639,0,81360,0,0,0,0,0,0,0,0,0,0,0 18184639,0,0,10680,0,0,0,0,0,0,0,0,0,0 18184639,0,0,0,0,0,0,0,0,38280,0,0,0,0 18184639,0,0,0,6480,0,0,0,0,0,0,0,0,0 18184639,0,0,0,0,0,0,0,8040,0,0,0,0,0 18184639,0,0,0,0,0,0,12081596,0,0,0,0,0,0 18184639,0,0,0,0,0,0,0,0,0,139920,0,0,0 18184639,0,0,0,0,16080,0,0,0,0,0,0,0,0 Can you think of some way to get these numbers on to one row? Thanks, Craig This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system. -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Friday, October 19, 2007 8:53 AM To: Weston, Craig (OFT) Cc: mysql@lists.mysql.com Subject: Re: [MYSQL]time of elapsed time Hi Craig, Weston, Craig (OFT) wrote: Hello everyone. Once again, I am jousting at the windmill of time and date formulae within MYSQL. I seek to create a cross-tab or pivot table of the SUM of all times with a specific category, on a per-ticket basis. I have everything working except the math part. Even that is kind of working ok, but it is not adding up The math part is: IF(`status` = 'WIP',SUM(time_to_sec(`CLOCK_TIME`)),0) AS 'WIP' (the same formula with 'CLOSED','OPEN', etc lists all the various statuses available.) CLOCK_TIME is a varchar field that contains a 4 digit date counter and a timer, in the format of 0293 23:44 0001 00:29 0001 19:15 My hunch is this is the problem. You should split the field into two: one for the date counter, one for the time. time_to_secs() is probably returning zero for most of these. Now, I run the query and get results. Every ticket has more than one status. But, for each ticket, I get a single line that appears to have the entire ticket time (in seconds) in one field and the rest are zero. I think this tells me that the statement is working but that I am grouping them wrong? `key` is the ticket number. From `clock_data` group by `clock_data`.`key` So my result set looks like 17881375,0,0,0,0,15235975,0,0,0,0,0,0,0,0 18184639,21362513,0,0,0,0,0,0,0,0,0,0,0,0 Etc. Can anyone help me over this hill? I think I am writing the if statement incorrectly somehow but don't see a way out of the box yet. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow Subquery
Ryan Bates wrote: I'm trying to determine why a subquery is slower than running two separate queries. I have a simple many-to-many association using 3 tables: projects, tags and projects_tags. Here's the query I'm using to find the projects with a given tag: SELECT * FROM projects WHERE id IN (SELECT projects_tags.project_id FROM tags, projects_tags WHERE tags.name='foo' AND projects_tags.project_id=projects.id); (0.36 sec) As another poster said, this kind of subquery runs slowly, but just to elaborate on it: it's not every subquery that's a problem, just IN() and NOT IN(). Use EXPLAIN EXTENDED followed by SHOW WARNINGS to see what's happening. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow Subquery
Ryan, Why is it so much faster? Subquery optimisation in MySQL is a problem. For ideas see 'The unbearable slowness of IN()' at http://www.artfulsoftware.com/infotree/queries.php. PB Ryan Bates wrote: I'm trying to determine why a subquery is slower than running two separate queries. I have a simple many-to-many association using 3 tables: projects, tags and projects_tags. Here's the query I'm using to find the projects with a given tag: SELECT * FROM projects WHERE id IN (SELECT projects_tags.project_id FROM tags, projects_tags WHERE tags.name='foo' AND projects_tags.project_id=projects.id); (0.36 sec) Compare that with splitting it into two queries: SELECT projects_tags.project_id FROM tags, projects_tags WHERE tags.name='foo' AND projects_tags.project_id=projects.id (0.00 sec) /* returns 1, 2, 3 */ SELECT * FROM projects WHERE id IN (1, 2, 3); (0.00 sec) Why is it so much faster? Looking at the explain statement (below) of the one with the subquery, it appears it's not using the primary key index on the projects table. Why is it that MySQL doesn't perform this simple optimization? And is there a solution that will allow me to still use a subquery? I realize I can use a join instead of a subquery, but this is a simplified example. Here's the explain statement: *** 1. row *** id: 1 select_type: PRIMARY table: projects type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 15433 Extra: Using where *** 2. row *** id: 2 select_type: DEPENDENT SUBQUERY table: tags type: ref possible_keys: PRIMARY,index_tags_on_name key: index_tags_on_name key_len: 258 ref: const rows: 1 Extra: Using where; Using index *** 3. row *** id: 2 select_type: DEPENDENT SUBQUERY table: projects_tags type: ref possible_keys: tag_id key: tag_id key_len: 5 ref: my_database.tags.id rows: 10 Extra: Using where Here's the table dumps: CREATE TABLE `projects` ( `id` int(11) NOT NULL auto_increment, `name` varchar(255) default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `tags` ( `id` int(11) NOT NULL auto_increment, `name` varchar(255) default NULL, PRIMARY KEY (`id`), KEY `index_tags_on_name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `projects_tags` ( `project_id` int(11) default NULL, `tag_id` int(11) default NULL, KEY `tag_id` (`tag_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; I'm using MySQL 5.0.37. Thanks in advance. Ryan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
german datetime format?
Hi, can i change the datetime format on mysql in a german format? At the moment the dates are stored like 2007-10-19 19:06:17 but if i send a query (which comes from user input) the query looks like WHERE Datum = '19.10.2007' and i got not what i want. iam using mysql (5.0.45) on (german) windows 2000, clients are connected via ODBC-Driver (3.51.21.00) and the application is using ADODB (mdac-lastest version). thanks Ralf -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need ideas on handling aliases and a.k.a.s
I'm trying to wrap my head around dealing with people in a table that have multiple names or akas. I run an entertainment news site and have to deal with people like Pamela Anderson (who was once Pamela Lee, Pamela Anderson Lee, Pamela Denise Anderson), Eva Longoria (who's now Eva Longoria Parker) and Courteney Cox, who's Courteney Cox Arquette. I haven't really dealt with this yet, but I guess now I better handle it before I get stung too badly. Right now I have a people table that has: PeopleID First Middle Last Display URL So as an example you'd have: PeopleID: 1078 First: Eva Middle: Last: Longoria Display: Eva Longoria URL: evalongoria It's worked well for me. I have a peopleinphotos table...add Eva to a photo caption and it's just a matter of grabbing her id number (1078) and putting it in the table with the photoid #. She gets nominated, the input form looks up her id# and adds it to the nomination table. I've been lucky in that most entertainers keep their public and personal names separate. But suddenly Eva wants her credits to read Eva Longoria Parker. Sure I can add Parker to the Last field and remember to always use Longoria Parker when I input new info, but what happens if she gets divorced? Just wondering how some of you have handled akas/aliases/divorces for things like customer databases. How do you ensure that a name change doesn't actually cause a brand new record for the person if the data entry person uses the old name, etc. Thanks for any advice. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: german datetime format?
Ralf Hüsing wrote: Hi, can i change the datetime format on mysql in a german format? At the moment the dates are stored like 2007-10-19 19:06:17 but if i send a query (which comes from user input) the query looks like WHERE Datum = '19.10.2007' and i got not what i want. iam using mysql (5.0.45) on (german) windows 2000, clients are connected via ODBC-Driver (3.51.21.00) and the application is using ADODB (mdac-lastest version). Try converting the user input to the correct type with STR_TO_DATE(), which despite its name can return a DATETIME value. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need ideas on handling aliases and a.k.a.s
Ian M. Evans wrote: I'm trying to wrap my head around dealing with people in a table that have multiple names or akas. I run an entertainment news site and have to deal with people like Pamela Anderson (who was once Pamela Lee, Pamela Anderson Lee, Pamela Denise Anderson), Eva Longoria (who's now Eva Longoria Parker) and Courteney Cox, who's Courteney Cox Arquette. I haven't really dealt with this yet, but I guess now I better handle it before I get stung too badly. Right now I have a people table that has: PeopleID First Middle Last Display URL So as an example you'd have: PeopleID: 1078 First: Eva Middle: Last: Longoria Display: Eva Longoria URL: evalongoria It's worked well for me. I have a peopleinphotos table...add Eva to a photo caption and it's just a matter of grabbing her id number (1078) and putting it in the table with the photoid #. She gets nominated, the input form looks up her id# and adds it to the nomination table. I've been lucky in that most entertainers keep their public and personal names separate. But suddenly Eva wants her credits to read Eva Longoria Parker. Sure I can add Parker to the Last field and remember to always use Longoria Parker when I input new info, but what happens if she gets divorced? Just wondering how some of you have handled akas/aliases/divorces for things like customer databases. How do you ensure that a name change doesn't actually cause a brand new record for the person if the data entry person uses the old name, etc. Thanks for any advice. Only use the id (primary key); the name should be treated as if it's arbitrary. I mean, what would you do in your setup if you had two or more people with the same name? So you can't rely on just the name. Obviously, remembering the IDs for all of these people is out of the question, so you should create a select list of the names with the IDs as the option values. IIUC, you're submitting a name and either getting a form with that person's info, or an empty form to enter a new record. If that's correct, you risk creating new records because of a misspelling. This is why i always have a select list of countries, for instance, because there are so many different ways that someone might fsck up the spelling of a country. It sees like you have a similar situation. So, you select the name you want from the list and, when your form comes up with Ms Longoria's (who the heck is that?) info, you can alter the name fields but the main identifying item--the ID--is just a hidden field. Then the only thing you have to worry about with these name changes is locating them in the select list. Of course, you should also be doing some kind of search on *new* names, just in case the person is in there under a similar name (eg. 'Eva Longoria' - 'Eva Longoria Parker'). But maybe i didn't grok precisely what is you need to do. As for AKAs, you can create an aka table that lists these with foreign keys pointing back to your people IDs. Thinking about it for all of 30 seconds, i'd guess that the easiest thing to do would be to make the name a single field for this table, then add that table to your query using fuzzy search (with the field having a FULL TEXT index). brian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slow Subquery
I'm trying to determine why a subquery is slower than running two separate queries. I have a simple many-to-many association using 3 tables: projects, tags and projects_tags. Here's the query I'm using to find the projects with a given tag: SELECT * FROM projects WHERE id IN (SELECT projects_tags.project_id FROM tags, projects_tags WHERE tags.name='foo' AND projects_tags.project_id=projects.id); (0.36 sec) Compare that with splitting it into two queries: SELECT projects_tags.project_id FROM tags, projects_tags WHERE tags.name='foo' AND projects_tags.project_id=projects.id (0.00 sec) /* returns 1, 2, 3 */ SELECT * FROM projects WHERE id IN (1, 2, 3); (0.00 sec) Why is it so much faster? Looking at the explain statement (below) of the one with the subquery, it appears it's not using the primary key index on the projects table. Why is it that MySQL doesn't perform this simple optimization? And is there a solution that will allow me to still use a subquery? I realize I can use a join instead of a subquery, but this is a simplified example. Here's the explain statement: *** 1. row *** id: 1 select_type: PRIMARY table: projects type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 15433 Extra: Using where *** 2. row *** id: 2 select_type: DEPENDENT SUBQUERY table: tags type: ref possible_keys: PRIMARY,index_tags_on_name key: index_tags_on_name key_len: 258 ref: const rows: 1 Extra: Using where; Using index *** 3. row *** id: 2 select_type: DEPENDENT SUBQUERY table: projects_tags type: ref possible_keys: tag_id key: tag_id key_len: 5 ref: my_database.tags.id rows: 10 Extra: Using where Here's the table dumps: CREATE TABLE `projects` ( `id` int(11) NOT NULL auto_increment, `name` varchar(255) default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `tags` ( `id` int(11) NOT NULL auto_increment, `name` varchar(255) default NULL, PRIMARY KEY (`id`), KEY `index_tags_on_name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `projects_tags` ( `project_id` int(11) default NULL, `tag_id` int(11) default NULL, KEY `tag_id` (`tag_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; I'm using MySQL 5.0.37. Thanks in advance. Ryan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: server optimization
Can you send us a copy of your my.cnf at the moment. Also, if you can provide some reports that will help us determine the best functionality, that would also help a lot. It is useful to know what is getting read/writing (or which function is being requested the most...). Table format is also important, as you indicated... At the MySQL command line run: show status; Copy/pipe output to a text file for us to view... Also it would be helpful: show innodb status; show table status; Thank you! On 10/18/07, Jeff Mckeon [EMAIL PROTECTED] wrote: Hey all, I've got a new server set up, with dual Intel quad core processors, 4 gig of ram, OpenSuse 10.3 (64bit) and MySql 5.0.45. The majority of the tables are MyISAM with a few InnoDB here or there. I'm using the huge-my.cnf as the base for my config. Can anyone suggest some tweeking to the my.conf that will give me the best performance on this platform? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: [MYSQL]time of elapsed time
Weston, Craig (OFT) wrote: There were 2 changes - First was IF(`status` = 'WIP',SUM(time_to_sec(RIGHT(`CLOCK_TIME`),8)),0) AS 'WIP' ( I addded the RIGHT limit on the string) And second was grouping by KEY,STATUS Further experimentation makes it appear that I am getting the right number of seconds for the entire string, so I am playing without the RIGHT() modifier right now. Maybe making a table with the status's on separate rows and then querying against that to make the single line?? Changes to what? Did i miss the post where you included the entire SELECT statement? (please don't top-post) brian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: [MYSQL]time of elapsed time
There were 2 changes - First was IF(`status` = 'WIP',SUM(time_to_sec(RIGHT(`CLOCK_TIME`),8)),0) AS 'WIP' ( I addded the RIGHT limit on the string) And second was grouping by KEY,STATUS Further experimentation makes it appear that I am getting the right number of seconds for the entire string, so I am playing without the RIGHT() modifier right now. Maybe making a table with the status's on separate rows and then querying against that to make the single line?? This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, October 19, 2007 12:02 PM To: mysql@lists.mysql.com Subject: Re: [MYSQL]time of elapsed time Weston, Craig (OFT) wrote: Baron, thank you for your response. I did get a different result for the query: 18184639,12544976,0,0,0,0,0,0,0,0,0,0,0,0 But it still is all the data for the ticket in the row. If I group by the ticket (key) number and the status, I get one line per status with the right numbers. 18184639,240,0,0,0,0,0,0,0,0,0,0,0,0 18184639,0,81360,0,0,0,0,0,0,0,0,0,0,0 18184639,0,0,10680,0,0,0,0,0,0,0,0,0,0 18184639,0,0,0,0,0,0,0,0,38280,0,0,0,0 18184639,0,0,0,6480,0,0,0,0,0,0,0,0,0 18184639,0,0,0,0,0,0,0,8040,0,0,0,0,0 18184639,0,0,0,0,0,0,12081596,0,0,0,0,0,0 18184639,0,0,0,0,0,0,0,0,0,139920,0,0,0 18184639,0,0,0,0,16080,0,0,0,0,0,0,0,0 Can you think of some way to get these numbers on to one row? What is the query used for that result? brian -- 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: [MYSQL]time of elapsed time
Weston, Craig (OFT) wrote: Baron, thank you for your response. I did get a different result for the query: 18184639,12544976,0,0,0,0,0,0,0,0,0,0,0,0 But it still is all the data for the ticket in the row. If I group by the ticket (key) number and the status, I get one line per status with the right numbers. 18184639,240,0,0,0,0,0,0,0,0,0,0,0,0 18184639,0,81360,0,0,0,0,0,0,0,0,0,0,0 18184639,0,0,10680,0,0,0,0,0,0,0,0,0,0 18184639,0,0,0,0,0,0,0,0,38280,0,0,0,0 18184639,0,0,0,6480,0,0,0,0,0,0,0,0,0 18184639,0,0,0,0,0,0,0,8040,0,0,0,0,0 18184639,0,0,0,0,0,0,12081596,0,0,0,0,0,0 18184639,0,0,0,0,0,0,0,0,0,139920,0,0,0 18184639,0,0,0,0,16080,0,0,0,0,0,0,0,0 Can you think of some way to get these numbers on to one row? What is the query used for that result? brian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[ANN] New PBXT/MyBS release enables JDBC-based BLOB streaming!
Hi All, I have just released PBXT 0.9.90 and MyBS version 0.5.03. PBXT is a transactional storage engine for MySQL (http://www.primebase.com/xt), and MyBS is a storage engine that enables streaming of BLOB data directly in and out of a MySQL database (http://www.blobstreaming.org) . For this release I have completed changes to the MySQL Connector/J 5.0.7, to allow BLOB data to be transparently stored and retrieved from the MyBS BLOB repository. The new version of the driver is called MySQL Connector/J SE (streaming enabled). Uploading a BLOB is as simple as using setBinaryStream() or setBlob() on INSERT. By using getBinaryStream() or getBlob() after a SELECT you get direct access to the data stream coming from the repository. More information and some examples are provided in the documentation at: http://www.blobstreaming.org/documentation. To try this out you need to install the latest versions of PBXT and MyBS. Both are available from: http://www.blobstreaming.org/download. Binary versions of the storage engines are also available for MySQL 5.1.22 running on 32-bit Linux and x86 Mac OS X. The modified version of the JDBC source code is included in the MyBS source code distribution, but the driver can also be downloaded here: http:// www.blobstreaming.org/download/mysql-connector-java-5.0.7se-bin.jar. I have included a small test program, TestJDBC.java, as part of the JDBC driver. So once you have installed the engines, you can test BLOB streaming as follows: java -cp mysql-connector-java-5.0.7se-bin.jar TestJDBC TestJDBC connects to a local MySQL server, creates a PBXT table and tests INSERT and SELECT of rows containing BLOBs. The program also serves as an example of how to do BLOB streaming with JDBC. To get started quickly, the most important things to note are: * Set EnableBlobStreaming=true in your JDBC connection URL. * Streamable BLOBs can only be stored in LONGBLOB columns in PBXT tables. * Use setBinaryStream(), setAsciiStream or setBlob() and specify the length to upload a BLOB. As usual, any comments, questions or bug reports can be sent directly to me: paul-dot-mccullagh-at-primebase-dot-com. Make sure you put the word PBXT or MyBS in the e-mail title to make it through my spam filter! :) Best regards, Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: importing a single table from mysqldump
A little bit easier of a way to do this could be the following command: mysql -u[user] -h[host] -p [database] [mysql dump file] Make sure the database you're importing into is EMPTY (or at least the TABLES you are importing to are empty...) On 10/19/07, Werner Van Belle [EMAIL PROTECTED] wrote: Hello, If it is a dump you can pipe it into mysql. If you have a csv like file you can import it with LOAD DATA LOCAL INFILE like things. An example below: DROP TABLE IF EXISTS EnsgDescriptions; CREATE TABLE IF NOT EXISTS EnsgDescriptions (stable_id VARCHAR(128) PRIMARY KEY, description VARCHAR(128)); LOAD DATA LOCAL INFILE 'imports/ensgdescriptions.csv' INTO TABLE EnsgDescriptions; Wkr (don't write this in your script :-), -- Dr. Werner Van Belle http://werner.sigtrans.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
a scheme for the future mysql-protocol, Re: [ANN] New PBXT/MyBS release enables JDBC-based BLOB streaming!
On 19 Oct 2007, at 04:35, Paul McCullagh wrote: For this release I have completed changes to the MySQL Connector/J 5.0.7, to allow BLOB data to be transparently stored and retrieved from the MyBS BLOB repository. The new version of the driver is called MySQL Connector/J SE (streaming enabled). That's a beautiful hack, Paul. Of course, you know what I'm going to say, but most of your readers weren't at MySQLCamp-US-East where I proposed it: We can use this experience to change mysql-proxy, as a stepping stone to making the next mysql protocol (labled p+1 below) that has (at least) streaming built-in: now: client - server next: client (p+1) - proxy=server + mybs : client - proxy-server (p+1) : client (p+1)- server (p+1) so, older versions of client and server could speak to newer versions of server and client, perhaps with proxy as an intermediary. Alas, this is a long-term dream. I'm not proposing it for anyone soon, but it's fun to think about. - chad -- Chad Miller, Software Developer [EMAIL PROTECTED] MySQL Inc., www.mysql.com Orlando, Florida, USA13-20z, UTC-0400 Office: +1 408 213 6740 sip:[EMAIL PROTECTED] PGP.sig Description: This is a digitally signed message part
Re: [MYSQL]time of elapsed time
Hi Craig, Weston, Craig (OFT) wrote: Hello everyone. Once again, I am jousting at the windmill of time and date formulae within MYSQL. I seek to create a cross-tab or pivot table of the SUM of all times with a specific category, on a per-ticket basis. I have everything working except the math part. Even that is kind of working ok, but it is not adding up The math part is: IF(`status` = 'WIP',SUM(time_to_sec(`CLOCK_TIME`)),0) AS 'WIP' (the same formula with 'CLOSED','OPEN', etc lists all the various statuses available.) CLOCK_TIME is a varchar field that contains a 4 digit date counter and a timer, in the format of 0293 23:44 0001 00:29 0001 19:15 My hunch is this is the problem. You should split the field into two: one for the date counter, one for the time. time_to_secs() is probably returning zero for most of these. Now, I run the query and get results. Every ticket has more than one status. But, for each ticket, I get a single line that appears to have the entire ticket time (in seconds) in one field and the rest are zero. I think this tells me that the statement is working but that I am grouping them wrong? `key` is the ticket number. From `clock_data` group by `clock_data`.`key` So my result set looks like 17881375,0,0,0,0,15235975,0,0,0,0,0,0,0,0 18184639,21362513,0,0,0,0,0,0,0,0,0,0,0,0 Etc. Can anyone help me over this hill? I think I am writing the if statement incorrectly somehow but don't see a way out of the box yet. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: importing a single table from mysqldump
Hello, If it is a dump you can pipe it into mysql. If you have a csv like file you can import it with LOAD DATA LOCAL INFILE like things. An example below: DROP TABLE IF EXISTS EnsgDescriptions; CREATE TABLE IF NOT EXISTS EnsgDescriptions (stable_id VARCHAR(128) PRIMARY KEY, description VARCHAR(128)); LOAD DATA LOCAL INFILE 'imports/ensgdescriptions.csv' INTO TABLE EnsgDescriptions; Wkr (don't write this in your script :-), -- Dr. Werner Van Belle http://werner.sigtrans.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
importing a single table from mysqldump
Hi, I have a 250mb dump and need to extract some data. I know how to export a single table, but not import a single table using mysqldump. Any ideas? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[MYSQL]time of elapsed time
Hello everyone. Once again, I am jousting at the windmill of time and date formulae within MYSQL. I seek to create a cross-tab or pivot table of the SUM of all times with a specific category, on a per-ticket basis. I have everything working except the math part. Even that is kind of working ok, but it is not adding up The math part is: IF(`status` = 'WIP',SUM(time_to_sec(`CLOCK_TIME`)),0) AS 'WIP' (the same formula with 'CLOSED','OPEN', etc lists all the various statuses available.) CLOCK_TIME is a varchar field that contains a 4 digit date counter and a timer, in the format of 0293 23:44 0001 00:29 0001 19:15 ... Now, I run the query and get results. Every ticket has more than one status. But, for each ticket, I get a single line that appears to have the entire ticket time (in seconds) in one field and the rest are zero. I think this tells me that the statement is working but that I am grouping them wrong? `key` is the ticket number. From `clock_data` group by `clock_data`.`key` So my result set looks like 17881375,0,0,0,0,15235975,0,0,0,0,0,0,0,0 18184639,21362513,0,0,0,0,0,0,0,0,0,0,0,0 Etc. Can anyone help me over this hill? I think I am writing the if statement incorrectly somehow but don't see a way out of the box yet. Thank you. This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.
