Select distinct year from unix timestamp

[dr. zoidberg]

Hello,

I'm trying to select all distinct years from a unixtimestamp field in
MySQL database. I have a query:

SELECT DISTINCT YEAR(date_field ) As theYear FROM table

but it gives me an empty array. What am I doing wrong?

TNX

[Chris Hope]

Re: Select distinct year from unix timestamp

dr. zoidberg wrote:
[color=blue]
> I'm trying to select all distinct years from a unixtimestamp field in
> MySQL database. I have a query:
>
> SELECT DISTINCT YEAR(date_field ) As theYear FROM table
>
> but it gives me an empty array. What am I doing wrong?[/color]

Read the manual at
http://dev.mysql.com/doc/mysql/en/Da...functions.html

The year() function requires a date or datetime field, not a unix timestamp
field. You can use the from_unixtime() function to convert to a datetime
field but remember that every row in the database must have both functions
applied against them to get the distinct year values. If you have a lot of
records this may take some time.

--
Chris Hope
The Electric Toolbox - http://www.electrictoolbox.com/

[Gordon Burditt]

Re: Select distinct year from unix timestamp

>I'm trying to select all distinct years from a unixtimestamp field in[color=blue]
>MySQL database. I have a query:
>
>SELECT DISTINCT YEAR(date_field ) As theYear FROM table
>
>but it gives me an empty array. What am I doing wrong?[/color]

If it's a UNIX timestamp field (type int), rather than a timestamp field
(type datetime or timestamp), shouldn't that be:

SELECT DISTINCT YEAR(FROM_UNIXT IME(date_field) ) AS theYear FROM table;

Gordon L. Burditt