MySQL:MySQL日期数据类型、MySQL时间项目安详严整

本文主要是总结一些常用的在实际运用中常用的一些mysql时间日期以及转换的函数

MySQL:MySQL日期数据类型、MySQL时间类型详解

1.now() :返回当前日期和时间

一、MySQL 日期类型:日期格式、所占存储空间、日期范围 比较

日期类型 存储空间 日期格式 日期范围
------------ --------- --------------------- -----------------------------------------

datetime 8 bytes YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59

timestamp 4 bytes YYYY-MM-DD HH:MM:SS 1970-01-01 00:00:01 ~ 2038

date 3 bytes YYYY-MM-DD 1000-01-01 ~ 9999-12-31
year 1 bytes YYYY 1901 ~ 2155

在 MySQL
中创建表时,对照上面的表格,很容易就能选择到合适自己的数据类型。不过到底是选择
datetime 还是
timestamp,可能会有点犯难。这两个日期时间类型各有优点:datetime
的日期范围比较大;timestamp 所占存储空间比较小,只是 datetime 的一半。

另外,timestamp 类型的列还有个特性:默认情况下,在 insert, update 数据时,timestamp 列会自动以当前时间(CURRENT_TIMESTAMP)填充/更新。“自动”的意思就是,你不去管它,MySQL 会替你去处理。
建表的代码为:
create table t8 (
`id1` timestamp NOT NULL default CURRENT_TIMESTAMP,
`id2` datetime default NULL
);

一般情况下,我倾向于使用 datetime 日期类型。

两者之间的比较:

  1. timestamp容易所支持的范围比timedate要小。 并且容易出现超出的情况
  2. timestamp比较受时区timezone的影响以及MYSQL版本和服务器的SQL
    MODE的影响.
select now();//2018-04-21 09:19:21 

二、MySQL 时间类型:时间格式、所占存储空间、时间范围

时间类型 存储空间 时间格式 时间范围
------------ --------- --------------------- -----------------------------------------
time 3 bytes HH:MM:SS -838:59:59 ~ 838:59:59

time 时间范围居然有这么大的范围,特别是 time 可以取负值,有点奇怪。后来,看了 MySQL 手册才知道这是为了满足两个日期时间相减才这样设计的。
比如下面:
select timediff('2000:01:31 23:59:59', '2000:01:01 00:00:00'); -- 743:59:59
select timediff('2000:01:01 00:00:00', '2000:01:31 23:59:59'); -- -743:59:59
select timediff('23:59:59', '12:00:00'); -- 11:59:59

注意,timediff 的两个参数只能是 datetime/timestamp, time
类型的,并且这两个参数类型要相同。即:datetime/timestamp 和
datetime/timestamp 比较;time 和 time 相比较。

相关知识
参考:

虽然 MySQL
中的日期时间类型比较丰富,但遗憾的是,目前(2008-08-08)这些日期时间类型只能支持到秒级别,不支持毫秒、微秒。也没有产生毫秒的函数。

2.curdate() :返回当前日期

三、MySQL日期数据类型、MySQL时间类型使用总结

注:MySQL日期数据类型、MySQL时间类型使用总结,适用于 MySQL 5.X
及以上版本。

select curdate();//2018-04-21

1、MySQL 获得当前日期时间 函数

  1. dayofweek(data) : 返回日期 data 是星期几

    select dayofweek(‘2018-02-13′); // 3

1.1 获得当前日期+时间(date + time)函数:now()

mysql> select now();

+-----------------------------+
| now() |
+-----------------------------+
| 2014-10-29 10:52:57 |
+-----------------------------+

除了 now() 函数能获得当前的日期时间外,MySQL 中还有下面的函数:

current_timestamp()

current_timestamp

localtime()

localtime

localtimestamp -- (v4.0.6)

localtimestamp() -- (v4.0.6)

这些日期时间函数,都等同于 now()。鉴于 now() 函数简短易记,建议总是使用
now() 来替代上面列出的函数。

4.weekday(date) : 返回日期 data 是星期几

1.2 获得当前日期+时间(date + time)函数:sysdate()

sysdate() 日期时间函数跟 now() 类似,不同之处在于:now()
在执行开始时值就得到了, sysdate()
在函数执行时动态得到值。看下面的例子就明白了:

mysql> select now(), sleep(3), now();

+-----------------------------+--------------+-----------------------------+
| now() | sleep(3) | now() |
+-----------------------------+--------------+-----------------------------+
| 2014-10-29 10:55:11 | 0 | 2014-10-29 10:55:11 |
+-----------------------------+--------------+-----------------------------+

mysql> select sysdate(), sleep(3), sysdate();

+-----------------------------+--------------+-----------------------------+
| sysdate() | sleep(3) |sysdate() |
+-----------------------------+--------------+-----------------------------+
| 2014-10-29 10:55:11 | 0 | 2014-10-29 10:55:14 |
+-----------------------------+--------------+-----------------------------+

可以看到,虽然中途 sleep 3 秒,但 now() 函数两次的时间值是相同的;
sysdate() 函数两次得到的时间值相差 3 秒。MySQL Manual 中是这样描述
sysdate() 的:Return the time at which the function executes。

sysdate() 日期时间函数,一般情况下很少用到。

select WEEKDAY('1997-10-04 22:23:00');//5 

2. 获得当前日期(date)函数:curdate()

mysql> select curdate();

+----------------+
| curdate() |
+----------------+
| 2014-10-29 |
+----------------+

其中,下面的两个日期函数等同于 curdate():

current_date()

current_date

5.dayofmonth(date) :返回date是一个月中的第几天(1-31 范围)

3. 获得当前时间(time)函数:curtime()

mysql> select curtime();

+-----------+
| curtime() |
+-----------+
| 10:59:58 |
+-----------+

其中,下面的两个时间函数等同于 curtime():

current_time()

current_time
select dayofmonth('2018-04-21');//21 

4. 获得当前 UTC 日期时间函数:utc_date(), utc_time(), utc_timestamp() 、

mysql> select utc_timestamp(), utc_date(), utc_time(), now() ;

+-------------------------------+-----------------+-----------------+-------------------------------+
| utc_timestamp() | utc_date() | utc_time() | now() |
+-------------------------------+-----------------+-----------------+-------------------------------+
| 2014-10-29 03:00:55 | 2014-10-29 | 03:00:55 | 2014-10-29 11:00:55 |
+-------------------------------+-----------------+-----------------+-------------------------------+

因为我国位于东八时区,所以本地时间 = UTC 时间 + 8 小时。UTC
时间在业务涉及多个国家和地区的时候,非常有用。

6.dayofyear(date) : 返回date是一年中的第几天

5、MySQL 日期时间 Extract(选取) 函数。

select dayofyear('2018-01-01');//1 

5.1. 选取日期时间的各个部分:日期、时间、年、季度、月、日、小时、分钟、秒、微秒

set @dt = '2008-09-10 07:15:30.123456';

select date(@dt); -- 2008-09-10

select time(@dt); -- 07:15:30.123456

select year(@dt); -- 2008

select quarter(@dt); -- 3

select month(@dt); -- 9

select week(@dt); -- 36

select day(@dt); -- 10

select hour(@dt); -- 7

select minute(@dt); -- 15

select second(@dt); -- 30

select microsecond(@dt); -- 123456

5.2. MySQL Extract() 函数,可以上面实现类似的功能:

set @dt = '2008-09-10 07:15:30.123456';

select extract(year from @dt); -- 2008
select extract(quarter from @dt); -- 3
select extract(month from @dt); -- 9
select extract(week from @dt); -- 36
select extract(day from @dt); -- 10
select extract(hour from @dt); -- 7
select extract(minute from @dt); -- 15
select extract(second from @dt); -- 30
select extract(microsecond from @dt); -- 123456

select extract(year_month from @dt); -- 200809
select extract(day_hour from @dt); -- 1007
select extract(day_minute from @dt); -- 100715
select extract(day_second from @dt); -- 10071530
select extract(day_microsecond from @dt); -- 10071530123456
select extract(hour_minute from @dt); -- 715
select extract(hour_second from @dt); -- 71530
select extract(hour_microsecond from @dt); -- 71530123456
select extract(minute_second from @dt); -- 1530
select extract(minute_microsecond from @dt); -- 1530123456
select extract(second_microsecond from @dt); -- 30123456

MySQL Extract() 函数除了没有date(),time()
的功能外,其他功能一应具全。并且还具有选取‘day_microsecond’
等功能。注意这里不是只选取 day 和 microsecond,而是从日期的 day
部分一直选取到 microsecond 部分。够强悍的吧!

MySQL Extract() 函数唯一不好的地方在于:你需要多敲几次键盘。

7.month(date):返回date 中的月份值

6. MySQL dayof… 函数:dayofweek(), dayofmonth(), dayofyear()

分别返回日期参数,在一周、一月、一年中的位置。

set @dt = '2008-08-08';

select dayofweek(@dt); -- 6

select dayofmonth(@dt); -- 8

select dayofyear(@dt); -- 221

日期 ‘2008-08-08’ 是一周中的第 6 天(1 = Sunday, 2 = Monday, …, 7 =
Saturday);一月中的第 8 天;一年中的第 221 天。

select month('2018-04-21');//4

7. MySQL week… 函数:week(), weekofyear(), dayofweek(), weekday(), yearweek()

set @dt = '2008-08-08';
select week(@dt); -- 31
select week(@dt,3); -- 32
select weekofyear(@dt); -- 32
select dayofweek(@dt); -- 6
select weekday(@dt); -- 4
select yearweek(@dt); -- 200831

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.dayname(date):返回date 是星期几(英文名返回)

8. MySQL 返回星期和月份名称函数:dayname(), monthname()

set @dt = '2008-08-08';
select dayname(@dt); -- Friday
select monthname(@dt); -- August

思考,如何返回中文的名称呢?

select dayname('1998-02-05');//Thursday

9. MySQL last_day() 函数:返回月份中的最后一天

select last_day('2008-02-01'); -- 2008-02-29
select last_day('2008-08-08'); -- 2008-08-31

MySQL last_day() 函数非常有用,比如我想得到当前月份中有多少天,可以这样来计算:

mysql> select now(), day(last_day(now())) as days;

+---------------------+------+
| now() | days |
+---------------------+------+
| 2014-10-29 11:06:55 | 31 |
+---------------------+------+

9.monthname(date):返回date 是几月

四、MySQL 日期时间计算函数