How to convert a string (in three separate columns) to a timestamp(one column) MYSQL

Question

Hi i'm a new coder and messed up on my sql table. Instead of storing my date with a timestamp I made the date in three separate columns: day, month, and year. I now realized that I need these in a timestamp. So I can perform more complicated queries.

Here is what I need the UPDATE to look like:

UPDATE coding_tracker SET coded_at = column(day)"/"column(month_number(month))"/"column(year);

Thank you in advance

Answer 1

Assuming your columns are called day, month_number and year, this query should work:

UPDATE coding_tracker SET coded_at = STR_TO_DATE(CONCAT_WS('/', day, month_number, year), '%d/%m/%Y')

In the case where your month column is a name, you can change %m in the above query to %b for short month names (Jan..Dec) or %M for long month names (January..December) e.g. for long names:

UPDATE coding_tracker SET coded_at = STR_TO_DATE(CONCAT_WS('/', day, month, year), '%d/%M/%Y')

Documentation about formats for STR_TO_DATE can be found in the DATE_FORMAT section of the MySQL manual.