How Can I Convert MySQL Unix Timestamps to Human-Readable Dates?

MySQL: Converting Unix Timestamps to Human-Readable Dates

Question:

How can I efficiently convert Unix timestamps stored in a MySQL table into human-readable dates?

Answer:

MySQL provides the FROM_UNIXTIME() function to perform this conversion. It takes a Unix timestamp as an argument and returns a string representation of the corresponding date and time in the specified format.

SELECT
  FROM_UNIXTIME(timestamp)
FROM
  your_table;

Example:

Consider the following table with a Unix timestamp field:

CREATE TABLE timestamps (
  id INT NOT NULL AUTO_INCREMENT,
  timestamp INT NOT NULL,
  PRIMARY KEY (id)
);

And some sample data:

INSERT INTO timestamps (timestamp) VALUES (1657414456);

To convert the timestamp to a human-readable date, execute the following query:

SELECT
  FROM_UNIXTIME(timestamp)
FROM
  timestamps;

This will output:

2023-07-06 18:34:16

Additional Notes:

• FROM_UNIXTIME() supports various date and time formats, as documented in the MySQL reference manual.
• To specify a custom date and time format, use the following syntax:

FROM_UNIXTIME(timestamp, '%Y-%m-%d %H:%i:%s')

• For timestamps with microseconds, use the MICROSECOND modifier:

FROM_UNIXTIME(timestamp, '%Y-%m-%d %H:%i:%S.%f')

The above is the detailed content of How Can I Convert MySQL Unix Timestamps to Human-Readable Dates?. For more information, please follow other related articles on the PHP Chinese website!