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
>jmarques@stripped
>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 <mysql-thread122308@stripped>
>To unsubscribe, e-mail <mysql-unsubscribe-paul=snake.net@stripped>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
