Thread: Mysql date/time, was Re: Press Release -- Just Waiting for Tom
On Tuesday 19 Nov 2002 2:43 pm, Jason Hihn wrote: > Interesting treatment of Date/Time? +-------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------+------+-----+---------+-------+ | a | int(11) | YES | | NULL | | | b | timestamp(14) | YES | | NULL | | +-------+---------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> INSERT INTO foo VALUES (1,now()); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO foo VALUES (2,null); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO foo VALUES (3,'abcd'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO foo VALUES (4,''); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM foo; +------+----------------+ | a | b | +------+----------------+ | 1 | 20021120104314 | | 2 | 20021120104326 | | 3 | 00000000000000 | | 4 | 00000000000000 | +------+----------------+ 4 rows in set (0.01 sec) mysql> select version(); +-----------+ | version() | +-----------+ | 3.23.49 | +-----------+ The last case caught me out in real code. The handling of null can't be right, and case 3,4 store an invalid date/time. Not sure if it's been changed in more recent versions, but it was considered a feature. -- Richard Huxton
Oh, that is interesting... But that is not a datetime. Timestamp (in case you don't know, though you probably do already)will automatically update itself when the row is edited, but it only works on the first one. I learned that thehard way. They consider this a "feature" as well, but it looks intentional... -J -----Original Message----- From: Richard Huxton [mailto:dev@archonet.com] Sent: Wednesday, November 20, 2002 5:48 AM To: Jason Hihn Cc: pgsql-advocacy@postgresql.org Subject: Mysql date/time, was Re: [pgsql-advocacy] Press Release -- Just Waiting for Tom On Tuesday 19 Nov 2002 2:43 pm, Jason Hihn wrote: > Interesting treatment of Date/Time? +-------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------+------+-----+---------+-------+ | a | int(11) | YES | | NULL | | | b | timestamp(14) | YES | | NULL | | +-------+---------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> INSERT INTO foo VALUES (1,now()); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO foo VALUES (2,null); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO foo VALUES (3,'abcd'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO foo VALUES (4,''); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM foo; +------+----------------+ | a | b | +------+----------------+ | 1 | 20021120104314 | | 2 | 20021120104326 | | 3 | 00000000000000 | | 4 | 00000000000000 | +------+----------------+ 4 rows in set (0.01 sec) mysql> select version(); +-----------+ | version() | +-----------+ | 3.23.49 | +-----------+ The last case caught me out in real code. The handling of null can't be right, and case 3,4 store an invalid date/time. Not sure if it's been changed in more recent versions, but it was considered a feature. -- Richard Huxton
On Wednesday 20 Nov 2002 2:02 pm, Jason Hihn wrote: > Oh, that is interesting... But that is not a datetime. Doh - operating a keyboard without engaging the brain first. mysql> insert into foo2 values (1,now()); mysql> insert into foo2 values (2,''); mysql> insert into foo2 values (3,'asdf'); mysql> select * from foo2; +------+---------------------+ | a | b | +------+---------------------+ | 1 | 2002-11-20 15:07:54 | | 2 | 0000-00-00 00:00:00 | | 3 | 0000-00-00 00:00:00 | +------+---------------------+ -- Richard Huxton
FWIW: I'd prefer not to see unproductive criticism of MySQL in this forum. It's not helpful in promoting PostgreSQL. Further, if members of the press should browse our archives, we'll see "MySQL - PostgreSQL Feud Escalates" on Slashdot, which will muddle our attempts to get our message in print, i.e.: Justin: "Version 7.4 has been released, with point-in-time recovery ... " Reporter: "Are these new features intended as an attack on MySQL?" I agree that the MySQL AB staff have been poor "team players" in the Open Source world. However, that does not give us the excuse to do the same. (This is only my opinion -- I am *not* moderator of this list) -Josh Berkus
On Wednesday 20 Nov 2002 5:01 pm, Josh Berkus wrote: > FWIW: > > I'd prefer not to see unproductive criticism of MySQL in this forum. > It's not helpful in promoting PostgreSQL. Further, if members of > the press should browse our archives, we'll see "MySQL - PostgreSQL > Feud Escalates" on Slashdot, which will muddle our attempts to get our > message in print, i.e.: > > Justin: "Version 7.4 has been released, with point-in-time recovery ... > " > Reporter: "Are these new features intended as an attack on MySQL?" > > I agree that the MySQL AB staff have been poor "team players" in the > Open Source world. However, that does not give us the excuse to do > the same. > > (This is only my opinion -- I am *not* moderator of this list) Apologies - I was replying to a query and should have taken it off list. -- Richard Huxton
On 20 Nov 2002 at 9:01, Josh Berkus wrote: > I agree that the MySQL AB staff have been poor "team players" in the > Open Source world. However, that does not give us the excuse to do > the same. > > (This is only my opinion -- I am *not* moderator of this list) Well said.. I agree with that wholeheartedly.. Bye Shridhar -- Van Roy's Law: Honesty is the best policy - there's less competition.Van Roy's Truism: Life is a whole series of circumstances beyond your control.