mysql cast(timestamp as date)_如何在MySQL中将DATETIME转换为TIMESTAMP？

I have DATETIME column in my table, with 2015-04-23 11:17:49 properties

Trying to convert it to unix timestamp, acording to the mysql documentation I need just put the field into UNIX_TIMESTAMP() function and I'll get -> 1223423442 - timestamp but it's doesn't work, I've got only 0000-00-00 00:00:00

Tried a lot of stuff:

// doesn't work

UNIX_TIMESTAMP(CAST(`updated` AS CHAR(100))) AS updated_at,

// doesn't work

UNIX_TIMESTAMP(`updated`) AS updated_at,

//doesn't work

UNIX_TIMESTAMP(STR_TO_DATE(CAST(`created` AS CHAR(100)), \'%M %e %Y %h:%i%p\'))

AS created_at'

// doesn't work

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

AS created_at

Without `` doesn't work as well, am I missing something?

解决方案

Try:

select

o1.id,

o1.operation_date_time,

(unix_timestamp(o2.operation_date_time) - unix_timestamp(o1.operation_date_time))

as duration

from operations as o1

inner join operations as o2

where o1.operation = "START"

and o2.operation = "STOP"

and o1.id = (o2.id - 1);

It should give as output:

+------+---------------------+----------+

| id | operation_date_time | duration |

+------+---------------------+----------+

| 1 | 2000-01-01 06:30:45 | 4455 |

| 3 | 2000-01-01 08:18:12 | 11146 |

| 5 | 2000-01-01 15:45:01 | 11792 |

+------+---------------------+----------+

3 rows in set (0.00 sec)