mysql 常见的时间查询

本贴最后更新于 2742 天前,其中的信息可能已经时移俗易

最近使用的一些 时间查询统计

测试表结构如下:
Create Table |

CREATE TABLE test_t (
id int(10) NOT NULL AUTO_INCREMENT,
create_time datetime DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8

两个字段 方便操作
测试数据如下:

ac3917ef3ff24044a89197002e675548-image.png

insert into test_t (id, create_time) values('1','2017-11-03 14:22:50');
insert into test_t (id, create_time) values('2','2017-11-04 14:22:54');
insert into test_t (id, create_time) values('3','2017-11-03 14:22:57');
insert into test_t (id, create_time) values('4','2017-11-01 14:23:00');
insert into test_t (id, create_time) values('5','2017-11-05 14:23:03');
insert into test_t (id, create_time) values('6','2017-11-01 14:23:11');
insert into test_t (id, create_time) values('7','2017-11-07 14:23:15');
insert into test_t (id, create_time) values('8','2017-11-02 14:23:27');
insert into test_t (id, create_time) values('9','2017-10-31 14:23:40');
insert into test_t (id, create_time) values('10','2017-11-06 14:38:37');
insert into test_t (id, create_time) values('11','2017-07-21 15:38:25');
insert into test_t (id, create_time) values('12','2017-08-18 15:38:35');
insert into test_t (id, create_time) values('13','2017-11-07 15:53:48');
insert into test_t (id, create_time) values('14','2016-11-07 16:20:25');

为了方便查看 , 都叫上了 order by 排序

1.要统计每日的新增数据 ,就要把时间戳格式的时间 FORMAT 成年-月-日的格式,并按照 format 过的字段做分组(group by),计算 count

  • SELECT DATE_FORMAT(create_time,'%Y-%m-%d') AS TIME , COUNT(*) AS COUNT FROM test_t GROUP BY TIME
    92f626f294d0438d860b2b3bb8dff90a-image.png

2.要统计最近 7 天每天的新增数据,按时间倒序排序

  • SELECT DATE_FORMAT(create_time,'%Y-%m-%d') AS TIME, COUNT(*) AS COUNT FROM test_t WHERE create_time >= DATE(NOW()) - INTERVAL 6 DAY GROUP BY DAY(create_time) ORDER BY create_time DESC;

5801efa0c1fc42ecb81f2e8fb497396f-image.png

3.要统计最近 7 天的新增数据,总计数据

  • SELECT * FROM test_t WHERE DATE_SUB(CURDATE(), INTERVAL 6 DAY) <= DATE(create_time) ORDER BY create_time DESC
    589d2709bad742e296b25c27a59aedc4-image.png

4.统计今日新增数据

  • SELECT * FROM test_t WHERE TO_DAYS(create_time) = TO_DAYS(NOW());
    10b312f6641548f382cc56ae2630c543-image.png

5.统计昨日新增数据
方法 1:

  • SELECT * FROM test_t WHERE TO_DAYS(NOW()) = 1 + TO_DAYS(create_time);
    209f1fccec3e444387d31dae04b5b2cd-image.png

方法 2: 取出昨天的和今天的记录过滤掉今天的记录

6.统计最近一周的数据
note: 最近一周不等于最近 7 天,一周是从周日开始算的,比如测试的时间是 2017-11-7,那么往前的一周就是 11-5 开始

  • SELECT * FROM test_t WHERE YEARWEEK(DATE_FORMAT(create_time,'%Y-%m-%d')) = YEARWEEK(NOW()) ORDER BY create_time DESC
    60d78eee9b9543da92cc1ac6aca04e19-image.png

7.统计最近一个月的数据
note: 最近一个月不等于最近 30 天

  • SELECT * FROM test_t WHERE create_time BETWEEN DATE_SUB(NOW(),INTERVAL 1 MONTH) AND NOW() ORDER BY create_time DESC
    df7dd728f84d4c3c8f43f1cef8036f96-image.png

8.统计上个月的数据

  • SELECT * FROM test_t WHERE PERIOD_DIFF(DATE_FORMAT(NOW() , '%Y%m') , DATE_FORMAT(create_time, '%Y%m')) = 1 ORDER BY create_time DESC
    1de438d4f8204d38afcd26a43c562e8e-image.png

9.统计本季度的数据

  • SELECT * FROM test_t WHERE QUARTER(create_time)=QUARTER(NOW()) ORDER BY create_time DESC
    7be4f3e72b3743c7bd9693a6e022479a-image.png

10.统计上个季度的数据

  • SELECT * FROM test_t WHERE QUARTER(create_time)=QUARTER(DATE_SUB(NOW(),INTERVAL 1 QUARTER)) ORDER BY create_time DESC
    d14d6c5a26e6469184f3eef3091a8107-image.png

11.统计今年的数据

  • SELECT * FROM test_t WHERE YEAR(create_time)=YEAR(NOW()) ORDER BY create_time DESC

54cca9b1ee834bbf8ec09425b69019c5-image.png

12.统计去年的数据

  • SELECT * FROM test_t WHERE YEAR(create_time)=YEAR(DATE_SUB(NOW(),INTERVAL 1 YEAR)) ORDER BY create_time DESC
    44672d8c79bb41ad95422f34044f1cdd-image.png
  • 数据库

    据说 99% 的性能瓶颈都在数据库。

    345 引用 • 747 回帖
  • MySQL

    MySQL 是一个关系型数据库管理系统,由瑞典 MySQL AB 公司开发,目前属于 Oracle 公司。MySQL 是最流行的关系型数据库管理系统之一。

    693 引用 • 537 回帖 • 1 关注
  • 时间

    这世上唯一公平的东西就是时间。

    12 引用 • 111 回帖

相关帖子

欢迎来到这里!

我们正在构建一个小众社区,大家在这里相互信任,以平等 • 自由 • 奔放的价值观进行分享交流。最终,希望大家能够找到与自己志同道合的伙伴,共同成长。

注册 关于
请输入回帖内容 ...
c3gen
羡慕有什么用,你又不努力! 成都

推荐标签 标签

  • Vditor

    Vditor 是一款浏览器端的 Markdown 编辑器,支持所见即所得、即时渲染(类似 Typora)和分屏预览模式。它使用 TypeScript 实现,支持原生 JavaScript、Vue、React 和 Angular。

    369 引用 • 1846 回帖 • 4 关注
  • 七牛云

    七牛云是国内领先的企业级公有云服务商,致力于打造以数据为核心的场景化 PaaS 服务。围绕富媒体场景,七牛先后推出了对象存储,融合 CDN 加速,数据通用处理,内容反垃圾服务,以及直播云服务等。

    28 引用 • 226 回帖 • 128 关注
  • 叶归
    8 引用 • 36 回帖 • 17 关注
  • Vim

    Vim 是类 UNIX 系统文本编辑器 Vi 的加强版本,加入了更多特性来帮助编辑源代码。Vim 的部分增强功能包括文件比较(vimdiff)、语法高亮、全面的帮助系统、本地脚本(Vimscript)和便于选择的可视化模式。

    29 引用 • 66 回帖 • 1 关注
  • PostgreSQL

    PostgreSQL 是一款功能强大的企业级数据库系统,在 BSD 开源许可证下发布。

    22 引用 • 22 回帖 • 3 关注
  • 资讯

    资讯是用户因为及时地获得它并利用它而能够在相对短的时间内给自己带来价值的信息,资讯有时效性和地域性。

    56 引用 • 85 回帖
  • Hibernate

    Hibernate 是一个开放源代码的对象关系映射框架,它对 JDBC 进行了非常轻量级的对象封装,使得 Java 程序员可以随心所欲的使用对象编程思维来操纵数据库。

    39 引用 • 103 回帖 • 725 关注
  • ZeroNet

    ZeroNet 是一个基于比特币加密技术和 BT 网络技术的去中心化的、开放开源的网络和交流系统。

    1 引用 • 21 回帖 • 647 关注
  • SMTP

    SMTP(Simple Mail Transfer Protocol)即简单邮件传输协议,它是一组用于由源地址到目的地址传送邮件的规则,由它来控制信件的中转方式。SMTP 协议属于 TCP/IP 协议簇,它帮助每台计算机在发送或中转信件时找到下一个目的地。

    4 引用 • 18 回帖 • 636 关注
  • WebComponents

    Web Components 是 W3C 定义的标准,它给了前端开发者扩展浏览器标签的能力,可以方便地定制可复用组件,更好的进行模块化开发,解放了前端开发者的生产力。

    1 引用 • 8 关注
  • WebClipper

    Web Clipper 是一款浏览器剪藏扩展,它可以帮助你把网页内容剪藏到本地。

    3 引用 • 9 回帖
  • Windows

    Microsoft Windows 是美国微软公司研发的一套操作系统,它问世于 1985 年,起初仅仅是 Microsoft-DOS 模拟环境,后续的系统版本由于微软不断的更新升级,不但易用,也慢慢的成为家家户户人们最喜爱的操作系统。

    227 引用 • 476 回帖
  • JavaScript

    JavaScript 一种动态类型、弱类型、基于原型的直译式脚本语言,内置支持类型。它的解释器被称为 JavaScript 引擎,为浏览器的一部分,广泛用于客户端的脚本语言,最早是在 HTML 网页上使用,用来给 HTML 网页增加动态功能。

    730 引用 • 1280 回帖
  • API

    应用程序编程接口(Application Programming Interface)是一些预先定义的函数,目的是提供应用程序与开发人员基于某软件或硬件得以访问一组例程的能力,而又无需访问源码,或理解内部工作机制的细节。

    79 引用 • 431 回帖
  • DevOps

    DevOps(Development 和 Operations 的组合词)是一组过程、方法与系统的统称,用于促进开发(应用程序/软件工程)、技术运营和质量保障(QA)部门之间的沟通、协作与整合。

    58 引用 • 25 回帖 • 3 关注
  • 房星科技

    房星网,我们不和没有钱的程序员谈理想,我们要让程序员又有理想又有钱。我们有雄厚的房地产行业线下资源,遍布昆明全城的 100 家门店、四千地产经纪人是我们坚实的后盾。

    6 引用 • 141 回帖 • 590 关注
  • Swagger

    Swagger 是一款非常流行的 API 开发工具,它遵循 OpenAPI Specification(这是一种通用的、和编程语言无关的 API 描述规范)。Swagger 贯穿整个 API 生命周期,如 API 的设计、编写文档、测试和部署。

    26 引用 • 35 回帖
  • 反馈

    Communication channel for makers and users.

    121 引用 • 907 回帖 • 274 关注
  • 单点登录

    单点登录(Single Sign On)是目前比较流行的企业业务整合的解决方案之一。SSO 的定义是在多个应用系统中,用户只需要登录一次就可以访问所有相互信任的应用系统。

    9 引用 • 25 回帖
  • 以太坊

    以太坊(Ethereum)并不是一个机构,而是一款能够在区块链上实现智能合约、开源的底层系统。以太坊是一个平台和一种编程语言 Solidity,使开发人员能够建立和发布下一代去中心化应用。 以太坊可以用来编程、分散、担保和交易任何事物:投票、域名、金融交易所、众筹、公司管理、合同和知识产权等等。

    34 引用 • 367 回帖 • 1 关注
  • Netty

    Netty 是一个基于 NIO 的客户端-服务器编程框架,使用 Netty 可以让你快速、简单地开发出一个可维护、高性能的网络应用,例如实现了某种协议的客户、服务端应用。

    49 引用 • 33 回帖 • 35 关注
  • OAuth

    OAuth 协议为用户资源的授权提供了一个安全的、开放而又简易的标准。与以往的授权方式不同之处是 oAuth 的授权不会使第三方触及到用户的帐号信息(如用户名与密码),即第三方无需使用用户的用户名与密码就可以申请获得该用户资源的授权,因此 oAuth 是安全的。oAuth 是 Open Authorization 的简写。

    36 引用 • 103 回帖 • 30 关注
  • JVM

    JVM(Java Virtual Machine)Java 虚拟机是一个微型操作系统,有自己的硬件构架体系,还有相应的指令系统。能够识别 Java 独特的 .class 文件(字节码),能够将这些文件中的信息读取出来,使得 Java 程序只需要生成 Java 虚拟机上的字节码后就能在不同操作系统平台上进行运行。

    180 引用 • 120 回帖 • 2 关注
  • 博客

    记录并分享人生的经历。

    273 引用 • 2388 回帖 • 1 关注
  • TextBundle

    TextBundle 文件格式旨在应用程序之间交换 Markdown 或 Fountain 之类的纯文本文件时,提供更无缝的用户体验。

    1 引用 • 2 回帖 • 81 关注
  • 机器学习

    机器学习(Machine Learning)是一门多领域交叉学科,涉及概率论、统计学、逼近论、凸分析、算法复杂度理论等多门学科。专门研究计算机怎样模拟或实现人类的学习行为,以获取新的知识或技能,重新组织已有的知识结构使之不断改善自身的性能。

    83 引用 • 37 回帖
  • 思源笔记

    思源笔记是一款隐私优先的个人知识管理系统,支持完全离线使用,同时也支持端到端加密同步。

    融合块、大纲和双向链接,重构你的思维。

    25453 引用 • 105285 回帖