DATE,TIME,DATETIME和TIMESTAMP类型之间有什么区别 [英] What difference between the DATE, TIME, DATETIME, and TIMESTAMP Types
问题描述
我需要在mysql中存储时间和日期。所以我使用了 NOW()
函数。但是我不知道应该如何使用类型列 im phpmyadmin。应该注意的是, NOW()
返回时间和日期如下所示:
I need to store both time and date in the mysql. So I used of NOW()
function for that. But I don't know what should I use for type column im phpmyadmin. It should be noted that NOW()
returns both time and date like this:
2014-11-11 12:45:34
这里有一个解决方案,我可以使用用于分隔日期和时间的分隔符( 2014-11-11
和 12:45:34
)然后存储它们分别是DATE类型和TIME类型。或者我可以使用VARCHAR类型将它们存储在一列中。但我认为这些方式不是标准的。存储日期和时间的标准类型是什么?
Here is a solution, I can use of a separator for separating date and time (2014-11-11
and 12:45:34
) and then store them in the DATE type and TIME type individually. Or I can use of VARCHAR type for storing both of them in one column. But I think these ways are not standard. what is standard type for storing both date and time ?
这是我的查询:(也不知道为什么 NOW()
函数不起作用)
Here is my query: (also I don't know why NOW()
function does not works)
INSERT INTO table (timedate) VALUES (NOW())
推荐答案
DATE:日期部分,但没有时间部分。 MySQL在'YYYY-MM-DD'
格式中检索并显示DATE值。支持的范围是'1000-01-01'至'9999-12-31'。
DATE: It is used for values with a date part but no time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD'
format. The supported range is '1000-01-01' to '9999-12-31'.
DATETIME:它用于包含日期和时间部分的值。 MySQL在'YYYY-MM-DD HH:MM:SS'
格式中检索并显示DATETIME值。支持的范围是'1000-01-01 00:00:00'至'9999-12-31 23:59:59'。
DATETIME: It is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS'
format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.
TIMESTAMP:它用于包含日期和时间部分的值。 TIMESTAMP的范围是'1970-01-01 00:00:01'UTC到'2038-01-19 03:14:07'UTC。
TIMESTAMP: It is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.
TIME:其值'HH:MM:SS'格式
(或'HHH:MM :SS'格式为大小时值)。 TIME值的范围可以从' - 838:59:59'到'838:59:59'
。小时部分可能会很大,因为TIME类型不仅可以用于表示一天中的时间(必须小于24小时),而且可以使用两个事件之间的时间或时间间隔(可能远远大于24小时,甚至否定)。
TIME: Its values in 'HH:MM:SS' format
(or 'HHH:MM:SS' format for large hours values). TIME values may range from '-838:59:59' to '838:59:59'
. The hours part may be so large because the TIME type can be used not only to represent a time of day (which must be less than 24 hours), but also elapsed time or a time interval between two events (which may be much greater than 24 hours, or even negative).
这篇关于DATE,TIME,DATETIME和TIMESTAMP类型之间有什么区别的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!