0

I was thinking of how to find date(which does not exist in the table) based on time.

Example: Remember, I only have the time 

time = c("9:44","15:30","23:48","00:30","05:30", "15:30", "22:00", "00:45")

I know for the fact that the start date is 2014-08-28, but how do I get the date which changes after midnight.

Expected outcome would be 

9:44   2014-08-28
15:30  2014-08-28
23:48  2014-08-28
00:30  2014-08-29
05:30  2014-08-29
15:30  2014-08-29
22:00  2014-08-29
00:45  2014-08-30
hrbrmstr
  • 77,368
  • 11
  • 139
  • 205
Boro Dega
  • 393
  • 1
  • 3
  • 13
  • Welcome to SO! What have you tried that did not work? Please (also) see [how to make a great reproducible question](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example). – hrbrmstr Oct 18 '15 at 14:18

4 Answers4

3

Here's an example using data.table package ITime class which enables you to manipulate time (upon converting time to this class you can now subtract/add minutes/hours/etc.)

library(data.table)
time <- as.ITime(time)
Date <- as.IDate("2014-08-28") + c(0, cumsum(diff(time) < 0))

data.table(time, Date)
#        time       Date
# 1: 09:44:00 2014-08-28
# 2: 15:30:00 2014-08-28
# 3: 23:48:00 2014-08-28
# 4: 00:30:00 2014-08-29
# 5: 05:30:00 2014-08-29
# 6: 15:30:00 2014-08-29
# 7: 22:00:00 2014-08-29
# 8: 00:45:00 2014-08-30
David Arenburg
  • 91,361
  • 17
  • 137
  • 196
  • Thanks @David Arenburg. Would elaborate a bit on what this part of your code i.e. what is c(0, cumsum(diff(time) < 0)) do? Just for my understanding. Thanks – Boro Dega Oct 18 '15 at 14:34
  • It's takes the difference in the time vector and checks if it is negative (that means that the day have changed) and then creates a cumulative sum of these changes in order to create a vector that will classify each observation to a certain day. Try just running `c(0, cumsum(diff(time) < 0))` and see what you get. Afterwards I'm adding this vector to `"2014-08-28"` in order to create a date sequence. If you want the end result to be a single date string, just do `as.POSIXct(time, Date)` or `as.POSIXct(paste(Date, time))` instead of `data.table(time, Date)` – David Arenburg Oct 18 '15 at 14:35
1

Using the chron package we assume that a later time is on the same day and an earlier time is on the next day:

library(chron)
date <- as.Date("2014-08-28") + cumsum(c(0, diff(times(paste0(time, ":00"))) < 0))
data.frame(time, date)

giving:

   time       date
1  9:44 2014-08-28
2 15:30 2014-08-28
3 23:48 2014-08-28
4 00:30 2014-08-29
5 05:30 2014-08-29
6 15:30 2014-08-29
7 22:00 2014-08-29
8 00:45 2014-08-30
G. Grothendieck
  • 254,981
  • 17
  • 203
  • 341
0

Here's one way to do it:

time = c("9:44","15:30","23:48","00:30","05:30", "15:30", "22:00", "00:45")
times <- sapply(strsplit(time, ":", TRUE), function(x) Reduce("+", as.numeric(x) * c(60, 1)))
as.POSIXct("2014-08-28") + times + 60*60*24*cumsum(c(F, tail(times < lag(times), -1)))
# [1] "2014-08-28 00:09:44 CEST" "2014-08-28 00:15:30 CEST" "2014-08-28 00:23:48 CEST" "2014-08-29 00:00:30 CEST" "2014-08-29 00:05:30 CEST" "2014-08-29 00:15:30 CEST" "2014-08-29 00:22:00 CEST" "2014-08-30 00:00:45 CEST"
lukeA
  • 53,097
  • 5
  • 97
  • 100
0

You can concatenate system date with time and get result. For example, in Oracle we can get date with time as:

to_char(sysdate,'DD-MM-RRRR')|| ' ' || To_char(sysdate,'HH:MIAM')

This will result as eg. 12-09-2015 09:50 AM

For your requirement, use this as:

 to_char(sysdate,'DD-MM-RRRR')|| ' 00:45' and so on.
Muhammad Muazzam
  • 2,810
  • 6
  • 33
  • 62