-1

I'm trying to get acquainted with weatherData in R.

Having downloaded a set of temperature data I've then exported it to CSV.

Opening the CSV in Libre Calc shows the date and time for each temperature reading as a string of ten digits. In spite of some Googling I have not found a way of successfully converting the string into the format in which it appears in R.

For example: 1357084200 I believe should translate to 2013-01-01 23:50:00

Any help in getting the correct date in the same date format to appear in Calc via the CSV greatly appreciated.

tbodt
  • 16,609
  • 6
  • 58
  • 83
Curious56
  • 51
  • 1
  • 3

2 Answers2

2

Here is the direct way:

as.POSIXct(1357084200, origin="1970-01-01", tz="GMT")
#[1] "2013-01-01 23:50:00 GMT"

If it's really a character:

as.POSIXct(as.numeric("1357084200"), origin="1970-01-01", tz="GMT")
Roland
  • 127,288
  • 10
  • 191
  • 288
  • Thanks to all for the solutions. The direct method works fine for me on individual date numbers but I struggled without success to add a new column to the table to translate all of the date numbers in another. What I'm trying is:R> dat$newcol <- apply(dat,1,as.POSIXct(row) origin="1970-01-01", tz="GMT") – Curious56 Feb 23 '14 at 09:50
  • Read a tutorial. R is vectorized, which means you can simply do `dat$newcol <- as.POSIXct(numdatetimes, origin="1970-01-01", tz="GMT")` – Roland Feb 23 '14 at 09:59
0

I'm not aware of a direct way of doing this, but I believe I've figured out a workaround.

For starters your example is correct. The long number (timestamp) is the number of seconds passed since 1970-01-01 00:00:00. Knowing this you can actually calculate the exact date and time from the timestamp. It's a bit complicated due to needing to take into account the leap years.

What comes in handy is the ability to supply an arbitrary number of days/months/years to LibreOffice function DATE. So in essence you can find out the number of days represented in timestamp by dividing it by 60*60*24 (number of seconds in a minute, number of minutes in an hour, number of hours in a day). And then supply that number to the date function.

timestamp = 1357084200
days = timestamp / FLOOR(timestamp / (60*60*24); 1) // comes out at 15706
actualdate = DATE(1970; 1; 1 + days) // comes out at 2013-01-01
seconds = timestamp - days * 60 * 60 * 24 // comes out at 85800
actualtime = TIME(0; 0; seconds) // comes out at 23:50:00

Then you can concatenate these or whatever else you want to do.

LauriK
  • 1,899
  • 15
  • 20