I'm trying to get the MAX & MIN Timestamp for the certain time frame. As you can see here:
I will use mysql or pl/sql
Is it possible to get it?
Example:
Black block: 11/2/2022 05:45:28 ~ 11/2/2022 06:02:58
Red block: 13/2/2022 05:34:09 ~ 13/2/2022 06:32:44
Yellow block: 13/2/2022 10:28:06 ~ 13/2/2022 10:40:35
CodePudding user response：
The question is: Is it possible to get it?
Sure it is. I'm done here right Question answered?
Ok as to How... :P
NOTES:
Assumes each group begins with a Y and ends with the record prior to next Y and that is sorted by count column which has no gaps.
- if there are gaps, we could assign a row_number() over (partition by X order by ...) as part of an earlier step and use it instead of count
- Didn't test edge case of Y group without an N. but I think the min/max will just be the same and it will work out.
Not a complete solution but does provide a sufficient example to build from. This solves your Grouping problem and obtains the min/max but it doesn't do it using all your columns/data and I don't actually subtract them... but it does give your "Example" results.
I cheated and simply used #'s instead of actual timestmaps because I'm lazy and didn't want to setup the data.
Create CTE with sample data
Create STEP1 CTE which created a column of data which defines each Block/Group based on the next "Y" in your list I called it "NextMinCount" it would be more aptly named "MyGrouping"
Then once we have the block/group column
We use a query to get min/max within each group
SAMPLE DATA: DEMO DB FIDDLE UK
----------- ------------- ------------ --------
| Equipment | Status_Flag | Time_Stamp | counts |
----------- ------------- ------------ --------
| EXPCA1Z43 | Y | 1234 | 0 | <--Begn Grp 4
| EXPCA1Z43 | N | 1235 | 1 |
| EXPCA1Z43 | N | 1236 | 2 |
| EXPCA1Z43 | N | 1237 | 3 |
| EXPCA1Z43 | N | 1238 | 4 | <--End Grp 4
| EXPCA1Z43 | Y | 1239 | 5 | <--Begin Grp 7
| EXPCA1Z43 | N | 1240 | 6 |
| EXPCA1Z43 | N | 1241 | 7 | <--End Grp 7
| EXPCA1Z43 | Y | 1242 | 8 | <--Begin Grp 'Last'
| EXPCA1Z43 | N | 1243 | 9 |
| EXPCA1Z43 | N | 1244 | 10 |
| EXPCA1Z43 | N | 1245 | 11 | <--End Grp 'Last'
----------- ------------- ------------ --------
Assumes the groups begin with a Y and end with row prior to next Y assuming sorted by counts column with no gaps.
With CTE AS (SELECT 'EXPCA1Z43' Equipment, 'Y' Status_Flag, 1234 Time_Stamp, 0 counts UNION ALL
SELECT 'EXPCA1Z43' Equipment, 'N' Status_Flag, 1235 Time_Stamp, 1 counts UNION ALL
SELECT 'EXPCA1Z43' Equipment, 'N' Status_Flag, 1236 Time_Stamp, 2 counts UNION ALL
SELECT 'EXPCA1Z43' Equipment, 'N' Status_Flag, 1237 Time_Stamp, 3 counts UNION ALL
SELECT 'EXPCA1Z43' Equipment, 'N' Status_Flag, 1238 Time_Stamp, 4 counts UNION ALL
SELECT 'EXPCA1Z43' Equipment, 'Y' Status_Flag, 1239 Time_Stamp, 5 counts UNION ALL
SELECT 'EXPCA1Z43' Equipment, 'N' Status_Flag, 1240 Time_Stamp, 6 counts UNION ALL
SELECT 'EXPCA1Z43' Equipment, 'N' Status_Flag, 1241 Time_Stamp, 7 counts UNION ALL
SELECT 'EXPCA1Z43' Equipment, 'Y' Status_Flag, 1242 Time_Stamp, 8 counts UNION ALL
SELECT 'EXPCA1Z43' Equipment, 'N' Status_Flag, 1243 Time_Stamp, 9 counts UNION ALL
SELECT 'EXPCA1Z43' Equipment, 'N' Status_Flag, 1244 Time_Stamp, 10 counts UNION ALL
SELECT 'EXPCA1Z43' Equipment, 'N' Status_Flag, 1245 Time_Stamp, 11 counts ),
STEP1 as (SELECT A.*, coalesce((SELECT MIN(Counts)-1
FROM CTE B
WHERE A.Counts < B.Counts
and B.Status_Flag='Y'),'LASTGROUP') NextMinCount
FROM CTE A)
SELECT nextMincount, Min(Time_Stamp) MinForGroup, max(Time_Stamp) maxForGroup
FROM STEP1
GROUP BY nextMinCount
Giving us:
The heart of the above which defines the grouping is this inline select:
coalesce((SELECT MIN(Counts)-1
FROM CTE B
WHERE A.Counts < B.Counts
and B.Status_Flag='Y'),'LASTGROUP')
It simply identifies the lowest "Count" that is a "Y" that is greater than the current count . Thus defining the range for the group. We then subtract 1 to get the row prior to the Y. we use the coalesce to handle the last grouping which would not have a next Y value and would otherwise be NULL name; and we get the Step1 Results depicted for STEP 1
STEP 1 gives us:
----------- ------------- ------------ -------- --------------
| Equipment | Status_Flag | Time_Stamp | counts | NextMinCount | <--Should have called
----------- ------------- ------------ -------- -------------- This MyGrouping
| EXPCA1Z43 | Y | 1234 | 0 | 4 |
| EXPCA1Z43 | N | 1235 | 1 | 4 |
| EXPCA1Z43 | N | 1236 | 2 | 4 |
| EXPCA1Z43 | N | 1237 | 3 | 4 |
| EXPCA1Z43 | N | 1238 | 4 | 4 |
| EXPCA1Z43 | Y | 1239 | 5 | 7 |
| EXPCA1Z43 | N | 1240 | 6 | 7 |
| EXPCA1Z43 | N | 1241 | 7 | 7 |
| EXPCA1Z43 | Y | 1242 | 8 | LASTGROUP |
| EXPCA1Z43 | N | 1243 | 9 | LASTGROUP |
| EXPCA1Z43 | N | 1244 | 10 | LASTGROUP |
| EXPCA1Z43 | N | 1245 | 11 | LASTGROUP |
----------- ------------- ------------ -------- --------------
The final query gives us
-------------- ------------- -------------
| nextMincount | MinForGroup | maxForGroup |
-------------- ------------- -------------
| 4 | 1234 | 1238 |
| 7 | 1239 | 1241 |
| LASTGROUP | 1242 | 1245 |
-------------- ------------- -------------
WHERE the nextMinCount equates to your blocks of data as the Step1 results show.
- 4 = your black
- 7 = your red
- LAST GROUP = your yellow
Which, I believe, is what you're after.
CodePudding user response：
You can refer these site it will help you to find difference between two timestamps: https://learnsql.com/cookbook/how-to-calculate-the-difference-between-two-timestamps-in-mysql/#:~:text=To calculate the difference between the timestamps in MySQL, use,MONTH , QUARTER , or YEAR .