MySQL 从时间戳 sql 中获取年份
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3180086/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get the year out of timestamp sql
提问by Sam Vloeberghs
I have a problem extracting the year out of a mysql timestamp field. I managed to get it work with a datetime fieldusing this method:
我在从 mysql 时间戳字段中提取年份时遇到问题。我设法使用此方法使其与日期时间字段一起使用：
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.
服务器版本：5.1.41 是否有类似的方式在时间戳字段上执行此操作？如果可能，我想将此操作保留在我的 SQL 语句中，而不是尝试将其移动到 PHP 或任何其他脚本语言。
回答by Mike
What problem are you encountering, and can you include the output of CREATE TABLEin your question? This works for me on MySQL 5.1.41-3ubuntu12.3:
你遇到什么问题，你能CREATE TABLE在你的问题中包括输出吗？这在 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 |
+----+
回答by Juampy NR
In my case cast() did not work. from_unixtime() did though. The query would be the following one:
就我而言， cast() 不起作用。不过 from_unixtime() 做到了。查询将是以下之一：
SELECT id FROM TABLE WHERE YEAR(FROM_UNIXTIME(creation_date)) = 2010
回答by dweeves
use CASTfunction :)
使用CAST功能:)
SELECT id FROM TABLE WHERE YEAR(CAST(creation_date AS DATE))=2010
SELECT id FROM TABLE WHERE YEAR(CAST(creation_date AS DATE))=2010
should work with creation_date timestamp
应该与 creation_date 时间戳一起使用
[[edit]] ,added () around cast
[[edit]] , 在演员周围添加 ()
回答by user11153
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...
适用于 MySQL、PostgreSQL、Oracle...
回答by Dave Rix
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...
格式化数据对于这样的查询非常有用，我已经多次使用它来将数据分解为 10 分钟的间隔，例如......
回答by Mahesh
select * from table1 where year(ts) = 2016 and month(ts) = 9
