Convert dd/mm/yyyy string to Unix timestamp in MySQL

Question

In my table I have a varchar column called date containing string representations of dates in dd/mm/yyyy format. How can I convert these to Unix times in a SELECT query?

You probably shouldn't be storing dates as dd/mm/yyyy strings in the first place. MySQL has a DATE type for storing dates; you should always use that to represent dates unless you have a good reason to do otherwise. If you were doing that, then you'd be able to get your Unix timestamp by just doing SELECT UNIX_TIMESTAMP(date) FROM yourtable. Since you're not storing your dates as dates in the first place, though, there's an extra conversion step (shown in the accepted answer) which makes things just a little more complicated. — Mark Amery
– Mark Amery, Commented Nov 28, 2015 at 19:30

ADW · Accepted Answer · 2011-05-30 16:12:14Z

25

select unix_timestamp(str_to_date('30/05/2011','%d/%m/%Y'));

or:

select unix_timestamp(str_to_date(myfield,'%d/%m/%Y')) from mytable;

answered May 30, 2011 at 16:12

ADW

4,08019 silver badges13 bronze badges

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

Comments

kuriouscoder · Accepted Answer · 2011-05-30 16:11:36Z

2

I think UNIX_TIMESTAMP should do the trick. Can you specify your select query here?

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_unix-timestamp

answered May 30, 2011 at 16:11

kuriouscoder

5,6428 gold badges30 silver badges41 bronze badges

1 Comment

Zizo Over a year ago

Here is what I did select unix_timestamp(str_to_date('date','%d/%m/%Y')) from tbl; Where date is column name. And the result is null

Mahdi Majidzadeh · Accepted Answer · 2017-04-13 07:33:23Z

0

just use unix_timestamp function

answered Apr 13, 2017 at 7:33

Mahdi Majidzadeh

8781 gold badge16 silver badges27 bronze badges

Collectives™ on Stack Overflow

Convert dd/mm/yyyy string to Unix timestamp in MySQL

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related