Sounds like there is a misunderstanding about what exactly a data type can
do. What you can't do is change the 'type' of a column with an UPDATE
command. You can do it with an ALTER TABLE command, but this would be a
_misuse_ of the command, given what you are trying to do.
You have one of 2 options. Add a 'datetime' column to the table (let's call
it start_date) and then issue the command:
Add the new column (one time operation to create the column in your table)
ALTER TABLE table ADD start_date datetime DEFAULT NULL;
Now, your human readable column will be start_date:
UPDATE table SET start_date = FROM_UNIXTIME(start);
Of course, this is all really quite unnecessary, since when you select the
data in some other part of your application, you can do the conversion at
that time. For example
SELECT FROM_UNIXTIME(start) FROM table;
or even...
SELECT FROM_UNIXTIME( start, '%m/%d/%Y' ) as start_date FROM table;
For more information go to
http://www.mysql.com/doc/en/Date_and_time_functions.html
Hope that helps,
Erik Osterman
http://osterman.com/
-----Original Message-----
From: Ron McKeever [mailto:[EMAIL PROTECTED]
Sent: Saturday, November 08, 2003 7:26 AM
To: [EMAIL PROTECTED]
Subject: Update table with UNIX_TIMESTAMP
Hello
I have a table that gets appended to every night with the LOAD DATA command.
The column 'start' is a unix timestamp. So when the LOAD DATA is done I then
want to update the Column to be human readable.
I was thinking of the following:
UPDATE table SET start = UNIX_TIMESTAMP(start);
I issue I'm trying to figure out is I dont want this update command to run
on rows that have already been
converted. What would someone suggest? thanks
Ron
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
