Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Joining on POSIXct types with mismatched timezones #1143

Closed
DavidArenburg opened this issue May 10, 2015 · 0 comments
Closed

Joining on POSIXct types with mismatched timezones #1143

DavidArenburg opened this issue May 10, 2015 · 0 comments

Comments

@DavidArenburg
Copy link
Member

@DavidArenburg DavidArenburg commented May 10, 2015

Consider the following example

require(data.table)
wd <- structure(list(Year = c(2006L, 2006L, 2006L), day = c(361L, 361L, 
          360L), hour = c(14L, 8L, 8L), mint = c(30L, 0L, 30L), valu1 = c(0.5, 
           0.3, 0.4), Date = structure(c(1167229800, 1167206400, 1167121800
           ), class = c("POSIXct", "POSIXt"), tzone = "UTC")), .Names = c("Year", 
           "day", "hour", "mint", "valu1", "Date"), row.names = c(NA, -3L
           ), class = "data.frame") ## "UTC" time zone
setDT(wd)[, `:=`(start = Date - 1800L, end = Date + 1800L)]
#    Year day hour mint valu1                Date               start                 end
#1: 2006 361   14   30   0.5 2006-12-27 14:30:00 2006-12-27 14:00:00 2006-12-27 15:00:00
#2: 2006 361    8    0   0.3 2006-12-27 08:00:00 2006-12-27 07:30:00 2006-12-27 08:30:00
#3: 2006 360    8   30   0.4 2006-12-26 08:30:00 2006-12-26 08:00:00 2006-12-26 09:00:00

tt <- as.POSIXct(c("2006/12/27 14:23:59", "2006/12/27 16:47:59", "2006/12/27 19:12:00"), 
                         format = "%Y/%m/%d %T", tz = "Asia/Jerusalem") ## different time zone
wg <- data.table(start = tt, end = tt)
#                  start                 end
#1: 2006-12-27 14:23:59 2006-12-27 14:23:59
#2: 2006-12-27 16:47:59 2006-12-27 16:47:59
#3: 2006-12-27 19:12:00 2006-12-27 19:12:00
setkey(wg)
foverlaps(wd, wg, nomatch = 0L)[, .(wdDate = Date, valu1, WGDate = start)]
#                 wdDate valu1              WGDate
#1: 2006-12-27 14:30:00   0.5 2006-12-27 16:47:59 <--- The matched times are incorrect

See this SO question for reference.

It'd be nice to, in general, warn when joining on POSIXct types with mismatched timezones.

@arunsrinivasan arunsrinivasan changed the title Add to foverlaps documentation that when matching on as.POSIXct class, tz parameter must be populated correctly in both data sets. Joining on POSIXct types with mismatched timezones May 10, 2015
@arunsrinivasan arunsrinivasan added this to the v2.0.0 milestone Nov 30, 2015
@mattdowle mattdowle removed this from the Candidate milestone May 10, 2018
arunsrinivasan added a commit that referenced this issue Feb 18, 2019
* type = ‘equal’ support for foverlaps

* Add debugging info with LLDB for future-me.

* Rename len1 to count and len2 to type_count.

* Better error messages when interval cols have NAs, #3007

Closes #3007.

* Better error/warnings for POSIXct intervals, #1143
MichaelChirico pushed a commit that referenced this issue Apr 3, 2019
* type = ‘equal’ support for foverlaps

* Add debugging info with LLDB for future-me.

* Rename len1 to count and len2 to type_count.

* Better error messages when interval cols have NAs, #3007

Closes #3007.

* Better error/warnings for POSIXct intervals, #1143
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

3 participants