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!
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`
You need to tell STR_TO_DATE the correct format of your date and time, which you suggested was '%d/%m/%Y %H:%i'
SELECT UNIX_TIMESTAMP(
STR_TO_DATE(
CONCAT('10/08/2020', ' ', '12:20'), '%d/%m/%Y %H:%i' )
)
)
FROM `playerinfractions`
So using your columns
SELECT UNIX_TIMESTAMP(
STR_TO_DATE(
CONCAT(`InfractionDate`, ' ', `InfractionTime`), '%d/%m/%Y %H:%i' )
)
)
FROM `playerinfractions`
I tried passing a string to the same function in below format and it worked. Also you can share your format to check it further.
select UNIX_TIMESTAMP(STR_TO_DATE(CONCAT('8/12/2020',' ', '12:01:49' ),'%m/%d/%Y %h:%i:%s'))