Convert SQL string field into unix timestamp

Question

I have a field, 'mydate' in my database which displays the date as a string like this

'8/1/2015 12:35:22 PM'

I am trying to convert this into a unix timestamp i have tried

cast(UNIX_TIMESTAMP('mydate',"yyyy-MM-dd HH:mm:ss.SSSS")) AS new_date

This has resulted in the query not completing completely. How can i convert this 'mydate' field into a unix timestamp.

Scath · Accepted Answer · 2018-01-08 14:32:08Z

2

Try something like this:

UNIX_TIMESTAMP(STR_TO_DATE(mydate, '%M %e %Y %h:%i%p'))

answered Jan 8, 2018 at 14:32

Scath

3,81710 gold badges32 silver badges40 bronze badges

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

Comments

Lokesh Kumar Gaurav · Accepted Answer · 2018-01-09 05:58:38Z

1

You can simply check in mysql string format -

SELECT STR_TO_DATE('8/1/2015 11:35:22 PM','%d/%m/%Y %h:%i:%s %p');

answered Jan 9, 2018 at 5:58

7261 gold badge8 silver badges24 bronze badges