List:General Discussion« Previous MessageNext Message »
From:Don Read Date:July 27 1999 10:54pm
Subject:RE: Converting DATETIME to DATE, TIME
View as plain text  
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
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