A-A+

MySQL datetime类型 mysql查询指定日期数据 指定时间数据

2020年08月27日 12:00 汪洋大海 暂无评论 阅读 83 views 次
例如查询8月27日的数据
SELECT * FROM  表名 WHERE DATE(`addtime`)='2020-08-27' 

 

假如有个表product有个字段add_time,它的数据类型为datetime,有人可能会这样写sql:

select * from product where add_time = '2013-01-12'

对于这种语句,如果你存储的格式是YY-mm-dd是这样的,那么OK,如果你存储的格式是:2013-01-12 23:23:56这种格式你就悲剧了,这是你就可以使用DATE()函数用来返回日期的部分,所以这条sql应该如下处理:

select * from product where Date(add_time) = '2013-01-12'

如果按照日期+时间模式(2013-01-12 23:23:56—时间精确到秒)查询数据库,则可以使用timestamp()函数来查询:

select * from product where timestamp(add_time) = '2013-01-12 23:23:56'

或者查询一个时间范围,可以用:

select * from product where timestamp(add_time) between '2013-01-12 03:23:56' and '2013-01-12 23:23:56';

或者

select * from product where timestamp(add_time) >= '2013-01-12 03:23:56' < '2013-01-12 23:23:56'

再来一个,如果你要查询2013年1月份加入的产品呢?


select * from product where date(add_time) between '2013-01-01' and '2013-01-31'

你还可以这样写:

select * from product where Year(add_time) = 2013 and Month(add_time) = 1

这些你该知道mysql日期函数在对你处理日期比较问题的作用了吧?

其date_col的值是在最后30天以内:


mysql> SELECT something FROM table 
WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) <= 30;

DAYOFWEEK(date)
返回日期date的星期索引(1=星期天,2=星期一, ……7=星期六)。这些索引值对应于ODBC标准。

| mysql> select DAYOFWEEK('1998-02-03'); 
-> 3 |

WEEKDAY(date)
返回date的星期索引(0=星期一,1=星期二, ……6= 星期天)。

| mysql> select WEEKDAY('1997-10-04 22:23:00'); 
-> 5 
mysql> select WEEKDAY('1997-11-05'); 
-> 2 |

DAYOFMONTH(date)
返回date的月份中日期,在1到31范围内。

| mysql> select DAYOFMONTH('1998-02-03'); 
-> 3 |

DAYOFYEAR(date)
返回date在一年中的日数, 在1到366范围内。

| mysql> select DAYOFYEAR('1998-02-03'); 
-> 34 |

MONTH(date)
返回date的月份,范围1到12。

| mysql> select MONTH('1998-02-03'); 
-> 2 |

DAYNAME(date)
返回date的星期名字。

| mysql> select DAYNAME("1998-02-05"); 
-> 'Thursday' |

MONTHNAME(date)
返回date的月份名字。

| mysql> select MONTHNAME("1998-02-05"); 
-> 'February' |

QUARTER(date)
返回date一年中的季度,范围1到4。

| mysql> select QUARTER('98-04-01'); 
-> 2 |

链接:https://www.jianshu.com/p/9305e8698b52

 

在本教程中,您将了解MySQL DATETIME数据类型以及如何使用一些方便的函数来有效地操作DATETIME类型数据。

MySQL DATETIME数据类型简介

MySQL DATETIME存储包含日期和时间的值。 当您从DATETIME列查询数据时,MySQL会以以下格式显示DATETIME值:

YYYY-MM-DD HH:MM:SS
SQL

默认情况下,DATETIME的值范围为1000-01-01 00:00:009999-12-31 23:59:59

DATETIME值使用5个字节进行存储。另外,DATETIME值可以包括格式为YYYY-MM-DD HH:MM:SS [.fraction]例如:2017-12-20 10:01:00.999999的尾数有小数秒。 当包含小数秒精度时,DATETIME值需要更多存储,如下表所示:

分数秒精度 存储(字节)
0 0
1,2 1
3,4 2
5,6 3

例如,2017-12-20 10:01:00.999999需要8个字节,2015-12-20 10:01:00需要5个字节,3个字节为.999999,而2017-12-20 10:01:00.9只需要6个字节,小数秒精度为1字节。

请注意,在MySQL 5.6.4之前,DATETIME值需要8字节存储而不是5个字节。

MySQL DATETIME与TIMESTAMP类型

MySQL提供了另一种类似于DATETIME,叫作TIMESTAMP的时间数据类型。

TIMESTAMP需要4个字节,而DATETIME需要5个字节。 TIMESTAMPDATETIME都需要额外的字节,用于分数秒精度。

TIMESTAMP值范围从1970-01-01 00:00:01 UTC2038-01-19 03:14:07 UTC。 如果要存储超过2038的时间值,则应使用DATETIME而不是TIMESTAMP

MySQL将TIMESTAMP存储在UTC(有时区)值中。 但是,MySQL存储DATETIME值是没有时区的。下面来看看看下面的例子。

首先,将当前连接的时区设置为+00:00

接下来,创建一个名为timestamp_n_datetime的表,它由两列组成:tsdt,这两列分别使用TIMESTAMPDATETIME类型,如以下语句 -

USE testdb;

CREATE TABLE timestamp_n_datetime (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ts TIMESTAMP,
    dt DATETIME
);
SQL

然后,将当前日期和时间插入到timestamp_n_datetime表的tsdt列中,如下语句所示 -

INSERT INTO timestamp_n_datetime(ts,dt)
VALUES(NOW(),NOW());
SQL

之后,从timestamp_n_datetime表查询数据,如下语句所示 -

SELECT 
    ts, 
    dt
FROM
    timestamp_n_datetime;
SQL

执行上面查询语句,得到以下结果 -

+---------------------+---------------------+
| ts                  | dt                  |
+---------------------+---------------------+
| 2017-07-26 00:38:14 | 2017-07-26 00:38:14 |
+---------------------+---------------------+
1 row in set
SQL

DATETIMETIMESTAMP列中的两个值相同。

最后,将连接的时区设置为+03:00,再次从timestamp_n_datetime表查询数据。

SET time_zone = '+03:00';

SELECT 
    ts, 
    dt
FROM
    timestamp_n_datetime;
SQL

执行上面查询语句,得到以下结果 -

+---------------------+---------------------+
| ts                  | dt                  |
+---------------------+---------------------+
| 2017-07-25 19:38:14 | 2017-07-26 00:38:14 |
+---------------------+---------------------+
1 row in set
SQL

可以看到,ts列为TIMESTAMP数据类型的值变了。这是因为在更改时区时,TIMESTAMP列以UTC为单位存储日期和时间值,根据新时区调整TIMESTAMP列的值。

这意味着如果使用TIMESTAMP数据来存储日期和时间值,则在将数据库移动到位于不同时区的服务器时时间的值可能不一样,所以应该认真考虑这个问题。

MySQL DATETIME函数

以下语句使用NOW()函数将变量@dt设置为当前日期和时间。

SET @dt =  NOW();
SQL

要查询@dt变量的值,请使用以下SELECT语句:

SELECT @dt;
SQL

执行上面查询语句,得到以下结果 -

mysql> SELECT @dt;
+---------------------+
| @dt                 |
+---------------------+
| 2017-07-25 19:41:14 |
+---------------------+
1 row in set
SQL

MySQL DATE函数

要从DATETIME值提取日期部分,请使用DATE函数,如下所示:

mysql> SELECT DATE(@dt);
+------------+
| DATE(@dt)  |
+------------+
| 2017-07-25 |
+------------+
1 row in set
SQL

如果希望根据日期查询数据,但是列中存储的数据是基于日期和时间,则此功能非常有用。

下面来看看看下面的例子。

USE testdb;

CREATE TABLE test_dt (
    id INT AUTO_INCREMENT PRIMARY KEY,
    created_at DATETIME
);

INSERT INTO test_dt(created_at)
VALUES('2017-11-05 20:29:36');
SQL

假设您想知道在2017-11-05当天创建的行,请使用以下查询:

SELECT 
    *
FROM
    test_dt
WHERE
    created_at = '2017-11-05';
SQL

执行上面查询语句,得到以下结果 -

mysql> SELECT 
    *
FROM
    test_dt
WHERE
    created_at = '2017-11-05';
Empty set
SQL

它不返回任何行记录。

这是因为created_at列不仅包含日期,还包含时间。要纠正它,请使用DATE函数,如下所示:

SELECT 
    *
FROM
    test_dt
WHERE
    DATE(created_at) = '2017-11-05';
SQL

执行上面查询语句,得到以下结果 -

+----+---------------------+
| id | created_at          |
+----+---------------------+
|  1 | 2017-11-05 20:29:36 |
+----+---------------------+
1 row in set
SQL

它按预期返回一行。 如果表有多行,MySQL必须执行全表扫描以查找与条件匹配的行。

MySQL TIME函数

要从DATETIME值中提取时间部分,可以使用TIME函数,如以下语句所示:

SELECT TIME(@dt);
SQL

执行上面查询语句,得到以下结果 -

mysql> SELECT TIME(@dt);
+-----------+
| TIME(@dt) |
+-----------+
| 19:41:14  |
+-----------+
1 row in set
SQL

MySQL YEAR, QUARTER, MONTH, WEEK, DAY, HOUR,MINUTE和SECOND函数

要从DATETIME值获取年,季,月,周,日,小时,分和秒,可以使用以下语句中所示的函数:

SET @dt =  NOW();
SELECT 
    HOUR(@dt),
    MINUTE(@dt),
    SECOND(@dt),
    DAY(@dt),
    WEEK(@dt),
    MONTH(@dt),
    QUARTER(@dt),
    YEAR(@dt);
SQL

执行上面查询语句,得到以下结果 -

+-----------+-------------+-------------+----------+-----------+------------+--------------+-----------+
| HOUR(@dt) | MINUTE(@dt) | SECOND(@dt) | DAY(@dt) | WEEK(@dt) | MONTH(@dt) | QUARTER(@dt) | YEAR(@dt) |
+-----------+-------------+-------------+----------+-----------+------------+--------------+-----------+
|        19 |          42 |          56 |       25 |        30 |          7 |            3 |      2017 |
+-----------+-------------+-------------+----------+-----------+------------+--------------+-----------+
1 row in set
SQL

MySQL DATE_FORMAT函数

要格式化DATETIME值,可以使用DATE_FORMAT函数。 例如,以下语句基于%H:%i:%s - %W%M%Y格式来格式化DATETIME值:

SET @dt =  NOW();
SELECT DATE_FORMAT(@dt, '%H:%i:%s - %W %M %Y');
SQL

执行上面查询语句,得到以下结果 -

+-----------------------------------------+
| DATE_FORMAT(@dt, '%H:%i:%s - %W %M %Y') |
+-----------------------------------------+
| 19:43:10 - Tuesday July 2017            |
+-----------------------------------------+
1 row in set
SQL

MySQL DATE_ADD函数

要将间隔添加到DATETIME值,请使用DATE_ADD函数,如下所示:

SET @dt =  NOW();
SELECT @dt start, 
       DATE_ADD(@dt, INTERVAL 1 SECOND) '1 second later',
       DATE_ADD(@dt, INTERVAL 1 MINUTE) '1 minute later',
       DATE_ADD(@dt, INTERVAL 1 HOUR) '1 hour later',
       DATE_ADD(@dt, INTERVAL 1 DAY) '1 day later',
       DATE_ADD(@dt, INTERVAL 1 WEEK) '1 week later',
       DATE_ADD(@dt, INTERVAL 1 MONTH) '1 month later',
       DATE_ADD(@dt, INTERVAL 1 YEAR) '1 year later';
SQL

执行上面查询语句,得到以下结果 -

+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
| start               | 1 second later      | 1 minute later      | 1 hour later        | 1 day later         | 1 week later        | 1 month later       | 1 year later        |
+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
| 2017-07-25 19:43:22 | 2017-07-25 19:43:23 | 2017-07-25 19:44:22 | 2017-07-25 20:43:22 | 2017-07-26 19:43:22 | 2017-08-01 19:43:22 | 2017-08-25 19:43:22 | 2018-07-25 19:43:22 |
+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
1 row in set
SQL

MySQL DATE_SUB函数

要从DATETIME值中减去一个间隔值,可以使用DATE_SUB函数,如下所示:

SET @dt =  NOW();
SELECT @dt start, 
       DATE_SUB(@dt, INTERVAL 1 SECOND) '1 second before',
       DATE_SUB(@dt, INTERVAL 1 MINUTE) '1 minute before',
       DATE_SUB(@dt, INTERVAL 1 HOUR) '1 hour before',
       DATE_SUB(@dt, INTERVAL 1 DAY) '1 day before',
       DATE_SUB(@dt, INTERVAL 1 WEEK) '1 week before',
       DATE_SUB(@dt, INTERVAL 1 MONTH) '1 month before',
       DATE_SUB(@dt, INTERVAL 1 YEAR) '1 year before';
SQL

执行上面查询语句,得到以下结果 -

+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
| start               | 1 second before     | 1 minute before     | 1 hour before       | 1 day before        | 1 week before       | 1 month before      | 1 year before       |
+
SQL

MySQL DATE_DIFF函数

要计算两个DATETIME值之间的差值,可以使用DATEDIFF函数。 请注意,DATEDIFF函数仅在计算中考虑DATETIME值的日期部分。

请参见以下示例。

首先,创建一个名为datediff_test的表,其中只有一个dt列,其数据类型为DATETIME

USE testdb;
CREATE TABLE datediff_test (
    dt DATETIME
);
SQL

其次,将一些行插入到datediff_test表中。

INSERT INTO datediff_test(dt)
VALUES('2017-04-30 07:27:39'),
 ('2017-05-17 22:52:21'),
 ('2017-05-18 01:19:10'),
 ('2017-05-22 14:17:16'),
 ('2017-05-26 03:26:56'),
 ('2017-06-10 04:44:38'),
 ('2017-06-13 13:55:53');
SQL

第三,使用DATEDIFF函数将当前日期时间与datediff_test表的每一行中的值进行比较。

SELECT 
    dt, 
    DATEDIFF(NOW(), dt)
FROM
    datediff_test;
SQL

执行上面查询语句,得到以下结果 -

+---------------------+---------------------+
| dt                  | DATEDIFF(NOW(), dt) |
+---------------------+---------------------+
| 2017-04-30 07:27:39 |                  86 |
| 2017-05-17 22:52:21 |                  69 |
| 2017-05-18 01:19:10 |                  68 |
| 2017-05-22 14:17:16 |                  64 |
| 2017-05-26 03:26:56 |                  60 |
| 2017-06-10 04:44:38 |                  45 |
| 2017-06-13 13:55:53 |                  42 |
+---------------------+---------------------+
7 rows in set
SQL

在本教程中,您已经了解了MySQL DATETIME数据类型和一些有用的DATETIME函数。

原文出自  https://www.yiibai.com/mysql/datetime.html

布施恩德可便相知重

微信扫一扫打赏

支付宝扫一扫打赏

×

给我留言