Negative time in seconds when transforming Posixct to numeric

Question

I have a time variable in POSXIct format and want to transform it into numeric format, to work with the differences in seconds, but when passing it into numeric format, the seconds appear as negative:

 Sys.setenv(TZ='GMT')
 
 time=c("1899-12-31 09:11:37 UTC", "1899-12-31 09:12:34 UTC", "1899-12-31 09:13:04 UTC", "1899-12-31 09:13:34 UTC", "1899-12-31 09:14:04 UTC",
        "1899-12-31 09:14:34 UTC", "1899-12-31 09:15:04 UTC", "1899-12-31 09:15:34 UTC", "1899-12-31 09:16:04 UTC", "1899-12-31 09:16:34 UTC")
 
 df<-data.frame(time)
 df$time<-as.POSIXct(df$time)
 df
                  time
1  1899-12-31 09:11:37
2  1899-12-31 09:12:34
3  1899-12-31 09:13:04
4  1899-12-31 09:13:34
5  1899-12-31 09:14:04
6  1899-12-31 09:14:34
7  1899-12-31 09:15:04
8  1899-12-31 09:15:34
9  1899-12-31 09:16:04
10 1899-12-31 09:16:34
 
 df$time<-as.numeric(df$time)
 df
          time
1  -2209042103
2  -2209042046
3  -2209042016
4  -2209041986
5  -2209041956
6  -2209041926
7  -2209041896
8  -2209041866
9  -2209041836
10 -2209041806

How can I transform them as positive? Is this issue linked to the time origin or the date?

Thanks in advance!

Answer 1

Yes, the issue is related to origin of the date. The number that you see is number of seconds since 1970-01-01 00:00:00. Since year 1899 is before 1970 you see the value as negative.

However, I would suggest them to keep time in POSIXct format only. You can calculate the differences in seconds with it.

For eg -

library(dplyr)
df %>% mutate(diff_in_secs = as.integer(difftime(time,lag(time), units = "secs")))

#                  time diff_in_secs
#1  1899-12-31 09:11:37           NA
#2  1899-12-31 09:12:34           57
#3  1899-12-31 09:13:04           30
#4  1899-12-31 09:13:34           30
#5  1899-12-31 09:14:04           30
#6  1899-12-31 09:14:34           30
#7  1899-12-31 09:15:04           30
#8  1899-12-31 09:15:34           30
#9  1899-12-31 09:16:04           30
#10 1899-12-31 09:16:34           30

In this example, a simple subtraction would also give the same result.

df %>% mutate(diff_in_secs = as.integer(time - lag(time)))

However, with difftime we have more control on the output as we can specify the units in which we want our result.

Answer 2

The POSXICT format store the date under the hood as the number of seconds since 1970-01-01. All dates before that are stored as negative values.

So, in your case, the negative numbers are the seconds from your dates until 1970. You can still calculate the difference in seconds as if they were positive, the result will be the same.