mysql时间相关的函数

于 2020-07-15 23:59:40 发布
阅读量337 收藏 2
点赞数 1
分类专栏： 记录 文章标签： mysql 数据库 sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/weixin_43368449/article/details/107373259
版权

mysql时间相关的函数

近日因为需要从别人的数据库中获取对应的数据，添加到自己的数据库中，所以涉及到了时间处理这一块，故而在此记录平时工作中用到的以及需要学习的。

首先，时间在数据库中的记录方式有多种，有存时间戳、时间不同格式字符串的（如：yyyyMMddHHmmss），也有存储对应的时间数据类型。

下面是mysql的时间和日期数据类型：

字符串类型描述
DATEYYYY-MM-DD格式的日期值
TIMEhh:mm:ss格式的时间值
DATETIMEYYYY-MM-DD hh:mm:ss格式的日期和时间值
TIMESTAMPYYYY-MM-DD hh:mm:ss格式的时间戳记值

这里记录一下dateTime 和timeStamp这两个的区别

  1. DATETIME的日期范围是1001——9999年，TIMESTAMP的时间范围是1970——2038年。DATETIME使用8字节的存储空间，TIMESTAMP的存储空间为4字节。因此，TIMESTAMP比DATETIME的空间利用率更高。

  2. DATETIME存储时间与时区无关，TIMESTAMP存储时间与时区有关，显示的值也依赖于时区。在mysql服务器，操作系统以及客户端连接都有时区的设置。

  3. DATETIME的默认值为null；TIMESTAMP的字段默认不为空（not null）,默认值为当前时间（CURRENT_TIMESTAMP），如果不做特殊处理，并且update语句中没有指定该列的更新值，则默认更新为当前时间。注意：第三点为默认行为，mysql中可以手动指定DateTime为CURRENT_TIMESTAMP，见下面的建表语句

    CREATE TABLE IF NOT EXISTS test.t_date
(
    id                 int                                 NOT NULL
        PRIMARY KEY,
    my_local_date      date                                NOT NULL COMMENT 'myLocalDate',
    my_local_date_time datetime                            NULL COMMENT 'myLocalDateTime',
    my_local_time      time                                NULL COMMENT 'myLocalTime',
    my_date            datetime  DEFAULT CURRENT_TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP COMMENT 'myDate',
    my_timestamp       timestamp DEFAULT CURRENT_TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP COMMENT 'myTimestamp'
)
    COMMENT '时间表' CHARSET = utf8mb4;
获取当前日期、当前时间以及日期和时间的函数

当前日期：CURRENT_DATE, CURRENT_DATE(), CURDATE();

SELECT CURRENT_DATE, CURRENT_DATE(), CURDATE();
		2020-07-12    2020-07-12      2020-07-12
SELECT CURDATE(), CURDATE() + 0, CURDATE() + 1
		2020-07-12   20200712       20200713

当前时间: CURTIME([fsp]) CURRENT_TIME, CURRENT_TIME([fsp])

SELECT current_time,current_time(),curtime()
		15:34:49	 15:34:49		15:34:49
SELECT curtime(),     curtime(2),             curtime(6);
       15:35:41    15:35:41.030000000   15:35:41.031212000
SELECT curtime(), curtime() + 1, curtime() + 0
       15:36:33      153634        153633

当前日期和时间：CURRENT_TIMESTAMP, CURRENT_TIMESTAMP([fsp]) NOW([fsp]) LOCALTIME, LOCALTIME([fsp]) LOCALTIMESTAMP, LOCALTIMESTAMP([fsp])

SELECT NOW(),					-->2020-07-12 16:06:55
       current_timestamp(),	    -->2020-07-12 16:06:55
       current_timestamp,	    -->2020-07-12 16:06:55
       localtime,	            -->2020-07-12 16:06:55
       localtime(),	            -->2020-07-12 16:06:55
       localtimestamp,	        -->2020-07-12 16:06:55
       localtimestamp()	        -->2020-07-12 16:06:55
       
SELECT            now(),                         now(2),                         now(6);
                2020-07-12 16:10:26        2020-07-12 16:10:26.660000000       2020-07-12 16:10:26.660563000
SELECT           NOW(),             SLEEP(2),               NOW();
             2020-07-12 16:11:46       0                 2020-07-12 16:11:46
SELECT           SYSDATE(),         SLEEP(2),              SYSDATE();
			 2020-07-12 16:11:48       0 				 2020-07-12 16:11:50

总结：

  1. curdate()函数语法：Returns the current date as a value in ‘YYYY-MM-DD’ or YYYYMMDD format, depending on whether the function is used in a string or numeric context
  2. CURTIME([fsp]) 函数语法：Returns the current time as a value in ‘HH:MM:SS’ or HHMMSS format, depending on whether the function is used in a string or numeric context. The value is expressed in the current time zone.（当前时区） If the fsp argument is given to specify a fractional seconds precision from 0 to 6, the return value includes a fractional seconds part of that many digits（如果fsp存在数值的话，那么获取对应小数位的毫秒、微秒）
  3. 获取当前时间和日期的函数见上，均为获取当前日期时间的一种方式，
  4. now()和sysdate()的区别在于：now()函数返回语句开始执行的时间；而sysdate()返回函数执行到的时间。
获取时间戳的函数

UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date) FROM_UNIXTIME(unix_timestamp), FROM_UNIXTIME(unix_timestamp,format)

SELECT UNIX_TIMESTAMP(), unix_timestamp(now() + 0), unix_timestamp('20200712162200'), unix_timestamp(20200712)
			1594542316             1594542316                 1594542120                  1594483200
SELECT from_unixtime(unix_timestamp()),                           -->2020-07-12 16:40:54
       from_unixtime(unix_timestamp()) + 0,                       -->20200712164054
       from_unixtime(unix_timestamp(), '%Y/%m/%d %H.%i.%s')       -->2020/07/12 16.40.54

总结：

  1. UNIX_TIMESTAMP 无参数时获取当前时间的时间戳，当date存在时，获取该时间的时间戳。注意date的日期时间格式问题，通用的几种格式都支持将其转为时间戳的方式。
  2. FROM_UNIXTIME(date)函数UNIX时间戳转换为普通格式的时间，与UNIX_TIMESTAMP(date)函数互为反函数，其中format可以参看DATE_FORMAT函数
获取年份、月份、星期、季度、小时、分钟和秒钟的函数

年份：YEAR(date) 月份： MONTH(date), MONTHNAME(date)，季度：QUARTER(date)

SELECT year('1999/12/12'), year(now());
				1999         2020
SELECT month('1999/12/12'), month(now()), monthname(now())
          12                  7               July
SELECT quarter('2020-07-15')
			3

星期：WEEK(date[,mode]) WEEKOFYEAR(date)

SELECT week('2020-07-11'), week('2020-07-13'), week('2020-07-13', 1)
		27						28					29
SELECT weekofyear('2020-07-15'), week('2020-07-15', 3), weekday('2020-07-15')
			29							29					2

小时 ：HOUR(time) 分钟：MINUTE(time) 秒钟：SECOND(time)

SELECT hour('2020-07-15 12:12:12'), hour(now());
				12						21
SELECT minute('2020-07-15 12:12:12'), minute(now());
				12						12
SELECT SECOND('2020-07-15 12:12:12'), SECOND(now());
				12						34

总结：

  1. WEEKOFYEAR(date) 该函数和WEEK(date,3)具有同样的效果
  2. WEEK(date[,mode])函数中的mode为可选参数，用于确定周数计算的逻辑。它允许您指定本周是从星期一还是星期日开始，可以通过sql语句：SHOW VARIABLES LIKE ‘default_week_format’ 获取默认值，关于mode影响week函数：
获取日期的指定值

EXTRACT(type FROM date)

SELECT EXTRACT(YEAR_MONTH FROM '2020-07-15 12:13:14'), extract(HOUR_MINUTE FROM '2020-07-15 12:13:14')
				202007											1213

总结：

  1. EXTRACT(type FROM date)函数提取日期中的一部分。type值为YEAR时，只返回年值。type为YEAR_MONTH时返回年与月份。type为DAY_MINUTE时，返回日、小时和分钟值。具体的type可以参看官方文档：[https://dev.mysql.com/doc/refman/8.0/en/expressions.html#temporal-interv%20als](https://dev.mysql.com/doc/refman/8.0/en/expressions.html#temporal-interv als)
计算日期和时间的函数

DATE_ADD(date,INTERVAL expr unit),ADDDATE(date,INTERVAL expr unit), ADDDATE(expr,days)

DATE_SUB(date,INTERVAL expr unit) SUBDATE(date,INTERVAL expr unit), SUBDATE(expr,days)

ADDTIME(expr1,expr2) SUBTIME(expr1,expr2)

# 加减时间
SELECT ADDTIME('2020-07-15 12:13:14', '1 1:01:01'),			2020-07-16 13:14:15
       ADDTIME('12:13:14', '1 1:01:01'),					37:14:15
       ADDTIME('2020-07-15 12:13:14', '1:01:01'),			2020-07-15 13:14:15
       SUBTIME('2020-07-15 12:13:14', '1 1:01:01'),			2020-07-14 11:12:13
       SUBTIME('12:13:14', '1 1:01:01'),					-12:47:47
       SUBTIME('2020-07-15 12:13:14', '1:01:01')			2020-07-15 11:12:13
#  增加时间
SELECT adddate('2020-07-15 12:13:14', INTERVAL 1 DAY),		2020-07-16 12:13:14
       adddate('2020-07-15 12:13:14', 1),					2020-07-16 12:13:14
       adddate('2020-07-15 12:13:14', INTERVAL 1 HOUR),		2020-07-15 13:13:14
       date_add('2020-07-15 12:13:14', INTERVAL 1 HOUR)		2020-07-15 13:13:14

总结：

  1. 使用ADDTIME(expr1,expr2) SUBTIME(expr1,expr2)对时间进行加减。
  2. ADDDATE(date,INTERVAL expr DAY), ADDDATE(expr,days)，将函数的单位修改为天之后，这两个函数的功能是等同的
  3. DATE_ADD(date,INTERVAL expr unit),ADDDATE(date,INTERVAL expr unit)两个函数的功能是一致的，均为增加固定的时间单位，具体的可以参看官方文档，链接[https://dev.mysql.com/doc/refman/8.0/en/expressions.html#temporal-interv%20als](https://dev.mysql.com/doc/refman/8.0/en/expressions.html#temporal-interv als)
日期和时间格式化

DATE_FORMAT(date,format) GET_FORMAT({DATE|TIME|DATETIME}, {‘EUR’|‘USA’|‘JIS’|‘ISO’|‘INTERNAL’})

STR_TO_DATE(str,format)

SELECT DATE_FORMAT('2020-07-15 12:13:14', '%H:%i:%s'),				12:13:14
       DATE_FORMAT('2020-07-15 12:13:14', '%Y-%m-%d %H:%i:%s')		2020-07-15 12:13:14
SELECT get_format(DATE, 'ISO'),										%Y-%m-%d
       get_format(DATETIME, 'ISO'),									%Y-%m-%d %H:%i:%s
       get_format(TIME, 'ISO')										%H:%i:%s
SELECT DATE_FORMAT('2020-07-15 12:13:14', get_format(DATE, 'ISO')),			2020-07-15
       DATE_FORMAT('2020-07-15 12:13:14', get_format(TIME, 'ISO')),			12:13:14
       DATE_FORMAT('2020-07-15 12:13:14', get_format(DATETIME, 'ISO'))		2020-07-15 12:13:14
SELECT str_to_date('2020-07-15-12:13:14', '%Y-%m-%d-%H:%i:%s'),				2020-07-15 12:13:14
       str_to_date('2020-07-15-12:13:14', '%Y-%m-%d %H:%i:%s')				NULL

总结：

  1. DATE_FORMAT(date,format)根据format指定的格式显示date值。具体的格式仍旧是参看官方文档
  2. GET_FORMAT()函数显示不同格式化类型下的格式字符串，具体的格式参看官方文档。我们可以在date_format函数中使用get_format函数
  3. STR_TO_DATE(str,format) 为将字符串按照指定的格式转换为时间，如果字符串和时间格式不匹配，返回null
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值