How to Convert Timestamp to Date and Time Format in MySql

How to Convert Timestamp to Date and Time format in MySql. As Timestamp is not a human readable format, you can easily convert Timestamp to Date and Time format using an inbuilt function in MySql. If you find difficulty in converting

If this tutorial, We’ll learn how to convert a timestamp to human readable date and time format.

MySql Interview Questions.

Difference Between Inner Join and Left Join .

How to Convert TimeStamp to Date and Time Format in MySql

MySql provides FROM_UNIXTIME() method, using this method you can easily convert TimeStamp to Date and Time format.


It takes a timestamp as an argument and output a value in ‘YYYY-MM-DD HH:MM:SS‘ format. You can also convert the timestamp into other formats using modifiers.

1. How to extract date from timestamp in MySQL

We have learned how to convert a timestamp into date and time format. Suppose, we have to only extract date part as we don’t need to print time. To print

Subscribe Our Tutorials

Get Latest Updates on Facebook

To extract and print a date, we can write a query like this DATE( FROM_UNIXTIME( timestamp ) ).

How to install MySql workbench

First, FROM_UNIXTIME convert a timestamp to date and time format and then we are using DATE function to extract a date.


2.  How to extract time from a timestamp in MySQL

To print only time we use TIME(FROM_UNIXTIME(timestamp)) .

Time() method prints the time in HH:MM:SS format. You can further extract and print hour. To do this MySql provides HOUR() method. It takes time as an argument and print hour.

3. DATE_FORMAT() function in MySQL

It formats the date and time value as per format string you specify.

Print only time

Similarly, you can print the date and time in any format. Here is a list of format specifier use by DATE_FORMAT()

Reset auto-increment value in MySql

About WebRewrite

I am technology lover who loves to keep updated with latest technology. My interest field is Web Development.
Tagged , . Bookmark the permalink.