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