CODEWITHSUNDEEP
mysqldatetimeunix-timestamp
Roman
Roman

Reputation: 394

How to convert DATETIME to TIMESTAMP in mysql?

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?

Upvotes: 7

Views: 18518

Answers (2)

Pierre François
Pierre François

Reputation: 6083

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)

Upvotes: 3

Taras
Taras

Reputation: 582

I do not understand why do you need to convert DATETIME to TIMESTAMP.

You can use INT(11) field to store UNIX TIMESTAMPs converted from DATETIME using function UNIX_TIMESTAMP(your_datetime_field).

Note, according to documentation: http://dev.mysql.com/doc/refman/5.5/en/datetime.html

The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

Upvotes: 2

Related Questions