如何在MySQL查询中选择时间戳的一部分？
要在查询中选择时间戳的一部分，您需要使用YEAR()函数。语法在MySQL中如下。
select YEAR(yourTimestampColumnName) as anyAliasName from yourTableName;
为了理解上述语法，让我们创建一个表。创建表的查询如下-
mysql> create table SelectPartOfTimestampDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> ShippingTime TIMESTAMP -> );
现在，您可以使用insert命令在表中插入一些记录。查询如下-
mysql> insert into SelectPartOfTimestampDemo(ShippingTime) values(now()); mysql> insert into SelectPartOfTimestampDemo(ShippingTime) values(date_add(now(),interval -2 year)); mysql> insert into SelectPartOfTimestampDemo(ShippingTime) values(date_add(now(),interval -3 year)); mysql> insert into SelectPartOfTimestampDemo(ShippingTime) values(date_add(now(),interval -1 year)); mysql> insert into SelectPartOfTimestampDemo(ShippingTime) values(date_add(now(),interval 1 year)); mysql> insert into SelectPartOfTimestampDemo(ShippingTime) values(date_add(now(),interval 2 year)); mysql> insert into SelectPartOfTimestampDemo(ShippingTime) values(date_add(now(),interval 3 year));
使用select语句显示表中的所有记录。查询如下-
mysql> select *from SelectPartOfTimestampDemo;
以下是输出-
+----+---------------------+ | Id | ShippingTime | +----+---------------------+ | 1 | 2019-02-08 15:03:27 | | 2 | 2017-02-08 15:03:50 | | 3 | 2016-02-08 15:04:03 | | 4 | 2018-02-08 15:04:06 | | 5 | 2020-02-08 15:04:10 | | 6 | 2021-02-08 15:04:24 | | 7 | 2022-02-08 15:04:30 | +----+---------------------+ 7 rows in set (0.00 sec)
这是使用YEAR()函数选择时间戳的一部分的查询-
mysql> select YEAR(ShippingTime) as PartOfTimeStamp from SelectPartOfTimestampDemo;
以下是输出-
+-----------------+ | PartOfTimeStamp | +-----------------+ | 2019 | | 2017 | | 2016 | | 2018 | | 2020 | | 2021 | | 2022 | +-----------------+ 7 rows in set (0.00 sec)