0

I am working with a data set with a measuring interval of 15 mins. The data is stored with a DateTime column with the format yyyy/mm/dd hh/mm/ (POSIXct). I would like to subset my data using the DateTime column. So for example I would like to make a subset that ranges from 2019-07-25 00:00 to 2019-07-25 23:45.

Here is an example of my data

 DateTime                     S4_SM1_30 S4_SM2_30 S4_SM3_30 S4_SM4_30 S4_SM1_60 S4_SM2_60 S4_SM3_60 S4_SM1_90 S4_SM2_90
1   2019-05-01 00:00:00       8.5       7.8      10.3       6.6      24.1      28.5      26.5      11.4        17
2   2019-05-01 00:15:00       8.6       7.8      10.4       6.6      24.3      28.6      26.5      11.4        17
3   2019-05-01 00:30:00       8.6       7.8      10.3       6.6      24.3      28.5      26.5      11.3        17
4   2019-05-01 00:45:00       8.7       7.8      10.3       6.6      24.3      28.5      26.5      11.3        17
5   2019-05-01 01:00:00       8.6       7.8      10.4       6.6      24.2      28.5      26.5      11.3        17
6   2019-05-01 01:15:00       8.6       7.8      10.3       6.6      24.2      28.5      26.6      11.4        17
7   2019-05-01 01:30:00       8.5       7.8      10.4       6.6      24.3      28.5      26.5      11.3        17
8   2019-05-01 01:45:00       8.6       7.8      10.4       6.6      24.3      28.5      26.5      11.4        17

I`ve tried to use the following code to make the subset.

df <- df %>% 
  filter(DateTime >= as.POSIXct('2019-07-25 01:45') & DateTime <= as.POSIXct('2019-07-25 23:45'))

The code seems to kind of work, but the result I get is flawed. The subset starts on the 2019-07-24 22:00 and ends on the 2019-07-25 21:45. So it basically starts and ends 2 hours to early. Here is an example.

DateTime S4_SM1_30 S4_SM2_30 S4_SM3_30 S4_SM4_30 S4_SM1_60 S4_SM2_60 S4_SM3_60 S4_SM1_90 S4_SM2_90
1  2019-07-24 22:00:00         7       5.9       5.9       5.3        17      18.8      16.9       7.2       9.6
2  2019-07-24 22:15:00         7       5.9       5.9       5.3      17.1      18.8      16.9       7.2       9.6
3  2019-07-24 22:30:00         7       5.9       5.9       5.3        17      18.8      16.9       7.2       9.6
4  2019-07-24 22:45:00       6.9       5.9       5.9       5.3        17      18.8      16.9       7.2       9.6
5  2019-07-24 23:00:00         7       5.9       5.9       5.3        17      18.8      16.9       7.2       9.7
6  2019-07-24 23:15:00         7       5.9       5.9       5.3        17      18.8      16.9       7.2       9.6
7  2019-07-24 23:30:00         7       5.9       5.9       5.3        17      18.8      16.9       7.2       9.6
8  2019-07-24 23:45:00       6.9       5.9       5.9       5.3        17      18.8      16.9       7.1       9.7
9  2019-07-25 00:00:00         7       5.9       5.9       5.3      17.1      18.8      16.9       7.2       9.6
10 2019-07-25 00:15:00       6.9       5.9       5.9       5.3      17.1      18.8      16.9       7.2       9.6
11 2019-07-25 00:30:00         7       5.9       5.9       5.3      17.1      18.8      16.9       7.2       9.6
12 2019-07-25 00:45:00         7       5.9       5.9       5.3      17.1      18.8      16.9       7.2       9.6
13 2019-07-25 01:00:00         7       5.9       5.9       5.3        17      18.8      16.9       7.2       9.6
14 2019-07-25 01:15:00       6.9       5.9       5.9       5.3        17      18.8      16.9       7.3       9.6
15 2019-07-25 01:30:00       6.9       5.9       5.9       5.3      17.1      18.8      16.9       7.2       9.6
16 2019-07-25 01:45:00         7       5.9       5.9       5.3      17.1      18.8      16.9       7.2       9.6
17 2019-07-25 02:00:00         7       5.9         6       5.4        17      18.8      16.9       7.2       9.6
18 2019-07-25 02:15:00       6.9       5.9         6       5.3        17      18.8      16.9       7.2       9.6
19 2019-07-25 02:30:00         7       5.9       5.9       5.3        17      18.8      16.9       7.2       9.6
20 2019-07-25 02:45:00         7       5.9       5.9       5.3        17      18.8      16.9       7.2       9.6
21 2019-07-25 03:00:00         7       5.9       5.9       5.3        17      18.8      16.9       7.2       9.6
22 2019-07-25 03:15:00         7       5.9       5.9       5.3        17      18.8      16.9       7.2       9.6
23 2019-07-25 03:30:00         7       5.9       5.9       5.3        17      18.8      16.9       7.2       9.6
24 2019-07-25 03:45:00       6.9       5.9       5.9       5.3        17      18.8      16.9       7.2       9.6
25 2019-07-25 04:00:00       6.9       5.9       5.9       5.3        17      18.8      16.9       7.2       9.6
26 2019-07-25 04:15:00         7       5.9       5.9       5.3        17      18.8      16.9       7.2       9.6
27 2019-07-25 04:30:00         7       5.9       5.9       5.3        17      18.8      16.9       7.2       9.6
28 2019-07-25 04:45:00         7       5.9       5.9       5.3        17      18.8        17       7.2       9.6
29 2019-07-25 05:00:00         7       5.9       5.9       5.3        17      18.8      16.9       7.2       9.6
30 2019-07-25 05:15:00       6.9       5.9       5.9       5.3        17      18.8      16.9       7.2       9.6
31 2019-07-25 05:30:00       6.9       5.9       5.9       5.3        17      18.8      16.9       7.2       9.6
32 2019-07-25 05:45:00         7       5.9       5.9       5.3        17      18.8        17       7.2       9.6
33 2019-07-25 06:00:00         7       5.9       5.9       5.3        17      18.8        17       7.2       9.6
34 2019-07-25 06:15:00       6.9       5.9       5.9       5.3        17      18.8      16.9       7.2       9.6
35 2019-07-25 06:30:00       6.9       5.9         6       5.3        17      18.8        17       7.2       9.6
36 2019-07-25 06:45:00         7       5.9       5.9       5.3        17      18.8        17       7.2       9.6
37 2019-07-25 07:00:00         7       5.9         6       5.3        17      18.8        17       7.2       9.6
38 2019-07-25 07:15:00         7       5.9       5.9       5.3        17      18.8        17       7.2       9.6
39 2019-07-25 07:30:00         7       5.9       5.9       5.3        17      18.8        17       7.2       9.6
40 2019-07-25 07:45:00         7       5.9       5.9       5.3        17      18.8        17       7.2       9.6
41 2019-07-25 08:00:00         7       5.9         6       5.3        17      18.8      16.9       7.2       9.6
42 2019-07-25 08:15:00         7       5.9       5.9       5.3        17      18.8      16.9       7.2       9.6
43 2019-07-25 08:30:00         7       5.9       5.9       5.3        17      18.8      16.9       7.2       9.6
44 2019-07-25 08:45:00         7       5.9       5.9       5.3        17      18.8      16.9       7.2       9.6
45 2019-07-25 09:00:00       6.9       5.9       5.9       5.3        17      18.8      16.9       7.2       9.6
46 2019-07-25 09:15:00         7       5.9       5.9       5.3        17      18.8      16.9       7.2       9.6
47 2019-07-25 09:30:00         7       5.9       5.9       5.3        17      18.8      16.9       7.2       9.6
48 2019-07-25 09:45:00         7       5.9       5.9       5.3        17      18.8      16.9       7.2       9.6
49 2019-07-25 10:00:00         7       5.9       5.9       5.3        17      18.8      16.8       7.2       9.6
50 2019-07-25 10:15:00         7       5.9       5.9       5.3        17      18.8      16.8       7.2       9.6
51 2019-07-25 10:30:00       6.9       5.9       5.9       5.3      16.9      18.8      16.8       7.2       9.6
52 2019-07-25 10:45:00       6.9       5.9       5.9       5.3      16.9      18.8      16.8       7.3       9.6
53 2019-07-25 11:00:00       6.9       5.9       5.9       5.3      16.9      18.8      16.8       7.2       9.6
54 2019-07-25 11:15:00       6.9       5.9       5.9       5.3      16.9      18.8      16.8       7.2       9.6
55 2019-07-25 11:30:00       6.9       5.9       5.9       5.3        17      18.7      16.8       7.2       9.6
56 2019-07-25 11:45:00       6.9       5.9       5.9       5.3      16.9      18.7      16.8       7.2       9.6
57 2019-07-25 12:00:00       6.9       5.9       5.9       5.3        17      18.8      16.8       7.2       9.6
58 2019-07-25 12:15:00       6.9       5.9       5.9       5.3        17      18.7      16.8       7.2       9.6
59 2019-07-25 12:30:00       6.9       5.9       5.9       5.3        17      18.7      16.8       7.2       9.6
60 2019-07-25 12:45:00       6.9       5.9       5.9       5.3      16.9      18.7      16.8       7.3       9.6
61 2019-07-25 13:00:00       6.9       5.9       5.9       5.3      16.9      18.7      16.8       7.3       9.6
62 2019-07-25 13:15:00       6.9       5.9       5.9       5.3      16.9      18.7      16.7       7.2       9.6
63 2019-07-25 13:30:00       6.9       5.9       5.9       5.3      16.9      18.7      16.8       7.2       9.6
64 2019-07-25 13:45:00       6.9       5.9       5.9       5.3      16.9      18.7      16.8       7.2       9.6
65 2019-07-25 14:00:00       6.9       5.9       5.9       5.3      16.9      18.7      16.8       7.2       9.6
66 2019-07-25 14:15:00       6.9       5.9       5.9       5.3      16.9      18.7      16.7       7.2       9.6
67 2019-07-25 14:30:00       6.9       5.9       5.9       5.3      16.9      18.7      16.7       7.2       9.6
68 2019-07-25 14:45:00       6.9       5.8       5.9       5.3      16.9      18.7      16.7       7.2       9.6
69 2019-07-25 15:00:00       6.9       5.9       5.9       5.3      16.9      18.7      16.7       7.2       9.6
70 2019-07-25 15:15:00       6.9       5.9       5.9       5.3      16.9      18.7      16.7       7.2       9.6
71 2019-07-25 15:30:00       6.9       5.9       5.9       5.3      16.9      18.7      16.7       7.1       9.6
72 2019-07-25 15:45:00       6.9       5.9       5.9       5.3      16.9      18.7      16.7       7.2       9.6
73 2019-07-25 16:00:00       6.9       5.9       5.9       5.3      16.9      18.7      16.7       7.2       9.6
74 2019-07-25 16:15:00       6.9       5.8       5.9       5.3      16.8      18.7      16.7       7.2       9.6
75 2019-07-25 16:30:00       6.9       5.9       5.8       5.3      16.9      18.7      16.7       7.2       9.6
76 2019-07-25 16:45:00       6.9       5.9       5.9       5.3      16.8      18.7      16.7       7.2       9.6
77 2019-07-25 17:00:00       6.9       5.9       5.9       5.3      16.9      18.7      16.7       7.2       9.6
78 2019-07-25 17:15:00       6.9       5.9       5.9       5.3      16.9      18.7      16.7       7.2       9.6
79 2019-07-25 17:30:00       6.9       5.9       5.9       5.3      16.9      18.7      16.7       7.2       9.6
80 2019-07-25 17:45:00       6.9       5.9       5.9       5.3      16.9      18.7      16.7       7.2       9.6
81 2019-07-25 18:00:00       6.9       5.9       5.9       5.3      16.9      18.7      16.7       7.2       9.6
82 2019-07-25 18:15:00       6.9       5.9       5.8       5.3      16.9      18.6      16.7       7.2       9.6
83 2019-07-25 18:30:00       6.9       5.8       5.9       5.3      16.9      18.6      16.7       7.2       9.6
84 2019-07-25 18:45:00       6.9       5.9       5.9       5.3      16.9      18.6      16.7       7.2       9.6
85 2019-07-25 19:00:00       6.9       5.9       5.9       5.3      16.9      18.6      16.7       7.2       9.6
86 2019-07-25 19:15:00         7       5.9       5.9       5.3      16.9      18.6      16.7       7.2       9.6
87 2019-07-25 19:30:00       6.9       5.9       5.9       5.3      16.9      18.6      16.7       7.2       9.6
88 2019-07-25 19:45:00       6.9       5.9       5.9       5.3      16.9      18.6      16.7       7.2       9.6
89 2019-07-25 20:00:00       6.9       5.9       5.9       5.3      16.9      18.6      16.7       7.2       9.6
90 2019-07-25 20:15:00         7       5.9       5.9       5.3      16.8      18.6      16.8       7.2       9.6
91 2019-07-25 20:30:00       6.9       5.9       5.9       5.3      16.9      18.6      16.8       7.2       9.6
92 2019-07-25 20:45:00       6.9       5.9       5.9       5.3      16.9      18.6      16.7       7.2       9.6
93 2019-07-25 21:00:00       6.9       5.8       5.9       5.3      16.9      18.6      16.8       7.1       9.5
94 2019-07-25 21:15:00       6.9       5.9       5.8       5.3      16.8      18.6      16.8       7.2       9.6
95 2019-07-25 21:30:00       6.9       5.9       5.9       5.3      16.9      18.6      16.8       7.2       9.5
96 2019-07-25 21:45:00       6.9       5.9       5.9       5.3      16.9      18.6      16.8       7.2       9.6

I would be thankful for any help!

Matthias Hacker
  • 15
  • 3

1 Answers1

0

By default as.POSIXct takes the local timezone, you might want to add specific timezone to it.

library(dplyr)

df %>% filter(DateTime >= as.POSIXct('2019-07-25 01:45', tz = 'GMT') & 
              DateTime <= as.POSIXct('2019-07-25 23:45', tz = 'GMT'))

Moreover, if you need GMT/UTC timezone you can use lubridate::ymd_hms which has UTC as default timezone.

Ronak Shah
  • 377,200
  • 20
  • 156
  • 213