MySQL 数据库中的时间操作与常见函数

2024-09-03 19:56:46 浏览数 (3)

MySQL 数据库中的时间操作与常见函数

我不知道大家第一次接触代码是什么，但是我可以告诉大家青阳第一次接触代码就是数据库查询语句，也就是SQL。第一本买的和编程相关的书是《mysql应知应会》。我是半路出家的，在最开始我天真的一万mysql就是所有了，接触越深感觉，直接越浅薄，也截止这次机会回顾一下，mysql数据库中的时间操作。在数据库的实际应用中，时间操作和处理是非常常见的需求。今天，就让我和大家一起回顾了解以下 MySQL 中的时间操作和常见函数。

一、MySQL 中的时间数据类型

MySQL 提供了多种时间数据类型，以满足不同的应用场景。

  1. DATE：用于日期，格式为 YYYY-MM-DD。例如，'2024-09-02'。
  2. TIME：用于时间，格式为 HH:MM:SS。比如，'10:30:00'。
  3. DATETIME：日期和时间，格式为 YYYY-MM-DD HH:MM:SS。像'2024-09-02 10:30:00'。
  4. TIMESTAMP：时间戳。

二、时间操作方法

  1. 获取当前时间
    • NOW()函数可以获取当前的日期和时间，返回值为 DATETIME 类型。例如，执行SELECT NOW();，就会返回当前的具体日期和时间。
    • CURDATE()函数用于获取当前日期，返回值为 DATE 类型。比如SELECT CURDATE();会给出今天的日期。
    • CURTIME()函数则是获取当前时间，返回值为 TIME 类型。执行SELECT CURTIME();就能看到当前时间。
  2. 时间的提取和计算
    • 可以使用YEAR()、MONTH()、DAY()、HOUR()、MINUTE()、SECOND()等函数从日期时间类型的数据中提取特定的部分。比如，SELECT YEAR('2024-09-02 10:30:00');将返回 2024，表示提取出的年份。
    • 利用DATE_ADD()和DATE_SUB()函数进行日期时间的加减运算。例如，SELECT DATE_ADD('2024-09-02', INTERVAL 1 DAY);将返回 2024-09-03，即给指定日期加上一天。

三、常见时间函数

  1. DATEDIFF()：这个函数用于计算两个日期之间的天数差。例如，SELECT DATEDIFF('2024-09-10','2024-09-02');将返回 8，表示两个日期之间相差 8 天。
  2. TIMEDIFF()：计算两个时间之间的时间差。比如，SELECT TIMEDIFF('10:30:00','10:00:00');将返回 00:30:00，表示两个时间的差值。
  3. UNIX_TIMESTAMP()：将日期时间转换为 Unix 时间戳。执行SELECT UNIX_TIMESTAMP('2024-09-02 10:30:00');会返回一个整数，表示从 1970 年 1 月 1 日到指定时间的秒数。
  4. FROM_UNIXTIME()：与UNIX_TIMESTAMP()相反，这个函数将 Unix 时间戳转换为日期时间。例如，SELECT FROM_UNIXTIME(1693644200);将返回 2024-09-02 10:30:00。

四、MySql查询当天、本周、本月、本季度、本年的数据

1.今天

代码语言：sql复制
SELECT * FROM 表名 WHERE TO_DAYS(时间字段名) = TO_DAYS(NOW());

2.昨天

代码语言：sql复制
SELECT * FROM 表名 WHERE TO_DAYS(NOW()) - TO_DAYS(时间字段名) <= 1;

3.本周

代码语言：sql复制
SELECT * FROM 表名 WHERE YEARWEEK(DATE_FORMAT(时间字段名,'%Y-%m-%d')) = YEARWEEK(NOW());

4.上周

代码语言：sql复制
SELECT * FROM 表名 WHERE YEARWEEK(DATE_FORMAT(时间字段名,'%Y-%m-%d')) = YEARWEEK(NOW())-1;

5.近7天

代码语言：sql复制
SELECT * FROM 表名 WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= DATE(时间字段名);

6.近30天

代码语言：sql复制
SELECT * FROM 表名 WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= DATE(时间字段名);

7.本月

代码语言：sql复制
SELECT * FROM 表名 WHERE DATE_FORMAT(时间字段名,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m');

8.上月

代码语言：sql复制
SELECT * FROM 表名 WHERE PERIOD_DIFF(DATE_FORMAT(NOW(),'%Y%m'),DATE_FORMAT(时间字段名,'%Y%m')) = 1;
代码语言：sql复制
SELECT * FROM 表名 WHERE DATE_FORMAT(时间字段名,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') ;
代码语言：sql复制
SELECT * FROM 表名 WHERE WEEKOFYEAR(FROM_UNIXTIME(时间字段名,'%y-%m-%d')) = WEEKOFYEAR(NOW());
代码语言：sql复制
SELECT * FROM 表名 WHERE MONTH(FROM_UNIXTIME(时间字段名,'%y-%m-%d')) = MONTH(NOW());
代码语言：sql复制
SELECT * FROM 表名 WHERE YEAR(FROM_UNIXTIME(时间字段名,'%y-%m-%d')) = YEAR(NOW()) AND MONTH(FROM_UNIXTIME(时间字段名,'%y-%m-%d')) = MONTH(NOW())；

9.近6个月

代码语言：sql复制
SELECT * FROM 表名 WHERE 时间字段名 BETWEEN DATE_SUB(NOW(),INTERVAL 6 MONTH) AND NOW();

10.本季度

代码语言：sql复制
SELECT * FROM 表名 WHERE QUARTER(时间字段名) = QUARTER(NOW());

11.上季度

代码语言：sql复制
SELECT * FROM 表名 WHERE QUARTER(时间字段名) = QUARTER(DATE_SUB(NOW(),INTERVAL 1 QUARTER));

12.本年

代码语言：sql复制
SELECT * FROM 表名 WHERE YEAR(时间字段名)=YEAR(NOW());

13.去年

代码语言：sql复制
SELECT * FROM 表名 WHERE YEAR(时间字段名) = YEAR(DATE_SUB(NOW(),INTERVAL 1 YEAR));

希望大家通过我分享的内容可以掌握。这些 MySQL 中的时间操作和常见函数，让我们可以更加灵活地处理数据库中的时间字段，满足各种各样的需求。

数据库 mysql 中国数据库前世今生 热点技术征文第九期

0 人点赞