logo
down
shadow

Determine max number of overlapping DATETIME ranges


Determine max number of overlapping DATETIME ranges

By : user2175490
Date : October 15 2020, 08:10 AM
seems to work fine This answer is for MySQL 8.0 that contains window functions. The core of the solution will be the following query that finds a number of overlapping intervals for every interesting interval in the data:
code :
select t2.startDt, t2.endDt, count(*) overlaps_count
from
(
    select lag(t1.dt) over (order by t1.dt) startDt, t1.dt endDt
    from
    (
        select startt dt from data
        union
        select endt dt from data
    ) t1
) t2
join data on t2.startDt < data.endt and t2.endDt > data.startt
group by t2.startDt, t2.endDt
with Overlap as
(
   -- the query above
)
select max(overlaps_count)
from Overlap 
where @start < endDt and @end > startDt


Share : facebook icon twitter icon
Datetime -Determine whether multiple(n) datetime ranges overlap each other in R

Datetime -Determine whether multiple(n) datetime ranges overlap each other in R


By : user2091396
Date : March 29 2020, 07:55 AM
fixed the issue. Will look into that further 1) Based on the sample data we assume that the data is in the form of hh:mm:00 where hh < 24.
Read in the test data. Create two functions which convert a character string of the form hh:mm:00 to number of minutes and a function which converts number of minutes to a chron "times" object. Create minute by minute sequences for each row of the data giving the Intervals list. Union those sequences which correspond to the same switch giving the list Intervals.u and then intersect the components of that list to give the sequence Intersection. Compute the runs, r, in Intersection to give a set of start and end points. Finally calcualte the number of minutes and converting that to "times" class the duration. (The number of minutes and duration only depend on r and Intersection so we could skip the lines ending in ## if intervals.df were not needed.)
code :
# test data
Lines <- "Switches,State,Intime,Outtime
sw3,1,9:00:00,10:40:00
sw2,1,9:30:00,10:15:00
sw1,1,10:00:00,11:00:00
sw2,1,10:20:00,10:30:00"
DF <- read.csv(text = Lines, as.is = TRUE)

library(chron)

to.num <- function(x) floor(as.numeric(times(x)) * 24 * 60 + 1e-6)
to.times <- function(x) times(x / (24 * 60))

Seq <- function(r) seq(to.num(DF$Intime[r]), to.num(DF$Outtime[r]))    
Intervals <- lapply(1:nrow(DF), Seq)
Intervals.u <- lapply(split(Intervals, DF$Switches), 
     function(L) Reduce(union, L))
Intersection <- Reduce(intersect, Intervals.u)

r <- rle(c(FALSE, diff(Intersection) == 1))

i.ends <- cumsum(r$lengths)[r$values] ##
ends <- to.times(Intersection[i.ends]) ##
starts <- ends - to.times(r$lengths[r$values]) ##
intervals.df <- data.frame(start = starts, end = ends); intervals.df ##
##         start      end
##    1 10:00:00 10:15:00
##    2 10:20:00 10:30:00

mins <- length(Intersection) - sum(r$values); mins
## [1] 25
duration <- to.times(mins); duration
## [1] 00:25:00
library(IRanges)
Intervals <- IRanges(to.num(DF$Intime), to.num(DF$Outtime))
Intersection <- Reduce(intersect, split(Intervals, DF$Switches))

intervals.df <- data.frame(start = to.times(start(Intersection)), 
                           end = to.times(end(Intersection)))
intervals.df
##      start      end
## 1 10:00:00 10:15:00
## 2 10:20:00 10:30:00

mins <- sum(width(Intersection) - 1); mins
## [1] 25
duration <- to.times(mins); duration
## [1] 00:25:00
How to find free number ranges between other overlapping number ranges

How to find free number ranges between other overlapping number ranges


By : Nick
Date : March 29 2020, 07:55 AM
I hope this helps you . I have n Arrays. Each array have m elements. Each element m consist of two attributes [numbers]. , Here's a simplified version for you.
code :
// for example i use two arrays with ranges, but in reality they are n (>= 1)
var numbersRanges1 = [
    {start: 100, end: 120},
    {start: 180, end: 200},
    {start: 400, end: 500}
];

var numbersRanges2 = [
    {start: 10, end: 80},
    {start: 150, end: 220},
    {start: 480, end: 500}
];

var boundary = {
    start: 0,
    end: 600
};

// merge arrays
var mergedRanges = numbersRanges1.concat(numbersRanges2);

// sort by start
function sortByStart(a, b){
    return a.start - b.start;
}

mergedRanges = mergedRanges.sort(sortByStart);

// go throw merged ranges and save ranges between in addition array
var freeRanges = [];

var start=0;
mergedRanges.forEach(function(one) {
  if(one.start<start) return;
  freeRanges.push({start:start,end:one.start});
  start=one.end;
});

if(start<boundary.end) {
  freeRanges.push({start:start,end:boundary.end});
}

console.log(JSON.stringify(freeRanges,null,2));
Determine presence and extent of overlapping date ranges by ID number - two data frames

Determine presence and extent of overlapping date ranges by ID number - two data frames


By : Parvathy
Date : March 29 2020, 07:55 AM
wish help you to fix your issue We can do this with non-equi joins in data.table (>=v1.9.8):
code :
library(data.table)
setDT(adm)
setDT(admDx)[, (4:9):= NULL]

# period bounds
admDx[, `:=`(dxDtLo=dxDt-31, dxDtHi=dxDt+31)]

# hospitalized pre-dxnosis?
admDx = adm[, .(id, admDt, sepDt, dxDt=admDt, dxDtLo=sepDt)][admDx,
  on=.(id, dxDt < dxDt, dxDtLo > dxDtLo)]
admDx[, admPreDx:= as.numeric(!is.na(admDt))]
admDx[, `:=`(admPreDxNbr=sum(admPreDx), admPreDxDur=as.numeric(
  sum(pmin(dxDt,sepDt) - pmax(admDt,dxDtLo)))), by='id']
admDx[, c('admDt','sepDt'):= NULL]

# hospitalized post-dxnosis?
admDx = adm[, .(id, admDt, sepDt, dxDtHi=admDt, dxDt=sepDt)][admDx,
  on=.(id, dxDtHi < dxDtHi, dxDt > dxDt)]
admDx[, admPostDx:= as.numeric(!is.na(admDt))]
admDx[, `:=`(admPostDxNbr=sum(admPostDx), admPostDxDur=as.numeric(
  sum(pmin(sepDt,dxDtHi) - pmax(dxDt,admDt)))), by='id']
admDx[, c('admDt','sepDt'):= NULL]

admDx[is.na(admDx)] = 0
admDx = unique(admDx)[, c('dxDtLo','dxDtHi'):= NULL]

> admDx
#          id       dxDt    birthDt admPreDx admPreDxNbr admPreDxDur admPostDx admPostDxNbr admPostDxDur
#  1:       1 2000-02-24 1971-07-22        0           0           0         1            1            1
#  2:       2 2000-04-30 1982-08-09        0           0           0         1            1           25
#  3:       3 2000-03-03 1976-01-30        0           0           0         0            0            0
#  4:       4 2000-01-31 1972-02-03        1           1           6         0            0            0
#  5:       5 2000-06-20 1958-05-26        0           0           0         0            0            0
#  6:       6 2000-12-13 1979-05-24        0           0           0         0            0            0
#  7:       7 2000-05-14 1971-11-03        0           0           0         1            1           14
#  8:       8 2000-01-23 1980-02-05        0           0           0         1            1           31
#  9:       9 2000-03-09 1978-06-08        1           1          14         0            0            0
# 10:      10 2000-02-15 1969-10-14        1           1          19         1            1            8
# 11:      11 2000-05-01 1962-01-01        1           1          20         0            0            0
# 12:      12 2000-06-30 1977-03-09        1           1           2         1            1           27
# 13:      13 2000-08-15 1952-01-24        1           1          31         0            0            0
# 14:      14 2000-06-22 1974-12-16        1           1           9         0            0            0
# 15:      15 2000-01-27 1956-05-05        1           1          31         1            1           16
# 16: 5451396 2000-06-01 1963-07-16        1           1          25         1            1           31
Get Hours From 2 Overlapping Datetime Ranges

Get Hours From 2 Overlapping Datetime Ranges


By : Joanna Morla
Date : March 29 2020, 07:55 AM
it should still fix some issue Yes, you said I previously got a way using multiple IF statement, and this solution looks like it, but anyway, just try, may be better ?
code :
select `schedules`.*, 
CASE 
    WHEN `end2` <=  `start1` OR `start2` >= `end1` THEN  NULL -- not overlaps at all
    WHEN `start2` <= `start1` AND `end2` <= `end1` THEN  TIMESTAMPDIFF(HOUR, `start1`, `end2`)  
    WHEN `start2` <= `start1` AND `end2` > `end1`  THEN  TIMESTAMPDIFF(HOUR, `start1`, `end1`)
    WHEN `start2` > `start1` AND `end2` <= `end1`  THEN  TIMESTAMPDIFF(HOUR, `start2`, `end2`)
    WHEN `start2` > `start1` AND `end2` > `end1`   THEN  TIMESTAMPDIFF(HOUR, `start2`, `end1`)
END AS hourdiff
from `schedules`
Determine overlapping ranges - R

Determine overlapping ranges - R


By : Jacob
Date : March 29 2020, 07:55 AM
To fix the issue you can do Try foverlaps in data.table, it's designed for this (I can't think of an elegant dplyr way, sorry). You need to have a start/end date column in both tables, so the start/end date for the ad is the start date to 4 days later; the start/end date for the purchase are the same.
Related Posts Related Posts :
  • How to setup local server for wordpress site with git
  • Multiplying CASE row with different values
  • Index on a table not being used all the time
  • How can I get a date from mysql database if it is not null?
  • How to execute a TRIGGER in MSSQl?
  • MySQL 8 Window Functions + Full-text searching
  • Join on large table getting slower
  • Select record from two different table
  • Getting 1064 error while creating mysql trigger
  • MySQL Database Operations
  • How can I make this SQL sort by most relevent?
  • Database query on month
  • Select number of matching rows of a particular column in MySQL
  • How to use rake database commands with password
  • If we change a primary key value, why don't we have to change a dependent column value?
  • MySQL - selecting all records except the ones already associated in the relational table
  • Delete Duplicate MySQL rows but keep one
  • ORDER BY does not perform any function
  • Update column with output of select within the same table in mysql
  • Set a variable inside case statement in mysql
  • MYSQL: How to get rows inserted in the last X hours without querying the entire table
  • AWS RDS MySQL Cross-region replication
  • Cannot truncate a table referenced in a foreign key constraint from empty table
  • How does SQL determine a character's length in a varchar?
  • MySQL : Getting DB row with exact same data from a vector
  • Mysql update query with join
  • Group values that have the same name in one column and same id in other column
  • Mysql query syntax for conditional inserts
  • Is it faster to run an SQL count(*) query in a loop, or try to merge it into the parent query?
  • MySQL query to fetch product variants
  • Report Reindex taking too long after destroy
  • MySQL - Adding varchar as a foreign key
  • How to find duplicated entries which has different slug?
  • SQL Query comparing values in different rows
  • Eloquent giving error but query executes fine in phpmyadmin
  • JPQL fetch data from multiple tables
  • MySql Query - Expanding data into new table
  • Official MySQL Docker container not caching queries?
  • MySQL, Count values in every fields in a table
  • SQL : number different dates within users
  • elastic beanstalk docker app cannot connect to mysql
  • Why am I getting errno: 150 "Foreign key constraint is incorrectly formed"?
  • Update columns which included in payload
  • MySQL subquery in select
  • Get difference in top 2 salary of employee from each department
  • connecting to database using RMySQL and .my.cnf file in R
  • How to get data by mysql
  • SQL Error [1054] [42S22]: Unknown column ' ' in 'field list'
  • turn c# code into mysql function
  • How to loop through and output nested array in Laravel
  • MySQL TRIM spaces inside text
  • Mysql query with multiple selects results in high CPU load
  • Backup DB Django MysqlDump
  • How to select all rows from group, until occurrence of a value
  • Using substring to filter a specific word from a string in MySQL
  • Mysql - Alias in Left Outer Join giving error
  • How use custom alias field from select fields in join?
  • How to sum durations in units of Year, Month and Day in MySQL?
  • Is it possible to assign the values from select exist query in MySQL to multiple variables in a stored procedure?
  • How does mysql resolves conflict when same option is configured twice?
  • shadow
    Privacy Policy - Terms - Contact Us © voile276.org