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> 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>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 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.
