Converting date and time TEXT fields to one INT unix timestamp field in SQL

I currently have a mySQL database with two TEXT fields: Date and Time. These are in the format ‘dd/mm/yyyy’ and ‘0:00′ respectively, for example ’11/08/2020’ and 19:12. I want to create a third field called Timestamp (of type INT) and convert the two original fields into this timestamp field and remove the date/time text fields.

I have done a bit of research in regards to using UNIX_TIMESTAMP() and STR_TO_DATE() but I can’t seem to get it to work, the format seems to be wrong for it.

How can I achieve this in SQL and convert two string fields which represent date and time into a third field to replace them both which just stores the unix timestamp?

This is my best attempt so far..

SELECT UNIX_TIMESTAMP(STR_TO_DATE(CONCAT(`InfractionDate`, " ", `InfractionTime`), '%d %M %Y %h:%i%p')) FROM `playerinfractions`

The table is called playerinfractions and the date/time are stored in the TEXT fields InfractionDateand InfractionTime.

Many thanks in advance!

Answer

The format pattern that you use in the function STR_TO_DATE()is wrong.
Try this:

SELECT 
  UNIX_TIMESTAMP(
    STR_TO_DATE(
      CONCAT(`InfractionDate`, ' ', `InfractionTime`), 
      '%d/%m/%Y %H:%i')
  )     
FROM `playerinfractions`

Advertisement

Answer