获取不在时间戳中的特定年份的记录?
您可以使用YEAR()
函数获得一年的时间戳。语法如下-
SELECT yourColumnName FROM yourTableName WHERE YEAR(yourTimestampColumnName)='yourYearValue’';
为了理解上述语法,让我们创建一个表。创建表的查询如下-
mysql> create table getYearOut -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Name varchar(10), -> yourTimestamp timestamp default current_timestamp, -> PRIMARY KEY(Id) -> );
使用INSERT命令在表中插入一些记录-
mysql> insert into getYearOut(Name,yourTimestamp) values('John',now()); mysql> insert into getYearOut(Name,yourTimestamp) values('Carol','2018-09-23 17:34:44'); mysql> insert into getYearOut(Name,yourTimestamp) values('Bob','2016-05-12 16:12:34');
使用select语句显示表中的所有记录。查询如下-
mysql> select *from getYearOut;
以下是输出-
+----+-------+---------------------+ | Id | Name | yourTimestamp | +----+-------+---------------------+ | 1 | John | 2019-02-05 11:46:11 | | 2 | Carol | 2018-09-23 17:34:44 | | 3 | Bob | 2016-05-12 16:12:34 | +----+-------+---------------------+ 3 rows in set (0.00 sec)
这是获取时间戳结束年份的查询。如果您的表有特定年份,那么查询如下-
mysql> select Id, Name from getYearOut where year(yourTimestamp)='2019';
以下是显示2019年记录的输出-
+----+-------+ | Id | Name | +----+-------+ | 1 | John + 1 row in set (0.00 sec)