0

I have a very large data set from an instrument which measures depth of a water column, and attaches a date/time stamp to each measurement as a modified Julian Date. Below is a subset of the data. The column is in a data.frame (date) and contains numeric values.

head(date)

Date 
41498.736111,
41498.736123,
41498.736134,
41498.736146,
41498.736157,
41498.736169,

When I transfer the first 2 values (i.e. 41498.736111 and 41498.736123) from the Date column to Excel and format the column to (MM:DD:YYYY hh:mm:ss.000), I get the correct value of 8/12/2013 17:39:59.990 and 8/12/2013 17:40:01.027. This tells me that the origin date for these Julian days is "1899-12-30". I would do this all in Excel, but my dataset is much too large and I will have to do this several times for the next few years, so I'd like to be able to do it all in R.

Is there any way to convert the entire column of these modified Julian Dates to the format "%M%D%Y %H:%M:OS3"?

Prasanna Nandakumar
  • 4,295
  • 34
  • 63
Zalophus
  • 3
  • 2

1 Answers1

2

Yeah, you can just add the number of seconds to the origin:

as.POSIXct('1899-12-30')+(date$Date*24*60*60)
nograpes
  • 18,623
  • 1
  • 44
  • 67
  • 1
    `format(as.POSIXct('1899-12-30')+(41498.736111*24*60*60),"%Y-%m-%d %H:%M:%OS3")` – crogg01 Jan 16 '14 at 23:53
  • Thank you so much! I actually had to use the code below to get the correct time, but I'm sure there are variations. Thanks for pointing me in the right direction! y = format(as.POSIXct('1899-12-30')+((date*24*60*60)-3600),"%Y-%m-%d %H:%M:%OS3") – Zalophus Jan 17 '14 at 00:16
  • @Zalophus - there is a difference here between formatting and the correct time. A simple `as.Date` on your data will still accurately record the correct time - see [here](http://stackoverflow.com/a/19173366/496803). Just beware that `format` converts your original numeric data to a character string, with no extra information. – thelatemail Jan 17 '14 at 00:24
  • @thelatemail - Thanks for the info. When attempting as.Date in the past on these modified Julian Date values, I've only been able to obtain "yyyy-mm-dd" and no time stamp. And when I do try to include a time, the values all become NA's. Do you know how to obtain the format "%M%D%Y %H:%M:OS3" from the Julian date values listed above, using as.Date? – Zalophus Jan 17 '14 at 01:15
  • @Zalophus - you won't be able to extract an hour/minute etc from a `Date` object but for example you can see that the fraction of a day data is still there, it is just not able to be formatted: e.g. `dput(as.Date(41498.736111,origin="1899-12-30"))` – thelatemail Jan 17 '14 at 01:59
  • @thelatemail using the lubridate package, you can extract the hour , similarly functions for minute and second exist. – vinnief Mar 31 '17 at 09:58