CODEWITHSUNDEEP
roverlapposixct
heatherr
heatherr

Reputation: 143

Trouble using foverlaps with POSIXct objects - maybe due to fractional seconds?

I have 2 data tables and I would like to find the rows that overlap using foverlaps. I think I am getting tripped up because some of the dates have fractional seconds. 

library(data.table)

First create a data table of shift times

On <- as.POSIXct(c("2017-08-01 00:05:54", "2017-08-01 00:07:20", "2017-08-01 00:21:53"),  format = "%Y-%m-%d %H:%M:%S", tz = "UTC")
Off <- as.POSIXct(c("2017-08-01 00:05:54", "2017-08-01 00:07:20", "2017-08-01 00:21:53"),  format = "%Y-%m-%d %H:%M:%S", tz = "UTC")
shifts <- data.table(On, Off)

Now create a data table of observations times The first bunch of observation times are from Matlab, so need to be converted to POSIXct first. These end up giving me fractional seconds

timestamp <- c(736908.0041, 736908.0051, 736908.009, 736908.012, 736908.0152)
Obs = data.table(SightingTime = as.POSIXct((timestamp-719529)*86400, origin = "1970-01-01", tz = "UTC"))
#add a variable for the "date type"
Obs$DateType = "Long"

Add a row to the data table that does not have fractional seconds (for the purpose of this example)

Obs <- rbind(Obs, data.table(SightingTime=as.POSIXct("2017-08-01 00:05:54",  format = "%Y-%m-%d %H:%M:%S", tz = "UTC"), DateType = "Short"))

create point intervals so can use foverlaps

Obs[, SightingTime2 := SightingTime]

get ready for foverlaps

setkey(Obs, SightingTime, SightingTime2)
setkey(shifts, On, Off)

do the overlap join

Obs.ov <- foverlaps(shifts, Obs ,type="any",nomatch=0L)

This results in Obs.ov having a single row - the overlaps with the "Short" date format. Rows with the "Long" date format don't get included in the overlap. I would have expected that three rows would overlap (assuming that the fractional seconds would be rounded off, I would expect overlaps with the 00:05:54 and 00:21:53 "Long" timestamps as well.

I think this might be due to the fractional seconds in the dates I converted from Matlab, but I don't know how to get rid of the fractional bit. I did try using 

attributes(Obs$SightingTime)$format <- "%Y-%m-%d %H:%M:%OS"

as well as including the "format" argument when the SightingTime variable was created from the "timestamp" variable early on. But have had no luck with either.

I did look here How to format fractional seconds in POSIXct in r, but can't quite figure out what change I need to make based on this.

Upvotes: 2

Views: 120

Answers (1)

heatherr
heatherr

Reputation: 143

I found what I needed here Remove seconds from time in R

I just needed to round off the seconds after creating the SightingTime variable, but before creating the "SightingTime2" variable.

Obs$SightingTime <- as.POSIXct(round(Obs$SightingTime, units="secs"))

Now when I do the overlaps, I get the 3 overlapping rows as expected.

Upvotes: 2

Related Questions