On Wednesday, April 13, 2005 20:17, [EMAIL PROTECTED] wrote:
> MySql vers 4.0.20
>
> A table "noticeboard" has three test entries.
> A unix time from date("U") is stored in the field "published" of
> type, bigint20.
>
> I am not able to workout how to select the year. Assuming that the
> value from date("U") can be treated as a unixtime value, my latest
> unsuccessful effort is ....
>
> $yearslist = mysql_query("SELECT published,
> FROM_UNIXTIME(UNIX_TIMESTAMP(published),'%Y') AS year FROM
> noticeboard") or die("Cannot get list of years.<br>" .
> mysql_error());
>
> $counttotal = mysql_num_rows($yearslist); echo "<div>Rows found
> $counttotal</div>";
>
> while ($myrow = mysql_fetch_array($yearslist)) {
>
> if (ISSET($myrow['published'])){$published=$myrow['published'];}
>
> $yearpub=date("Y",$published);
>
> if (ISSET($myrow['year'])){
> $year=$myrow['year'];}
>
> else {echo "<div>Year is not set</div>";}
>
> echo "<div>Year $year but value was $published. Year value should be
> $yearpub</div>";
>
> }
>
> The result is
>
> Rows found 3
> Year 1969 but value was 1083923875. Year value should be 2004 Year
> 1969 but value was 1113300220. Year value should be 2005 Year 1969
> but value was 1113351870. Year value should be 2005
>
>
> Louise
My guess is that you are passing what is already a unix timestamp to
the function unix_timestamp, and since that is an invalid datetime it
returns zero which then causes from_unixtime to return 1969 (when epoch
time started in your time zone).
Try using FROM_UNIXTIME(published,'%Y')
--
Tom Crimmins
Interface Specialist
Pottawattamie County, Iowa
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
