- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to select part of a Timestamp in a MySQL Query?
To select part of a timestamp in a query, you need to use YEAR() function. The syntax is as follows in MySQL.
select YEAR(yourTimestampColumnName) as anyAliasName from yourTableName;
To understand the above syntax, let us create a table. The query to create a table is as follows −
mysql> create table SelectPartOfTimestampDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> ShippingTime TIMESTAMP -> ); Query OK, 0 rows affected (1.11 sec)
Now you can insert some records in the table using insert command. The query is as follows −
mysql> insert into SelectPartOfTimestampDemo(ShippingTime) values(now()); Query OK, 1 row affected (0.20 sec) mysql> insert into SelectPartOfTimestampDemo(ShippingTime) values(date_add(now(),interval -2 year)); Query OK, 1 row affected (0.17 sec) mysql> insert into SelectPartOfTimestampDemo(ShippingTime) values(date_add(now(),interval -3 year)); Query OK, 1 row affected (0.18 sec) mysql> insert into SelectPartOfTimestampDemo(ShippingTime) values(date_add(now(),interval -1 year)); Query OK, 1 row affected (0.18 sec) mysql> insert into SelectPartOfTimestampDemo(ShippingTime) values(date_add(now(),interval 1 year)); Query OK, 1 row affected (0.16 sec) mysql> insert into SelectPartOfTimestampDemo(ShippingTime) values(date_add(now(),interval 2 year)); Query OK, 1 row affected (0.48 sec) mysql> insert into SelectPartOfTimestampDemo(ShippingTime) values(date_add(now(),interval 3 year)); Query OK, 1 row affected (0.10 sec)
Display all records from the table using a select statement. The query is as follows −
mysql> select *from SelectPartOfTimestampDemo;
The following is the output −
+----+---------------------+ | 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)
Here is the query to select part of a timestamp using YEAR() function −
mysql> select YEAR(ShippingTime) as PartOfTimeStamp from SelectPartOfTimestampDemo;
The following is the output −
+-----------------+ | PartOfTimeStamp | +-----------------+ | 2019 | | 2017 | | 2016 | | 2018 | | 2020 | | 2021 | | 2022 | +-----------------+ 7 rows in set (0.00 sec)
Updated on: 30-Jul-2019
97 Views
Advertisements