Mysql - selecting year from a unix timestamp

Question

I am using this:

SELECT FROM_UNIXTIME(my_unix_timestamp_column, '%Y') AS year FROM table_name WHERE year = 2009;

but it gives me an error:

Unknown column 'year' in 'where clause'SELECT FROM_UNIXTIME(my_unix_timestamp_column, '%Y') AS year FROM table_name WHERE year = 2009

Both "my_unix_timestamp_column" and "table_name" are correct, i dont know why it gives me this!!!

I'm using PHP 5.3.0

Answer 1

You can't use a column created the SELECT section in your WHERE clause

replace the year variable in your where clause with the actual function to create that column (aka FROM_UNIXTIME(my_unix_timestamp_column, '%Y') ) and you should be fine.

This is because the SELECT section of your query isn't executed until the WHERE section has finished matching rows to return.

Answer 2

The WHERE part is executed before the aliasing in the field list. Best thing is to use BETWEEN in the WHERE clause so an index can be used.

Answer 3

SELECT  FROM_UNIXTIME(my_unix_timestamp_column, '%Y') AS `year`
FROM    table_name
HAVING  `year` = 2009

Unlike WHERE clause, HAVING clause can reference the SELECT clause aliases.

More index efficient way would be:

SELECT  FROM_UNIXTIME(my_unix_timestamp_column, '%Y') AS `year`
FROM    table_name
WHERE   my_unix_timestamp_column >= UNIX_TIMESTAMP('2009-01-01')
        AND my_unix_timestamp_column < UNIX_TIMESTAMP('2010-01-01')

Answer 4

I'm not quite sure whether this is due to YEAR being a reserved word in MySQL or because it wants you to do something along the lines of:

SELECT
  FROM_UNIXTIME(my_unix_timestamp_column, '%Y') AS year
FROM
  table_name
WHERE
  FROM_UNIXTIME(my_unix_timestamp_column, '%Y') = 2009;

Can't remember whether the last issue is only relevant to GROUPings :S

Answer 5

Another alternative, avoiding repetition of a biggish function call:

SELECT year
  FROM (SELECT FROM_UNIXTIME(my_unix_timestamp_column, '%Y') AS year
          FROM table_name) AS list_of_years
 WHERE year = 2009;

You might still need to use back-quotes around the word 'year' to avoid conflicts with YEAR as a keyword. The optimizer should not need to create an intermediate table to answer this query.