MySQL query to copy date from different columns

Question

I have the following 6 integer db columns:

• Year
• Day
• Month
• Hour
• Minute
• Second

I need a query that takes these values for every row, combines them into a DATETIME and puts into another column.

Answer 1

You can put them all into a string and then convert that string to datetime, like so:

update tbl
set datecol = cast(cast(`year` as varchar) + '-' +
              cast(`month` as varchar) + '-' + 
              cast(`day` as varchar) + ' ' +
              cast(`hour` as varchar) + ':' +
              cast(`minute` as varchar) + ':' +
              cast(`second` as varchar) as datetime)

Although, I think MySQL uses concat, so it would be:

cast(concat(cast(`year` as varchar), '-', cast(`month` as varchar), '-',...) as datetime)

Answer 2

It might not be the most efficient but you could do this:

UPDATE my_table
SET my_date = CONCAT(my_year, '-', my_month, '-', my_day, ' ',
    my_hour, ':', my_mins, ':', my_seconds)
WHERE ...

Answer 3

You can use CONCAT your data together to make something that looks like a DATETIME value

CONCAT(year,'-', month, '-', day,' ',hour, ':', min, ':', sec)

While you could use that directly, you could also turn into a real DATETIME by using STR_TO_DATE with it, e.g.

STR_TO_DATE(
   CONCAT(year,'-', month, '-', day,' ',hour, ':', min, ':', sec),
   '%Y-%m-%d %H:%i:%S');

Answer 4

CREATE TABLE `x` (
  `y` int(4) DEFAULT NULL,
  `m` int(4) DEFAULT NULL,
  `d` int(4) DEFAULT NULL,
  `h` int(4) DEFAULT NULL,
  `i` int(4) DEFAULT NULL,
  `s` int(4) DEFAULT NULL
);
INSERT INTO x SELECT 2009, 9, 1, 9, 59, 59;
SELECT STR_TO_DATE(CONCAT(y,'-', m,'-', d,'-', h,'-', i,'-', s), '%Y-%m-%d-%h-%i-%s') FROM x;