Location>code7788 >text

Six cases of comparing two time periods, and examples of brief sql statements for intersection, concatenation, and complementary sets

Popularity：495 ℃/2024-10-23 16:18:15

○. Full picture of the comparison between the two time periods

In total, there are six scenarios as shown below:

The following section will operate further on these six scenarios.

Note that the chart says that the two time periods, dynamic and fixed, are the two common time periods and are not distinguished as primary or secondary and are used only as an aid to understanding.

I. Determining whether two time periods intersect (intersection)

Under normal circumstances, it is the figure of 2, 3, 4, 5 four cases with cross. If the four cases directly through the judgment, it will require four judgment conditions combination, too complex, this paper is skipped.

If you pass thereverse psychologyIn that case, you only need to judge 1 and 6. Of courseReverse thinking is recommended, less judgmental and easy to understand, detailed below.

Judging two time periodsNon-cross-cutting conditions：

-- dynamic end time < fixed start time
jieshusj_dt < kaishisj_gd -- case 1
jieshusj_dt < kaishisj_gd -- case 1
kaishisj_dt > jieshusj_gd -- Case 6
-- dynamic start time > fixed end time

Then it is possible topass (a bill or inspection etc)retrieve the opposite of what one intended, obtaining the condition that the two time periods have intersected：

-- dynamic end time >= fixed start time
jieshusj_dt >= kaishisj_gd
and
kaishisj_dt <= jieshusj_gd
-- dynamic start time <= fixed end time

II. Taking the intersection of two time periods (intersection)

Prerequisite:Passed with cross judgment. That is, it contains 2, 3, 4, and 5.

It is then necessary to compare the start and end times of the two time periods separately:

  • Start time: take the larger one;
  • End time: take the smaller one.
-- Example of dynamic time period: 2024-10-16 ~ 2024-10-20
select
-- Starting time is the greater of
(Case When kaishisj_gd > '2024-10-16' Then kaishisj_gd Else '2024-10-16' End) kaishisj, -- end time takes the smaller one.
-- End time takes the lesser of
(Case When jieshusj_gd < '2024-10-20' Then jieshusj_gd Else '2024-10-20' End) jieshusj
from table_name

III. Taking two time periods and setting them together

Taking a concatenated set also needs to be separated into two cases, that is, whether there is a crossover or not.

  • Type I: no crossover

It is straightforward to perform an 'or' operation on the two time periods.

  • Second: with crossover

This case also compares the start and end times of the two time periods separately, but takes just the opposite values as in the previous section,:

  • Start time: take the smaller one;
  • End time: take the larger one.
-- Example of dynamic time period: 2024-10-16 ~ 2024-10-20
select
-- Start time is the lesser of
(Case When kaishisj_gd > '2024-10-16' Then '2024-10-16' Else kaishisj_gd End) kaishisj, -- end time takes the larger one.
-- End time is the greater of
(Case When jieshusj_gd < '2024-10-20' Then '2024-10-20' Else jieshusj_gd End) jieshusj
from table_name

IV. Taking the complement of two time periods

First, what is a complement? A simple schematic is shown below:

(of which.omnibus'Set B' contains 'Set A', then the graph ofThe gray part is the complement of 'set A'.）

So, the prerequisite for finding the complementary set is that there is an inclusion relationship between the two time periods, i.e., cases 3 and 4.

  • Situation 3:

-- Example of dynamic time period: 2024-10-16 ~ 2024-10-20
select
kaishisj_gd,'2024-10-16' as jieshusj_new, -- two times, form new time period
'2024-10-20' as kaishisj_new,jieshusj_gd -- two times, form new time period
from table_name
where kaishisj_gd < '2024-10-16' and jieshusj_gd > '2024-10-20'

Such as the query statement above, you can get four times, respectively, to form two time periods, and then subsequent personalized operations.

  • Situation 4:

-- Example of dynamic time period: 2024-10-16 ~ 2024-10-20
select
'2024-10-16' as kaishisj_new, kaishisj_gd, -- two times, form new time period
jieshusj_gd, '2024-10-20' as jiehsusj_new -- two times, form new time period
from table_name
where kaishisj_gd > '2024-10-16' and jieshusj_gd < '2024-10-20'

Such as the query statement above, you can get four times, respectively, to form two time periods, and then subsequent personalized operations.

<Previous Next>