Get the year out of timestamp sql

I have a problem extracting the year out of a mysql timestamp field. I managed to get it work with a datetime field using this method:

SELECT id FROM TABLE WHERE YEAR(creation_date) = 2010

CREATE TABLE IF NOT EXISTS `pub_media` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAUL开发者_运维问答T NULL,
  `title` text,
  `filename` text,
  `path` text,
  `serv_path` text,
  `type` enum('images','videos','files','audio','gallery') DEFAULT NULL,
  `keywords` text,
  `label_id` int(11) DEFAULT NULL,
  `creation_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `update_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  `rank` int(11) NOT NULL DEFAULT '0',
  `deleted` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Server version : 5.1.41 Is there a simalar way to perform this action on a timestamp field? I want to keep this action in my SQL statement and not try to move it to PHP or any other scripting language, if possible.

What problem are you encountering, and can you include the output of CREATE TABLE in your question? This works for me on MySQL 5.1.41-3ubuntu12.3:

DROP TABLE IF EXISTS `table1`;

CREATE TABLE `table1` (
    `id` int(11) NOT NULL auto_increment,
    `ts` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY  (`id`)
) ENGINE=InnoDB;

INSERT INTO table1 (id) VALUES (1);

SELECT * FROM table1;
+----+---------------------+
| id | ts                  |
+----+---------------------+
|  1 | 2010-07-05 15:32:11 |
+----+---------------------+

SELECT id FROM table1 WHERE YEAR(ts) = 2010;
+----+
| id |
+----+
|  1 |
+----+

Extract year from date or timestamp in more portable way:

SELECT id FROM table WHERE EXTRACT(year FROM creation_date) = 2010

Works with MySQL, PostgreSQL, Oracle...

use CAST function :)

SELECT id FROM TABLE WHERE YEAR(CAST(creation_date AS DATE))=2010

should work with creation_date timestamp

[[edit]] ,added () around cast

In my case cast() did not work. from_unixtime() did though. The query would be the following one:

SELECT id FROM TABLE WHERE YEAR(FROM_UNIXTIME(creation_date)) = 2010

The following works ok,

SELECT id FROM TABLE WHERE DATE_FORMAT( creation_date,  "%Y" ) = "2010"

Formatting the data is really useful for queries like this, I have used it numerous times for breaking data down to 10 minute intervals for example...

select * from table1 where year(ts) = 2016 and month(ts) = 9