How to update a unixtime column based on a Timestamp column?

Question

In MariaDB 10, I want to fill slug column with unixtime based on created_at column, which is formatted like 2017-09-07 02:39:18

Here is the query that I came up with based on this answer:

UPDATE joke SET slug = UNIX_TIMESTAMP(STR_TO_DATE(created_at, '%Y %m %d  %h:%i:%s'));

But the result is 0 for all column. How can I fix it?

Answer 1

Remove STR_TO_DATE part you don't need string to date conversion if you already have a date object stored in your created_at column

UPDATE joke SET slug = UNIX_TIMESTAMP(created_at);

UNIX_TIMESTAMP(date)