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
