How to Convert Timestamp to Datetime in MySQL?

In this article, we are going to learn how to convert Timestamp to Datetime in MySQL.To execute these queries, we need to first add integer data(written in timestamp format) and then use the FROM_UNIXTIME() function to convert it into “Datetime” Data Type.

FROM_UNIXTIME(): 

This function in MySQL returns a Datetime representation of a Unix Timestamp that has been entered as a parameter.

Step 1: We will create a Database

For the creation of a database, we will use the following query

Query:

CREATE DATABASE custom_db;

Step 2: Use the created Database

Now we use a created database with the help of the following query.

Query:

USE custom_db;

Step 3: Creating a table inside the database

We will create a table with a single column having datatype int for storing the timestamp values. We use the following query to create a table:

Query:

CREATE TABLE custom_table
(TIMESTAMP_VAL INT);

Step 4: Insertion of data into the created table

We will insert data in timestamp format into the table by using the following query:

Query:

INSERT INTO custom_table 
VALUES(1242187029),
(1692076451),
(1434021855);

Step 5: 

Here we need to show data in another format, so we first select it using the “SELECT statement” in MySQL. We will then pass the selected data from the TIMESTAMP_VAL column as a parameter in FROM_UNIXTIME() function. The syntax of the FROM_UNIXTIME() is : 

Syntax: 
FROM_UNIXTIME(timestamp,format)

The format is NOT compulsory here. If we do not write the format, the query used will be : 

Query I:

SELECT FROM_UNIXTIME(TIMESTAMP_VAL)
FROM custom_table;

If we use the format ‘%Y %D %M %h:%i:%s’ , then the query used will be:

Query II:

SELECT FROM_UNIXTIME(TIMESTAMP_VAL,
'%Y %D %M %h:%i:%s') FROM custom_table;