Mysql select - Get the dates of timestamps

At the mysql table, there are stored values of timestamps (like 1265138145). What i want is to display the dates (eg 27/2/2011,10/3/2011,15/3/2011, 16/03/2011 etc) which belong to these timestamps. Is this 开发者_开发问答possible? (but only display one time the date, eg if there is 1265138145 and 1265138140 then display only one time the date - which is 16/3)

---

There are a variety of ways of doing this, the FROM_UNIXTIME command probably being the easiest.

For example: SELECT FROM_UNIXTIME(<timestamp field>, '%d/%m/%Y');

I'm not sure what you mean about "only display one time the date", but using DISTINCT on the necessary column should help.

i.e.: SELECT DISTINCT(FROM_UNIXTIME(<timestamp field>, '%d/%m/%Y')); may be all you require.

---

From within MySQL, use ADDDATE and interval of unixtimestamp seconds to the epoch, e.g.

select adddate('1970-01-01', interval 1265138145 second)

then display only one time the date

Use DISTINCT in your query, e.g.

select distinct date(adddate('1970-01-01', interval 1265138145 second))
from tbl ..

Both queries above return the column as a datetime value, which you can apply default formatting to in PHP.

---

Note about using FROM_UNIXTIME - you get your local UTC offset added to the time, which is unlikely to be what you want, unless the data was populated using UNIX_TIMESTAMP in the first place.

FROM_UNIXTIME: Returns a representation of the unix_timestamp argument as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS.uuuuuu format, depending on whether the function is used in a string or numeric context. The value is expressed in the current time zone

---

<?php
    print date("d/m/Y", $timestamp);
?>

http://us.php.net/manual/en/function.date.php

---

Fetch your data and use just date() function.

echo date('d/m/Y', $row['date']);

---

use FROM_UNIXTIME(unix_timestamp), FROM_UNIXTIME(unix_timestamp,format) http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_from-unixtime

in mySQL statement