Simplify query to make TIMESTAMP column

Question

I have a table with columns of 'year', 'month', 'day', 'hour' and 'minute' separately.

All columns INTEGER except 'month' (VARCHAR) and I added a new column to put everything together, 'date_time' (TIMESTAMP).

+------+-------+------+------+--------+-----------+
| year | month |  day | hour | minute | date_time |
+------+-------+------+------+--------+-----------+
| 1987 | 12    |   15 |    9 |     25 |   <NULL>  |
| 1997 | 10    |   10 |   10 |     40 |   <NULL>  |
| 1994 | 08    |    9 |    6 |     30 |   <NULL>  |
+------+-------+------+------+--------+-----------+

I made a query to fill the 'date_time' column like this:

UPDATE dates
SET date_time =
    COALESCE(
        STR_TO_DATE(
            CASE WHEN
                CONCAT(
                    year,
                    '-',
                    CASE WHEN LPAD(month, 2, 0) BETWEEN 1 AND 12 THEN LPAD(month, 2, 0) ELSE NULL END,
                    '-',
                    CASE WHEN LPAD(day, 2, 0) BETWEEN 1 AND 31 THEN LPAD(day, 2, 0) ELSE NULL END,
                    ' ',
                    CASE WHEN LPAD(hour, 2, 0) BETWEEN 0 AND 23 THEN LPAD(hour, 2, 0) ELSE NULL END,
                    ':',
                    CASE WHEN LPAD(minute, 2, 0) BETWEEN 0 AND 59 THEN LPAD(minute, 2, 0) ELSE NULL END,
                    ':00'
                )
            LIKE '%NULL%' THEN NULL
            ELSE
                CONCAT(
                    year,
                    '-',
                    CASE WHEN LPAD(month, 2, 0) BETWEEN 1 AND 12 THEN LPAD(month, 2, 0) ELSE NULL END,
                    '-',
                    CASE WHEN LPAD(day, 2, 0) BETWEEN 1 AND 31 THEN LPAD(day, 2, 0) ELSE NULL END,
                    ' ',
                    CASE WHEN LPAD(hour, 2, 0) BETWEEN 0 AND 23 THEN LPAD(hour, 2, 0) ELSE NULL END,
                    ':',
                    CASE WHEN LPAD(minute, 2, 0) BETWEEN 0 AND 59 THEN LPAD(minute, 2, 0) ELSE NULL END,
                    ':00'
                )
            END,
            '%Y-%m-%d %H:%i:%s'
        ),
        '0000-00-00 00:00:00'
    );

It worked but... My question is if there's a simpler way to write a query to do the same thing.

LukStorms · Answer 1 · 2019-01-28T08:14:17.887

Putting a number or string in the "YYYYMMDD" format in the DATE() function results in a DATE.

And the MAKETIME() function accepts hours, minutes and seconds to return a TIME.

Then when you concat a DATE and a TIME with a space in between, the resulting string can be put in a DATETIME or TIMESTAMP.

UPDATE dates
SET date_time = CONCAT(DATE(year*10000 + month*100 + day),' ',MAKETIME(hour,minute,0));

A test on db<>fiddle here

score 0 · Answer 2 · answered Jan 27 '19 at 23:01

You can use STR_TO_DATE to convert a string into a datetime. The second argument is a format specifier, that you can use to describe the format of your input data. There are format specifiers available for some of your use cases, such as :

%c : Month in numeric e.g., 1, 2, 3…12
%e : Day of the month without leading zeros e.g., 1,2,…31
%k : Hour in 24-hour format without leading zero e.g., 0,1,2…23

I cannot see a format specifier for "minutes without leading zeros" and "seconds without leading zeros", but it does not seem to cause issue for MySQL to process them (I tested on MySQL 8.0).

So I guess that you could do :

UPDATE dates 
SET datetime = STR_TO_DATE(
    CONCAT(year, '-', month, '-', day, ' ', hour, ':', minute, ':', second),
    '%Y-%c-%e %k:%i:%s'
)

Simplify query to make TIMESTAMP column

2 Answers2