Timestamp conversion and column order

[chz013]

Hi,
I have the following questions. Any help is fully appreciated.

1 - I have a time column, which has a data type of longtext. It looks
like this in a record; 13:15
Q) Is there in any way that I could convert this longtext data type into
timestamp data type to only include
13:15
in the record.
My understanding is that timestamp only has the following display
formats:
TIMESTAMP(14) YYYYMMDDHHMMSS
TIMESTAMP(12) YYMMDDHHMMSS
TIMESTAMP(10) YYMMDDHHMM
TIMESTAMP(8) YYYYMMDD
TIMESTAMP(6) YYMMDD
TIMESTAMP(4) YYMM
TIMESTAMP(2) YY
I only want HH:MM


2) I added a new column a of data type date and drop column b of data
type longtext. However, column a is out of order with other columns in
the table. I like to restore that column order as in the original table.

Q) How do I do that and not having to create another new table ? Is it
possible ?

Many thanks

 

[sleipnir214]

1) Don't use a timestamp column. Use a time column, which has a format of HH:MM:SS.

Timestamp has a specific additional property -- it is automatically updated by MySQL every time the record is inserted or updated, which you probably don't want.


2) Why bother? The order of the columns is immaterial to a database.

But if you think you must, create a new column in the right place using the "ALTER TABLE ADD COLUMN ... AFTER column_name" syntax (

http://www.mysql.com/doc/en/ALTER_TABLE.html).

Copy the data from your column A to this new column. Drop column A.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[chz013]

sleipnir214
thanks for your reply.

1- Someone wrote a graphing code that uses TIMESTAMP as
a data type and not TIME. So I'm stuck with the option to
find ways to make TIMESTAMP to show HH:MM. I dont believe
there's a way. But I could be wrong.

2- I thought the column order is important of loading
data using LOAD DATA....command. The data file is already
structured to assume that the column order columns exist.
Am I right in this case that column order is important ?

 

[sleipnir214]

1. There is no way to reconfigure MySQL to display a TIMESTAMP column the way you want -- MySQL's designers work with the assumption that changing the display of a column is a data presentation issue, not a data issue, so is outside the scope of the product.

Assuming that the column in question is named "fred", you could perform a query like:

SELECT foo, bar, DATE_FORMAT(fred, '%I:%i') AS fred FROM tablename

then no app will ever be the wiser.


2. By default, yes. But if you look at MySQL's online documentation of the LOAD DATA command (

http://www.mysql.com/doc/en/LOAD_DATA.html),

you will see there is a final optional set of parameters you can feed the command: the names of the columns into which the data will be loaded.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!