try timestamp(14) instead. [EMAIL PROTECTED] 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 <[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
