选择数据并根据MySQL中的timestamp列将值设置为boolean
为此，请使用IF()。首先让我们看看当前日期-
mysql> select curdate(); +------------+ | curdate() | +------------+ | 2019-12-10 | +------------+ 1 row in set (0.00 sec)
让我们首先创建一个表-
mysql> create table DemoTable1890 ( DueDate timestamp );
使用插入命令在表中插入一些记录-
mysql> insert into DemoTable1890 values('2017-12-10');
mysql> insert into DemoTable1890 values('2021-12-10');
mysql> insert into DemoTable1890 values('2018-04-24');
mysql> insert into DemoTable1890 values('2020-05-11');使用select语句显示表中的所有记录-
mysql> select * from DemoTable1890;
这将产生以下输出-
+---------------------+ | DueDate | +---------------------+ | 2017-12-10 00:00:00 | | 2021-12-10 00:00:00 | | 2018-04-24 00:00:00 | | 2020-05-11 00:00:00 | +---------------------+ 4 rows in set (0.00 sec)
这是根据时间戳列选择数据和设置值的查询-
mysql> select if(DueDate > curdate() - interval 2 year,false,true) as Status from DemoTable1890;
这将产生以下输出-
+--------+ | Status | +--------+ | 1 | | 0 | | 0 | | 0 | +--------+ 4 rows in set (0.00 sec)