On 26-Jul-99 Chris Trown wrote:
>
>
> This relates to the question I asked last week about indexing,
> functions,
> etc... on datetime fields
>
> I'm thinking about changing one of the column definitions. Currently it
> is a DATETIME and I would like to change it to two columns defined as DATE
> and
> TIME. After a bit of tinkering with ALTER on a test table, it seems to me
> that
> the only way to do that is to dump the table, drop it, recreate the table
> then
> pass it through a PERL script that splits off the two fields.
>
> That the only way?
>
> Alternatively, I could just drop an index on the DATETIME field and use
> a
> lot of between-type queries. That seems a little klunky though.
>
> I'm reluctant to make any changes to theis table as it is rather large
> and
> resides on a slow machine with just a SCSI-2 disk.
>
> Anybody ever face this sort of dilemma?
>
mysql> select * from detable;
+------+------+---------------------+
| id | name | dt |
+------+------+---------------------+
| 1 | don | 1999-07-01 12:23:13 |
| 2 | bob | 1999-07-02 14:15:39 |
+------+------+---------------------+
2 rows in set (0.00 sec)
mysql> alter table detable add d date after dt;
Query OK, 2 rows affected (0.52 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> alter table detable add t time after d;
Query OK, 2 rows affected (0.70 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> update detable set d=left(dt,10);
Query OK, 2 rows affected (0.52 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> update detable set t=right(dt,8);
Query OK, 2 rows affected (0.41 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from detable;
+------+------+---------------------+------------+----------+
| id | name | dt | d | t |
+------+------+---------------------+------------+----------+
| 1 | don | 1999-07-01 12:23:13 | 1999-07-01 | 12:23:13 |
| 2 | bob | 1999-07-02 14:15:39 | 1999-07-02 | 14:15:39 |
+------+------+---------------------+------------+----------+
2 rows in set (0.55 sec)
mysql> alter table detable drop dt;
Query OK, 2 rows affected (0.52 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from detable;
+------+------+------------+----------+
| id | name | d | t |
+------+------+------------+----------+
| 1 | don | 1999-07-01 | 12:23:13 |
| 2 | bob | 1999-07-02 | 14:15:39 |
+------+------+------------+----------+
2 rows in set (0.48 sec)
Regards,
---
Don Read dread@stripped
EDP Manager dread@stripped
Calcasieu Lumber Co. Austin TX
-- But I'm in good company, sendmail has kicked a great many
butts in the past
