CODEWITHSUNDEEP
mysqldatetime
foxns7
foxns7

Reputation: 518

Datetime to Integer

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?

Upvotes: 10

Views: 37653

Answers (2)

Chris
Chris

Reputation: 3121

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);

Upvotes: 16

Mchl
Mchl

Reputation: 62395

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

Upvotes: 6

Related Questions