This tutorial explains how to use the MySQL FROM_UNIXTIME() function with the help of examples. By using it, you can convert or display a UNIX_TIMESTAMP value to a timestamp.
The output format is either YYYY-MM-DD-HH-MM-SS or YYYYMMDDHHMMSS, which depends on the calling context of the function. It means that FROM_UNIXTIME() would return a string for a string and a number in a numeric operation.
Please note that MySQL also has this function UNIX_TIMESTAMP(). It gives us a value in seconds since ‘1970-01-01 00:00:00’ UTC as an unsigned number. We are going to use the result of this function in our examples.
MySQL FROM_UNIXTIME()
This function provides a date/datetime formatted in a Unix timestamp style. The return value represents the number of seconds elapsed since January 1, 1970, GMT, with 12:00:01 (EPOC TIME).
Syntax
It has the following template:
-- MySQL From_UNIXTIME functions
FROM_UNIXTIME(UNIX_TIMESTAMP, [FORMAT]);
The UNIX_TIMESTAMP parameter is a UNIX timestamp value. The FORMAT is an optional argument, and it describes the format of the output.
Let’s now go through some of the examples using the MySQL FROM_UNIXTIME() function.
We think that the following topics would even help more along with this tutorial:
Examples
Calling FROM_UNIXTIME() in string context
In this example, we are passing plain EPOC time value in the string context.
-- Numeric context
SELECT FROM_UNIXTIME(99);
SELECT FROM_UNIXTIME(1441563925);
SELECT FROM_UNIXTIME(1565043000);
The above MySQL statements would produce the date in standard string format. Check below:
-- Output
1970-01-01 00:01:39
2015-09-06 18:25:25
2019-08-05 22:10:00
Passing UNIX_TIMESTAMP() as input to FROM_UNIXTIME()
Let us check out what happens when we use the return value of UNIX_TIMESTAMP() to pass as a parameter. Here, we like to demonstrate how one function handles the output of the other.
-- Calling one function as parameter of other
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP("2019-08-07"));
SELECT UNIX_TIMESTAMP("2019-08-07"), FROM_UNIXTIME(1565136000);
SELECT UNIX_TIMESTAMP(CURDATE()), FROM_UNIXTIME(1565198329);
The above MySQL statements would produce the date in standard string format. Check below:
-- Output
2019-08-07 00:00:00
1565136000 2019-08-07 00:00:00
1565136000 2019-08-07 17:18:49
Calling FROM_UNIXTIME() in numeric context
In this example, we are passing plain EPOC time value in the string as well as in numeric context.
-- Compare string vs. numeric output
SELECT FROM_UNIXTIME(1565136000);
SELECT FROM_UNIXTIME(1565136000) + 0;
The output is as follows:
-- Output
2019-08-07 00:00:00
20190807000000
Using FROM_UNIXTIME() format argument
Now, we’ll fill up the second argument of FROM_UNIXTIME() function. And try to customize the output format.
-- Printing a formatted output using FROM_UNIXTIME()
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),'%Y %D %M %h:%i:%s %x');
The result of this command is as follows:
-- Output
2019 7th August 05:25:21 2019
Print hour from MySQL FROM_UNIXTIME() output
We’ll use DATE_FORMAT() along with FROM_UNIXTIME() to determine the format of the hour.
-- Print hour
SELECT DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP()), '%H');
The result after execution is:
-- Output
17
Summary – MySQL FROM_UNIXTIME()
We hope you should now feel comfortable in using the MySQL FROM_UNIXTIME() function. However, you can take up more examples and practice.
Also, to learn SQL from scratch to depth, do read our step by step MySQL tutorial.
