How to Convert MySQL Timestamps to Datetime Strings?

MySQL: Converting Timestamps to Datetime

Converting timestamps to datetime is a common task when working with data in MySQL. A timestamp is a numeric representation of a specific point in time, typically expressed as the number of seconds that have elapsed since a reference point (often referred to as the Unix epoch).

Question:

How can I convert a timestamp value like 1300464000 to a datetime string in the format "2011-03-18 16:00:00"?

Answer:

To convert a timestamp to a datetime in MySQL, you can use the FROM_UNIXTIME() function. This function takes a timestamp as input and returns a datetime string. The syntax is as follows:

FROM_UNIXTIME(timestamp)

In this case, you can use the following query to convert the timestamp 1300464000 to a datetime:

SELECT FROM_UNIXTIME(1300464000);

This query will return the following result:

2011-03-18 16:00:00

Note:

If you are using a framework that stores timestamps in milliseconds (e.g., Java), you will need to divide the timestamp by 1000 to obtain the correct Unix time in seconds before using the FROM_UNIXTIME() function.

The above is the detailed content of How to Convert MySQL Timestamps to Datetime Strings?. For more information, please follow other related articles on the PHP Chinese website!