Merging two columns of fields in MySQL [duplicate]

This question already has an answer here: Closed 11 years ago.

Possible Duplicate:

Merge date from one datetime and time from another datetime

How can I 开发者_如何学运维physically merge two MySQL column/fields into one column/field without losing any data on it

Specifically I will be combining a DATE and TIME column/field

thanks

---

Add your timestamp field, initialize it with the timestamp function, and then once you feel safe, drop the old columns. Something like this:

alter table your_table add timestamp_column timestamp;
update your_table set timestamp_column = timestamp(date_column, time_column);
-- Pause and make sure everything is okay.
-- Take your time, the database will wait.
alter table your_table drop column date_column;
alter table your_table drop column time_column;
alter table your_table modify timestamp_column timestamp not null;

---

By "merge", do you mean "add"? It sounds like you've got the date and time in separate fields, and so putting them together is the same thing as adding them.

If that's the case, you can use the MySQL's ADDTIME() function:

mysql> SELECT ADDTIME(aDate, aTime) FROM aTable;

If you mean to concatenate them, then MySQL has a CONCAT() function... But it doesn't seem suited for your purpose.

--edited--

If it's another column you're after, it's a two step process:

-- Add the column
mysql> ALTER TABLE aTable ADD COLUMN aDateTime DateTime DEFAULT NULL;
-- Insert the data
mysql> UPDATE aTable SET aDateTime = ADDTIME(aDate, aTime);

But after this point, you'll have to maintain it in all three columns. That is, aDateTime won't automatically update when you add/change aDate or aTime.

---

i dont wanna if you want to create a new field but just in case you wanna do it

alter table t add yournewfield datetime not null; 
UPDATE table t  SET younerwfield = addtime(t.date,t.time);