CODEWITHSUNDEEP
rdatetimedplyrlubridate
ℕʘʘḆḽḘ
ℕʘʘḆḽḘ

Reputation: 19375

R: how to filter a timestamp by hour and minute?

I am struggling with the following example

time = c('2013-01-03 21:59:21.549', '2013-01-04 22:00:21.549', '2013-01-05 22:01:21.222', '2013-01-06 22:06:23.559' )
value = c(1,2,3,4)

data <- data_frame(time, value)
data <-data %>%  mutate(time = ymd_hms(time))

> data
# A tibble: 4 × 2
                 time value
               <dttm> <dbl>
1 2013-01-03 21:59:21     1
2 2013-01-04 22:00:21     2
3 2013-01-05 22:01:21     3
4 2013-01-06 22:06:23     4

How can I write a dplyr::filter statement than only keeps observations between 21:59 and 22:01 (included) every day?

Playing separately with hour(time) and minute(time) does not seem to work very well here.

Am I missing something here?

Output expected: row 1,2 and 3 only. Thanks!

Upvotes: 8

Views: 18168

Answers (5)

Antex
Antex

Reputation: 1444

Another way of filtering time window can be attained by converting the timestamp to minutes or seconds (with time setup from 0000 - 2400), store it in a new variable and filter using the new variable.

Upvotes: 0

ℕʘʘḆḽḘ
ℕʘʘḆḽḘ

Reputation: 19375

2019 is here! Here is a better (and simpler) solution using as.hms. The tz argument is mandatory.

    time_str = c('2013-01-03 21:59:21.549', '2013-01-04 22:00:21.549', '2013-01-05 
    22:01:21.222', '2013-01-06 22:06:23.559' )
    value = c(1,2,3,4)
    data <- tibble(time_str, value)

    data %>%  mutate(timestamp_utc = ymd_hms(time_str, tz = 'UTC'),
                     timestamp_est = with_tz(timestamp_utc, 'America/New_York'),
                            time_est = as.hms(timestamp_est, tz = 'America/New_York')) %>% 
      filter(time_est >= hms::as.hms('16:59:00', tz = 'America/New_York'),
             time_est <= hms::as.hms('17:01:00', tz = 'America/New_York'))

will do the job

# A tibble: 2 x 5
  time_str                value timestamp_utc           timestamp_est           time_est 
  <chr>                   <dbl> <dttm>                  <dttm>                  <time>   
1 2013-01-03 21:59:21.549     1 2013-01-03 21:59:21.549 2013-01-03 16:59:21.549 16:59.549
2 2013-01-04 22:00:21.549     2 2013-01-04 22:00:21.549 2013-01-04 17:00:21.549 17:00.549

Upvotes: 6

konvas
konvas

Reputation: 14346

You mentioned hour and minute from lubridate don't work but not sure I get why. Does this not work?

filter(data, 
    (hour(time) == 21 & minute(time) == 59) | 
    (hour(time) == 22 & minute(time) <= 1)
)

An alternative way is to get the number of seconds elapsed in the day and compare to the number of seconds corresponding to your desired limits. The number of seconds elapsed in the day is calculated as the total number of seconds modulo the seconds in a day: as.numeric(data$time) %% (60 * 60 * 24). The number of seconds elapsed until (the start of the minute) 21:59 is 60 * 60 * 21 + 60 * 59 and the number of seconds until (the end of the minute) 22:01 is 60 * 60 * 22 + 60 + 59. Combining everything, 

filter(data, between(as.numeric(time) %% (60 * 60 * 24),
                     60 * 60 * 21 + 60 * 59,
                     60 * 60 * 22 + 60 + 59
             )
 )

Upvotes: 4

Felix Grossmann
Felix Grossmann

Reputation: 1314

I guess this solves your problem:

library(dplyr) 

result <- data %>%  
  mutate(time2 = format(time, format="%H:%M:%S")) %>%
  filter(time2 >= "21:59:00" & time2 < "22:02:00") %>%
  select(-time2)

Upvotes: 1

jazzurro
jazzurro

Reputation: 23574

Another idea would be the following. You can create a numeric vector using hour, minute, and second. You can extract them with format() and convert character to numeric. Then, you subset the data with the two numbers indicating the time range you want (i.e., 215900, 220100).

library(dplyr)

data %>%
mutate(foo = as.numeric(format(time, "%H%M%S"))) %>%
filter(between(foo, 215900, 220100)) %>%
select(-foo)

#                 time value
#               <dttm> <dbl>
#1 2013-01-03 21:59:21     1
#2 2013-01-04 22:00:21     2

Upvotes: 2

Related Questions