Search code examples
mysqlmysql-workbenchworkbench

Getting The Start and End Time MYSQL

I'm trying to Retrieve data that can determine the Start and end date and view the changing interval of petname

Here is my sample data.

Raw Data

Here is my expected output

Expected Output

I tried to query like this

    SELECT 
    `line`, `Petname`, MIN(`start date`), MAX(`end date`)
FROM
    `tablename`
WHERE
    timestamp BETWEEN '2020-07-01 00:00:00' AND NOW() group by petname

but my output return like this

tried query

Solution

  • I don't get the create sql of your table, therefore I create one just for test. The following are my verification table. It may be different with yours, please modify according to your own table.

    +----+---------+------------+------------+
| id | petname | start_date | end_date   |
+----+---------+------------+------------+
|  1 | FKN92B  | 2020-07-01 | 2020-07-02 |
|  2 | FKN92B  | 2020-07-02 | 2020-07-04 |
|  3 | FKN8LD  | 2020-07-04 | 2020-07-05 |
|  4 | FKN8LD  | 2020-07-07 | 2020-07-08 |
|  5 | FKN92B  | 2020-07-11 | 2020-07-12 |
|  6 | FKN92B  | 2020-07-12 | 2020-07-14 |
|  7 | FKN8LD  | 2020-07-08 | 2020-07-09 |
|  8 | FKN8LD  | 2020-07-11 | 2020-07-11 |
+----+---------+------------+------------+
8 rows in set (0.00 sec)

    The SQL is as folloing, please have a try and let me know if there are any issues:

    mysql> SET @lastpetname := null;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @groupnum := 0;
Query OK, 0 rows affected (0.00 sec)

mysql> select petname, min(start_date), max(end_date)
    -> from
    ->     (SELECT
    ->             case
    ->                when @lastpetname is null then @groupnum := @groupnum + 1
    ->                when @lastpetname != petname > 0 then @groupnum := @groupnum + 1
    ->                else @groupnum := @groupnum
    ->             end as group_num,
    ->             @lastpetname := petname as petname,
    ->             `start_date`,
    ->            `end_date`
    ->     from `petlog`) t
    -> group by group_num;
+---------+-----------------+---------------+
| petname | min(start_date) | max(end_date) |
+---------+-----------------+---------------+
| FKN92B  | 2020-07-01      | 2020-07-04    |
| FKN8LD  | 2020-07-04      | 2020-07-08    |
| FKN92B  | 2020-07-11      | 2020-07-14    |
| FKN8LD  | 2020-07-08      | 2020-07-11    |
+---------+-----------------+---------------+
4 rows in set (0.00 sec)