SQL：各种处理时间的总结

最新推荐文章于 2023-05-23 15:18:32 发布
最新推荐文章于 2023-05-23 15:18:32 发布
阅读量560 收藏 1
点赞数
分类专栏： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zhizhengguan/article/details/86364869
版权
日期类型占用空间(byte)（<5.6）占用空间(byte)(>=5.6)表示范围
DATETIME85 + 微秒存储空间1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
DATE331000-01-01 ~ 9999-12-31
TIMESTAMP44 + 微秒存储空间1970-01-01 00:00:00UTC ~ 2038-01-19 03:14:07UTC
YEAR11YEAR(2):1970-2070, YEAR(4):1901-2155
TIME33 + 微秒存储空间-838:59:59 ~ 838:59:59
微秒位数存储空间
00
1, 21 byte
3, 42 bytes
5, 63 bytes

实时时间

当前时间

[时间+日期]
> select now() AS '当前时间';
select current_timestamp() AS '当前时间';
select localtime() AS '当前时间';
select localtimestamp() AS '当前时间';
-- 上面四个完全相同，建议用now()
+---------------------+
| 当前时间            |
+---------------------+
| 2019-01-28 23:56:55 |
+---------------------+

select sysdate()  AS '当前时间';
-- sysdate() 日期时间函数跟 now() 类似，不同之处在于：now() 在执行开始时值就得到了， sysdate() 在函数执行时动态得到值。 

```sql
--
-- NOW和SYSDATE的区别
--
mysql> select now(6),sysdate(6),sleep(5),now(6),sysdate(6);
+----------------------------+----------------------------+----------+----------------------------+----------------------------+
| now(6)                     | sysdate(6)                 | sleep(5) | now(6)                     | sysdate(6)                 |
+----------------------------+----------------------------+----------+----------------------------+----------------------------+
| 2015-11-30 21:40:58.572383 | 2015-11-30 21:40:58.572542 |        0 | 2015-11-30 21:40:58.572383 | 2015-11-30 21:41:03.572720 |
+----------------------------+----------------------------+----------+----------------------------+----------------------------+
1 row in set (5.00 sec)
--
-- 两个now(6)都相等，因为是SQL执行时的时间(可以简单立理解为按回车的时间)
-- 两个sysdate(6)差了5秒，刚好是sleep(5)的时间
--

(root@127.0.0.1) [mysql]> select now(6), sysdate(6);
+----------------------------+----------------------------+
| now(6)                     | sysdate(6)                 |
+----------------------------+----------------------------+
| 2019-01-28 23:56:07.463050 | 2019-01-28 23:56:07.463103 |
+----------------------------+----------------------------+
  • 从MySQL5.6.X开始，支持微秒，最大显示6位
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2019-01-13 06:06:40 |
+---------------------+
1 row in set (0.00 sec)

mysql> select now(6);
+----------------------------+
| now(6)                     |
+----------------------------+
| 2019-01-13 06:06:43.081746 |  //  -- 由于是用了6位微秒位数，根据表格显示，实际存储的空间是 5 + 3 = 8 byte
+----------------------------+
1 row in set (0.00 sec)

mysql> select now(7);
ERROR 1426 (42000): Too-big precision 7 specified for 'now'. Maximum is 6.
[当前日期]
(root@127.0.0.1) [mysql]> SELECT  curdate() AS ‘当前日期’;
+--------------------+
| ‘当前日期’         |
+--------------------+
| 2019-01-28         |
+--------------------+
>> current_date(),current_date类似;
[当前时间]
(root@127.0.0.1) [mysql]>  SELECT CURTIME() AS '当前时间';
+--------------+
| 当前时间     |
+--------------+
| 23:59:53     |
+--------------+
> current_time(),current_time 类似
[当前 UTC 日期时间]
(root@127.0.0.1) [mysql]> SELECT utc_date();
+------------+
| utc_date() |
+------------+
| 2019-01-29 |
+------------+
> utc_date(), utc_time(), utc_timestamp() 相同

前30分钟&后30分钟

(root@127.0.0.1) [mysql]>  SELECT DATE_SUB(NOW(), INTERVAL 30 MINUTE) AS '前30分钟';
+---------------------+
| 前30分钟            |
+---------------------+
| 2019-01-28 23:31:06 |
+---------------------+
1 row in set (0.00 sec)

(root@127.0.0.1) [mysql]> SELECT DATE_SUB(NOW(), INTERVAL -30 MINUTE) AS '后30分钟';
+---------------------+
| 后30分钟            |
+---------------------+
| 2019-01-29 00:31:14 |
+---------------------+

前一个小时&后一个小时

(root@127.0.0.1) [mysql]> SELECT  DATE_SUB(NOW(), INTERVAL 1 HOUR) AS '前一个小时';
+---------------------+
| 前一个小时          |
+---------------------+
| 2019-01-28 23:02:04 |
+---------------------+
1 row in set (0.00 sec)

(root@127.0.0.1) [mysql]> SELECT DATE_SUB(NOW(), INTERVAL -1 HOUR)AS '后一个小时';
+---------------------+
| 后一个小时          |
+---------------------+
| 2019-01-29 01:02:10 |
+---------------------+

开始与结束

今天的开始与结束

(root@127.0.0.1) [mysql]> SELECT DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00') AS '今天开始';
(root@127.0.0.1) [mysql]> SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 0 DAY), '%Y-%m-%d 00:00:00') AS '今天开始';
+---------------------+
| 今天开始            |
+---------------------+
| 2019-01-29 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

(root@127.0.0.1) [mysql]> SELECT DATE_FORMAT(NOW(),'%Y-%m-%d 23:59:59') AS '今天结束';
(root@127.0.0.1) [mysql]> SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 0 DAY), '%Y-%m-%d 23:59:59') AS '今天结束';
+---------------------+
| 今天结束            |
+---------------------+
| 2019-01-29 23:59:59 |
+---------------------+

明天的开始与结束

(root@127.0.0.1) [mysql]> SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL -1 DAY), '%Y-%m-%d 00:00:00') AS '明天开始';
+---------------------+
| 明天开始            |
+---------------------+
| 2019-01-30 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

(root@127.0.0.1) [mysql]> SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL -1 DAY), '%Y-%m-%d 23:59:59') AS '明天结束';
+---------------------+
| 明天结束            |
+---------------------+
| 2019-01-30 23:59:59 |
+---------------------+

昨天的开始与结束

(root@127.0.0.1) [mysql]> SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 00:00:00') AS '昨天开始';
+---------------------+
| 昨天开始            |
+---------------------+
| 2019-01-28 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

(root@127.0.0.1) [mysql]> SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 23:59:59') AS '昨天结束';
+---------------------+
| 昨天结束            |
+---------------------+
| 2019-01-28 23:59:59 |
+---------------------+
1 row in set (0.00 sec)

本周的开始与结束

  • SELECT DATE_FORMAT( SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),’%w’)-1), ‘%Y-%m-%d 00:00:00’) AS ‘本周一’;
  • SELECT DATE_FORMAT( SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),’%w’)-7), ‘%Y-%m-%d 23:59:59’) AS ‘本周末’;
  • SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY), ‘%Y-%m-%d 00:00:00’) AS ‘本周一’;
  • SELECT DATE_FORMAT( DATE_ADD(SUBDATE(CURDATE(), WEEKDAY(CURDATE())), INTERVAL 6 DAY), ‘%Y-%m-%d 23:59:59’) AS ‘本周末’;在这里插入图片描述
    注意：mysql是按照周日为一周第一天,如果当前是周日的话,会把时间定为到下一周.

上周的开始与结束

  • SELECT DATE_FORMAT( DATE_SUB( DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY), INTERVAL 1 WEEK), ‘%Y-%m-%d 00:00:00’) AS ‘上周一’;
  • SELECT DATE_FORMAT( SUBDATE(CURDATE(), WEEKDAY(CURDATE()) + 1), ‘%Y-%m-%d 23:59:59’) AS ‘上周末’;
    在这里插入图片描述

本月的开始与结束

  • SELECT DATE_FORMAT( CURDATE(), ‘%Y-%m-01 00:00:00’) AS ‘本月初’;
  • SELECT DATE_FORMAT( LAST_DAY(CURDATE()), ‘%Y-%m-%d 23:59:59’) AS ‘本月末’;
    在这里插入图片描述

上月的开始与结束

  • SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 MONTH), ‘%Y-%m-01 00:00:00’) AS ‘上月初’;
  • SELECT DATE_FORMAT( LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)), ‘%Y-%m-%d 23:59:59’) AS ‘上月末’;
    在这里插入图片描述

两个日期之间隔了多少天

    -- DATEDIFF(o_orderdate, '1970-01-05')
    mysql> select datediff('1971-01-01', '1970-01-05');  -- 随意取一个1971-01-01，作为演示
    +--------------------------------------+
    | datediff('1971-01-01', '1970-01-05') |
    +--------------------------------------+
    |                                  361 |  -- 1971-01-01 减去 1970-01-5 为361天
    +--------------------------------------+
    1 row in set (0.00 sec)
        
    mysql> select datediff('1971-01-01', '1970-01-05') / 7;  -- 求周数
    +------------------------------------------+
    | datediff('1971-01-01', '1970-01-05') / 7 |
    +------------------------------------------+
    |                                  51.5714 |  -- 相隔51.5714周
    +------------------------------------------+
    1 row in set (0.00 sec)

总结【时间函数】

函数名函数说明备注
NOW返回SQL执行时的时间 如果不考虑其他因素，可以理解为写完SQL，敲下回车瞬间的时间
CURRENT_TIMESTAMP与NOW()函数同义
SYSDATE返回函数执行时的时间MySQL处理你的函数时的时间，统一SQL语句中，大于NOW
DATA_ADD(date, interval expr uint)增加时间
DATA_SUB(date, interval expr uint)减少时间 可用ADD，然后unit给负数
DATE FORMAT格式化时间

所有时间函数参考官方文档：https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html

mysql> create  table test_field_update(
    -> a int(10),
    -> b timestamp not null default current_timestamp on update current_timestamp
    -> );

mysql> insert into test_field_update values(1, now(6));
Query OK, 1 row affected (0.03 sec)

mysql> select * from test_field_update;
+------+---------------------+
| a    | b                   |
+------+---------------------+
|    1 | 2015-11-30 21:55:18 |  -- 上面使用了now(6)，但是这里没有微秒，是因为定义的时候就是timestamp
+------+---------------------+  -- 如果写成timestamp(6),就可以显示微秒

mysql> update test_field_update set a=100 where a=1;  -- 只更新a字段
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test_field_update;    
+------+---------------------+
| a    | b                   |
+------+---------------------+
|  100 | 2015-11-30 22:01:03 |  -- 发现b字段跟着改变了
+------+---------------------+

--
-- 测试timestamp(6)
--
mysql> create table test_time_disp(
    -> a int(10),
    -> b timestamp(6) not null default current_timestamp(6) on update current_timestamp(6)  -- 定义了(6)
    -> );

mysql> insert into test_time_disp values(1, now(6));      

mysql> select * from test_time_disp;
+------+----------------------------+
| a    | b                          |
+------+----------------------------+
|    1 | 2015-11-30 22:03:23.545406 |  -- 插入了now(6), 这里就显示了6位微秒
+------+----------------------------+

参考：https://www.jianshu.com/p/095c2952dd20
http://blog.sina.com.cn/s/blog_6d39dc6f0100m7eo.html 【推荐：这一篇文章总结的很好，推荐】

OceanStar的学习笔记
关注
专栏目录
Text2SQL提问中包括时间的实战方案
herosunly的博客
07-07 6万+
本文主要介绍了Text2SQL提问中包括时间的实战方案，希望对学习大语言模型的同学们有所帮助。 文章目录 1. 前言 2. 实战方案
sql时间处理sql时间处理sql时间处理
10-28
sql时间处理sql时间处理sql时间处理sql时间处理sql时间处理sql时间处理sql时间处理
SQL 时间处理
weixin_34314962的博客
06-13 201
为什么80%的码农都做不了架构师？>>> ...
sql 时间处理
weixin_30267785的博客
03-15 54
1. 1 declare @dt datetime 2 set @dt=GETDATE() 3 select DATEADD(mm,-6,@dt); --减半年 4 select DATEADD(yy,-1,@dt); --减1年 SELECT convert(varchar,(YEAR(LEFT('201801',4))+2))+'-07-01' 转载于:h...
SQL常用时间处理函数
编程笔记
04-19 4642
求本周第一天 select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) DAY)
SQLSERVER处理时间小结
domino的专栏
03-18 988
　　在开发数据库应用中，经常会遇到处理时间的问题，如查询指定时间的记录等。下面就这些常见的问题，结合自己的一些经验，和大家探讨一下这类问题。 　　首先介绍一下，SQL Server里处理时间的几个主要函数的用法： getdate()函数：取得系统当前的日期和时间。返回值为datetime类型的。 用法：getdate() 例子： select getdate() as dte,dateadd(da
sqlserver 中时间为空的处理小结
09-11
在SQL Server中，DateTime类型的字段经常用于存储日期和时间数据，但当这些字段的值为空（NULL）时，处理起来可能会遇到一些问题。本篇文章将总结处理SQL Server中DateTime类型字段为空值的主要方法。 首先，我们来...
sql server 获取系统时间的方法
12-15
总结一下，SQL Server中获取系统时间主要通过`GETDATE()`函数实现，结合`CONVERT()`函数可以方便地将日期时间格式化成各种风格，以满足不同的显示需求。理解这些函数及其用法对于SQL Server的日常操作至关重要，特别...
SqlServer 按时间段查询问题
09-09
总结起来，SQL Server的日期时间查询需要注意时间部分的影响，尤其是在使用`BETWEEN`操作符时。通过理解这个问题并应用上述解决方案，我们可以确保准确地按日期范围获取所需的数据。在进行此类查询时，始终要测试并...
SQL常用的时间处理方式
ycqfdqy的博客
11-24 593
sql常用几种获取时间的方式： CONVERT(VARCHAR,GETDATE(),23)--2020-11-24 CONVERT(VARCHAR,GETDATE(), 8)--14:45:34 CONVERT(VARCHAR,GETDATE(),120)--2020-11-24 14:00:47 CONVERT(VARCHAR,GETDATE(),21)--2020-11-24 14:35:12.167 DATENAME(HH,GETDATE())--获取当前小时//HH、YEAR、DAY REPLACE
在SQL Server中将本地日期时间格式化为UTC
04-02
本文旨在描述一种将本地日期时间转换为UTC并将其存储在DB中的简单而优雅的方法。
SQL的时间处理
xujingpilot的博客
10-30 701
先整理一波吧，这里主要介绍SQL中时间处理   1、字段是字符串 则直接字段 大于 或小于该时间段 即可 select * from t_person where born &gt;= '20000101' and  born &lt;= '20170101'  或者用 BETWEEN  AND （前开[后闭)） 2、字段不是字符串 我们可以用CAST函数 （转换函数），可以将字符型...
SQL必会——常见时间数据的处理
前景
08-02 616
SQL必会——常见时间数据的处理
SQL中时间处理总结
pscool的博客
03-28 1218
TIMESTAMPDIFF() 计算两个时间日期的时间差、 # 计算两个日期时间的时间差值 TIMESTAMPDIFF(time_type, begin_time, end_time) -- time_type参数(最后获取时间差的单位) /** SECOND / 秒 MINUTE / 分钟 HOUR / 小时 DAY / 天 MONTH / 月 YEAR / 年 */ -- begin_time 开始时间或开始时间字段 -- end_time 结束时间或结束时间字段 例如：获取某两个时间的时间
sql中关于时间的处理常用函数整理
d_vv_b的博客
07-22 961
1.返回当前日期及时间，日期，时间 now(),curdate(),curtime() select now(),curdate(),curtime(); 2.返回时间中的日/周/月/年 day(),week(),month(),year() select day(now()),week(now()),month(now()),year(now()); 3.将字符串转化为时间格式 str_to_date() select str_to_date('1990.1.1','%Y.%m.%d'); 4
SQL常用日期时间处理函数
WaterShuiMiao的专栏
07-12 624
<br />/**//**//**//* datepart()函数的使用                          <br />* datepart()函数可以方便的取到时期中的各个部分<br />*如日期：2006-07--02 18：15：36.513<br />* yy:取年           2006<br />* mm:取月           7<br />* dd:取月中的天     2<br />* dy:取年中的天     183<br />* wk:取年中的周     27<b
sql日期时间处理
weixin_30449453的博客
10-03 54
日期时间批量增加UPDATE ManualSign SET signtime=dateadd(month,1,signtime) where datepart(month,signtime)<=61、日期格式化处理　　DECLARE @dt datetime 　　SET @dt=GETDATE() 　　--1.短日期格式：yyyy-m-d 　　SELECT R...
SQL-时间处理汇总
最新发布
weixin_47198715的博客
05-23 3851
常见日期提取，日期格式转换，日期加减计算等时间处理方法汇总
SQL关于日期时间处理
Afreshmemory的博客
03-01 6640
SQL Date： MySQL　Date: Now() --返回当前日期和时间 2018-3-1 16:25:46 CURDATE() --返回当前日期 2018-3-1 CURTIME() --返回当前时间 16:25:46 DATE() --提取日期或日期/时间表达式的日期部分 DATE(date)
Flink SQL：流批统一处理详解
动态表是Flink SQL的核心概念，它是一种可以随时间变化的数据表。动态表可以从数据流中生成，并且可以持续接收新数据或更新。这种表的状态会随着新的事件到来而不断更新，体现了流处理的特性。连续查询则是在动态表...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值