mysql merge two columns into timestamp

Question

Hi I have a mysql table that looks like so:

date        time
2009-07-31  02:30
2009-07-31  02:45
2009-07-31  03:00

'date' column is a date field and 'time' column is a text field.

I would like to, using mysql only, merge these into a third column, 'datetime' which is a timestamp field.

So the end result would look like this:

date        time     datetime
2009-07-31  02:30    2009-07-31 02:30
2009-07-31  02:45    2009-07-31 02:45
2009-07-31  03:00    2009-07-31 03:00

Not sure how to go about this. Any suggestions?

fancyPants · Accepted Answer · 2012-08-30 18:40:32Z

2

Easy...

ALTER TABLE yourTable ADD COLUMN `datetime` timestamp;

UPDATE yourTable SET `datetime` = CONCAT(`date`, '  ', `time`);

Although I might add that reserved keywords like date, time and datetime are not good column names.

answered Aug 30, 2012 at 18:40

fancyPants

52.1k34 gold badges94 silver badges100 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

rrehbein · Accepted Answer · 2012-08-30 18:40:38Z

1

MySQL does have some 'loose type' behavior, so you can merge them as strings, and set the resulting string to the date-time column.

update `my_table` set `datetime`=concat(`date`, ' ', `time`);

Not all of the back-ticks are needed, but used to clarify the query.

answered Aug 30, 2012 at 18:40

rrehbein

4,1701 gold badge20 silver badges23 bronze badges