How to Convert Timestamp Field to Date in Mysql Query

1. Convert Timestamp in MySQL Using Date Format
2. Convert Timestamp in MySQL Using the Unixtime
3. Convert Unixtime in MySQL Using Cast

This tutorial article will show you how to convert a timestamp field to a date field in a MySQL query. This demonstrates how to use formatting when querying data properly.

Convert Timestamp in MySQL Using Date Format

The TIMESTAMP data type allows you to register both date and time. It has 19 fixed characters that will enable a range of dates from the first second of January the 1st, 1970, to the nineteenth of January, 2038, at exactly 3 hours, 14 minutes, and 7 seconds.

The DATE_FORMAT function will allow you to take a field from a table and convert it to the desired format in a MySQL query. The following is a value from the operations table. That registers the details of any operational events, such as a shutdown, in a factory.

|      shutdown       |
| ------------------- |
| 2021-12-31 09:45:07 |

The following is an example of a conversion that will take the column shutdown and convert it from a timestamp with the default format YYYY-MM-DD HH:MM:SS to a date with the format DD-MM-YYYY.

CREATE TABLE operations (
  user TEXT NOT NULL, 
  shutdown TIMESTAMP
);

INSERT INTO operations VALUES ('Mark', '2021-12-31 09:45:07');

SELECT DATE_FORMAT(operations.shutdown, '%d %m %Y')
AS 'Shutdown Date' 
FROM operations

Output:

| Shutdown Date |
|:--------------|
|  31 12 2021   |

You can also opt to include the time in the final date format by including %H, %i, and %s for the hour, minute, and second values, respectively.

CREATE TABLE operations (
  user TEXT NOT NULL, 
  shutdown TIMESTAMP
);

INSERT INTO operations VALUES ('Mark', '2021-12-31 09:45:07');

SELECT DATE_FORMAT(operations.shutdown, '%d %m %Y %h%:%i%:%s') 
AS 'Shutdown Date' 
FROM operations

Output:

|    Shutdown Date     |
|:---------------------|
|  31 12 2021 09:45:07 |

The %i symbol is used for minutes since %m is already allocated to monthly numeric values.

Convert Timestamp in MySQL Using the Unixtime

If the timestamp is in its proper format, the date/time format 31 12 2021 09:45:07 might look like this: 1640943907. You can use FROM_UNIXTIME to convert the sequence of numbers to a date/time format.

CREATE TABLE operations (
  user TEXT NOT NULL, 
  shutdown int(11)
);

INSERT INTO operations VALUES ('Mark', 1640943907);

SELECT DATE_FORMAT(FROM_UNIXTIME(operations.shutdown), '%d %m %Y %h%:%i%:%s') 
AS 'Shutdown Date' 
FROM operations

Outcome:

|    Shutdown Date     |
|:---------------------|
|  31 12 2021 09:45:07 |

As seen above, the timestamp was converted to the same format by simply adding FROM_UNIXTIME to convert the sequence of numbers into the desired format.

Convert Unixtime in MySQL Using Cast

You can also use CAST in place of DATE_FORMAT to convert the UNIXTIME format into a date.

CREATE TABLE operations (
  user TEXT NOT NULL, 
  shutdown int(11)
);

INSERT INTO operations VALUES ('Mark', 1640943907);

SELECT CAST(FROM_UNIXTIME(operations.shutdown) 
AS DATE)
AS 'Shutdown Date'
FROM operations

Output:

| Shutdown Date |
|:--------------|
|  2021-12-31   |

However, this method restricts your ability to format the date as you want, which is why using DATE_FORMAT is preferred.