Mysql日期类型大小比较---拉取给定时间段的记录

我们知道，mysql里边，日期类型有很多表现形式，date, datetime,timestamp等类型。考虑这样一种场景：

按时间段拉取给定时间段的内容，这时，我们就得使用日期类型的比较了。

表结构：tablename_tbl

+-----------------+--------------+------+-----+-------------------+----------------+
| Field 　　 | 　　Type | 　　Null | 　　Key | 　　Default | 　　Extra |
+-----------------+--------------+------+-----+-------------------+----------------+
| id 　　 | 　　mediumint(8) 　　| NO 　　 | PRI 　　| NULL 　　 | 　　auto_increment |
| title 　　 | varchar(40) 　　 | NO 　　 | 　　 | NULL | 　　 |
| content  | mediumtext 　　 | NO 　　 | 　　 | NULL | 　　 |
| addtime  | timestamp 　　 | NO | 　　 | 　　CURRENT_TIMESTAMP | 　　 |

其中addtime为timestamp类型的，现在我们要拉去过去三小时内新增的记录，我们可以采取比较unix时间戳以及比较日期这两种方式实现；

首先看第一种：unix时间戳比较

Mysql内置函数：UNIX_TIMESTAMP（）

若无参数调用，则默认返回一个 从 '1970-01-01 00:00:00' 到当前时刻的秒数(无符号整数)。若用date 来调用 unix_timestamp()，它会返回从 '1970-01-01 00:00:00' 到 date表示的时刻为止的秒数；date 可以是一个 date 字符串、一个 datetime字符串、一个 timestamp或一个当地时间的yymmdd 或yyymmdd格式的数字。

则sql语句可以这样写：

select id,title,content,addtime from tablename_tbl where UNIX_TIMESTAMP（addtime）>= UNIX_TIMESTAMP() - 10800 and UNIX_TIMESTAMP（addtime） <= UNIX_TIMESTAMP()   //10800为3小时的秒数。

第二种方式：日期比较

首先看直接使用+-运算：

这种方式中，特别需要注意的是，一个时间日期值加/减一个值的含义。

select now();
+---------------------+
| now() |
+---------------------+
| 2013-03-15 13:08:45 |
+---------------------+

通过now函数，我们可以获取到当前的时间，然后再请看：

select now(),now()+1;
+---------------------+-----------------------+
| now() | now()+1 |
+---------------------+-----------------------+
| 2013-03-15 13:10:15 | 20130315131016.000000 |
+---------------------+-----------------------+

select now(),now()-1;
+---------------------+-----------------------+
| now() | now()-1 |
+---------------------+-----------------------+
| 2013-03-15 13:14:18 | 20130315131417.000000 |
+---------------------+-----------------------+

这两条语句执行后，第二列表示是在当前时刻加上/减去 一秒，这里要注意看第二列的表示形式：

20130315131016.000000  （yyyy mm dd HH MM ss.000000）即：在给now()加上或者减去一个值的时候，跟now()的结果的表示形式是不同的，我们再看：

select now(),now()-01;
+---------------------+-----------------------+
| now() | now()-01 |
+---------------------+-----------------------+
| 2013-03-15 13:15:05 | 20130315131504.000000 |
+---------------------+-----------------------+

select now(),now()+01;
+---------------------+-----------------------+
| now() | now()+01 |
+---------------------+-----------------------+
| 2013-03-15 13:15:11 | 20130315131512.000000 |
+---------------------+-----------------------+

看到这里，很奇怪是不是，怎么直接在数字前加0也可以执行？加减的难道不是秒数吗？(我之前就是这样认为的)，执行的结果也是给now()加、减1秒；

select now(),now()+101;
+---------------------+-----------------------+
| now() | now()+101 |
+---------------------+-----------------------+
| 2013-03-15 13:17:14 | 20130315131815.000000 |
+---------------------+-----------------------+

select now(),now()-101;
+---------------------+-----------------------+
| now() | now()-101 |
+---------------------+-----------------------+
| 2013-03-15 13:17:22 | 20130315131621.000000 |
+---------------------+-----------------------+

我们这两条语句后的执行结果，通过对比两列，我们可以发现，分别是给now()的时刻加上1分钟、1秒钟， 减去1分钟、1秒钟；再看个例子

select now(),now()+0101;
+---------------------+-----------------------+
| now() | now()+0101 |
+---------------------+-----------------------+
| 2013-03-15 13:19:34 | 20130315132035.000000 |
+---------------------+-----------------------+

select now(),now()-0101;
+---------------------+-----------------------+
| now() | now()-0101 |
+---------------------+-----------------------+
| 2013-03-15 13:19:38 | 20130315131837.000000 |
+---------------------+-----------------------+

是不是跟奇怪了，这两条语句同样也是给当前时间加/减1分钟1秒钟。再看一个结合了年月日的例子：

select now(), now()+00010203040506;
+---------------------+-----------------------+
| now() | now()+00010203040506 |
+---------------------+-----------------------+
| 2013-03-15 13:21:50 | 20140518172656.000000 |
+---------------------+-----------------------+

select now(), now()-00010203040506;
+---------------------+-----------------------+
| now() | now()-00010203040506 |
+---------------------+-----------------------+
| 2013-03-15 13:21:54 | 20120112091648.000000 |
+---------------------+-----------------------+

看到这里，我们大概就能够明白了+-的含义了吧；上边两条语句是：给当前日期加减：1年2个月3天 4小时5分钟6秒钟。

所以，当我们在给now（）+-一个时间的时候，其实应该这样理解的：

+1/+01：加1秒钟

+101/+0101：加1分钟1秒钟

+10101/+010101：加1小时1分钟1秒钟

+1010101/+01010101：加1天1分钟1秒钟

+101010101/+0101010101：加1月1天1分钟1秒钟

+1101010101/+010101010101：加1年1月1天1分钟1秒钟,这里要注意下，年这个部分可以是4位(高位没有的话会补零)：00010101010101

(减也是类似的)

所以对于前边的问题：拉去过去三小时内新增的记录， 就可以这样写sql了：

select id,title,content,addtime from tablename_tbl where addtime >= now() - 30000 and addtime <= now()

另外还有一种方式就是使用mysql提供的：

date_add（向日期添加指定的时间间隔）， DATE_ADD(date,INTERVAL expr type)

date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。

Type 值
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH

date_sub（向日期减去指定的时间间隔）， DATE_SUB(date,INTERVAL expr type) (使用方法同date_add类似)

sql可以这样写：

select id,title,content,addtime from tablename_tbl where addtime >= date_sub(now, INTERVAL 3 HOUR) AND addtime < now()

(完)

时间： 2025-01-05 20:23:50

Mysql日期类型大小比较---拉取给定时间段的记录的相关文章

mysql使用GROUP BY分组实现取前N条记录的方法_Mysql

本文实例讲述了mysql使用GROUP BY分组实现取前N条记录的方法.分享给大家供大家参考,具体如下: MySQL中GROUP BY分组取前N条记录实现 mysql分组,取记录 GROUP BY之后如何取每组的前两位下面我来讲述mysql中GROUP BY分组取前N条记录实现方法. 这是测试表(也不知道怎么想的,当时表名直接敲了个aa,汗~~~~): 结果: 方法一: 复制代码 代码如下: SELECT a.id,a.SName,a.ClsNo,a.Score FROM aa a LEFT J

MySQL日期数据类型、时间类型使用总结_Mysql

MySQL 日期类型:日期格式.所占存储空间.日期范围 比较. 日期类型        存储空间       日期格式                 日期范围 ------------ ---------   --------------------- ----------------------------------------- datetime       8 bytes   YYYY-MM-DD HH:MM:SS   1000-01-01 00:00:00 ~ 9999-12-31

详解MySQL日期 字符串 时间戳互转

平时比较常用的时间.字符串.时间戳之间的互相转换,虽然常用但是几乎每次使用时候都喜欢去搜索一下用法:本文将作为一个笔记,整理一下三者之间的 转换(即:date转字符串.date转时间戳.字符串转date.字符串转时间戳.时间戳转date,时间戳转字符串)用法,方便日后查看: 涉及的函数 date_format(date, format) 函数,MySQL日期格式化函数date_format() unix_timestamp() 函数 str_to_date(str, format) 函数 fro

MySQL · 功能介绍 · binlog拉取速度的控制

binlog拉取存在的问题 MySQL 主备之间数据同步是通过binlog进行的,当主库更新产生binlog时,备库需要同步主库的数据,通过binlog协议从主库拉取binlog进行数据同步,以达到主备数据一致性的目的.但当主库tps较高时会产生大量的binlog,以致备库拉取主库产生的binlog时占用较多的网络带宽,引起以下问题: 在MySQL中,写入与读取binlog使用的是同一把锁(Lock_log),频繁的读取binlog,会加剧Lock_log冲突,影响主库执行,进而造成TPS降低或

MySQL各种日期类型与整型（转）

日期类型 存储空间 日期格式 日期范围 datetime 8 bytes YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 timestamp 4 bytes YYYY-MM-DD HH:MM:SS 1970-01-01 00:00:01 ~ 2038 date 3 bytes YYYY-MM-DD 1000-01-01 ~ 9999-12-31 year 1 bytes YYYY 1901 ~ 2155   timest

struts2 form 接受extjs datefield日期，IE后台可以取到String类型的日期，而ff只能取到null

问题描述 extjsdatefield如下://日历控件Ext.onReady(function(){vardatefield1=newExt.form.DateField({id:'date1',name:'boardingPass.dateofbirth',editable:false,//不允许对日期进行编辑width:120,format:'Y-m-d',emptyText:'请选择出生日期...'});datefield1.render('divdateofbirth');}strut

mysql中日期比较大小方法详解

假如有个表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 *

MYSQL列类型参考

mysql|参考    本附录介绍MySQL提供的每种列类型.关于利用每种类型的详细说明请参阅第2 章.除非另有说明,否则所列出的类型早在MySQL3.21.0 中就已经有了.    按下列约定给出类型名说明:    方括号( [ ]) 可选信息.    M 最大显示宽度.除非另有说明,否则M 应该是一个1到255 之间的整数.    D 有小数部分的类型的小数位数.D 为一个0 到30 之间的整数.D 应该小于等于M - 2.否则,M 的值将调整为D + 2.    在ODBC 术语中,M 和

Java 日期类型详解

 java.util.Date表示特定的瞬间,精确到了毫秒: Timestamp() Time(): java api中日期类型的继承关系     java.lang.Object       --java.util.Date           --java.sql.Date             --java.sql.Time               --java.sql.Timestamp 回到顶部  java.util.Date表示特定的瞬间,精确到了毫秒:      两个构造函数