从 MySQL 中的日、月、年字段创建日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3960049/
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
Create date from day, month, year fields in MySQL
提问by TGuimond
I am currently developing an application that displays documents and allows the members to search for these documents by a number of different parameters, one of them being date range.
我目前正在开发一个显示文档的应用程序，并允许成员通过许多不同的参数搜索这些文档，其中一个参数是日期范围。
The problem I am having is that the database schema was not developed by myself and the creator of the database has created a 'date' table with fields for 'day','month','year'.
我遇到的问题是数据库模式不是由我自己开发的，并且数据库的创建者创建了一个包含“日”、“月”、“年”字段的“日期”表。
I would like to know how I can select a specific day, month, year from the table and create a date object in SQL so that I can compare dates input by the user using BETWEEN.
我想知道如何从表中选择特定的日、月、年并在 SQL 中创建一个日期对象，以便我可以使用 BETWEEN 比较用户输入的日期。
Below is the structure of the date table:
下面是日期表的结构：
CREATE TABLE IF NOT EXISTS `date` (
`deposition_id` varchar(11) NOT NULL default '',
`day` int(2) default NULL,
`month` int(2) default NULL,
`year` int(4) default NULL,
PRIMARY KEY (`deposition_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
采纳答案by Sachin Shanbhag
You can use STR_TO_DATE()function.
您可以使用STR_TO_DATE()函数。
回答by Joe Holt
When you have integer values for year, month and day you can make a DATETIME by combining MAKEDATE() and DATE_ADD(). MAKEDATE() with a constant day of 1 will give you a DATETIME for the first day of the given year, and then you can add to it the month and day with DATE_ADD():
当您有年、月和日的整数值时，您可以通过组合 MAKEDATE() 和 DATE_ADD() 来创建 DATETIME。MAKEDATE() 的固定天数为 1 将为您提供给定年份第一天的 DATETIME，然后您可以使用 DATE_ADD() 将月份和日期添加到其中：
mysql> SELECT MAKEDATE(2013, 1);
+-------------------+
| MAKEDATE(2013, 1) |
+-------------------+
| 2013-01-01 |
+-------------------+
mysql> SELECT DATE_ADD(MAKEDATE(2013, 1), INTERVAL (3)-1 MONTH);
+---------------------------------------------------+
| DATE_ADD(MAKEDATE(2013, 1), INTERVAL (3)-1 MONTH) |
+---------------------------------------------------+
| 2013-03-01 |
+---------------------------------------------------+
mysql> SELECT DATE_ADD(DATE_ADD(MAKEDATE(2013, 1), INTERVAL (3)-1 MONTH), INTERVAL (11)-1 DAY);
| DATE_ADD(DATE_ADD(MAKEDATE(2013, 1), INTERVAL (3)-1 MONTH), INTERVAL (11)-1 DAY) |
+----------------------------------------------------------------------------------+
| 2013-03-11 |
+----------------------------------------------------------------------------------+
So to answer the OP's question:
所以要回答OP的问题：
SELECT * FROM `date`
WHERE DATE_ADD(DATE_ADD(MAKEDATE(year, 1), INTERVAL (month)-1 MONTH), INTERVAL (day)-1 DAY)
BETWEEN '2013-01-01' AND '2014-01-01';
回答by Spudley
To build a sortable date string from that, you'll need CONCATto join the bits together and LPADto make sure the month and day fields are two digits long. Something like this:
要从中构建可排序的日期字符串，您需要CONCAT将这些位连接在一起并LPAD确保月份和日期字段是两位数长。像这样的东西：
CONCAT(`year`,'-',LPAD(`month`,2,'00'),'-',LPAD(`day`,2,'00'))
Once you have that, you should be able to use BETWEEN, as they'll be in a sortable format. However if you still need to convert them to actual datetime fields, you can wrap the whole thing in UNIX_TIMESTAMP()to get a timestamp value.
一旦你有了它，你应该可以使用BETWEEN，因为它们将采用可排序的格式。但是，如果您仍然需要将它们转换为实际的日期时间字段，则可以将整个内容包装起来UNIX_TIMESTAMP()以获取时间戳值。
So you'd end up with something like this:
所以你最终会得到这样的结果：
SELECT UNIX_TIMESTAMP(CONCAT(`year`,'-',LPAD(`month`,2,'00'),'-',LPAD(`day`,2,'00'))) as u_date
WHERE u_date BETWEEN timestamp_1 and timestamp_2
However, be aware that this will be massivelyslower than if the field was just a simple timestamp in the first place. And you should definitely make sure you have an index on the year, month and day fields.
但是，要知道，这将是大量比如果该字段只是摆在首位简单的时间戳慢。而且你绝对应该确保你有一个关于年、月和日字段的索引。
回答by Gannet
The simplest way to do this is:
最简单的方法是：
DATE(CONCAT_WS('-', year, month, day))
DATE(CONCAT_WS('-', year, month, day))
LPAD is not necessary as @pbarney pointed out earlier. If you are comparing with another date object, it's not strictly necessary to wrap it with DATE as MySQL will cast it automatically:
正如@pbarney 之前指出的那样，LPAD 不是必需的。如果您正在与另一个日期对象进行比较，则没有必要用 DATE 包装它，因为 MySQL 会自动转换它：
some_date_field > CONCAT_WS('-', year, month, day)
some_date_field > CONCAT_WS('-', year, month, day)
回答by Nae
Expanding this answer, here's my take on it:
扩展这个答案，这是我的看法：
DELIMITER $$
CREATE FUNCTION fn_year_month_to_date(var_year INTEGER,
var_month enum('01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12')
)
RETURNS DATE
BEGIN
RETURN (MAKEDATE(var_year, 1) + INTERVAL (var_month - 1) MONTH);
END $$
DELIMITER ;
SELECT fn_year_month_to_date(2020, 12)
;
回答by zod
Try to use CONCAT() and make it one field and compare .
尝试使用 CONCAT() 并将其设为一个字段并进行比较。
Am not sure you can compare it as date after concatenation.
我不确定您是否可以将其作为连接后的日期进行比较。
You can compare as integer.
您可以作为整数进行比较。
concatinate year month day and make an integer like this 20101017 and compare.
连接年月日并制作这样的整数 20101017 并进行比较。
Hopefully :)
希望 ：）
