Search code examples
mysqlconcatenationepoch

Convery a String to EPOCH MySQL

I have a table with three columns, birth_day, birth_month, and birth_year. All three are INT. I'd like to CONCAT() these and convert them to EPOCH and save them back into a new column.

To CONCAT() these values I've used

SELECT CONCAT(birth_month, "/", birth_day, "/", birth_year) AS new_birthday from birthdaytable

I'm getting stuck on how I would convert this to EPOCH. I would try and cast new_birthday to a day then perform some calculations on this but some of the years are outside of the DATE/YEAR range for MySQL (some are before 1901)

Thoughts?

Solution

  • You'll have to change your CONCAT first, and combine that with UNIX_TIMESTAMP

    SELECT UNIX_TIMESTAMP(CONCAT(birth_year, "-", IF(birth_month < 10, CONCAT('0', birth_month), birth_month), "-", IF(birth_day < 10, CONCAT('0', birth_day), birth_day))) AS newField from birthdaytable