yes jim, that's pretty close to what I managed to do after your first hint ### mys.100<-sprintf(".%02d", df$s.100) myhour<-strftime(df$hour, format = "%H:%M:%S") myday<-strftime(df$date, format = "%Y-%m-%d")
my.new.datetime<-as.POSIXct(paste0(paste(myday, myhour),mys.100)) op <- options(digits.secs = 2) my.new.datetime ### thank you again for the help best regards Il 29/04/2014 23:02, jim holtman ha scritto: > Here is how I would do the conversion. I start off by setting my > timezone to GMT. > ############################# > > df<-structure(list(date = structure(c(1395874800, 1395874800, > + 1395874800, > + 1395874800, 1395874800), class = c("POSIXct", "POSIXt"), tzone = ""), > + hour = structure(c(-2209121804, -2209121567, -2209121005, > + -2209118616, -2209116160), class = c("POSIXct", "POSIXt"), > tzone =""), > + s.100 = c(29L, 36L, 6L, 53L, 18L)), .Names = c("date", "hour", > + "s.100"), row.names = c(NA, -5L), class = "data.frame") > > > > # setup GMT as my timezone > > Sys.setenv(tz = "GMT") > > df > date hour s.100 > 1 2014-03-26 23:00:00 1899-12-30 11:03:16 29 > 2 2014-03-26 23:00:00 1899-12-30 11:07:13 36 > 3 2014-03-26 23:00:00 1899-12-30 11:16:35 6 > 4 2014-03-26 23:00:00 1899-12-30 11:56:24 53 > 5 2014-03-26 23:00:00 1899-12-30 12:37:20 18 > > # show numeric value of midnight on 2014-03-27 and compare to table > > as.numeric(as.POSIXct('2014-03-27 00:00', tz = "GMT")) > [1] 1395878400 > > as.numeric(df$date[1]) > [1] 1395874800 > > # shows your time is off by an hour so we have to add back > > # create the date format to convert > > newDate <- paste0(format(df$date + 3600, format = "%Y-%m-%d") > + , ' ' # blank separator > + , format(df$hour + 3600, format = "%H:%M:%S") > + , sprintf(".%02d", df$s.100) > + ) > > # result, and now convert back into a new column > > newDate > [1] "2014-03-27 12:03:16.29" "2014-03-27 12:07:13.36" "2014-03-27 > 12:16:35.06" "2014-03-27 12:56:24.53" > [5] "2014-03-27 13:37:20.18" > > df$time <- as.POSIXct(newDate, format = "%Y-%m-%d %H:%M:%OS", tz = > "GMT") > > df > date hour s.100 time > 1 2014-03-26 23:00:00 1899-12-30 11:03:16 29 2014-03-27 12:03:16 > 2 2014-03-26 23:00:00 1899-12-30 11:07:13 36 2014-03-27 12:07:13 > 3 2014-03-26 23:00:00 1899-12-30 11:16:35 6 2014-03-27 12:16:35 > 4 2014-03-26 23:00:00 1899-12-30 11:56:24 53 2014-03-27 12:56:24 > 5 2014-03-26 23:00:00 1899-12-30 12:37:20 18 2014-03-27 13:37:20 > > > # the time does have the hundredth of the second > > format(df$time, format = "%H:%M:%OS2") > [1] "12:03:16.28" "12:07:13.35" "12:16:35.05" "12:56:24.52" "13:37:20.18" > > > ############################################ > > Jim Holtman > Data Munger Guru > > What is the problem that you are trying to solve? > Tell me what you want to do, not how you want to do it. > > > On Tue, Apr 29, 2014 at 3:15 PM, <mbres...@arpa.veneto.it > <mailto:mbres...@arpa.veneto.it>> wrote: > > hi, thanks for your reply > > the first record for my date is 2014-03-27 and for my hour is > 1899-12-30 > 12:03:16 > > I got to this point by importing some data via RODBC from an > access database > > Now, I would like to get a single column with the date and the > time that > for the first record should be equal to: > > 2014-03-27 12:03:16.029 > > and this result should be the combination of relevant information > contained in the 3 columns: "date", "hour" and "s.100" > > I do not have the possibility to change the format of imported > data so I > need necessarly to convert them later on... > > how would you do that considering that regardless of the time zone > displacement the first column is storing the data, the second the > hour and > the third the hundredth of a second > > > best regards > > > When I read in your data, I get the following since I am in the > EDT time > > zone: > > > >> df<-structure(list(date = structure(c(1395874800, 1395874800, > >> 1395874800, > > + 1395874800, 1395874800), class = c("POSIXct", "POSIXt"), tzone > = ""), > > + hour = structure(c(-2209121804, -2209121567, -2209121005, > > + -2209118616, -2209116160), class = c("POSIXct", > "POSIXt"), tzone = > > ""), > > + s.100 = c(29L, 36L, 6L, 53L, 18L)), .Names = c("date", > "hour", > > + "s.100"), row.names = c(NA, -5L), class = "data.frame") > >> > >> df > > date hour s.100 > > 1 2014-03-26 19:00:00 1899-12-30 06:03:16 29 > > 2 2014-03-26 19:00:00 1899-12-30 06:07:13 36 > > 3 2014-03-26 19:00:00 1899-12-30 06:16:35 6 > > 4 2014-03-26 19:00:00 1899-12-30 06:56:24 53 > > 5 2014-03-26 19:00:00 1899-12-30 07:37:20 18 > > > > > > I assume your real date is probably 2014-03-27 if it supposed to > be GMT. > > And your 'hour' is funny for probably the same reason. You can > easily > > convert the hundredth of a second in the original input. > > > > Now I would really like to ask how you got to this point. Did you > > originally read in and convert the data yourself, or is this a > source that > > you do not have any control over? If the former, then just use the > > correct > > conversion. As shown below, if you have hundredths of a second, > that will > > be converted correctly and you don't need the extra column. > > > >> x <- as.POSIXct("2014-04-29 12:00:00.345") # decimal seconds > that are > > converted > >> > >> x > > [1] "2014-04-29 12:00:00 EDT" > >> format(x, format = "%H:%M:%OS3") # print with 3 decimals > > [1] "12:00:00.345" > > > > If you have the choice, start over again and do it correctly. > If not, > > convert the various components to the correct character format > for your > > timezone, combine back together and then use the conversion > shown above. > > > > > > Jim Holtman > > Data Munger Guru > > > > What is the problem that you are trying to solve? > > Tell me what you want to do, not how you want to do it. > > > > > > On Tue, Apr 29, 2014 at 9:06 AM, Massimo Bressan > > <mbres...@arpa.veneto.it <mailto:mbres...@arpa.veneto.it>>wrote: > > > >> I have this dataframe: > >> > >> df<-structure(list(date = structure(c(1395874800, 1395874800, > >> 1395874800, > >> 1395874800, 1395874800), class = c("POSIXct", "POSIXt"), tzone > = ""), > >> hour = structure(c(-2209121804, -2209121567, -2209121005, > >> -2209118616, -2209116160), class = c("POSIXct", "POSIXt"), > tzone = > >> ""), > >> s.100 = c(29L, 36L, 6L, 53L, 18L)), .Names = c("date", "hour", > >> "s.100"), row.names = c(NA, -5L), class = "data.frame") > >> > >> > >> and I would like to sum first two columns ("date" and "hour") > so that to > >> end up with a new column, say "date_hour", storing both the > information > >> about the "date" and the "hour" in one POSIXct object; > >> > >> I have been reading that POSIXct objects are a measure of > seconds from a > >> given origin (1st Jan 1970), so that a possible solution is to > tranform > >> the > >> column "hour" into seconds and then add it to the column "date"; > >> > >> but, is there a staightforward solution for accomplishing this > task? > >> I've been trying to extract from the column "hour" the digits > >> representing > >> hours, minutes and seconds and transform everything into > seconds but > >> that > >> seem to me quite cumbersome approach... > >> > >> and finally, one more question: is it possible to represent > hundred of > >> seconds as given in the column "s.100" of the given dataframe > within the > >> same new POSIXct object "date_hour"? > >> > >> > >> thanksfor the support > >> > >> > >> > >> > >> [[alternative HTML version deleted]] > >> > >> ______________________________________________ > >> R-help@r-project.org <mailto:R-help@r-project.org> mailing list > >> https://stat.ethz.ch/mailman/listinfo/r-help > >> PLEASE do read the posting guide > >> http://www.R-project.org/posting-guide.html > <http://www.r-project.org/posting-guide.html> > >> and provide commented, minimal, self-contained, reproducible code. > >> > > > > > [[alternative HTML version deleted]] ______________________________________________ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.