Get first date from timestamp in MySQL group by another column with duplicate value


For this, you can use aggregate function MIN() and GROUP BY. Let us first create a table −

mysql> create table DemoTable1870
     (
     Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
     Value int,
     ShippingTimestamp varchar(100)
     );
Query OK, 0 rows affected (0.00 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable1870(Value,ShippingTimestamp) values(10,'1570645800');
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1870(Value,ShippingTimestamp) values(10,'1546194600');
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1870(Value,ShippingTimestamp) values(11,'1573324200');
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1870(Value,ShippingTimestamp) values(11,'1557599400');
Query OK, 1 row affected (0.00 sec)

Display all records from the table using select statement −

mysql> select * from DemoTable1870;

This will produce the following output −

+----+-------+-------------------+
| Id | Value | ShippingTimestamp |
+----+-------+-------------------+
|  1 |    10 | 1570645800        |
|  2 |    10 | 1546194600        |
|  3 |    11 | 1573324200        |
|  4 |    11 | 1557599400        |
+----+-------+-------------------+
4 rows in set (0.00 sec)

Here is the query to get first date from timestamp in MySQL &Minus;

mysql> select min(ShippingTimestamp) from DemoTable1870 group by Value;

This will produce the following output −

+------------------------+
| min(ShippingTimestamp) |
+------------------------+
| 1546194600             |
| 1557599400             |
+------------------------+
2 rows in set (0.00 sec)

Updated on: 27-Dec-2019

233 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements