List:General Discussion« Previous MessageNext Message »
From:Gerald Clark Date:May 28 2002 2:10pm
Subject:Re: timestamp(8) GROUP BY problem
View as plain text  
try timestamp(14) instead.

sitnikov@stripped wrote:

>Description:
>	Problem with timestamp(8) and GROUP BY
>
>How-To-Repeat:
>
>mysql> CREATE TABLE test (
>    -> id int auto_increment,
>    -> dd timestamp(8),
>    -> data int,
>    -> PRIMARY KEY (id)
>    -> );
>Query OK, 0 rows affected (0.01 sec)
>
>mysql> INSERT INTO test (data) VALUES(1),(2),(3),(4);
>Query OK, 4 rows affected (0.00 sec)
>Records: 4  Duplicates: 0  Warnings: 0
>
>mysql> SELECT * FROM test;
>+----+----------+------+
>| id | dd       | data |
>+----+----------+------+
>|  1 | 20020527 |    1 |
>|  2 | 20020527 |    2 |
>|  3 | 20020527 |    3 |
>|  4 | 20020527 |    4 |
>+----+----------+------+
>4 rows in set (0.00 sec)
>
>mysql> SELECT dd,count(*) FROM test GROUP BY dd;
>+----------+----------+
>| dd       | count(*) |
>+----------+----------+
>| 20020527 |        4 |
>+----------+----------+
>1 row in set (0.00 sec)
>
>mysql> UPDATE test SET dd='20020527000001' WHERE id=1;
>Query OK, 1 row affected (0.00 sec)
>Rows matched: 1  Changed: 1  Warnings: 0
>
>mysql> UPDATE test SET dd='20020527000002' WHERE id=2;
>Query OK, 1 row affected (0.00 sec)
>Rows matched: 1  Changed: 1  Warnings: 0
>
>mysql> UPDATE test SET dd='20020527000003' WHERE id=3;
>Query OK, 1 row affected (0.00 sec)
>Rows matched: 1  Changed: 1  Warnings: 0
>
>mysql> UPDATE test SET dd='20020527000004' WHERE id=4;
>Query OK, 1 row affected (0.00 sec)
>Rows matched: 1  Changed: 1  Warnings: 0
>
>mysql> SELECT dd,count(*) FROM test GROUP BY dd;
>+----------+----------+
>| dd       | count(*) |
>+----------+----------+
>| 20020527 |        1 |
>| 20020527 |        1 |
>| 20020527 |        1 |
>| 20020527 |        1 |
>+----------+----------+
>4 rows in set (0.00 sec)
>
>Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
>This software comes with ABSOLUTELY NO WARRANTY. This is free software,
>and you are welcome to modify and redistribute it under the GPL license
>
>Server version		3.23.49a-max-log
>Protocol version	10
>Connection		Localhost via UNIX socket
>UNIX socket		/tmp/mysql.sock
>Uptime:			65 days 4 hours 9 min 10 sec
>
>Threads: 6  Questions: 69973912  Slow queries: 688  Opens: 129933  Flush tables: 3 
> Open tables: 506 Queries per second avg: 12.427
>
>>Environment:
>>
>	
>System: Linux gap 2.4.18 #3 SMP Fri Mar 15 14:40:03 EET 2002 i686 unknown
>Architecture: i686
>
>Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
>GCC: Reading specs from /usr/lib/gcc-lib/i486-suse-linux/2.95.3/specs
>gcc version 2.95.3 20010315 (SuSE)
>Compilation info: CC='gcc'  CFLAGS='-Wimplicit -Wreturn-type -Wid-clash-51 -Wswitch
> -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wimplicit-function-dec -Wimplicit-int
> -Wparentheses -Wsign-compare -Wwrite-strings -Wunused -mcpu=pentiumpro -O3
> -fno-omit-frame-pointer'  CXX='gcc'  CXXFLAGS='-Wimplicit -Wreturn-type -Wid-clash-51
> -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wimplicit-function-dec
> -Wimplicit-int -Wparentheses -Wsign-compare -Wwrite-strings -Woverloaded-virtual
> -Wextern-inline -Wsign-promo -Wreorder -Wctor-dtor-privacy -Wnon-virtual-dtor
> -felide-constructors -fno-exceptions -fno-rtti -mcpu=pentiumpro -O3
> -fno-omit-frame-pointer'  LDFLAGS=''
>LIBC: 
>-rwxr-xr-x    1 root     root      1384040 Dec 18 18:24 /lib/libc.so.6
>-rw-r--r--    1 root     root     25214756 Dec 18 18:07 /usr/lib/libc.a
>-rw-r--r--    1 root     root          178 Dec 18 18:07 /usr/lib/libc.so
>Configure command: ./configure --prefix=/usr/local/mysql --enable-assembler
> --with-extra-charsets=complex --enable-thread-safe-client --with-innodb --with-berkeley-db
> --enable-thread-safe-client --with-other-libc=/usr/local/mysql-glibc
> '--with-comment=Official MySQL Binary' --prefix=/usr/local/mysql
> --with-extra-charset=complex --enable-thread-safe-client --enable-local-infile
> --with-server-suffix=-max 'CFLAGS=-Wimplicit -Wreturn-type -Wid-clash-51 -Wswitch
> -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wimplicit-function-dec -Wimplicit-int
> -Wparentheses -Wsign-compare -Wwrite-strings -Wunused -mcpu=pentiumpro -O3
> -fno-omit-frame-pointer' 'CXXFLAGS=-Wimplicit -Wreturn-type -Wid-clash-51 -Wswitch
> -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wimplicit-function-dec -Wimplicit-int
> -Wparentheses -Wsign-compare -Wwrite-strings -Woverloaded-virtual -Wextern-inline
> -Wsign-promo -Wreorder -Wctor-dtor-privacy -Wnon-virtual-dtor -felide-constructors
> -fno-exceptions -fno-r!
> tti -mcpu=pentiumpro -O3 -fno-omit-frame-pointer' CXX=gcc
>
>
>---------------------------------------------------------------------
>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-thread110183@stripped>
>To unsubscribe, e-mail
> <mysql-unsubscribe-gerald_clark=suppliersystems.com@stripped>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>


Thread
timestamp(8) GROUP BY problemsitnikov27 May
  • Re: timestamp(8) GROUP BY problemGerald Clark28 May
  • Re: timestamp(8) GROUP BY problemEgor Egorov28 May
  • Re: timestamp(8) GROUP BY problemKeith C. Ivey28 May