Sam Vloeberghs Sam Vloeberghs - 3 months ago 9
MySQL Question

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) DEFAULT 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.

Answer

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