At 19:53 -0300 10/15/02, Joao A. Marques wrote: >Hi, >I“m getting problem with function CURTIME(). >The problem happens, apparently, when then current seconds of the current >time (server“s time) is between 43 and 59, as result of "CURTIME - <field of >type TIME>". >See :
No, the problem is that you cannot take the difference of two TIME values the way you're doing it. Use: SEC_TO_TIME(TIME_TO_SEC(CURTIME()) - TIME_TO_SEC(init_time))) > >[root@test01 /root]# mysql >Welcome to the MySQL monitor. Commands end with ; or \g. >Your MySQL connection id is 300932 to server version: 3.23.51 > >Type 'help;' or '\h' for help. Type '\c' to clear the buffer. > >mysql> create database mydb; >Query OK, 1 row affected (0.00 sec) > >mysql> CREATE TABLE mytable (id int unsigned NOT NULL auto_increment, >init_time time, PRIMARY KEY (id)); >Query OK, 0 rows affected (0.01 sec) > >mysql> insert mytable (init_time) values (curtime()); >Query OK, 1 row affected (0.00 sec) > ><snip> >start your chronometer now ... ></snip> > >mysql> select init_time,curtime(),time_format(curtime()-init_time, >'%H:%i:%S') from mytable; >+-----------+-----------+----------------------------------------------+ >| init_time | curtime() | time_format(curtime()-init_time, '%H:%i:%S') | >+-----------+-----------+----------------------------------------------+ >| 19:17:16 | 19:17:16 | 00:00:00 | >+-----------+-----------+----------------------------------------------+ >1 row in set (0.00 sec) > >mysql> select init_time,curtime(),time_format(curtime()-init_time, >'%H:%i:%S') from mytable; >+-----------+-----------+----------------------------------------------+ >| init_time | curtime() | time_format(curtime()-init_time, '%H:%i:%S') | >+-----------+-----------+----------------------------------------------+ >| 19:17:16 | 19:17:19 | 00:00:03 | >+-----------+-----------+----------------------------------------------+ >1 row in set (0.00 sec) > >mysql> select init_time,curtime(),time_format(curtime()-init_time, >'%H:%i:%S') from mytable; >+-----------+-----------+----------------------------------------------+ >| init_time | curtime() | time_format(curtime()-init_time, '%H:%i:%S') | >+-----------+-----------+----------------------------------------------+ >| 19:17:16 | 19:17:22 | 00:00:06 | >+-----------+-----------+----------------------------------------------+ >1 row in set (0.00 sec) > ><snip> >wait about 42 seconds ... ></snip> > >mysql> select init_time,curtime(),time_format(curtime()-init_time, >'%H:%i:%S') from mytable; >+-----------+-----------+----------------------------------------------+ >| init_time | curtime() | time_format(curtime()-init_time, '%H:%i:%S') | >+-----------+-----------+----------------------------------------------+ >| 19:17:16 | 19:17:58 | 00:00:42 | >+-----------+-----------+----------------------------------------------+ >1 row in set (0.00 sec) > >mysql> select init_time,curtime(),time_format(curtime()-init_time, >'%H:%i:%S') from mytable; >+-----------+-----------+----------------------------------------------+ >| init_time | curtime() | time_format(curtime()-init_time, '%H:%i:%S') | >+-----------+-----------+----------------------------------------------+ >| 19:17:16 | 19:17:59 | 00:00:43 | >+-----------+-----------+----------------------------------------------+ >1 row in set (0.00 sec) > >mysql> select init_time,curtime(),time_format(curtime()-init_time, >'%H:%i:%S') from mytable; >+-----------+-----------+----------------------------------------------+ >| init_time | curtime() | time_format(curtime()-init_time, '%H:%i:%S') | >+-----------+-----------+----------------------------------------------+ >| 19:17:16 | 19:18:00 | NULL | >+-----------+-----------+----------------------------------------------+ >1 row in set (0.01 sec) > >mysql> select init_time,curtime(),time_format(curtime()-init_time, >'%H:%i:%S') from mytable; >+-----------+-----------+----------------------------------------------+ >| init_time | curtime() | time_format(curtime()-init_time, '%H:%i:%S') | >+-----------+-----------+----------------------------------------------+ >| 19:17:16 | 19:18:01 | NULL | >+-----------+-----------+----------------------------------------------+ >1 row in set (0.00 sec) > ><snip> >the result is NULL ... wait more 20 seconds, and ... ></snip> > >mysql> select init_time,curtime(),time_format(curtime()-init_time, >'%H:%i:%S') from mytable; >+-----------+-----------+----------------------------------------------+ >| init_time | curtime() | time_format(curtime()-init_time, '%H:%i:%S') | >+-----------+-----------+----------------------------------------------+ >| 19:17:16 | 19:18:29 | 00:01:13 | >+-----------+-----------+----------------------------------------------+ >1 row in set (0.01 sec) > >mysql> select init_time,curtime(),time_format(curtime()-init_time, >'%H:%i:%S') from mytable; >+-----------+-----------+----------------------------------------------+ >| init_time | curtime() | time_format(curtime()-init_time, '%H:%i:%S') | >+-----------+-----------+----------------------------------------------+ >| 19:17:16 | 19:18:30 | 00:01:14 | >+-----------+-----------+----------------------------------------------+ >1 row in set (0.00 sec) > ><snip> >surprise ! it returns ok. ></snip> > >mysql> select version(); >+-----------+ >| version() | >+-----------+ >| 3.23.51 | >+-----------+ >1 row in set (0.00 sec) > >mysql> show variables; >+---------------------------------+---------- >| Variable_name | Value >+---------------------------------+---------- >| back_log | 50 >| basedir | / >| binlog_cache_size | 32768 >| character_set | latin1 >| character_sets | latin1 big5 czech euc_kr gb2312 gbk sjis >tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish >hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat >cp1257 latin5 | >| concurrent_insert | ON >| connect_timeout | 5 >| datadir | /var/lib/mysql/ >| delay_key_write | ON >| delayed_insert_limit | 100 >| delayed_insert_timeout | 300 >| delayed_queue_size | 1000 >| flush | OFF >| flush_time | 0 >| have_bdb | NO >| have_gemini | NO >| have_innodb | NO >| have_isam | YES >| have_raid | NO >| have_openssl | NO >| init_file | >| interactive_timeout | 28800 >| join_buffer_size | 131072 >| key_buffer_size | 8388600 >| language | /usr/share/mysql/english/ >| large_files_support | ON >| locked_in_memory | OFF >| log | OFF >| log_update | OFF >| log_bin | OFF >| log_slave_updates | OFF >| log_long_queries | OFF >| long_query_time | 10 >| low_priority_updates | OFF >| lower_case_table_names | 0 >| max_allowed_packet | 1048576 >| max_binlog_cache_size | 4294967295 >| max_binlog_size | 1073741824 >| max_connections | 200 >| max_connect_errors | 10 >| max_delayed_threads | 20 >| max_heap_table_size | 16777216 >| max_join_size | 4294967295 >| max_sort_length | 1024 >| max_user_connections | 0 >| max_tmp_tables | 32 >| max_write_lock_count | 4294967295 >| myisam_max_extra_sort_file_size | 256 >| myisam_max_sort_file_size | 2047 >| myisam_recover_options | 0 >| myisam_sort_buffer_size | 8388608 >| net_buffer_length | 16384 >| net_read_timeout | 30 >| net_retry_count | 10 >| net_write_timeout | 60 >| open_files_limit | 0 >| pid_file | /var/lib/mysql/teste01.mydomain.com.pid >| port | 3306 >| protocol_version | 10 >| record_buffer | 131072 >| record_rnd_buffer | 131072 >| query_buffer_size | 0 >| safe_show_database | OFF >| server_id | 0 >| slave_net_timeout | 3600 >| skip_locking | ON >| skip_networking | OFF >| skip_show_database | OFF >| slow_launch_time | 2 >| socket | /var/lib/mysql/mysql.sock >| sort_buffer | 2097144 >| sql_mode | 0 >| table_cache | 64 >| table_type | MYISAM >| thread_cache_size | 0 >| thread_stack | 65536 >| transaction_isolation | READ-COMMITTED >| timezone | East >| tmp_table_size | 33554432 >| tmpdir | /root/tmp/ >| version | 3.23.51 >| wait_timeout | 28800 >+---------------------------------+------------------------------- >82 rows in set (0.01 sec) > >mysql> > >Regards, > >Joćo Alexander Marques >[EMAIL PROTECTED] >Uranet Projetos & Sistemas Ltda. >Tel. : 55 11 3242-1353 > > > > >--------------------------------------------------------------------- >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
