mysql时间函数

最新推荐文章于 2025-08-09 18:20:04 发布

cpf2016

最新推荐文章于 2025-08-09 18:20:04 发布

阅读量327

点赞数

分类专栏： java 文章标签：数据库

java 专栏收录该内容

46 篇文章

订阅专栏

本文介绍MySQL中的日期时间类型及其操作，包括不同类型的使用场景、日期时间函数的应用、时间的加减运算及转换方法等。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

(1)日期类型

日期类型	日期格式	存储空间	日期范围
datetime	YYYY-MM-DD HH:mm:ss	8bytes	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
timestamp	YYYY-MM-DD HH:mm:ss	4bytes	1970-01-01 00:00:01 ~ 2038
date	YYYY-MM-DD	3bytes	1000-01-01 ~ 9999-12-31
time	HH:mm:ss	3bytes	-838:59:59 ~ 838:59:59
year	YYYY	1bytes	1901 ~ 2155

1.datetime与timestamp

1)datetime的日期范围比较大,timestamp容器出现超出的情况

2)timestamp在默认情况下,在insert\update数据时,timestamp列会自动以当前时间填充

建表代码如下:

create table t8 (
  `id1` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `id2` datetime default NULL
);

推荐使用datetime

2.time类型的范围为什么可以有负数

为了方便日期相减

(2)当前日期函数

1.获取当前日期时间:now()

mysql> select now();
+---------------------+ 
| now()               | 
+---------------------+ 
| 2008-08-08 22:20:46 | 
+---------------------+

2.获取当前日期:curdate()

mysql> select curdate();
+------------+ 
| curdate() | 
+------------+ 
| 2008-08-08 | 
+------------+

3.获取当前时间:curtime()

mysql> select curtime();
+-----------+ 
| curtime() | 
+-----------+ 
| 22:41:30 | 
+-----------+

4.获取当前UTC时间:utc_timestamp()

mysql> select utc_timestamp(), utc_date(), utc_time(), now()
+---------------------+------------+------------+---------------------+ 
| utc_timestamp()     | utc_date() | utc_time() | now()               | 
+---------------------+------------+------------+---------------------+ 
| 2008-08-08 14:47:11 | 2008-08-08 | 14:47:11   | 2008-08-08 22:47:11 | 
+---------------------+------------+------------+---------------------+

因为我国位于东八区,所以时间就是utc事件+8小时

(3)timestamp函数(时间+或-或转换)

1.date类型向timestamp转换

timestamp(date) //date to timestamp

2.时间相加

1)根据表达式

select timestamp('2008-08-08 08:00:00', '01:01:01');    -- 2008-08-08 09:01:01 
select timestamp('2008-08-08 08:00:00', '10 01:01:01'); -- 2008-08-18 09:01:01

2)根据timestampadd()

timestampadd(unit,interval,datetime_expr)

select timestampadd(day, 1, '2008-08-08 08:00:00');     -- 2008-08-09 08:00:00

3.时间相减

根据时间单位决定结果

select timestampdiff(year,'2002-05-01','2001-01-01');                    -- -1 
select timestampdiff(day ,'2002-05-01','2001-01-01');                    -- -485 
select timestampdiff(hour,'2008-08-08 12:00:00','2008-08-08 00:00:00'); -- -12

(4)时间单位选取

取哪个字段用什么单位即可

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

注意:比如上面的day_second不是说只选取day和second字段,而是从day到second

(5)返回日期在一周一月一年中的位置

set @dt = '2008-08-08';
select dayofweek(@dt);   -- 6 
select dayofmonth(@dt); -- 8 
select dayofyear(@dt);   -- 221

注意Sunday才是第一天

(6)返回日期属于这一年的第多少周

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

select weekofyear(@dt); -- 32

(7)返回月份中的最后一天

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

last_day函数一个非常重要的作用是算出指定日期的月份有多少天

select now(),extract(day from last_day(now()))

即从last day中抽取出day字段

(8)时间与字符串相互转换

1.str_to_date

能够将各种则乱格式的字符串转换为日期格式

select str_to_date('08/09/2008', '%m/%d/%Y');                   -- 2008-08-09 
select str_to_date('08/09/08' , '%m/%d/%y');                   -- 2008-08-09 
select str_to_date('08.09.2008', '%m.%d.%Y');                   -- 2008-08-09 
select str_to_date('08:09:30', '%h:%i:%s');                     -- 08:09:30 
select str_to_date('08.09.2008 08:09:30', '%m.%d.%Y %h:%i:%s'); -- 2008-08-09 08:09:30

2.date_format

能够将时间类型转化为指定的字符串格式

mysql> select date_format('2008-08-08 22:23:01', '%Y/%m/%d %H/%i/%s');
+----------------------------------------------------+ 
| date_format('2008-08-08 22:23:01', '%Y/%m/%d %H/%i/%s');| 
+----------------------------------------------------+ 
| 2008/08/08 22/23/01                                   |
+----------------------------------------------------+

mysql> select time_format('22:23:01', '%H.%i.%s');
+-------------------------------------+ 
| time_format('22:23:01', '%H.%i.%s') | 
+-------------------------------------+ 
| 22.23.01                            | 
+-------------------------------------+