R: How to Filter a Timestamp by Hour and Minute

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

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

dplyr & lubridate - filter by hour and minute (i.e. 10:15)

Another option is to calculate the number of minutes since midnight. To get between 09:00 and 20:15, the number of minutes since midnight needs to be between 9*60 and 20*16 + 15

library(dplyr)
library(lubridate)

df %>% 
  mutate(since_midnight = hour(timestamp) * 60 + minute(timestamp)) %>% 
  filter(since_midnight >= 9*60 & since_midnight < (20 * 60 + 15)) %>% 
  summarise(mean = mean(value))

How to filter a dataset by the time stamp

First, check if df$Datetime is a date variable:

class(df$Datetime)

If it's not, you can convert it with this:

df$Datetime <- ymd_hms(df&Datetime)

You use mutate to create a new variable called newdate that takes the earliest date of the bird's data and sets the date for cutoff which is the next day at 21:30:00 of the earliest date of a bird's observations.

Then you filter the Datetime column by the newdate column and you get the observations that are found earlier that the specified date.

library(dplyr); library(lubridate)
df %>% 
  group_by(BirdID) %>%
  mutate(newdate = as.POSIXct(date(min(Datetime)) + days(1) + hours(21) + minutes(30))) %>% 
  filter(Datetime < newdate)

Did a reproducible example:

library(dplyr); library(lubridate)

set.seed(1)

# Create a data frame (1000 observations)
BirdID <- paste(rep(floor(runif(250, 1, 20)),4),
  rep("k", 1000), rep(floor(runif(250, 1, 40)),4), sep = "")
x <- rnorm(1000, mean = 47000, sd = 2000)
y <- rnorm(1000, mean = 5650000, sd = 300000)
Datetime <- as.POSIXct(rnorm(1000, mean = as.numeric(as.POSIXct("2015-06-23 18:25:00")), sd = 99999), tz = "GMT", origin = "1970-01-01")
df <- data.frame(BirdID, x, y, Datetime, stringsAsFactors = FALSE)

# Filter the data frame by the specified date
df_filtered <- df %>% 
  group_by(BirdID) %>%
  mutate(newdate = as.POSIXct(date(min(Datetime)) + days(1) + hours(21) + minutes(30))) %>% 
  filter(Datetime < newdate)

This should fix any problem.

filtering to have unique date and time to the hour

Using round.POSIXt (and as.POSIXct, since the former returns POSIXlt, and dplyr doesn't like those):

library(dplyr)
df %>%
  group_by(NAME, rtime = as.POSIXct(round.POSIXt(`Local Time`, units = "hours"))) %>%
  slice(1)
# # A tibble: 9 x 4
# # Groups:   NAME, rtime [9]
#   `Local Time`          COG NAME  rtime              
#   <dttm>              <dbl> <chr> <dttm>             
# 1 2019-06-01 04:36:00  315  Aur   2019-06-01 05:00:00
# 2 2019-06-01 06:14:00  266. Aur   2019-06-01 06:00:00
# 3 2019-06-01 06:53:00  215. Aur   2019-06-01 07:00:00
# 4 2019-06-01 07:30:00  253. Aur   2019-06-01 08:00:00
# 5 2019-06-01 03:06:00   90  Cos   2019-06-01 03:00:00
# 6 2019-06-01 04:40:00   88  Cos   2019-06-01 05:00:00
# 7 2019-06-01 06:18:00   93  Cos   2019-06-01 06:00:00
# 8 2019-06-01 06:53:00   95  Cos   2019-06-01 07:00:00
# 9 2019-06-01 07:32:00   94  Cos   2019-06-01 08:00:00

If you prefer, you can instead return the last with slice(n()) or a random row with sample_n(1).

Filtering datetime by vector

After a lot of mucking around and talking to myself in my question

I found this thread:
filtering a dataset by time stamp

and it helped me to realise how to isolate the hour in the time stamp and then use that to filter the data properly.

the final answer is to isolate the hour by this

filter(hour(timestamp_utc) %in% school_hours2)

---

Related Topics