Search code examples
mysqltimestampvirtual

Create a timestamp virtual column from a text column

I need to add a virtual column in a MySQL table that stores a timestamp value generated from another column (of the same table).

I can't convert my existing column to timestamp because it could break many other processes so, to query faster my database, I need a column with the same data but with proper format.

I tried to create a virtual column with the default value of

TIMESTAMP('field_date_text')

but the result is NULL. The 'field_date_text' field has format "d/m/Y H:i"

What's the right syntax to add a virtual column as Timestamp from another field?

Solution

  • You can use TIMESTAMP() or UNIX_TIMESTAMP() and/or STR_TO_DATE():

    TIMESTAMP(STR_TO_DATE(field_date_text, '%d/%m/%Y %h:%i'))

    or

    UNIX_TIMESTAMP(STR_TO_DATE(field_date_text, '%d/%m/%Y %h:%i'))

    or just STR_TO_DATE():

    STR_TO_DATE(field_date_text, '%d/%m/%Y %h:%i')