List:General Discussion« Previous MessageNext Message »
From:Jim Faucette Date:July 26 1999 6:22pm
Subject:Re: Converting DATETIME to DATE, TIME
View as plain text  
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...
Thread
Converting DATETIME to DATE, TIMEChris Trown26 Jul
  • Re: Converting DATETIME to DATE, TIMEScott Hess26 Jul
  • Re: Converting DATETIME to DATE, TIMEJim Faucette26 Jul
  • RE: Converting DATETIME to DATE, TIMEDon Read28 Jul