MySQL FROM_UNIXTIME() Function

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:

• MySQL TIMESTAMP
• MySQL Date and Date Functions

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.