MySQL 如何制作带有日期和时间列的mysql表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25572871/
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
How to make a mysql table with date and time columns?
提问by Urler
I am trying to create a table that has date and time columns used to store the date and time information when the entry is recorded in the database. In the MySQL documentation shows the following example:
我正在尝试创建一个表，该表具有用于存储在数据库中记录条目时的日期和时间信息的日期和时间列。在 MySQL 文档中显示了以下示例：
CREATE TABLE t1 (
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
dt DATETIME DEFAULT CURRENT_TIMESTAMP
);
The problem is that I want separate fields for date and time. On another site I found this piece of code that suits my needs for the date:
问题是我想要单独的日期和时间字段。在另一个网站上，我找到了这段代码，它适合我对日期的需求：
CREATE TABLE test (id INT, date_added DATE);
but I wasn't able to find anything like that for the time. Will the following piece of code make separate date and time fields that would be filled as the entry is recorded?
但我暂时找不到这样的东西。以下代码是否会创建单独的日期和时间字段，以便在记录条目时填写这些字段？
CREATE TABLE test (id INT, date_added DATE, time_added TIME);
回答by SparkOn
There is no point in having separate columns for Date and Time. It does not make much sense
为日期和时间设置单独的列是没有意义的。这没有多大意义
You can create the table like this
您可以像这样创建表
CREATE TABLE timeDate (
id INT,
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
);
And if you want the Date part use this query
如果你想要日期部分使用这个查询
SELECT DATE(`ts`) FROM timedate WHERE id =someId
And if you want the Time part use this query
如果你想要时间部分使用这个查询
SELECT TIME(`ts`) FROM timedate WHERE id =someId
回答by beansontoast
CREATE TABLE t1 (
ts TIME,
dt DATE
);
Only a timestamp field can have DEFAULT CURRENT_TIMESTAMP. It contains both a date and time. TIME and DATE fields will need to be set by your application.
只有时间戳字段可以具有 DEFAULT CURRENT_TIMESTAMP。它包含日期和时间。TIME 和 DATE 字段需要由您的应用程序设置。
