MySql中时间类型总结_MySQL
最近建表要用到时间类型的数据，但对时间类型的数据一向不了解，就总结了一下。。
一、日期DATE
一个日期。支持的范围是“1000-01-01”以“9999-12-31”。MySQL显示日期用 “YYYY-MM-DD”格式，但允许使用字符串或数字列赋值日期。
1.在一个字符串“YYYY-MM-DD”或“yy-mm-dd”格式。“不严格“语法是允许任何标点符号 字符可以作为之间的日期部分的分隔符。 为，“2012-12-31”，’2012 / 12 / 31’，2012 ^ 12 ^ 31，和2012 @ 12 @ 31和“2012-12-31”是等价的
2.作为一个没有任何分隔符的字符串“yyyymmdd”格式字符串，只要是有意义的日期。对于 例子，“20070523”和“070523”被解释为“2007-05-23”，但“071332”是非法的（无意义的 月日部分）则为“0000-00-00”。
二、日期时间DATETIME
·日期和时间的组合。支持的范围是“1000-01-0100:00:00”以“9999-12-31 23:59:59”。MySQL显示日期时间用'YYYY-MM-DD HH:MM:SS'格式，但允许使用字符串或数字列赋值日期时间。
· 1.允许“不严格”语法：任何标点符都可以用做日期部分或时间部分之间的间割符。例如，'98-12-31 11:30:45'、'98.12.3111+30+45'、'98/12/31 11*30*45'和'9831 11^30^45'是等价的。
2.全为数字'19970523091528'和'970523091528'被解释为'1997-05-2309:15:28'，但'971122129015'是不合法的 (它有一个没有意义的分钟部分)，将变为'0000-00-00 00:00:00'。
三、时间TIME
· 时间范围“838:59:59”以“838:59:59”。格式“hh：MM：ss”（或“HHH：MM：ss”格式）。时间值的范围可以从“838:59:59”以“838:59:59”。时间可能是如此之大 因为时间型不仅可用于代表一天中的时间（必须小于24小时），但还经过两个事件之间的时间或时间间隔（这可能远大于24小时，或甚至是负的）。
缩写“1112”和1112作为意义“11:12:00”（十一点后12分钟），但 MySQL它们解释为“00:11:12”（11 分钟，12秒）。同样，“12”和12被解释为“00:00:12”。
l 四、时间戳TIMESTAMP
1.时间戳范围“1970-01-01 00:00:01”——“2038-01-19 03:14:07”UTC。
TIMESTAMP提供了自动 初始化和更新为当前的日期和时间。
TIMESTAMP和DATETIME比较：
这两个日期时间类型各有优点：datetime 的日期范围比较大；timestamp 所占存储空间比较小，只是 datetime 的一半。另外，timestamp 类型的列还有个特性：默认情况下，在 insert, update 数据时，timestamp 列会自动以当前时间（CURRENT_TIMESTAMP）填充/更新。“自动”的意思就是，你不去管它，MySQL 会替你去处理。
cx
五、 YEAR类型
YEAR类型是一个单字节类型用于表示年。
MySQL以YYYY格式检索和显示YEAR值。范围是1901到2155。
可以指定各种格式的YEAR值：
· 四位字符串，范围为'1901'到'2155'。
· 四位数字，范围为1901到2155。
· 两位字符串，范围为'00'到'99'。'00'到'69'和'70'到'99'范围的值被转换为2000到2069和1970到1999范围的YEAR值。
· 两位整数，范围为1到99。1到69 被转换为2001到2069
70到99范围的值被转换为1970到1999范围的YEAR值。
请注意两位整数范围与两位字符串范围稍有不同，因为你不能直接将零指定为数字并将它解释为2000。你必须将它指定为一个字符串'0'或'00'或它被解释为0000。
·函数返回的结果，其值适合YEAR上下文，例如NOW()。
非法YEAR值被转换为0000。
总的格式表：
DATATYPE VALUE
|
DATE |
'0000-00-00' |
|
TIME |
'00:00:00' |
|
DATETIME |
'0000-00-00 00:00:00' |
|
TIMESTAMP |
'0000-00-00 00:00:00' |
|
YEAR |
0000 |
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
AI Hentai Generator
Generate AI Hentai for free.
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
How to change network type to private or public in Windows 11
Aug 24, 2023 pm 12:37 PM
Setting up a wireless network is common, but choosing or changing the network type can be confusing, especially if you don't know the consequences. If you're looking for advice on how to change the network type from public to private or vice versa in Windows 11, read on for some helpful information. What are the different network profiles in Windows 11? Windows 11 comes with a number of network profiles, which are essentially sets of settings that can be used to configure various network connections. This is useful if you have multiple connections at home or office so you don't have to set it all up every time you connect to a new network. Private and public network profiles are two common types in Windows 11, but generally
Implementing dynamic arrays in Python: from beginner to proficient
Apr 21, 2023 pm 12:04 PM
Part 1 Let’s talk about the nature of Python sequence types. In this blog, let’s talk about Python’s various “sequence” classes and the three built-in commonly used data structures – list, tuple and character. The nature of the string class (str). I don’t know if you have noticed it, but these classes have an obvious commonality. They can be used to save multiple data elements. The most important function is: each class supports subscript (index) access to the elements of the sequence, such as using SyntaxSeq[i]. In fact, each of the above classes is represented by a simple data structure such as an array. However, readers familiar with Python may know that these three data structures have some differences: for example, tuples and strings cannot be modified, while lists can.
How to create a video matrix account? What types of matrix accounts do it have?
Mar 21, 2024 pm 04:57 PM
With the popularity of short video platforms, video matrix account marketing has become an emerging marketing method. By creating and managing multiple accounts on different platforms, businesses and individuals can achieve goals such as brand promotion, fan growth, and product sales. This article will discuss how to effectively use video matrix accounts and introduce different types of video matrix accounts. 1. How to create a video matrix account? To make a good video matrix account, you need to follow the following steps: First, you must clarify what the goal of your video matrix account is, whether it is for brand communication, fan growth or product sales. Having clear goals helps develop strategies accordingly. 2. Choose a platform: Choose an appropriate short video platform based on your target audience. The current mainstream short video platforms include Douyin, Kuaishou, Huoshan Video, etc.
What is the type of return value of Golang function?
Apr 13, 2024 pm 05:42 PM
Go functions can return multiple values of different types. The return value type is specified in the function signature and returned through the return statement. For example, a function can return an integer and a string: funcgetDetails()(int,string). In practice, a function that calculates the area of a circle can return the area and an optional error: funccircleArea(radiusfloat64)(float64,error). Note: If the function signature does not specify a type, a null value is returned; it is recommended to use a return statement with an explicit type declaration to improve readability.
Best practices for type hints in Python
Apr 23, 2023 am 09:28 AM
It’s great to use dynamic language for a while, and the code is reconstructed in the crematorium. I believe you must have heard this sentence. Like unit testing, although it takes a small amount of time to write code, it is very worthwhile in the long run. This article shares how to better understand and use Python's type hints. 1. Type hints are only valid at the syntax level. Type hints (introduced since PEP3107) are used to add types to variables, parameters, function parameters, and their return values, class properties, and methods. Python's variable types are dynamic and can be modified at runtime to add type hints to the code. It is only supported at the syntax level and has no impact on the running of the code. The Python interpreter will ignore the type hints when running the code. Therefore the type
C++ function types and characteristics
Apr 11, 2024 pm 03:30 PM
C++ functions have the following types: simple functions, const functions, static functions, and virtual functions; features include: inline functions, default parameters, reference returns, and overloaded functions. For example, the calculateArea function uses π to calculate the area of a circle of a given radius and returns it as output.
What are the types and functions of Java annotations?
May 04, 2024 pm 09:33 PM
Java annotations are used to provide metadata for code elements and can be used for metaprogramming, error checking, code generation, document generation, and reflection. The Spring framework extensively uses annotations for configuration, simplifying application development.
What are the main self-media platforms? What are the types of self-media platforms?
Mar 21, 2024 pm 06:36 PM
With the rapid development of the Internet, self-media has become an important channel for information dissemination. We-media platforms provide a stage for individuals and companies to showcase themselves and spread information. Currently, the main self-media platforms on the market include WeChat official accounts, Toutiao, Yidian News, Penguin Media Platform, etc. Each of these platforms has its own characteristics and provides a wealth of creative space for the majority of self-media practitioners. Next, we will introduce these platforms in detail and explore the types of self-media platforms. 1. What are the main self-media platforms? WeChat official account is a self-media platform launched by Tencent to provide information release and dissemination services for individual and corporate users. It is divided into two types: service account and subscription account. Service account mainly provides services for enterprises, while subscription account focuses on information dissemination. Depend on
