MySQL:MySQL日期数据类型、MySQL时间类型详解_MySQL
一、MySQL 日期类型:日期格式、所占存储空间、日期范围 比较
1 2 3 4 5 6 7 8 9 |
|
在 MySQL 中创建表时,对照上面的表格,很容易就能选择到合适自己的数据类型。不过到底是选择 datetime 还是 timestamp,可能会有点犯难。这两个日期时间类型各有优点:datetime 的日期范围比较大;timestamp 所占存储空间比较小,只是 datetime 的一半。
1 2 3 4 5 6 |
|
一般情况下,我倾向于使用 datetime 日期类型。
两者之间的比较:
1. timestamp容易所支持的范围比timedate要小。 并且容易出现超出的情况
2. timestamp比较受时区timezone的影响以及MYSQL版本和服务器的SQL MODE的影响.
二、MySQL 时间类型:时间格式、所占存储空间、时间范围
1 2 3 4 5 6 7 8 9 |
|
注意,timediff 的两个参数只能是 datetime/timestamp, time 类型的,并且这两个参数类型要相同。即:datetime/timestamp 和 datetime/timestamp 比较;time 和 time 相比较。
相关知识 参考:http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html
虽然 MySQL 中的日期时间类型比较丰富,但遗憾的是,目前(2008-08-08)这些日期时间类型只能支持到秒级别,不支持毫秒、微秒。也没有产生毫秒的函数。三、MySQL日期数据类型、MySQL时间类型使用总结
注:MySQL日期数据类型、MySQL时间类型使用总结,适用于 MySQL 5.X 及以上版本。1、MySQL 获得当前日期时间 函数
1.1 获得当前日期+时间(date + time)函数:now()
1 2 3 4 5 6 7 |
|
除了 now() 函数能获得当前的日期时间外,MySQL 中还有下面的函数:
1 2 3 4 5 6 7 8 9 10 11 |
|
1.2 获得当前日期+时间(date + time)函数:sysdate()
sysdate() 日期时间函数跟 now() 类似,不同之处在于:now() 在执行开始时值就得到了, sysdate() 在函数执行时动态得到值。看下面的例子就明白了:1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
|
可以看到,虽然中途 sleep 3 秒,但 now() 函数两次的时间值是相同的; sysdate() 函数两次得到的时间值相差 3 秒。MySQL Manual 中是这样描述 sysdate() 的:Return the time at which the function executes。
sysdate() 日期时间函数,一般情况下很少用到。
2. 获得当前日期(date)函数:curdate()
1 2 3 4 5 6 7 |
|
其中,下面的两个日期函数等同于 curdate():
1 2 3 |
|
3. 获得当前时间(time)函数:curtime()
1 2 3 4 5 6 7 |
|
其中,下面的两个时间函数等同于 curtime():
1 2 3 |
|
4. 获得当前 UTC 日期时间函数:utc_date(), utc_time(), utc_timestamp() 、
1 2 3 4 5 6 7 |
|
因为我国位于东八时区,所以本地时间 = UTC 时间 + 8 小时。UTC 时间在业务涉及多个国家和地区的时候,非常有用。
5、MySQL 日期时间 Extract(选取) 函数。
5.1. 选取日期时间的各个部分:日期、时间、年、季度、月、日、小时、分钟、秒、微秒
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
|
MySQL Extract() 函数除了没有date(),time() 的功能外,其他功能一应具全。并且还具有选取‘day_microsecond’ 等功能。注意这里不是只选取 day 和 microsecond,而是从日期的 day 部分一直选取到 microsecond 部分。够强悍的吧!
MySQL Extract() 函数唯一不好的地方在于:你需要多敲几次键盘。
6. MySQL dayof... 函数:dayofweek(), dayofmonth(), dayofyear()
分别返回日期参数,在一周、一月、一年中的位置。1 2 3 4 5 6 7 |
|
7. MySQL week... 函数:week(), weekofyear(), dayofweek(), weekday(), yearweek()
1 2 3 4 5 6 7 |
|
MySQL week() 函数,可以有两个参数,具体可看手册。 weekofyear() 和 week() 一样,都是计算“某天”是位于一年中的第几周。 weekofyear(@dt) 等价于 week(@dt,3)。
MySQL weekday() 函数和 dayofweek() 类似,都是返回“某天”在一周中的位置。不同点在于参考的标准, weekday:(0 = Monday, 1 = Tuesday, ..., 6 = Sunday); dayofweek:(1 = Sunday, 2 = Monday, ..., 7 = Saturday)
MySQL yearweek() 函数,返回 year(2008) + week 位置(31)。
8. MySQL 返回星期和月份名称函数:dayname(), monthname()
1 2 3 |
|
思考,如何返回中文的名称呢?
9. MySQL last_day() 函数:返回月份中的最后一天
1 2 3 4 5 6 7 8 9 10 11 12 |
|
四、MySQL 日期时间计算函数
1. MySQL 为日期增加一个时间间隔:date_add()
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
|
date_add() 函数,分别为 @dt 增加了“1小时 15分 30秒” 和 “1天 1小时 15分 30秒”。建议:总是使用 date_add() 日期时间函数来替代 adddate(), addtime()。
2. MySQL 为日期减去一个时间间隔:date_sub()
1 2 3 4 5 6 7 |
|
MySQL date_sub() 日期时间函数 和 date_add() 用法一致,不再赘述。另外,MySQL 中还有两个函数 subdate(), subtime(),建议,用 date_sub() 来替代。
3. MySQL 另类日期函数:period_add(P,N), period_diff(P1,P2)
函数参数“P” 的格式为“YYYYMM” 或者 “YYMM”,第二个参数“N” 表示增加或减去 N month(月)。1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
|
4. MySQL 日期、时间相减函数:datediff(date1,date2), timediff(time1,time2)
1 2 3 4 5 6 7 8 9 10 |
|
五、MySQL 日期转换函数、时间转换函数
1. MySQL (时间、秒)转换函数:time_to_sec(time), sec_to_time(seconds)
1 2 |
|
2. MySQL (日期、天数)转换函数:to_days(date), from_days(days)
1 2 3 4 |
|
3. MySQL Str to Date (字符串转换为日期)函数:str_to_date(str, format)
1 2 3 4 5 |
|
可以看到,str_to_date(str,format) 转换函数,可以把一些杂乱无章的字符串转换为日期格式。另外,它也可以转换为时间。“format” 可以参看 MySQL 手册。
4. MySQL Date/Time to Str(日期/时间转换为字符串)函数:date_format(date,format), time_format(time,format)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
|
5. MySQL 获得国家地区时间格式函数:get_format()
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
|
6. MySQL 拼凑日期、时间函数:makdedate(year,dayofyear), maketime(hour,minute,second)
1 2 3 |
|
六、MySQL 时间戳(Timestamp)函数
1. MySQL 获得当前时间戳函数:current_timestamp, current_timestamp()
1 2 3 4 5 6 |
|
2. MySQL (Unix 时间戳、日期)转换函数:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
|
3. MySQL 时间戳(timestamp)转换、增、减函数:
1 2 3 4 |
|
请看示例部分:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
|
七、MySQL 时区(timezone)转换函数
1 2 3 |
|
时区转换也可以通过 date_add, date_sub, timestampadd 来实现。
1 2 3 4 |
|
注:资料来源网络

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

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

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











1. First open the mobile web browser, search for the Weibo web version, and click the avatar button in the upper left corner after entering. 2. Then click Settings in the upper right corner. 3. Click the version switching option in settings. 4. Then select the color version option in the version switch. 5. Click Search to enter the search page. 6. After entering the keywords, click Find People. 7. When the search completion interface appears, click Filter. 8. Finally, enter the specific date in the release time column and click Filter.

On the Douyin platform, many users are eager to obtain level certification, and the level 10 light sign shows the user's influence and recognition on Douyin. This article will delve into the price of Douyin’s level 10 light boards and the time it takes to reach this level to help users better understand the process. 1. How much does a level 10 Douyin light sign cost? The price of Douyin's 10-level light signs will vary depending on market fluctuations and supply and demand. The general price ranges from a few thousand yuan to ten thousand yuan. This price mainly includes the cost of the light sign itself and possible service fees. Users can purchase level 10 light signs through Douyin’s official channels or third-party service agencies, but they should pay attention to legal channels when purchasing to avoid false or fraudulent transactions. 2. How many days does it take to create a level 10 fan sign? Reach level 10 light sign

1. Let me first talk about the method I used at the beginning, maybe everyone is using it too. First, open [View]——]Remarks Template[. 2. A place where you can actually see the date after opening it. 3. Select it first and delete it. 4. After deleting, click [Close Master View]. 5. Open the print preview again and find that the date is still there. 6. In fact, this date was not deleted here. It should be in the [Handout Master]. Look at the picture below. 7. Delete the date after you find it. 8. Now when you open the preview and take a look, the date is no longer there. Note: In fact, this method is also very easy to remember, because the printed handouts are handouts, so you should look for the [Handout Master].

Players can experience the main plot of the game and collect game achievements when playing in Elden's Circle. Many players don't know how long it takes to clear Elden's Circle. The player's clearance process is 30 hours. How long does it take to clear the Elden Ring? Answer: 30 hours. 1. Although this 30-hour clearance time does not refer to a master-like speed pass, it also omits a lot of processes. 2. If you want to get a better game experience or experience the complete plot, then you will definitely need to spend more time on the duration. 3. If players collect them all, it will take about 100-120 hours. 4. If you only take the main line to brush BOSS, it will take about 50-60 hours. 5. If you want to experience it all: 150 hours of base time.

Xiaohongshu, a platform full of life and knowledge sharing, allows more and more creators to express their opinions freely. In order to get more attention and likes on Xiaohongshu, in addition to the quality of content, the time of publishing works is also crucial. So, how to set the time for Xiaohongshu to publish works? 1. How to set the time for publishing works on Xiaohongshu? 1. Understand the active time of users. First, it is necessary to clarify the active time of Xiaohongshu users. Generally speaking, 8 pm to 10 pm and weekend afternoons are the times when user activity is high. However, this time period will also vary depending on factors such as audience group and geography. Therefore, in order to better grasp the active period of users, it is recommended to conduct a more detailed analysis of the behavioral habits of different groups. By understanding users’ lives

Excel software has very powerful data processing functions. We often use excel software to process various data. Sometimes when we enter a date in an excel cell, the date in excel changes to a pound sign. How can we display the data normally? Let’s take a look at the solution below. 1. First, we put the mouse on the column width line between columns AB, double-click and adjust the column width, as shown in the figure below. 2. After the column is widened, we find that numbers are displayed in the cells instead of dates. This is definitely incorrect. Then we should check the format of the cells, as shown in the figure below. 3. Click the "Number" option in the "Home" tab, and click "Other Number Format" in the drop-down menu, as shown in the figure below.

Detailed explanation of Linux file time viewing techniques In Linux systems, file time information is very important for file management and tracking changes. The Linux system records file change information through three main time attributes, namely access time (atime), modification time (mtime) and change time (ctime). This article details how to view and manage this file time information, and provides specific code examples. 1. Check the file time information by using the ls command with the parameter -l to list the files.

Many users are not sure how to adjust the time of the gshock watch. The overall method is relatively simple. Here is a detailed introduction to the adjustment method of the gshock watch. Users who do not know how to operate can refer to it. How to adjust the time on a gshock watch 1. First label these buttons according to ABCD. A key: It can be understood as the exit key, usually used together with the C key. The B key and the D key can be understood as: plus and minus keys, which are the adjustment keys. For example, if it is 13 o'clock now, press the D key when it reaches 14 o'clock. The C key is the mode key. You can switch modes by pressing it. Generally, the mode key of a watch is in the lower left corner and has the word "mode" in English. Operation method 1. First we need to adjust the watch to the
