CODEWITHSUNDEEP
phpmysqldatetimestamp
Man_E
Man_E

Reputation: 81

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)

Upvotes: 2

Views: 6624

Answers (5)

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

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

Upvotes: 2

John Parker
John Parker

Reputation: 54445

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.

Upvotes: 3

bensiu
bensiu

Reputation: 25564

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

Upvotes: 0

hsz
hsz

Reputation: 152216

Fetch your data and use just date() function.

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

Upvotes: 0

dearlbry
dearlbry

Reputation: 3211

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

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

Upvotes: 0

Related Questions