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?
>
Something like this?
mysql> show fields from y2k;
+-------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| d1 | date | YES | | NULL | |
| d2 | datetime | YES | | NULL | |
| d3 | timestamp(14) | YES | | NULL | |
+-------+---------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> ALTER TABLE y2k ADD time TIME;
mysql> UPDATE y2k SET time=right(d2, 8);
mysql> ALTER TABLE y2k CHANGE d2 date DATE;
Test it on a sample table and you'll see how it works.
jim...
