Mysql - 从 unix 时间戳中选择年份

Mysql - selecting year from a unix timestamp

提问by Sam

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!!!

“my_unix_timestamp_column”和“table_name”都是正确的，我不知道为什么它给了我这个！！！

I'm using PHP 5.3.0

我正在使用 PHP 5.3.0

回答by jensgram

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

我不太确定这是由于YEARMySQL 中的保留字还是因为它希望您执行以下操作：

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

不记得上一期是否仅与GROUPings相关：S

回答by Quassnoi

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

Unlike WHEREclause, HAVINGclause can reference the SELECTclause aliases.

与WHERE子句不同，HAVING子句可以引用SELECT子句别名。

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')

回答by Jonathan Leffler

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.

您可能仍需要在单词“year”周围使用反引号，以避免与 YEAR 作为关键字发生冲突。优化器不需要创建中间表来回答这个查询。

回答by Jehiah

You can't use a column created the SELECTsection in your WHEREclause

您不能使用SELECT在您的WHERE子句中创建该部分的列

replace the yearvariable 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.

用year实际函数替换where 子句中的变量以创建该列（又名FROM_UNIXTIME(my_unix_timestamp_column, '%Y')），你应该没问题。

This is because the SELECTsection of your query isn't executed until the WHEREsection has finished matching rows to return.

这是因为在SELECT该WHERE部分完成匹配要返回的行之前，不会执行该部分查询。

回答by johannes

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

WHERE 部分在字段列表中的别名之前执行。最好的办法是在 WHERE 子句中使用BETWEEN以便可以使用索引。