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
Can MySQL automatically store timestamp in a row?
Yes, you can achieve this in the following two ways.
First Approach At the time of creation of a table.
Second Approach At the time of writing query.
The syntax is as follows.
CREATE TABLE yourTableName ( yourDateTimeColumnName datetime default current_timestamp );
You can use alter command.
The syntax is as follows.
ALTER TABLE yourTableName ADD yourColumnName datetime DEFAULT CURRENT_TIMESTAMP;
Implement both the syntaxes now.
The first approach is as follows.
mysql> create table CurrentTimeStampDemo -> ( -> CreationDate datetime default current_timestamp -> ); Query OK, 0 rows affected (0.61 sec)
If you do not pass any parameter for the column ‘CreationDate’, MySQL by default stores the current timestamp.
Insert record in the table using insert command. The query is as follows.
mysql> insert into CurrentTimeStampDemo values(); Query OK, 1 row affected (0.12 sec) Let us now display all records from the table
using select command. The query is as follows.
mysql> select *from CurrentTimeStampDemo;
The following is the output.
+---------------------+ | CreationDate | +---------------------+ | 2019-01-02 15:53:03 | +---------------------+ 1 row in set (0.00 sec)
Second Approach
This is using ALTER command. The query to create a table is as follows.
mysql> create table Current_TimestampDemo -> ( -> Id int -> ); Query OK, 0 rows affected (0.43 sec)
Here is the query to automatically store creation time. The query is as follows.
mysql> ALTER TABLE Current_TimestampDemo -> ADD CreationDate datetime DEFAULT CURRENT_TIMESTAMP; Query OK, 0 rows affected (0.53 sec) Records: 0 Duplicates: 0 Warnings: 0
If you do not pass the value for column ‘CreationDate’. MySQL stores the current datetime value for this column. The query is as follows.
mysql> insert into Current_TimestampDemo(Id) values(1); Query OK, 1 row affected (0.19 sec)
Display records of the table using select statement. The query is as follows.
mysql> select CreationDate from Current_TimestampDemo;
The following is the output.
+---------------------+ | CreationDate | +---------------------+ | 2019-01-02 16:25:12 | +---------------------+ 1 row in set (0.00 sec)
2K+ Views
