- 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
Get only the date in timestamp in MySQL?
In order to get the date from the timestamp, you can use DATE() function from MySQL.
The syntax is as follows −
Syntax
SELECT DATE(yourTimestampColumnName) as anyVariableName from yourTableName;
To understand the above syntax, let us first create a table. The query to create a table is as follows −
mysql> create table DateFromTimestamp -> ( -> ShippingDateTime timestamp -> ); Query OK, 0 rows affected (0.60 sec)
Insert date and time for the column ShippingDateTime we created above.
The query to insert record is as follows −
mysql> insert into DateFromTimestamp values('2012-12-26 13:24:35'); Query OK, 1 row affected (0.14 sec) mysql> insert into DateFromTimestamp values('2013-11-26 14:36:40'); Query OK, 1 row affected (0.13 sec) mysql> insert into DateFromTimestamp values('2016-07-22 15:20:10'); Query OK, 1 row affected (0.19 sec) mysql> insert into DateFromTimestamp values('2017-11-04 04:25:30'); Query OK, 1 row affected (0.15 sec)
Display all records from the table using select command. The query is as follows −
mysql> select *from DateFromTimestamp;
Output
+---------------------+ | ShippingDateTime | +---------------------+ | 2012-12-26 13:24:35 | | 2013-11-26 14:36:40 | | 2016-07-22 15:20:10 | | 2017-11-04 04:25:30 | +---------------------+ 4 rows in set (0.00 sec)
The following is the query to display only date from a timestamp using date() function −
mysql> select date(ShippingDateTime) as OnlyDatePartFromTimestamp from DateFromTimestamp;
Output
+---------------------------+ | OnlyDatePartFromTimestamp | +---------------------------+ | 2012-12-26 | | 2013-11-26 | | 2016-07-22 | | 2017-11-04 | +---------------------------+ 4 rows in set (0.00 sec)
Advertisements