By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
439,977 Members | 1,352 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 439,977 IT Pros & Developers. It's quick & easy.

Select distinct year from unix timestamp

P: n/a
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
Jul 17 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
dr. zoidberg wrote:
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?


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/
Jul 17 '05 #2

P: n/a
>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?


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_UNIXTIME(date_field)) AS theYear FROM table;

Gordon L. Burditt
Jul 17 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.