How to convert a date/time object into a decimal?

user_17143701 0
Viewed 65

I have an object in R that I have converted to a POSIXct object:

data<- data.frame(date_time= c('2021-06-24 18:37:00', '2021-06-24 19:07:00', '2021-06-24 19:37:00', '2021-06-24 20:07:00','2021-06-24 20:37:00'))

data$date_time<- as.POSIXct(data$date_time, format = "%Y-%m-%d %H:%M:%S")

I want to convert this column to a decimal that gets bigger as the time passes. For example, '2021-06-24 18:37:00' should be smaller than '2021-06-24 19:07:00' and so on. However everything that I have tried so far does yield a decimal, but it does not get bigger as the time goes on. I have tried this:

data$date_time2<- yday(data$date_time) + hour(data$date_time)/24 + minute(data$date_time)/60

However this yields:

[1] 176.3667 175.9083 176.4083 175.9500 176.4500

I need the numbers to increase incrementally as minutes go by. Any help?

r datetime
2 Answers

If you are only concerned that the number mapped to preserves order then xtfrm will map objects to order preserving numbers. In the case of POSIXct objects it just returns the internal numeric representation, i.e. seconds since the UNIX Epoch.

xtfrm(data$date_time)
user_5165480

A datetime object is an integer counting the number of seconds from 1/1/1970. So this works as.integer(data$date_time) to create an integer value. Note the datetime is reference to GMT timezone.
To get the date as a decimal, requires the use of some integer math. The end result is the number of days from 1/1/1970 and the time as fraction.

data<- data.frame(date_time= c('2021-06-24 18:37:00', '2021-06-24 19:07:00', '2021-06-24 19:37:00', '2021-06-24 20:07:00','2021-06-24 20:37:00'))

data$date_time<- as.POSIXct(data$date_time, format = "%Y-%m-%d %H:%M:%S", tz="GMT")

intvalue <- as.integer(data$date_time)

#numbers of seconds, take the MOD with the seconds per day
#divide the result by seconds per day to make the decimal part
decfraction <- intvalue%%(3600*24)/(3600*24)

#perform integer division to get the number of days 
days <- intvalue%/%(3600*24)
# or as.integer(as.Date(data$date_time))

#put together for the final answer
dateAsDecimal <- days + decfraction

#result
#18802.78 18802.80 18802.82 18802.84 18802.86
user_57922440
Related