How to convert Unix timestamp to Datetime in MySQL

🎈 🎈 🎈


    SELECT FROM_UNIXTIME(1321419395) AS formatted_date;

Output:

--------------------------- | formatted_date | --------------------------- | 2011-11-16 04:56:35 |

Example only. There may be multiple ways to perform this operation.

Code Explanation

The FROM_UNIXTIME() function in MySQL takes a Unix timestamp (which represents the number of seconds since January 1, 1970) as an argument and converts it into a readable date and time format ('YYYY-MM-DD HH:MM:SS').
In this example, the timestamp 1321419395 is converted to 2011-11-16 04:56:35.

Convert Date to Unix timestamp in MySql

Other useful SQL (MySQL) date functions

Function	Description	Example
`NOW()`	Returns the current date and time in the format 'YYYY-MM-DD HH:MM:SS'.	`NOW()` returns 2024-08-28 12:34:56
`CURDATE()`	Returns the current date in the format 'YYYY-MM-DD'.	`CURDATE()` returns 2024-08-28
`CURTIME()`	Returns the current time in the format 'HH:MM:SS'.	`CURTIME()` returns 12:34:56
`UNIX_TIMESTAMP()`	Returns the current Unix timestamp or the Unix timestamp for a given date.	`UNIX_TIMESTAMP('2023-08-24 00:00:00')` returns 1692835200
`DATE_ADD(date, INTERVAL expr unit)`	Adds a specified time interval to a date.	`DATE_ADD('2023-08-24', INTERVAL 5 DAY)` returns 2023-08-29
`DATE_SUB(date, INTERVAL expr unit)`	Subtracts a specified time interval from a date.	`DATE_SUB('2023-08-24', INTERVAL 3 MONTH)` returns 2023-05-24
`DATEDIFF(date1, date2)`	Returns the difference in days between two dates.	`DATEDIFF('2023-08-24', '2023-01-01')` returns 235
`STR_TO_DATE(str, format)`	Parses a string into a date according to the specified format.	`STR_TO_DATE('24-08-2023', '%d-%m-%Y')` returns 2023-08-24
`DATE_FORMAT(date, format)`	Formats a date according to a specified format string.	`DATE_FORMAT('2023-08-24', '%W %M %d %Y')` returns Thursday August 24 2023

Current Unix Timestamp SECONDS SINCE JAN 01 1970

Current UTC Time

2025-04-26