Mysql - selecting year from a unix timestamp
29,542
Solution 1
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 GROUP
ings :S
Solution 2
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')
Solution 3
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.
Author by
Admin
Updated on July 09, 2022Comments
-
Admin almost 2 years
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