5

I have a data frame containing what should be a datetime column that has been read into R. The time values are appearing as numeric time as seen in the below data example. I would like to convert these into datetime POSIXct or POSIXlt format, so that date and time can be viewed.

tdat <- c(974424L, 974430L, 974436L, 974442L, 974448L, 974454L, 974460L, 974466L, 974472L,
          974478L, 974484L, 974490L, 974496L, 974502L, 974508L, 974514L, 974520L, 974526L,
          974532L,974538L)

974424 should equate to 00:00:00 01/03/2011, but the do not know the origin time of the numeric values (i.e. 1970-01-01 used below does not work). I have tried using commands such as the below to achieve this and have spent time trying to get as.POXISct to work, but I haven’t found a solution (i.e. I either end up with a POSIXct object of NAs or end up with obscure datetime values).

Attempts to convert numeric time to datetime:

datetime <- as.POSIXct(strptime(time, format = "%d/%m/%Y %H:%M:%S"))
datetime <- as.POSIXct(as.numeric(time), origin='1970-01-01')

I am sure that this is a simple thing to do. Any help would be greatly received. Thanks!

jjulip
  • 1,093
  • 4
  • 16
  • 24
  • 1
    Are these time values in hours, minutes or seconds? If you don't know the origin time, you have to know this in order to do the conversion. – Theodore Lytras Jul 01 '14 at 11:36
  • @ Theodore: they should be in hours. The datetime vales should run as so 00:00:00 01/03/2011, 06:00:00 01/03/2011, 12:00:00 01/03/2011, 18:00:00 01/03/2011, 00:00:00 02/03/2011 and so on (6-hour intervals). – jjulip Jul 01 '14 at 11:53

1 Answers1

7

Try one of these depending on which time zone you want:

t.gmt <- as.POSIXct(3600 * (tdat - 974424), origin = '2011-03-01', tz = "GMT") 

t.local <- as.POSIXct(format(t.gmt))
G. Grothendieck
  • 254,981
  • 17
  • 203
  • 341
  • Thanks. The datetime vales should run from 00:00:00 01/03/2011 at 6-hour intervals i.e. 06:00:00 01/03/2011, 12:00:00 01/03/2011, 18:00:00 01/03/2011, 00:00:00 02/03/2011 and so on. How would I adapt the code above to achieve this? Currently it gives the intervals in minutes rather than hours: i.e. "2011-01-03 00:00:00 GMT" "2011-01-03 00:00:06 GMT" "2011-01-03 00:00:12 GMT". Thanks! – jjulip Jul 01 '14 at 11:56
  • 2
    @jjulip If `tdat` is in 6 hour intervals, then in the first line just multiply `tday-974424` by `60*6` to convert to minutes. – Theodore Lytras Jul 01 '14 at 11:58
  • @jjulip, See revised answer. – G. Grothendieck Jul 01 '14 at 12:45
  • @ Thank you both. Much appreciated. It worked using `as.POSIXct((tdat - 974424)*3600, origin='2011-03-01', tz = "GMT")`. – jjulip Jul 01 '14 at 12:47
  • 2
    Surprising. My numeric values (for the year 2019) are coming in range of ~43,000. What could be the units of this? – Lazarus Thurston May 09 '19 at 07:38
  • 1
    @Lazarus, days since 1/1/1900? Similar to Excel. – Mark Neal Jun 15 '20 at 23:56