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
精彩评论