MySQL FROM_UNIXTIME Function: Timestamp To Date

The FROM_UNIXTIME() function in MySQL is used to format Unix timestamp (a timestamp in seconds since '1970-01-01 00:00:00' UTC) to a date or datetime string.

The syntax for FROM_UNIXTIME() is:

FROM_UNIXTIME(unix_timestamp)

FROM_UNIXTIME(unix_timestamp, format)

Where unix_timestamp is the Unix timestamp you want to format, and format is the format in which you want the date to be returned. If no format is specified, the function returns a string in the format 'YYYY-MM-DD HH:MM:SS'.

Here's an example of using FROM_UNIXTIME():

SELECT FROM_UNIXTIME(1686124800);

This will return '2023-05-12 00:00:00' because the Unix timestamp 1686124800 corresponds to this date and time in UTC.

You can also specify a format:

SELECT FROM_UNIXTIME(1686124800, '%Y %D %M %h:%i:%s %p');

This will return '2023 12th May 12:00:00 AM' because the format string is set to display the year, day, month, and time.

The FROM_UNIXTIME() function is very useful when dealing with Unix timestamps, which are often used in programming and system administration. It allows you to convert these timestamps into a human-readable format for display or further processing in your MySQL queries.

MySQL FROM_UNIXTIME Function Example:
- Description: The FROM_UNIXTIME function in MySQL converts a Unix timestamp to a MySQL datetime format.
- Code:
```
-- Example of using FROM_UNIXTIME
SELECT FROM_UNIXTIME(1609459200) AS converted_datetime;
```

FROM_UNIXTIME vs DATE_FORMAT in MySQL:

Description: Understand the difference between FROM_UNIXTIME (converts Unix timestamp) and DATE_FORMAT (custom date formatting).

Code:

-- Example comparing FROM_UNIXTIME and DATE_FORMAT
SELECT FROM_UNIXTIME(1609459200) AS converted_datetime,
       DATE_FORMAT(FROM_UNIXTIME(1609459200), '%Y-%m-%d %H:%i:%s') AS custom_formatted_datetime;

Examples of Using FROM_UNIXTIME in MySQL Queries:
- Description: Demonstrate various scenarios where FROM_UNIXTIME is applied for Unix timestamp conversion.
- Code: Experiment with different Unix timestamps and observe the converted datetime results.
```
-- Additional examples of using FROM_UNIXTIME
SELECT FROM_UNIXTIME(1630454400) AS example1, FROM_UNIXTIME(1625097600) AS example2, FROM_UNIXTIME(1617235200) AS example3;
```