CODEWITHSUNDEEP
mysql
Antonio Mailtraq
Antonio Mailtraq

Reputation: 1407

MySQL compare DateTime

I have this data table in database MySQL.

+---------------------+---------------------+--------+----+
| Start___Date        | End___Date          | Coding | ID |
+---------------------+---------------------+--------+----+
| 2014-12-16 18:08:51 | 2014-12-16 20:00:16 | 7605   | 1  |
| 2014-12-16 22:01:39 | 2014-12-16 23:36:36 | 9905   | 2  |
| 2014-12-16 22:45:00 | 2014-12-16 23:36:36 | 9905   | 3  |
| 2014-12-16 23:18:32 | 2014-12-16 23:19:04 | 9905   | 4  |
+---------------------+---------------------+--------+----+

In this table I have three identical codings: 9905.

I need with one query delete all rows when the same coding is repeated within an hour of the time of start date.

In this case I need canceling the row number three.

+---------------------+---------------------+--------+----+
| Start___Date        | End___Date          | Coding | ID |
+---------------------+---------------------+--------+----+
| 2014-12-16 18:08:51 | 2014-12-16 20:00:16 | 7605   | 1  |
| 2014-12-16 22:01:39 | 2014-12-16 23:36:36 | 9905   | 2  |
| 2014-12-16 23:18:32 | 2014-12-16 23:19:04 | 9905   | 4  |
+---------------------+---------------------+--------+----+

Because the same coding 9905 is recorded in the table twice within an hour compared to the start time (22:01) and save the record number 4 with coding 9905 because the start time (23:18) is greater compared to the start time (22:01) with the same coding.

Can you help me? Thank you in advance.

EDIT #1

I have tried this query JOIN but the output is wrong:

mysql> SELECT
    CB.Start___Date,
    CB.End___Date,
    CB.Coding
FROM
    doTable AS CB
JOIN doTable AS CD ON (CB.Coding = CD.Coding)
WHERE
    TIME_TO_SEC(
        TIMEDIFF(
            CB.Start___Date,
            CD.Start___Date
        )
    ) / 60 > 60
GROUP BY
    CB.Coding;
+---------------------+---------------------+--------+
| Start___Date        | End___Date          | Coding |
+---------------------+---------------------+--------+
| 2014-12-16 23:18:32 | 2014-12-16 23:19:04 |   9905 |
+---------------------+---------------------+--------+
1 row in set

Upvotes: 0

Views: 81

Answers (2)

Hamamelis
Hamamelis

Reputation: 2115

This is my solution for your problem, happy coding!

mysql> DROP TABLE
IF EXISTS `doTable1`;

CREATE TABLE `doTable1` (
    `coding` INT (10) DEFAULT NULL,
    `Start___Date` datetime DEFAULT NULL,
    `id` INT (10) NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`id`)
) ENGINE = MyISAM;

INSERT INTO `doTable1` (coding, Start___Date)(
    SELECT
        coding,
        MIN(Start___Date)
    FROM
        doTable
    GROUP BY
        coding
);

SELECT
    a.*, b.*, TIMEDIFF(
        a.Start___Date,
        b.Start___Date
    ) AS diff
FROM
    doTable a
JOIN `doTable1` b ON a.coding = b.coding
WHERE
    (
        TIMEDIFF(
            a.Start___Date,
            b.Start___Date
        ) = '00:00:00'
        OR TIMEDIFF(
            a.Start___Date,
            b.Start___Date
        ) >= '01:01:01'
    );
Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 2 rows affected
Records: 2  Duplicates: 0  Warnings: 0

+--------+---------------------+---------------------+----+--------+---------------------+----+----------+
| Coding | End___Date          | Start___Date        | ID | coding | Start___Date        | id | diff     |
+--------+---------------------+---------------------+----+--------+---------------------+----+----------+
|   7605 | 2014-12-16 20:00:16 | 2014-12-16 18:08:51 |  1 |   7605 | 2014-12-16 18:08:51 |  1 | 00:00:00 |
|   9905 | 2014-12-16 23:36:36 | 2014-12-16 22:01:39 |  2 |   9905 | 2014-12-16 22:01:39 |  2 | 00:00:00 |
|   9905 | 2014-12-16 23:19:04 | 2014-12-16 23:18:32 |  4 |   9905 | 2014-12-16 22:01:39 |  2 | 01:16:53 |
+--------+---------------------+---------------------+----+--------+---------------------+----+----------+
3 rows in set

Upvotes: 1

Luke Holmwood
Luke Holmwood

Reputation: 60

With the following you can query the minutes and then check if its bigger or smaller than an hour.

datediff(minute,@startDate,@endDate) as minutes;

after you have the minutes and determined if its smaller than an hour you can then delete the rows.

Something similar to the following: (May not be exact but you get the point)

DELETE
FROM
    ExampleTable
WHERE 
    DATEDIFF(minute,@startDate,@endDate) < 61

Upvotes: 0

Related Questions