mysql中获取一天、一周、一月时间数据的各种sql语句写法

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介: 来源:http://www.jb51.net/article/50505.htm 今天抽时间整理了一篇mysql中与天、周、月有关的时间数据的sql语句的各种写法,部分是收集资料,全部手工整理,自己学习的同时,分享给大家,并首先默认创建一个表、插入2条数据,便于部分数据的测试,其中部分名词或函数进行了解释说明。
来源:http://www.jb51.net/article/50505.htm
 
今天抽时间整理了一篇mysql中与天、周、月有关的时间数据的sql语句的各种写法,部分是收集资料,全部手工整理,自己学习的同时,分享给大家,并首先默认创建一个表、插入2条数据,便于部分数据的测试,其中部分名词或函数进行了解释说明。直入主题
 

创建表:

 

复制代码代码如下:
create table if not exists t (    id int,    addTime datetime default '0000-00-00 00:00:00′ )

 

添加两条初始数据:

insert t values(1, '2012-07-12 21:00:00′); insert t values(2, '2012-07-22 21:00:00′);

一、当天或当日插入的数据:

1、传统对比判断:SELECT * FROM `t` WHERE DATE_FORMAT(addTime,'%Y-%m-%d') = date_format(now(),'%Y-%m-%d')"); 2、第一点的简写:SELECT * FROM `t` WHERE addTime >= date_format(NOW(),'%Y-%m-%d'); 3、利用函数判断:SELECT * FROM `t` WHERE DATEDIFF(addTime,NOW()) =0;//推荐此方法 4、利用时间戳判断:SELECT * FROM `t` WHERE addTime BETWEEN (UNIX_TIMESTAMP(now()-86440)) AND now(); 注:返回当天时间的mysql函数有CURDATE()、CURRENT_DATE()、CURRENT_DATE、NOW()几种;其中NOW()获取的日期格式为0000-00-00 00:00:00的时间;CURDATE()、CURRENT_DATE()、CURRENT_DATE是获取日期格式为0000-00-00的时间,所以返回的时间无时分秒;

二、当月的相关数据写法:

1、今天是当月的第几天:SELECT DAYOFMONTH( NOW( ) ); 2、获取当月的第一天时间:SELECT DATA_SUB( NOW(), INTERVAL DAYOFMONTH(NOW()) – 1 DAY); 日期运算函数,句型:date_add(date,INTERVAL expr type)和date_sub(date,INTERVAL expr type) date为一个datetime或date值,可当作起始时间,expr为一个表达式,用来指定增加或减少的时间间隔数目,可为一个字符串–若为负值,则在表达式前加个"-"符号。type为关键词,它表示了表达式被解释的方式,通常用年(year)、月(month)、日(day)、周(week)等。 INTERVAL的用户说明:

1、当函数使用时,即INTERVAL(),为比较函数,如:INTERVAL(10,1,3,5,7); 结果为4; 原理:10为被比较数,后面1,3,5,7为比较数,将后面四个依次与10比较,看后面数字组有多少个少于10,则返回其个数。前提是后面数字组为从小到大排列,否则返回结果0。 2、当关键词使用时,表示为设置时间间隔,常用在date_add()与date_sub()函数里,如:INTERVAL 1 DAY ,解释为将时间间隔设置为1天。

弄清楚了上面几个重要的日期运算函数,下面再来一个混合的日期运算。 3、上个月的第一天时间:SELECT DATE_SUB( DATE_SUB( NOW( ) , INTERVAL DAYOFMONTH( NOW( ) ) -1 DAY ) , INTERVAL 1 MONTH ); 是不是一目了然了!

三、当周的相关数据写法:

1、获取今天是一周第几天或星期几:SELECT WEEKDAY(now());返回的是数字:0为周一,6为周日 2、获取本周的第一天日期:SELECT DATE_SUB(now(),INTERVAL WEEKDAY(now()) day);或SELECT DATE_ADD(now(),INTERVAL -WEEKDAY(now()) day);或 SELECT CURDATE( ) – WEEKDAY( CURDATE( ) ); 3、再写一个上周的第一天至现在的数据:(以表t为数据表) SELECT * FROM `t` WHERE addTime >= date_format(date_sub(date_sub(now(), INTERVAL WEEKDAY(NOW()) DAY), INTERVAL 1 WEEK), ‘%Y-%m-%d');是不是有些感觉了!

注:若你是以时间戳保存的时间字段,那么请用from_unixtime()函数转换为时间格式,如:from_unixtime($timestamp)

四、Mysql中将日期转为时间戳

前三点的方法,讲的都是将日期转为相应时间常量,并没有提到时间戳与时间的转换,这里把它写出来,

1、时间戳转日期,方法是select from_unixtime(1336542121); 2、日期转时间戳,方法是:select unix_timestamp('2013-04-08′);

结合前面3点,对于时间戳就更能灵活运用了!最后更新于:2013.04.08
mysql查询 昨天 一周前 一月前 一年前的数据

mysql 昨天 一周前 一月前 一年前的数据 这里主要用到了DATE_SUB,

参考如下

 

复制代码代码如下:
SELECT * FROM yh_content where inputtime>DATE_SUB(CURDATE(), INTERVAL 1 DAY) where inputtime>DATE_SUB(CURDATE(), INTERVAL 1 WEEK) where inputtime>DATE_SUB(CURDATE(), INTERVAL 1 MONTH) where inputtime>DATE_SUB(CURDATE(), INTERVAL 1 YEAR)

 

注意:如果数据库中时间以UNIX时间戳的形式存放的话,在时间对比上需要更改为统一格式:

DATE_SUB()返回的是格式化后的时间:2014-05-17

需要用UNIX_TIMESTAMP()转化为UNIX时间戳形式对比:

 

复制代码代码如下:
where inputtime>UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 DAY)) where inputtime>UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 WEEK)) where inputtime>UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)) where inputtime>UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 YEAR))

 

详细请查看MySql时间函数:DATE_SUB、DATE_ADD、UNIX_TIMESTAMP等函数的用法

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
5天前
|
SQL 关系型数据库 MySQL
MySQL进阶突击系列(07) 她气鼓鼓递来一条SQL | 怎么看执行计划、SQL怎么优化?
在日常研发工作当中,系统性能优化,从大的方面来看主要涉及基础平台优化、业务系统性能优化、数据库优化。面对数据库优化,除了DBA在集群性能、服务器调优需要投入精力,我们研发需要负责业务SQL执行优化。当业务数据量达到一定规模后,SQL执行效率可能就会出现瓶颈,影响系统业务响应。掌握如何判断SQL执行慢、以及如何分析SQL执行计划、优化SQL的技能,在工作中解决SQL性能问题显得非常关键。
|
6天前
|
SQL 存储 关系型数据库
MySQL原理简介—1.SQL的执行流程
本文介绍了MySQL驱动、数据库连接池及SQL执行流程的关键组件和作用。主要内容包括:MySQL驱动用于建立Java系统与数据库的网络连接;数据库连接池提高多线程并发访问效率;MySQL中的连接池维护多个数据库连接并进行权限验证;网络连接由线程处理,监听请求并读取数据;SQL接口负责执行SQL语句;查询解析器将SQL语句解析为可执行逻辑;查询优化器选择最优查询路径;存储引擎接口负责实际的数据操作;执行器根据优化后的执行计划调用存储引擎接口完成SQL语句的执行。整个流程确保了高效、安全地处理SQL请求。
114 75
|
6天前
|
Java 关系型数据库 MySQL
SpringBoot 通过集成 Flink CDC 来实时追踪 MySql 数据变动
通过详细的步骤和示例代码,您可以在 SpringBoot 项目中成功集成 Flink CDC,并实时追踪 MySQL 数据库的变动。
80 43
|
2月前
|
SQL 存储 关系型数据库
【MySQL基础篇】全面学习总结SQL语法、DataGrip安装教程
本文详细介绍了MySQL中的SQL语法,包括数据定义(DDL)、数据操作(DML)、数据查询(DQL)和数据控制(DCL)四个主要部分。内容涵盖了创建、修改和删除数据库、表以及表字段的操作,以及通过图形化工具DataGrip进行数据库管理和查询。此外,还讲解了数据的增、删、改、查操作,以及查询语句的条件、聚合函数、分组、排序和分页等知识点。
【MySQL基础篇】全面学习总结SQL语法、DataGrip安装教程
|
2月前
|
存储 关系型数据库 MySQL
mysql怎么查询longblob类型数据的大小
通过本文的介绍,希望您能深入理解如何查询MySQL中 `LONG BLOB`类型数据的大小,并结合优化技术提升查询性能,以满足实际业务需求。
161 6
|
2月前
|
SQL 存储 缓存
MySQL进阶突击系列(02)一条更新SQL执行过程 | 讲透undoLog、redoLog、binLog日志三宝
本文详细介绍了MySQL中update SQL执行过程涉及的undoLog、redoLog和binLog三种日志的作用及其工作原理,包括它们如何确保数据的一致性和完整性,以及在事务提交过程中各自的角色。同时,文章还探讨了这些日志在故障恢复中的重要性,强调了合理配置相关参数对于提高系统稳定性的必要性。
|
2月前
|
SQL 关系型数据库 MySQL
MySQL 高级(进阶) SQL 语句
MySQL 提供了丰富的高级 SQL 语句功能,能够处理复杂的数据查询和管理需求。通过掌握窗口函数、子查询、联合查询、复杂连接操作和事务处理等高级技术,能够大幅提升数据库操作的效率和灵活性。在实际应用中,合理使用这些高级功能,可以更高效地管理和查询数据,满足多样化的业务需求。
279 3
|
2月前
|
SQL 关系型数据库 MySQL
MySQL导入.sql文件后数据库乱码问题
本文分析了导入.sql文件后数据库备注出现乱码的原因,包括字符集不匹配、备注内容编码问题及MySQL版本或配置问题,并提供了详细的解决步骤,如检查和统一字符集设置、修改客户端连接方式、检查MySQL配置等,确保导入过程顺利。
|
2月前
|
SQL 存储 关系型数据库
MySQL进阶突击系列(01)一条简单SQL搞懂MySQL架构原理 | 含实用命令参数集
本文从MySQL的架构原理出发,详细介绍其SQL查询的全过程,涵盖客户端发起SQL查询、服务端SQL接口、解析器、优化器、存储引擎及日志数据等内容。同时提供了MySQL常用的管理命令参数集,帮助读者深入了解MySQL的技术细节和优化方法。
|
SQL 关系型数据库 MySQL
sql点滴42—mysql中的时间转换
原文:sql点滴42—mysql中的时间转换 UNIX时间戳转换为日期用函数: FROM_UNIXTIME()   select FROM_UNIXTIME(1156219870);   日期转换为UNIX时间戳用函数: UNIX_TIMESTAMP()   Select UNIX...
973 0