How to create new column with timestamps, using old column of date/time in 24 hour format-CodePudding

I have a data frame with a column that has date/time. I have extracted the month and day to create 2 new columns, but I am trying to create another column with time and it won't work now that I've converted my time to 24 hour format

Here is what the column in my data frame looks like

# A tibble: 6 x 1
  df                 
  <dttm>             
1 2021-06-01 08:00:00
2 2021-06-01 08:15:00
3 2021-06-01 08:30:00
4 2021-06-01 08:45:00
5 2021-11-25 21:46:40
6 2021-11-25 22:01:40

Here is the data frame column

df = structure(list(df = structure(c(1622552400, 1622553300, 1622554200, 
                                     1622555100, 1637894800, 1637895700), class = c("POSIXct", "POSIXt"
                                     ), tzone = "EST")), row.names = c(NA, 6L), class = "data.frame")

when my time was in 12 hour format I used this code and it worked

  mutate(month = lubridate::month(Date_Time_GMT_3), 
         year = lubridate::year(Date_Time_GMT_3),
         day = lubridate::day(Date_Time_GMT_3),
#CODE FOR TIME COLUMN
         time = lubridate::hms(substr(Date_Time_GMT_3, 11, 
                                      nchar(Date_Time_GMT_3))))

now that I've changed my time format to 24 hour I get this error

Warning message:
Problem with `mutate()` column `time`.
i `time = lubridate::hms(substr(Date_Time_GMT_3, 11, nchar(Date_Time_GMT_3)))`.
i Some strings failed to parse, or all strings are NAs

Any idea how to fix this?

CodePudding user response：

Try sub instead of substr.

df %>% mutate(month = lubridate::month(df), 
         year = lubridate::year(df),
         day = lubridate::day(df),
         time = lubridate::hms(sub(".* ","",df)))
                   df month year day        time
1 2021-06-01 08:00:00     6 2021   1    8H 0M 0S
2 2021-06-01 08:15:00     6 2021   1   8H 15M 0S
3 2021-06-01 08:30:00     6 2021   1   8H 30M 0S
4 2021-06-01 08:45:00     6 2021   1   8H 45M 0S
5 2021-11-25 21:46:40    11 2021  25 21H 46M 40S
6 2021-11-25 22:01:40    11 2021  25  22H 1M 40S

Data

df <- structure(list(df = structure(c(1622552400, 1622553300, 1622554200, 
1622555100, 1637894800, 1637895700), class = c("POSIXct", "POSIXt"
), tzone = "EST")), row.names = c(NA, 6L), class = "data.frame")