List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:October 15 2002 11:57pm
Subject:Re: "select CURTIME - <field of type TIME>" returns NULL sometimes
View as plain text  
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

Thread
"select CURTIME - <field of type TIME>" returns NULL sometimesJoao A. Marques16 Oct
  • Re: "select CURTIME - <field of type TIME>" returns NULL sometimesPaul DuBois16 Oct