MySQL FROM_UNIXTIME() Function Explained with Examples – MPS


68 / 100

MySQL FROM_UNIXTIME() Function Explained with Examples – MPS

This tutorial explains methods to use the MySQL FROM_UNIXTIME() operate with the assistance of examples. By utilizing it, you possibly can convert or show a UNIX_TIMESTAMP worth to a timestamp.

The output format is both YYYY-MM-DD-HH-MM-SS or YYYYMMDDHHMMSS, which relies on the calling context of the operate. It signifies that FROM_UNIXTIME() would return a string for a string and a quantity in a numeric operation.

Please observe that MySQL additionally has this operate UNIX_TIMESTAMP(). It offers us a price in seconds since ‘1970-01-01 00:00:00’ UTC as an unsigned quantity. We are going to make use of the results of this operate in our examples.

MySQL FROM_UNIXTIME()

This operate offers a date/datetime formatted in a Unix timestamp model. The return worth represents the variety of seconds elapsed since January 1, 1970, GMT, with 12:00:01 (EPOC TIME).

Syntax

It has the next template:

-- MySQL From_UNIXTIME features
FROM_UNIXTIME(UNIX_TIMESTAMP, [FORMAT]);

The UNIX_TIMESTAMP parameter is a UNIX timestamp worth. The FORMAT is an elective argument, and it describes the format of the output.

Let’s now undergo a few of the examples utilizing the MySQL FROM_UNIXTIME() operate.

We assume that the next matters would even assist more alongside with this tutorial:

Examples

Calling FROM_UNIXTIME() in string context

In this instance, we’re passing plain EPOC time worth 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 commonplace string format. Check beneath:

-- Output
1970-01-01 00:01:39
2015-09-06 18:25:25
2019-08-05 22:10:00

Passing UNIX_TIMESTAMP() as enter to FROM_UNIXTIME()

Let us try what occurs once we use the return worth of UNIX_TIMESTAMP() to pass as a parameter. Here, we wish to show how one operate handles the output of the opposite.

-- Calling one operate as parameter of different
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 commonplace string format. Check beneath:

-- 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 instance, we’re passing plain EPOC time worth in the string as effectively 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 refill the second argument of FROM_UNIXTIME() operate. And try to customise the output format.

-- Printing a formatted output utilizing FROM_UNIXTIME()
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),'%Y %D %M %h:%i:%s %x');

The results of this command is as follows:

-- Output
2019 seventh August 05:25:21 2019

Print hour from MySQL FROM_UNIXTIME() output

We’ll use DATE_FORMAT() alongside with FROM_UNIXTIME() to find out the format of the hour.

-- Print hour
SELECT DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP()), '%H');

The consequence after execution is:

-- Output
17

Summary – MySQL FROM_UNIXTIME()

We hope you need to now really feel comfy in utilizing the MySQL FROM_UNIXTIME() operate. However, you possibly can take up more examples and follow.

Also, to be taught SQL from scratch to depth, do learn our step-by-step MySQL tutorial.

MYSQL Recommended publish:


Source link

Have any Question or Comment?

Leave a Reply

Your email address will not be published. Required fields are marked *

Categories

You have successfully subscribed to myprogrammingschool

There was an error while trying to send your request. Please try again.

My Programming School will use the information you provide on this form to be in touch with you and to provide updates and marketing.