Datetime to Integer

Question

I have one problem... There is a table in my MySQL database that stores to-do list entries inside a JQuery-type calendar.
I had to generate a calendar_id that will generate a reminder once I created timestamp (considered as time I clicked on any of the calender dateboxes, to key in some to-do tasks - put it simple: created datetime).

This to-do list activities app is an external application that I've been working on to integrate with my own management system. I noticed that,the timestamp column is in int(11) format, so whatever timestamp entered will be converted into integer.

For example, take a look at this:

2012-02-22 15:31:24

converted to

1329899400

How can we convert datetime to this format? It's not in seconds when I tried:

intval(floor($datetime/86400));

Any help?

Answer 1

FROM UNIXTIME can format UNIX timestamps into datetime fields:

SELECT FROM_UNIXTIME(time)
FROM ...

The reverse function would be UNIX_TIMESTAMP.

---

Alternatively you can do it in PHP, if available:
To store a date into the DB format it like this:

$datetimeStr = '2012-02-22 15:31:24';
$datetime = strtotime($datetimeStr);

To retrieve it from the DB and format it to the original format, use something like this:

$dateTimeFromDB = '1329921084';
$datetimeStr = date('Y-m-d H:i:s', $dateTimeFromDB);

Answer 2

Here's a nice MySQL's UNIX_TIMESTAMP() function for you