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

Question

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!

forpas · Accepted Answer · 2020-08-11 18:26:47Z

2

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`

answered Aug 11, 2020 at 18:26

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Michael Over a year ago

Yeah this worked, thanks! I had already tried with the slashes in place previously but it didn't work. It seems changing %M to %m and %h and %H made the difference.

forpas Over a year ago

m is used for double digit numeric month and H for 24H format. Check the link of the function STR_TO_DATE() in my answer for the proper format.

Michael Over a year ago

How would I go about running a query to update all the fields to be the result of this query? If I run this: UPDATE playerinfractions` SET InfractionIssuedTimestamp = (SELECT UNIX_TIMESTAMP(STR_TO_DATE(CONCAT(InfractionDate, " ", InfractionTime), '%d/%m/%Y %H:%i')) FROM playerinfractions);` it finds all rows and gives the error #1242 - Subquery returns more than 1 row. What kind of WHERE clause would I factor in here?

forpas Over a year ago

No need for SELECT:

SET InfractionIssuedTimestamp = UNIX_TIMESTAMP(STR_TO_DATE(CONCAT(InfractionDate, " ", InfractionTime), '%d/%m/%Y %H:%i'))

Michael Over a year ago

Ah I see, I was thinking perhaps a subquery wasn't the way to go, thank you again!

RiggsFolly · Accepted Answer · 2020-08-11 18:31:19Z

1

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`

answered Aug 11, 2020 at 18:31

RiggsFolly

94.7k22 gold badges108 silver badges152 bronze badges

Comments

Vishweshwar · Accepted Answer · 2020-08-11 18:42:59Z

1

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'))

answered Aug 11, 2020 at 18:42

Vishweshwar

565 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related