当前位置: 代码迷 >> MySQL >> Mysql日期类型大小比较-拉取给订时间段的记录
  详细解决方案

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

热度:140   发布时间:2016-05-05 16:47:32.0
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()

(完)
  相关解决方案