convert string to timestamp in MySQL query [duplicate]

Question

Is there any way to convert string to timestamp in MySQL?

For example I have string 01-05-2016 10:22 am which needs to be fetch with order by desc .

is it possible to sort that ?

What date is that? Is that the 1st of May or the 5th of January? — Mark Baker
– Mark Baker, Commented Dec 1, 2017 at 11:00
My database data type in varchar .. it fetch like 02-26-2015 10:40 am in order_date field .. i want to sort that field desc .. how to ? — G Roy
– G Roy, Commented Dec 4, 2017 at 4:46
Best option is for you to change from a varchar to a date/time datatype; then you can just apply a simple ORDER BY clause — Mark Baker
– Mark Baker, Commented Dec 4, 2017 at 8:00
not possible .. the requirement is store data as this format ( mm-dd-yyyy hh:mm a ) and fetch order by desc — G Roy
– G Roy, Commented Dec 5, 2017 at 5:10
@G Roy - Then you should be saying that the requirement is bad..... what is the business justification for treating strings as dates instead of using actual dates? — Mark Baker
– Mark Baker, Commented Dec 5, 2017 at 7:57

jasinth premkumar · Accepted Answer · 2017-12-01 11:03:47Z

0

use order by function in SQL

SELECT * FROM table_name order by datetime DESC;

this will return values in descending order to php. no need to be sorted again.

answered Dec 1, 2017 at 11:03

jasinth premkumar

1,4111 gold badge13 silver badges23 bronze badges

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

Comments

Sushant Bassi · Accepted Answer · 2017-12-01 11:20:50Z

0

UNIX_TIMESTAMP does the trick.

and

If you want to use the AM/PM notation, you will need to use STR_TO_DATE.

SELECT UNIX_TIMESTAMP(
    STR_TO_DATE(' 2016-05-01 10:22 am', '%Y-%m-%d %h:%i%p')
);

answered Dec 1, 2017 at 11:20

Sushant Bassi

3794 silver badges16 bronze badges

..its not working with SELECT UNIX_TIMESTAMP( STR_TO_DATE(' 2016-05-01 10:22 am', '%Y-%m-%d %h:%i%p') );