Converting a string to time stamp data format

[Panagiotis_Emmanouil]

Hello,
I work with a public dataset and the original dataset has the following column:
SleepDay "4/12/2016 12:00:00 AM", "4/13/2016 12:00:00 AM",
I split the date and time in 2 different columns and I converted the date part from
character to date. I tried also to convert time column from string to timestamp
(e.g. "12:00:00 AM" ) using various functions:
sleepday <- sleepday %>%
mutate(Time=hms(Time))
I tried also as.numeric but the result is the same
Any idea why only NA values are returned?
Thank you.
Panos

[technocrat]

Generally, it's a good idea to work with datetime objects, rather than strings because date arithmetic is easier.

library(lubridate)
#> 
#> Attaching package: 'lubridate'
#> The following objects are masked from 'package:base':
#> 
#>     date, intersect, setdiff, union
ds <- "4/12/2016 12:00:00 AM" # date string
d <- as_date(mdy_hms(ds))     # Date object
dts <- mdy_hms(ds)            # Date with time zone
dts
#> [1] "2016-04-12 UTC"
# fix loss of time stamp by removing AM/PM 
ds <- gsub("...$","",ds)
ds
#> [1] "4/12/2016 12:00:00"
dts <- mdy_hms(ds)            # Date with time zone
dts
#> [1] "2016-04-12 12:00:00 UTC"
dts <- mdy_hms(ds, tz = "America/New_York")
dts
#> [1] "2016-04-12 12:00:00 EDT"
td <- today()
td - d
#> Time difference of 2608 days

^{Created on 2023-06-03 with reprex v2.0.2}

[Panagiotis_Emmanouil]

Hi, thank you for your response. I have already tried the above solution but the column time
has no values (i.e. NA). Before the format was character such as "12:00:00"

[EconProf]

This uses the parse_hms() function from the hms package (part of the tidyverse) to convert the times from character format.

library(tidyverse)
library(hms)
#> 
#> Attaching package: 'hms'
#> The following object is masked from 'package:lubridate':
#> 
#>     hms

DF <- tibble(DateTime = c("11/20/2021 10:45:10 AM", "11/20/2021 06:19:00 PM")) |> 
  mutate(DateTime = mdy_hms(DateTime),
         DATE = as_date(DateTime), 
         TIME = format(DateTime, format = "%H:%M:%S") |> parse_hms()
)
DF
#> # A tibble: 2 × 3
#>   DateTime            DATE       TIME    
#>   <dttm>              <date>     <time>  
#> 1 2021-11-20 10:45:10 2021-11-20 10:45:10
#> 2 2021-11-20 18:19:00 2021-11-20 18:19:00

^{Created on 2023-06-04 with reprex v2.0.2}