Re: Substraction
Hi, I have the same problem the last week - I checked the manual and it seems this is from the new way the unsigned columns are treated in MySQL 4. Here is a quote from http://www.mysql.com/doc/en/Column_types.html Warning: you should be aware that when you use subtraction between integer values where one is of type UNSIGNED, the result will be unsigned! A workaround for this problem which is described in the manual is either to cast the column to a signed integer or to include a double value (0.0 or something like this) in the operation. Check http://www.mysql.com/doc/en/Cast_Functions.html for more information HTH Dobromir Velev [EMAIL PROTECTED] - Original Message - From: Stefan Hinz, iConnect (Berlin) [EMAIL PROTECTED] To: Brian Lindner [EMAIL PROTECTED]; Octavian Rasnita [EMAIL PROTECTED] Cc: MySQL [EMAIL PROTECTED] Sent: Monday, January 20, 2003 01:09 Subject: Re: Substraction Brian, create table test(id int unsigned); insert into test values(10); select 15 - id from test; The result is 18446744073709551611 instead of -5. Trying to confirm this with MySQL 4.0.7 on a Win2K box: mysql create table test(id int unsigned); Query OK, 0 rows affected (0.56 sec) mysql insert into test values(10); Query OK, 1 row affected (0.08 sec) mysql select 15 - id from test; +-+ | 15 - id | +-+ | 5 | +-+ 1 row in set (0.06 sec) Now, once again with phpMyAdmin 2.3.3pl1: SELECT 15 - id FROM test LIMIT 0, 30 15 - id 5 But with the following statement (same with phpMyAdmin): mysql select 5 - id from test; +--+ | 5 - id | +--+ | 18446744073709551611 | +--+ 1 row in set (0.00 sec) Check the 1 in your 15. Maybe there's something wrong. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Brian Lindner [EMAIL PROTECTED] To: Octavian Rasnita [EMAIL PROTECTED] Cc: MySQL [EMAIL PROTECTED] Sent: Sunday, January 19, 2003 10:28 PM Subject: Re: Substraction Octavian, Sunday, January 19, 2003, 7:31:57 AM, you wrote: Hi all, I've tried the following query: create table test(id int unsigned); insert into test values(10); select 15 - id from test; The result is 18446744073709551611 instead of -5. I ran this.. and it worked for me MySql 4.0.7 on Linux... ran it through phpMyAdmin Am I doing something wrong? Thank you. Teddy, Teddy's Center: http://teddy.fcc.ro/ Email: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Brian Email: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Substraction
But I don't want to perform a 15 - id, meaning 15 - 10. I want to calculate 5 - id, meaning 5 - 10. It should give me -5 or 5 but not | 18446744073709551611 | Teddy, Teddy's Center: http://teddy.fcc.ro/ Email: [EMAIL PROTECTED] - Original Message - From: Stefan Hinz, iConnect (Berlin) [EMAIL PROTECTED] To: Brian Lindner [EMAIL PROTECTED]; Octavian Rasnita [EMAIL PROTECTED] Cc: MySQL [EMAIL PROTECTED] Sent: Monday, January 20, 2003 1:09 AM Subject: Re: Substraction Brian, create table test(id int unsigned); insert into test values(10); select 15 - id from test; The result is 18446744073709551611 instead of -5. Trying to confirm this with MySQL 4.0.7 on a Win2K box: mysql create table test(id int unsigned); Query OK, 0 rows affected (0.56 sec) mysql insert into test values(10); Query OK, 1 row affected (0.08 sec) mysql select 15 - id from test; +-+ | 15 - id | +-+ | 5 | +-+ 1 row in set (0.06 sec) Now, once again with phpMyAdmin 2.3.3pl1: SELECT 15 - id FROM test LIMIT 0, 30 15 - id 5 But with the following statement (same with phpMyAdmin): mysql select 5 - id from test; +--+ | 5 - id | +--+ | 18446744073709551611 | +--+ 1 row in set (0.00 sec) Check the 1 in your 15. Maybe there's something wrong. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Brian Lindner [EMAIL PROTECTED] To: Octavian Rasnita [EMAIL PROTECTED] Cc: MySQL [EMAIL PROTECTED] Sent: Sunday, January 19, 2003 10:28 PM Subject: Re: Substraction Octavian, Sunday, January 19, 2003, 7:31:57 AM, you wrote: Hi all, I've tried the following query: create table test(id int unsigned); insert into test values(10); select 15 - id from test; The result is 18446744073709551611 instead of -5. I ran this.. and it worked for me MySql 4.0.7 on Linux... ran it through phpMyAdmin Am I doing something wrong? Thank you. Teddy, Teddy's Center: http://teddy.fcc.ro/ Email: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Brian Email: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Substraction
Hi, There are several ways to solve the problem - 1. Check if you really need an unsigned column - if not just alter the column to signed 2. A simple workaround for substracting unsigned values is to add a floating point number to the operation - then all values will be converted to float and the negative value will be displayed. For example this should give you what you need select 15.0 - id from test; 3. Use CAST function. Example: select cast(15-id as signed) from test; HTH Dobromir Velev [EMAIL PROTECTED] - Original Message - From: Octavian Rasnita [EMAIL PROTECTED] To: Stefan Hinz, iConnect (Berlin) [EMAIL PROTECTED]; Brian Lindner [EMAIL PROTECTED] Cc: MySQL [EMAIL PROTECTED] Sent: Monday, January 20, 2003 08:22 Subject: Re: Substraction But I don't want to perform a 15 - id, meaning 15 - 10. I want to calculate 5 - id, meaning 5 - 10. It should give me -5 or 5 but not | 18446744073709551611 | Teddy, Teddy's Center: http://teddy.fcc.ro/ Email: [EMAIL PROTECTED] - Original Message - From: Stefan Hinz, iConnect (Berlin) [EMAIL PROTECTED] To: Brian Lindner [EMAIL PROTECTED]; Octavian Rasnita [EMAIL PROTECTED] Cc: MySQL [EMAIL PROTECTED] Sent: Monday, January 20, 2003 1:09 AM Subject: Re: Substraction Brian, create table test(id int unsigned); insert into test values(10); select 15 - id from test; The result is 18446744073709551611 instead of -5. Trying to confirm this with MySQL 4.0.7 on a Win2K box: mysql create table test(id int unsigned); Query OK, 0 rows affected (0.56 sec) mysql insert into test values(10); Query OK, 1 row affected (0.08 sec) mysql select 15 - id from test; +-+ | 15 - id | +-+ | 5 | +-+ 1 row in set (0.06 sec) Now, once again with phpMyAdmin 2.3.3pl1: SELECT 15 - id FROM test LIMIT 0, 30 15 - id 5 But with the following statement (same with phpMyAdmin): mysql select 5 - id from test; +--+ | 5 - id | +--+ | 18446744073709551611 | +--+ 1 row in set (0.00 sec) Check the 1 in your 15. Maybe there's something wrong. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Brian Lindner [EMAIL PROTECTED] To: Octavian Rasnita [EMAIL PROTECTED] Cc: MySQL [EMAIL PROTECTED] Sent: Sunday, January 19, 2003 10:28 PM Subject: Re: Substraction Octavian, Sunday, January 19, 2003, 7:31:57 AM, you wrote: Hi all, I've tried the following query: create table test(id int unsigned); insert into test values(10); select 15 - id from test; The result is 18446744073709551611 instead of -5. I ran this.. and it worked for me MySql 4.0.7 on Linux... ran it through phpMyAdmin Am I doing something wrong? Thank you. Teddy, Teddy's Center: http://teddy.fcc.ro/ Email: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Brian Email: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: Re: Substraction
On Monday 20 January 2003 08:22, Octavian Rasnita wrote: But I don't want to perform a 15 - id, meaning 15 - 10. I want to calculate 5 - id, meaning 5 - 10. It should give me -5 or 5 but not | 18446744073709551611 | The result of the above expression is UNSIGNED, because column 'id' is INT UNSIGNED. If you want to get -5, use CAST() function in v4.0: mysql select CAST(5 - id AS SIGNED) from test; ++ | CAST(5 - id AS SIGNED) | ++ | -5 | ++ 1 row in set (0.00 sec) or in 3.23 you can do: mysql select 5 - (id+0.0) from test; +--+ | 5 - (id+0.0) | +--+ | -5.0 | +--+ 1 row in set (0.00 sec) For more info look at: http://www.mysql.com/doc/en/Cast_Functions.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re:Re: Substraction
1. Check if you really need an unsigned column - if not just alter the column to signed this is the most appropriate answer. i.e your table design should cater for the limited set or modulus arithmatics. Casting wouldn't work simply because you may really want the actual unsigned value of 2^64 - 5 which is 18446744073709551611 in one case and the 5 - 10 = -5 in another. If you cast both, it will return -5 (not a pleasant view for a very rich bank customer!!). And by the way this is not a MySQL limitation, it is a computer Science issue as computers can not implement infinite sets (N, R, Z, Q etc). Luckily, this doesn't happen in practice because database designers do allow for such issues in their table designs... Octavian's table is, however designed to show this issue and not to solve a commerical problem. Enough is enough! Let's stop this nonsense. Nasser. -- Dobromir Velev [EMAIL PROTECTED] writes: Return-Path: [EMAIL PROTECTED] Received: from mx12.lax.untd.com (mx12.lax.untd.com [10.130.24.72]) by maildeliver03.nyc.untd.com with SMTP id AAA9C2PGCAFYS4JA for [EMAIL PROTECTED] (sender [EMAIL PROTECTED]); Mon, 20 Jan 2003 12:41:22 -0500 (EST) Received: from web.mysql.com (web.mysql.com [213.136.49.183]) by mx12.lax.untd.com with SMTP id AAA9C2PGBAM2YKK2 for [EMAIL PROTECTED] (sender [EMAIL PROTECTED]); Mon, 20 Jan 2003 09:41:21 -0800 (PST) Received: (qmail 30395 invoked by uid 7797); 20 Jan 2003 17:12:03 - Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm (http://www.ezmlm.org) List-ID: mysql.mysql.com Precedence: bulk List-Help: mailto:[EMAIL PROTECTED] List-Unsubscribe: mailto:[EMAIL PROTECTED] List-Post: mailto:[EMAIL PROTECTED] List-Subscribe: mailto:[EMAIL PROTECTED] Delivered-To: mailing list [EMAIL PROTECTED] Received: (qmail 30024 invoked from network); 20 Jan 2003 17:11:38 - Message-ID: 05b801c2c0a7$8c882800$1d00a8c0@Dobromir From: Dobromir Velev [EMAIL PROTECTED] To: Octavian Rasnita [EMAIL PROTECTED] Cc: MySQL [EMAIL PROTECTED] References: 001901c2bfe4$58e8dda0$[EMAIL PROTECTED] [EMAIL PROTECTED] 00ad01c2c010$45ee73a0$dc4fc7d9@mephisto 006001c2c09a$9a4fc1d0$[EMAIL PROTECTED] Subject: Re: Substraction Date: Mon, 20 Jan 2003 19:15:33 +0200 MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2720.3000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600. X-Virus-Scanned: by AMaViS perl-11 Hi, There are several ways to solve the problem - 1. Check if you really need an unsigned column - if not just alter the column to signed 2. A simple workaround for substracting unsigned values is to add a floating point number to the operation - then all values will be converted to float and the negative value will be displayed. For example this should give you what you need select 15.0 - id from test; 3. Use CAST function. Example: select cast(15-id as signed) from test; HTH Dobromir Velev [EMAIL PROTECTED] - Original Message - From: Octavian Rasnita [EMAIL PROTECTED] To: Stefan Hinz, iConnect (Berlin) [EMAIL PROTECTED]; Brian Lindner [EMAIL PROTECTED] Cc: MySQL [EMAIL PROTECTED] Sent: Monday, January 20, 2003 08:22 Subject: Re: Substraction But I don't want to perform a 15 - id, meaning 15 - 10. I want to calculate 5 - id, meaning 5 - 10. It should give me -5 or 5 but not | 18446744073709551611 | Teddy, Teddy's Center: http://teddy.fcc.ro/ Email: [EMAIL PROTECTED] - Original Message - From: Stefan Hinz, iConnect (Berlin) [EMAIL PROTECTED] To: Brian Lindner [EMAIL PROTECTED]; Octavian Rasnita [EMAIL PROTECTED] Cc: MySQL [EMAIL PROTECTED] Sent: Monday, January 20, 2003 1:09 AM Subject: Re: Substraction Brian, create table test(id int unsigned); insert into test values(10); select 15 - id from test; The result is 18446744073709551611 instead of -5. Trying to confirm this with MySQL 4.0.7 on a Win2K box: mysql create table test(id int unsigned); Query OK, 0 rows affected (0.56 sec) mysql insert into test values(10); Query OK, 1 row affected (0.08 sec) mysql select 15 - id from test; +-+ | 15 - id | +-+ | 5 | +-+ 1 row in set (0.06 sec) Now, once again with phpMyAdmin 2.3.3pl1: SELECT 15 - id FROM test LIMIT 0, 30 15 - id 5 But with the following statement (same with phpMyAdmin): mysql select 5 - id from test; +--+ | 5 - id | +--+ | 18446744073709551611 | +--+ 1 row in set (0.00 sec) Check the 1 in your 15. Maybe there's something wrong. Regards, -- Stefan Hinz [EMAIL PROTECTED] Gesch䦴sf��r / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Brian Lindner [EMAIL
Substraction
Hi all, I've tried the following query: create table test(id int unsigned); insert into test values(10); select 15 - id from test; The result is 18446744073709551611 instead of -5. Am I doing something wrong? Thank you. Teddy, Teddy's Center: http://teddy.fcc.ro/ Email: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Substraction
Octavian, Sunday, January 19, 2003, 7:31:57 AM, you wrote: Hi all, I've tried the following query: create table test(id int unsigned); insert into test values(10); select 15 - id from test; The result is 18446744073709551611 instead of -5. I ran this.. and it worked for me MySql 4.0.7 on Linux... ran it through phpMyAdmin Am I doing something wrong? Thank you. Teddy, Teddy's Center: http://teddy.fcc.ro/ Email: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Brian Email: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re:Re: Substraction
Isn't there a typo somewhere... Shouldn't you expect 5 instead of -5 (15 - id where id = 10)?? Nevermind, let's assume that there is a typo somewhere and the correct expected value is -5... however, in the domain of unsigned arithmetics -5 is precisely 18446744073709551611... don't you believe me: look at this: 18446744073709551611 + 5 = 18446744073709551616 which is 2 to power 64 (2^64). Clearly in the architecture that you are using an unsigned int is a 64 bits integer... if you set all the bits to 1 you will get the largest unsigned int in your system and that is 18446744073709551615 which is (2^64)-1. if you increment it by one... all the bits switch to 0. Hence within your architecture 2^64 = 0 and -5 is represented correctly by 18446744073709551611 (which is 2^64 - 5). Nasser sql, smallint, ... -- Brian Lindner [EMAIL PROTECTED] writes: Return-Path: [EMAIL PROTECTED] Received: from mx11.lax.untd.com (mx11.lax.untd.com [10.130.24.71]) by maildeliver03.nyc.untd.com with SMTP id AAA9CYGS4AWCVYC2 for [EMAIL PROTECTED] (sender [EMAIL PROTECTED]); Sun, 19 Jan 2003 16:35:22 -0500 (EST) Received: from web.mysql.com (web.mysql.com [213.136.49.183]) by mx11.lax.untd.com with SMTP id AAA9CYGS3A42HYBJ for [EMAIL PROTECTED] (sender [EMAIL PROTECTED]); Sun, 19 Jan 2003 13:35:21 -0800 (PST) Received: (qmail 2737 invoked by uid 7797); 19 Jan 2003 21:29:14 - Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm (http://www.ezmlm.org) List-ID: mysql.mysql.com Precedence: bulk List-Help: mailto:[EMAIL PROTECTED] List-Unsubscribe: mailto:[EMAIL PROTECTED] List-Post: mailto:[EMAIL PROTECTED] List-Subscribe: mailto:[EMAIL PROTECTED] Delivered-To: mailing list [EMAIL PROTECTED] Received: (qmail 2704 invoked from network); 19 Jan 2003 21:29:13 - Date: Sun, 19 Jan 2003 16:28:43 -0500 From: Brian Lindner [EMAIL PROTECTED] X-Mailer: The Bat! (v1.62 Christmas Edition) UNREG / CD5BF9353B3B7091 Reply-To: Brian Lindner [EMAIL PROTECTED] X-Priority: 3 (Normal) Message-ID: [EMAIL PROTECTED] To: Octavian Rasnita [EMAIL PROTECTED] CC: MySQL [EMAIL PROTECTED] Subject: Re: Substraction In-Reply-To: 001901c2bfe4$58e8dda0$[EMAIL PROTECTED] References: 001901c2bfe4$58e8dda0$[EMAIL PROTECTED] MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Octavian, Sunday, January 19, 2003, 7:31:57 AM, you wrote: Hi all, I've tried the following query: create table test(id int unsigned); insert into test values(10); select 15 - id from test; The result is 18446744073709551611 instead of -5. I ran this.. and it worked for me MySql 4.0.7 on Linux... ran it through phpMyAdmin Am I doing something wrong? Thank you. Teddy, Teddy's Center: http://teddy.fcc.ro/ Email: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Brian Email: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Substraction
Brian, create table test(id int unsigned); insert into test values(10); select 15 - id from test; The result is 18446744073709551611 instead of -5. Trying to confirm this with MySQL 4.0.7 on a Win2K box: mysql create table test(id int unsigned); Query OK, 0 rows affected (0.56 sec) mysql insert into test values(10); Query OK, 1 row affected (0.08 sec) mysql select 15 - id from test; +-+ | 15 - id | +-+ | 5 | +-+ 1 row in set (0.06 sec) Now, once again with phpMyAdmin 2.3.3pl1: SELECT 15 - id FROM test LIMIT 0, 30 15 - id 5 But with the following statement (same with phpMyAdmin): mysql select 5 - id from test; +--+ | 5 - id | +--+ | 18446744073709551611 | +--+ 1 row in set (0.00 sec) Check the 1 in your 15. Maybe there's something wrong. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Brian Lindner [EMAIL PROTECTED] To: Octavian Rasnita [EMAIL PROTECTED] Cc: MySQL [EMAIL PROTECTED] Sent: Sunday, January 19, 2003 10:28 PM Subject: Re: Substraction Octavian, Sunday, January 19, 2003, 7:31:57 AM, you wrote: Hi all, I've tried the following query: create table test(id int unsigned); insert into test values(10); select 15 - id from test; The result is 18446744073709551611 instead of -5. I ran this.. and it worked for me MySql 4.0.7 on Linux... ran it through phpMyAdmin Am I doing something wrong? Thank you. Teddy, Teddy's Center: http://teddy.fcc.ro/ Email: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Brian Email: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re:Re: Substraction
On Sun, 2003-01-19 at 19:09, Nasser Ossareh wrote: Isn't there a typo somewhere... Shouldn't you expect 5 instead of -5 (15 - id where id = 10)?? Nevermind, let's assume that there is a typo somewhere and the correct expected value is -5... however, in the domain of unsigned arithmetics -5 is precisely 18446744073709551611... don't you believe me: look at this: 18446744073709551611 + 5 = 18446744073709551616 which is 2 to power 64 (2^64). Clearly in the architecture that you are using an unsigned int is a 64 bits integer... if you set all the bits to 1 you will get the largest unsigned int in your system and that is 18446744073709551615 which is (2^64)-1. if you increment it by one... all the bits switch to 0. Hence within your architecture 2^64 = 0 and -5 is represented correctly by 18446744073709551611 (which is 2^64 - 5). Nasser sql, smallint, ... Beautifully explained. Adolfo - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql DATETIME substraction problem/?
You can't just subtract datetime fields. Check the date and time functions in the manual. Jim Hogan wrote: Hello! I am working on an analysis that is very much dependent on calculating time differences in seconds. The most simple example: I have 3 variables: time_begin (DATETIME) time_end (DATETIME) and elapsed_ seconds (INT). The data in these DATETIMES looks fine -MM-DD HH:MM:SS as expected and actual differences between time_end and time_begin are usually on the order of 20-120 seconds. I run: UPDATE mytable SET elapsed_seconds=time_end-time_begin; When I browse the resulting data, the elapsed seconds often do, but do not always, agree with my own calculation of the elapsed seconds. For example, the first 12 rows look like: My calc elapsed_time 45 85 11 11 16 16 9 9 22 62 14 14 73 73 59 99 65 105 20 20 12 12 43 4083 That last one is a bit off!! There appeared to be a common problem with a 40-second difference, but obvioulsy that last one throws that pattern out the window! Anyhow, I have Googled quite a bit and have looked through http://www.mysql.com/doc/en/Date_and_time_functions.html several times and did not see a different function of syntax for the UPDATE that I want to do. That man page (in the section on DATE_SUB) says that as of MySQL 3.23 I can simply use +/- operators By way of troubleshooting, I created 6 other elapsed_time variables using small, medium and big int, and float, double and decimal to see if the result was different, but the result was the same for each. I'm guessing (and hoping!!) that I am missing something *very* basic in the structure of my UPDATE and will absolutely appreciate any pointers that could fix it. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: mysql DATETIME substraction problem/?
Jim, Sunday, November 03, 2002, 8:44:43 PM, you wrote: JH I am working on an analysis that is very much dependent on calculating JH time differences in seconds. The most simple example: JH I have 3 variables: time_begin (DATETIME) time_end (DATETIME) and elapsed_ JH seconds (INT). The data in these DATETIMES looks fine -MM-DD HH:MM:SS JH as expected and actual differences between time_end and time_begin are JH usually on the order of 20-120 seconds. JH I run: UPDATE mytable SET elapsed_seconds=time_end-time_begin; JH When I browse the resulting data, the elapsed seconds often do, but do JH not always, agree with my own calculation of the elapsed seconds. For JH example, the first 12 rows look like: JH My calc elapsed_time JH 45 85 JH 11 11 JH 16 16 JH 9 9 JH 22 62 JH 14 14 JH 73 73 JH 59 99 JH 65 105 JH 20 20 JH 12 12 JH 43 4083 JH That last one is a bit off!! There appeared to be a common problem with a JH 40-second difference, but obvioulsy that last one throws that pattern out JH the window! JH Anyhow, I have Googled quite a bit and have looked through JH http://www.mysql.com/doc/en/Date_and_time_functions.html several times and JH did not see a different function of syntax for the UPDATE that I want to JH do. That man page (in the section on DATE_SUB) says that as of MySQL 3.23 JH I can simply use +/- operators JH By way of troubleshooting, I created 6 other elapsed_time variables using JH small, medium and big int, and float, double and decimal to see if the JH result was different, but the result was the same for each. You can't apply arithmetic directly on DATETIME columns. You should use TIME_TO_SEC(time_end)-TIME_TO_SEC(time_begin) -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
NO PROBLEM: Re: mysql DATETIME substraction problem/?
On Mon, 4 Nov 2002, gerald_clark wrote: You can't just subtract datetime fields. Check the date and time functions in the manual. Thanks, I should know I run the risk of posting a RTFM/FAQ when I'm tired! I got off track by misinterpreting In MySQL Version 3.23, you can use + and - instead of DATE_ADD() and DATE_SUB() in the manual when all I needed was UNIX_TIMESTAMP() (that I misinterpreted when I looked at it the first time). Anyhow, I can *nearly* just subtract datetime fields and all is right with the world. Jim On Mon, 4 Nov 2002, gerald_clark wrote: You can't just subtract datetime fields. Check the date and time functions in the manual. Jim Hogan wrote: Hello! I am working on an analysis that is very much dependent on calculating time differences in seconds. The most simple example: I have 3 variables: time_begin (DATETIME) time_end (DATETIME) and elapsed_ seconds (INT). The data in these DATETIMES looks fine -MM-DD HH:MM:SS as expected and actual differences between time_end and time_begin are usually on the order of 20-120 seconds. I run: UPDATE mytable SET elapsed_seconds=time_end-time_begin; When I browse the resulting data, the elapsed seconds often do, but do not always, agree with my own calculation of the elapsed seconds. For example, the first 12 rows look like: My calc elapsed_time 45 85 11 11 16 16 99 22 62 14 14 73 73 59 99 65 105 20 20 12 12 43 4083 That last one is a bit off!! There appeared to be a common problem with a 40-second difference, but obvioulsy that last one throws that pattern out the window! Anyhow, I have Googled quite a bit and have looked through http://www.mysql.com/doc/en/Date_and_time_functions.html several times and did not see a different function of syntax for the UPDATE that I want to do. That man page (in the section on DATE_SUB) says that as of MySQL 3.23 I can simply use +/- operators By way of troubleshooting, I created 6 other elapsed_time variables using small, medium and big int, and float, double and decimal to see if the result was different, but the result was the same for each. I'm guessing (and hoping!!) that I am missing something *very* basic in the structure of my UPDATE and will absolutely appreciate any pointers that could fix it. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql DATETIME substraction problem/?
Hello. On Sun 2002-11-03 at 10:44:43 -0800, [EMAIL PROTECTED] wrote: Hello! I am working on an analysis that is very much dependent on calculating time differences in seconds. The most simple example: I have 3 variables: time_begin (DATETIME) time_end (DATETIME) and elapsed_ seconds (INT). The data in these DATETIMES looks fine -MM-DD HH:MM:SS as expected and actual differences between time_end and time_begin are usually on the order of 20-120 seconds. I run: UPDATE mytable SET elapsed_seconds=time_end-time_begin; You may not use +- on timestamps (DATETIME or TIMESTAMP) directly. For calculating differences between dates in seconds UNIX_TIMESTAMP() is probably the most useful, i.e. UPDATE mytable SET elapsed_seconds = UNIX_TIMESTAMP(time_end) - UNIX_TIMESTAMP(time_begin); Your method used the numer representation of the dates (e.g. 19971231235959) and did an integer substration, which will not take into account that seconds and minutes wrap at 60 and so on. [...] http://www.mysql.com/doc/en/Date_and_time_functions.html several times and did not see a different function of syntax for the UPDATE that I want to do. That man page (in the section on DATE_SUB) says that as of MySQL 3.23 I can simply use +/- operators Read that again. It only says that for using INTERVAL, i.e. SELECT 1997-12-31 23:59:59 + INTERVAL 1 SECOND; is allowed instead of SELECT DATE_ADD(1997-12-31 23:59:59, INTERVAL 1 SECOND); By way of troubleshooting, I created 6 other elapsed_time variables using small, medium and big int, and float, double and decimal to see if the result was different, but the result was the same for each. [...] That's because your problem does not stem from your storage. Btw, you can easily see that by using SELECT time_end-time_begin FROM mytable LIMIT 20; Regards, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: mysql DATETIME substraction problem/?
More appropriately, use curdate()feature from within sql. select * where date= curdate()-1; for example ~Kelly W. Black -Original Message- From: Benjamin Pflugmann [mailto:benjamin-mysql;pflugmann.de] Sent: Monday, November 04, 2002 1:06 PM To: Jim Hogan Cc: [EMAIL PROTECTED] Subject: Re: mysql DATETIME substraction problem/? Hello. On Sun 2002-11-03 at 10:44:43 -0800, [EMAIL PROTECTED] wrote: Hello! I am working on an analysis that is very much dependent on calculating time differences in seconds. The most simple example: I have 3 variables: time_begin (DATETIME) time_end (DATETIME) and elapsed_ seconds (INT). The data in these DATETIMES looks fine -MM-DD HH:MM:SS as expected and actual differences between time_end and time_begin are usually on the order of 20-120 seconds. I run: UPDATE mytable SET elapsed_seconds=time_end-time_begin; You may not use +- on timestamps (DATETIME or TIMESTAMP) directly. For calculating differences between dates in seconds UNIX_TIMESTAMP() is probably the most useful, i.e. UPDATE mytable SET elapsed_seconds = UNIX_TIMESTAMP(time_end) - UNIX_TIMESTAMP(time_begin); Your method used the numer representation of the dates (e.g. 19971231235959) and did an integer substration, which will not take into account that seconds and minutes wrap at 60 and so on. [...] http://www.mysql.com/doc/en/Date_and_time_functions.html several times and did not see a different function of syntax for the UPDATE that I want to do. That man page (in the section on DATE_SUB) says that as of MySQL 3.23 I can simply use +/- operators Read that again. It only says that for using INTERVAL, i.e. SELECT 1997-12-31 23:59:59 + INTERVAL 1 SECOND; is allowed instead of SELECT DATE_ADD(1997-12-31 23:59:59, INTERVAL 1 SECOND); By way of troubleshooting, I created 6 other elapsed_time variables using small, medium and big int, and float, double and decimal to see if the result was different, but the result was the same for each. [...] That's because your problem does not stem from your storage. Btw, you can easily see that by using SELECT time_end-time_begin FROM mytable LIMIT 20; Regards, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql DATETIME substraction problem/?
Hi. On Mon 2002-11-04 at 15:24:19 -0600, [EMAIL PROTECTED] wrote: More appropriately, use curdate()feature from within sql. select * where date= curdate()-1; Two points: - How does this relate to the origianl question? He wanted to know how to calculate a time interval. - The above has no reasonable behaviour. If the current date is 2002-10-01, you will get 20021000 as result. It's exactly the problem I just explained in the mail before. Regards, Benjamin. [...] You may not use +- on timestamps (DATETIME or TIMESTAMP) directly. For calculating differences between dates in seconds UNIX_TIMESTAMP() is probably the most useful, i.e. UPDATE mytable SET elapsed_seconds = UNIX_TIMESTAMP(time_end) - UNIX_TIMESTAMP(time_begin); Your method used the numer representation of the dates (e.g. 19971231235959) and did an integer substration, which will not take into account that seconds and minutes wrap at 60 and so on. [...] -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysql DATETIME substraction problem/?
Hello! I am working on an analysis that is very much dependent on calculating time differences in seconds. The most simple example: I have 3 variables: time_begin (DATETIME) time_end (DATETIME) and elapsed_ seconds (INT). The data in these DATETIMES looks fine -MM-DD HH:MM:SS as expected and actual differences between time_end and time_begin are usually on the order of 20-120 seconds. I run: UPDATE mytable SET elapsed_seconds=time_end-time_begin; When I browse the resulting data, the elapsed seconds often do, but do not always, agree with my own calculation of the elapsed seconds. For example, the first 12 rows look like: My calc elapsed_time 45 85 11 11 16 16 9 9 22 62 14 14 73 73 59 99 65 105 20 20 12 12 43 4083 That last one is a bit off!! There appeared to be a common problem with a 40-second difference, but obvioulsy that last one throws that pattern out the window! Anyhow, I have Googled quite a bit and have looked through http://www.mysql.com/doc/en/Date_and_time_functions.html several times and did not see a different function of syntax for the UPDATE that I want to do. That man page (in the section on DATE_SUB) says that as of MySQL 3.23 I can simply use +/- operators By way of troubleshooting, I created 6 other elapsed_time variables using small, medium and big int, and float, double and decimal to see if the result was different, but the result was the same for each. I'm guessing (and hoping!!) that I am missing something *very* basic in the structure of my UPDATE and will absolutely appreciate any pointers that could fix it. -- -*- Jim Hogan [EMAIL PROTECTED] Seattle, WA - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to do substraction between two tables?
Hi, On Mon, 2002-02-18 at 00:40, [EMAIL PROTECTED] wrote: I want to do substraction between tables in mysql, MINUS not yet implemented in mysql and this not work : select a.* from table_a a where a in (select * from table_b) Please post the structure of both tables. Then we can post you a readable example of how it can be done. The short answer is: use a join. Regards, Arjen. -- Get MySQL Training Worldwide, http://www.mysql.com/training/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Arjen G. Lentz [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer, Trainer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
