MySQL - FROM_UNIXTIME() Function

---

The DATE, DATETIME and TIMESTAMP datatypes in MySQL are used to store the date, date and time, time stamp values respectively. Where a time stamp is a numerical value representing the number of milliseconds from '1970-01-01 00:00:01' UTC (epoch) to the specified time. MySQL provides a set of functions to manipulate these values.

The MYSQL FROM_UNIXTIME() function accepts a UNIX timestamp as am datetime expression as a parameter, converts it into date-time value or a character string and returns the result.

Syntax

Following is the syntax of the above function –

FROM_UNIXTIME(unix_timestamp[,format])

Example 1

Following example demonstrates the usage of the FROM_UNIXTIME() function –

mysql> SELECT FROM_UNIXTIME(0);
+----------------------------+
|         FROM_UNIXTIME('0') |
+----------------------------+
| 1970-01-01 05:30:00.000000 |
+----------------------------+
1 row in set (0.00 sec)

Example 2

Following is another example of this function –

mysql> SELECT FROM_UNIXTIME(1351708200);
+---------------------------+
| FROM_UNIXTIME(1351708200) |
+---------------------------+
|       2012-11-01 00:00:00 |
+---------------------------+
1 row in set (0.00 sec)

Example 3

We can also pass the date-time expression as an argument to this function –

mysql> SELECT FROM_UNIXTIME(1441426245.2300);
+--------------------------------+
| FROM_UNIXTIME(1441426245.2300) |
+--------------------------------+
|       2015-09-05 09:40:45.2300 |
+--------------------------------+
1 row in set (0.00 sec)

Example 4

You can also pass timestamp values in the form of a string —

mysql> SELECT FROM_UNIXTIME('463455563');
+----------------------------+
| FROM_UNIXTIME('463455563') |
+----------------------------+
| 1984-09-08 07:09:23.000000 |
+----------------------------+
1 row in set (0.00 sec)

Example 5

mysql> SELECT FROM_UNIXTIME('520108200');
+----------------------------+
| FROM_UNIXTIME('520108200') |
+----------------------------+
| 1986-06-26 00:00:00.000000 |
+----------------------------+
1 row in set (0.00 sec)

Example 6

You can also pass the column name as an argument to this function. Let us create a table with name MyPlayers in MySQL database using CREATE statement as shown below –

mysql> CREATE TABLE MyPlayers(
	ID INT,
	First_Name VARCHAR(255),
	Last_Name VARCHAR(255),
	DOBTimestamp VARCHAR(255),
	Place_Of_Birth VARCHAR(255),
	Country VARCHAR(255),
	PRIMARY KEY (ID)
);

Now, we will insert 7 records in MyPlayers table using INSERT statements:

insert into MyPlayers values(1, 'Shikhar', 'Dhawan', '376338600', 'Delhi', 'India');
insert into MyPlayers values(2, 'Jonathan', 'Trott', '356725800', 'CapeTown', 'SouthAfrica');
insert into MyPlayers values(3, 'Kumara', 'Sangakkara', '246738600', 'Matale', 'Srilanka');
insert into MyPlayers values(4, 'Virat', 'Kohli', '594671400', 'Delhi', 'India');
insert into MyPlayers values(5, 'Rohit', 'Sharma', '546719400', 'Nagpur', 'India');
insert into MyPlayers values(6, 'Ravindra', 'Jadeja', '597349800', 'Nagpur', 'India');
insert into MyPlayers values(7, 'James', 'Anderson', '394223400', 'Burnley', 'England');

Following query converts the values of a DOBTimestamp column into date-time values—

mysql> SELECT First_Name, Last_Name, Country, FROM_UNIXTIME(DOBTimestamp) as Date_Of_Birth FROM MyPlayers;
+------------+------------+-------------+----------------------------+
| First_Name |  Last_Name | Country     | Date_Of_Birth              |
+------------+------------+-------------+----------------------------+
| Shikhar    | Dhawan     | India       | 1981-12-05 00:00:00.000000 |
| Jonathan   | Trott      | SouthAfrica | 1981-04-22 00:00:00.000000 |
| Kumara     | Sangakkara | Srilanka    | 1977-10-27 00:00:00.000000 |
| Virat      | Kohli      | India       | 1988-11-05 00:00:00.000000 |
| Rohit      | Sharma     | India       | 1987-04-30 00:00:00.000000 |
| Ravindra   | Jadeja     | India       | 1988-12-06 00:00:00.000000 |
| James      | Anderson   | England     | 1982-06-30 00:00:00.000000 |
+------------+------------+-------------+----------------------------+
7 rows in set (0.00 sec)

Example 7

Let us create another table with name Sales in MySQL database using CREATE statement as follows –

mysql> CREATE TABLE sales(
	ID INT,
	ProductName VARCHAR(255),
	CustomerName VARCHAR(255),
	DispatchTimestamp VARCHAR(255),
	Price INT,
	Location VARCHAR(255)
);
Query OK, 0 rows affected (2.22 sec)

Now, we will insert 5 records in Sales table using INSERT statements −

insert into sales values (1, 'Key-Board', 'Raja', '1626586200', 7000, 'Hyderabad');
insert into sales values (2, 'Earphones', 'Roja', '1626586200', 2000, 'Vishakhapatnam');
insert into sales values (3, 'Mouse', 'Puja', '1626586199', 3000, 'Vijayawada');
insert into sales values (4, 'Mobile', 'Vanaja', '1626583252', 9000, 'Chennai');
insert into sales values (5, 'Headset', 'Jalaja', '1626586739', 6000, 'Goa');

In the following query we are passing the DispatchTimestamp column as an argument to the FROM_UNIXTIME() function —

mysql> SELECT ProductName, CustomerName, DispatchTimestamp, Price, FROM_UNIXTIME(DispatchTimestamp) as Timestamp FROM sales;
+-------------+--------------+-------------------+-------+----------------------------+
| ProductName | CustomerName | DispatchTimestamp | Price |                  Timestamp |
+-------------+--------------+-------------------+-------+----------------------------+
| Key-Board   | Raja         | 1626586200        | 7000  | 2021-07-18 11:00:00.000000 |
| Earphones   | Roja         | 1626586200        | 2000  | 2021-07-18 11:00:00.000000 |
| Mouse       | Puja         | 1626586199        | 3000  | 2021-07-18 10:59:59.000000 |
| Mobile      | Vanaja       | 1626583252        | 9000  | 2021-07-18 10:10:52.000000 |
| Headset     | Jalaja       | 1626586739        | 6000  | 2021-07-18 11:08:59.000000 |
+-------------+--------------+-------------------+-------+----------------------------+
5 rows in set (0.03 sec)

Example 8

Suppose we have created a table named SubscribersData with 5 records in it using the following queries –

mysql> CREATE TABLE SubscribersData(
	SubscriberName VARCHAR(255),
	PackageName VARCHAR(255),
	UnixTimestamp VARCHAR(50)
);
insert into SubscribersData values('Raja', 'Premium', '1603218600');
insert into SubscribersData values('Roja', 'Basic', '1606329000');
insert into SubscribersData values('Puja', 'Moderate', '1615055400');
insert into SubscribersData values('Vanaja', 'Basic', '1613845800');
insert into SubscribersData values('Jalaja', 'Premium', '1611945000');

Following query retrieves the subscription timestamps of the subscribers from the UnixTimestamp column –

mysql> SELECT SubscriberName, PackageName, UnixTimestamp, FROM_UNIXTIME(UnixTimestamp) as SubscriptionTimestamp FROM SubscribersData;
+----------------+-------------+---------------+----------------------------+
| SubscriberName | PackageName | UnixTimestamp | SubscriptionTimestamp      |
+----------------+-------------+---------------+----------------------------+
| Raja           | Premium     | 1603218600    | 2020-10-21 00:00:00.000000 |
| Roja           | Basic       | 1606329000    | 2020-11-26 00:00:00.000000 |
| Puja           | Moderate    | 1615055400    | 2021-03-07 00:00:00.000000 |
| Vanaja         | Basic       | 1613845800    | 2021-02-21 00:00:00.000000 |
| Jalaja         | Premium     | 1611945000    | 2021-01-30 00:00:00.000000 |
+----------------+-------------+---------------+----------------------------+
5 rows in set (0.00 sec)