CODEWITHSUNDEEP
rdatetimedata.table
user41509
user41509

Reputation: 1040

POSIXct objects and time zones

I have date time data table imported from Excel and the date/time column in a number format (i.e., 43596.22). I used the following code to convert the number to a date time format with UTC time zone:

 info_dt1$Date_time<-convertToDateTime(info_dt1$date_time, origin = "1900-01-01",tx="UTC")

I am using the forverlaps function from data.table to merge this data table with another data table by date and time. When I first ran the following code:

info_dt3 = foverlaps(info_dt2, info_access3, by.x=c("Date_time", "dummy"), nomatch=NA)[, dummy := NULL]

I got an error message stating the two date time fields had different time zones. The time zone for the other data table was also specified as UTC.

I used the attr function to set both data tables date times columns to UTC:

   #make sure all date/times have same time zone
attr(info_access2$Start_time, "tzone") <- "UTC"
attr(info_access2$End_time, "tzone") <- "UTC"
attr(info_dt1$Date_time, "tzone") <- "UTC"

When I do this, the info_dt1 data table time moves forward 4 hours and the resulting merge is off. I would like to know what I am doing incorrect when setting the format and time zone for both data tables for the merge to work correctly. 

Some example data and code:

    #first data table reduced example 

        info_dt1<-
        structure(list(date_time = c(NA, 43596.2284722222, 43596.2285069444, 
        43596.2285416667, 43596.2285763889, 43596.2286111111, 43596.2286458333, 
        43596.2286805556, 43596.2287152778, 43596.22875), Temp = c(NA, 
        22.75, 22.66, 22.57, 22.49, 22.37, 22.28, 22.16, 22.08, 21.99
        ), Depth = c(NA, 0.19, 0.27, 0.7, 0.27, 0.27, 0.27, 0.19, 0.19, 
        0.19), Angle = c(NA, -3, -4, -3, -1, 1, -1, -2, 1, -6)), .Names = c("date_time", 
        "Temp", "Depth", "Angle"), row.names = c(NA, 10L), class = "data.frame")

        #convert date time to POSIXct
         info_dt1$Date_time<-convertToDateTime(info_dt1$date_time, origin = "1900-01-01",tx="UTC")

        #second example data set
        info_access2<-
        structure(list(Tow = 201905001:201905010, Start_time = structure(c(1557554271, 
        1557564948, 1557569853, 1557573081, 1557577149, 1557582317, 1557586050, 
        1557588636, 1557590697, 1557593679), class = c("POSIXct", "POSIXt"
        ), tzone = "UTC"), End_time = structure(c(1557555117, 1557565710, 
        1557570765, 1557573846, 1557577974, 1557583210, 1557586797, 1557589428, 
        1557591441, 1557594511), class = c("POSIXct", "POSIXt"), tzone = "UTC"), 
            time_interval = structure(c(846, 762, 912, 765, 825, 893, 
            747, 792, 744, 832), start = structure(c(1557554271, 1557564948, 
            1557569853, 1557573081, 1557577149, 1557582317, 1557586050, 
            1557588636, 1557590697, 1557593679), class = c("POSIXct", 
            "POSIXt"), tzone = "UTC"), tzone = "UTC", class = structure("Interval", package = "lubridate"))), .Names = c("Tow", 
        "Start_time", "End_time", "time_interval"), row.names = c(NA, 
        10L), class = "data.frame")

    library(data.table)

    #make info_dt2 and info_access2 data.tables
    info_access3<-as.data.table(info_access2)
    info_dt2<-as.data.table(info_dt1)

    #remove NA from info_dt2
    info_dt2<-info_dt2[complete.cases(info_dt2),]

    #set dummy column for info_dt2
    info_dt2[, dummy := Date_time]

    #define setkey for info_access2
    setkey(info_access3, Start_time, End_time)

    #if I run the code like this I get the error message about different time zones

    #use foverlaps to merge info_access3 and info_dt2
    info_dt3 = foverlaps(info_dt2, info_access3, by.x=c("Date_time", "dummy"), nomatch=NA)[, dummy := NULL]

    #if I run this chunk of code the times in info_dt1 are moved forward 4 hours
     #make sure all date/times have same time zone
        attr(info_access2$Start_time, "tzone") <- "UTC"
        attr(info_access2$End_time, "tzone") <- "UTC"
        attr(info_dt1$Date_time, "tzone") <- "UTC"

#make info_dt2 and info_access2 data.tables
    info_access3<-as.data.table(info_access2)
    info_dt2<-as.data.table(info_dt1)

    #remove NA from info_dt2
        info_dt2<-info_dt2[complete.cases(info_dt2),]


    #but the foverlaps to merge info_access2 and info_dt2 doesn't give an error message
        info_dt3 = foverlaps(info_dt2, info_access3, by.x=c("Date_time", "dummy"), nomatch=NA)[, dummy := NULL]

Upvotes: 1

Views: 182

Answers (1)

dave-edison
dave-edison

Reputation: 3736

You can use lubridate::force_tz() to change a timestamp which had an inaccurate timezone when it was read in:

lubridate::force_tz(Sys.time(), "UTC")
#[1] "2019-06-25 14:04:32 UTC"

This will change the underlying timestamp double whereas merely altering the attribute won't.

Upvotes: 2

Related Questions