Add milliseconds column to datetime

The dataframe looks like

datetime            msec    value 

2022-07-19 17:30:00 8       5   
2022-07-19 17:30:00 58      9   
2022-07-19 17:30:00 108     3

I want to add the milliseconds to the datetime like this

datetime               value  
2022-07-19 17:30:00.008   5    
2022-07-19 17:30:00.058   9   
2022-07-19 17:30:00.108   3

I tried

df$datetime <- as.POSIXct(paste(dfdatetime, df$msec), format="%y/%m/%d %H:%M:%OS")

Solution

You need to parse it the correct format to make it work. This can be done in several ways.

Adding the milliseconds (Thanks to @Ritchie Sacramento for this point):

new_datetime <- df$datetime + df$msec/1000

Or using lubridate:

new_datetime <- df$datetime + lubridate::milliseconds(df$msec)

Paste it like your own approach:

Beware though:

With a . between seconds and milliseconds, no spaces (thus paste0).
Date format with - not /
To pad the milliseconds with zeros (Thanks to @Ritchie Sacramento for this point)

df$new_datetime <- as.POSIXct(paste0(df$datetime, ".", sprintf("%03d", df$msec)), format="%Y-%m-%d %H:%M:%OS")

Output: (there will be rounding/representation errors) *

options(digits.secs=3)

df

# A tibble: 3 × 4
  datetime                 msec value new_datetime           
  <dttm>                  <dbl> <dbl> <dttm>                 
1 2022-07-19 17:30:00.000     8     5 2022-07-19 17:30:00.007
2 2022-07-19 17:30:00.000    58     9 2022-07-19 17:30:00.058
3 2022-07-19 17:30:00.000   108     3 2022-07-19 17:30:00.108

Alternatively: Format it to show the milliseconds with 3 digits.

format(df$new_datetime, "%Y-%m-%d %H:%M:%OS3")

[1] "2022-07-19 17:30:00.007" "2022-07-19 17:30:00.058" "2022-07-19 17:30:00.108"

Data:

library(readr)

df <- read_delim("datetime,msec,value
2022-07-19 17:30:00,8,5
2022-07-19 17:30:00,58,9
2022-07-19 17:30:00,108,3")

(*) See Milliseconds in POSIXct Class

Update: Fix the parsing errors. Sorry for not being aware!