I have a dataframe consisting of 3 cloumns. I want to change the format of the of the Datetime column to POSIXct. But i can't figure out a way to work with the milliseconds. Is it even possible to work with milliseconds in POSIXct? I have seen it in different posts
This is a sample of the dataframe I am working with:
If I first try to set the format to format="%Y-%m-%d %H:%M:%OS" I loose the milliseconds:
#data
df
Datetime Tag Value
1 2023-09-27 00:00:00.032 RobotSignals_RobotIR01LoadingUnloadingComIn_di_Lifebit 1.000000
2 2023-09-27 00:00:00.033 RobotSignals_RobotIR01LoadingUnloadingComIn_di_Lifebit 1.000000
3 2023-09-27 00:00:00.068 SystemTrack_ActivePowerL1 682.399960
4 2023-09-27 00:00:00.075 SystemTrack_ActivePowerL2 573.800000
5 2023-09-27 00:00:00.082 SystemTrack_ActivePowerL3 551.600000
6 2023-09-27 00:00:00.088 SystemTrack_AirPressure 5.426357
7 2023-09-27 00:00:00.094 SystemTrack_CurrentL1 3.577860
8 2023-09-27 00:00:00.102 SystemTrack_CurrentL2 3.161120
9 2023-09-27 00:00:00.102 RobotSignals_RobotIR03L3POSpringLoadingComIn_di_JobNoMirrored 10.000000
10 2023-09-27 00:00:00.108 SystemTrack_CurrentL3 3.389500
#changing the format from charakter to POSIXct
df$Datetime <- as.POSIXct(df$Datetime, format="%Y-%m-%d %H:%M:%OS")
#data without the milliseconds
Datetime Tag Value
1 2023-09-27 00:00:00 RobotSignals_RobotIR01LoadingUnloadingComIn_di_Lifebit 1.000000
2 2023-09-27 00:00:00 RobotSignals_RobotIR01LoadingUnloadingComIn_di_Lifebit 1.000000
3 2023-09-27 00:00:00 SystemTrack_ActivePowerL1 682.399960
4 2023-09-27 00:00:00 SystemTrack_ActivePowerL2 573.800000
5 2023-09-27 00:00:00 SystemTrack_ActivePowerL3 551.600000
6 2023-09-27 00:00:00 SystemTrack_AirPressure 5.426357
7 2023-09-27 00:00:00 SystemTrack_CurrentL1 3.577860
8 2023-09-27 00:00:00 SystemTrack_CurrentL2 3.161120
9 2023-09-27 00:00:00 RobotSignals_RobotIR03L3POSpringLoadingComIn_di_JobNoMirrored 10.000000
10 2023-09-27 00:00:00 SystemTrack_CurrentL3 3.389500
And if I try to directly follow OS by 3 format="%Y-%m-%d %H:%M:%OS3" the values change to NA.
#changing the format from charakter to POSIXct
df$Datetime <- as.POSIXct(df$Datetime, format="%Y-%m-%d %H:%M:%OS3")
#all values changed to NA
Datetime Tag Value
1 <NA> RobotSignals_RobotIR01LoadingUnloadingComIn_di_Lifebit 1.000000
2 <NA> RobotSignals_RobotIR01LoadingUnloadingComIn_di_Lifebit 1.000000
3 <NA> SystemTrack_ActivePowerL1 682.399960
4 <NA> SystemTrack_ActivePowerL2 573.800000
5 <NA> SystemTrack_ActivePowerL3 551.600000
6 <NA> SystemTrack_AirPressure 5.426357
7 <NA> SystemTrack_CurrentL1 3.577860
8 <NA> SystemTrack_CurrentL2 3.161120
9 <NA> RobotSignals_RobotIR03L3POSpringLoadingComIn_di_JobNoMirrored 10.000000
10 <NA> SystemTrack_CurrentL3 3.389500
What am I doing wrong here?
It only looks like you're losing the milliseconds. They don't print, but are retained. You can see when you take the difference between two values. From the printed output, it looks like all the differences should be zero, but you can see below that the differences are as you would expect from the differences in the milliseconds.
dat <- tibble::tribble(
~Datetime, ~Tag, ~Value,
"2023-09-27 00:00:00.032", "RobotSignals_RobotIR01LoadingUnloadingComIn_di_Lifebit", 1.000000,
"2023-09-27 00:00:00.033", "RobotSignals_RobotIR01LoadingUnloadingComIn_di_Lifebit", 1.000000,
"2023-09-27 00:00:00.068", "SystemTrack_ActivePowerL1", 682.399960,
"2023-09-27 00:00:00.075", "SystemTrack_ActivePowerL2", 573.800000,
"2023-09-27 00:00:00.082", "SystemTrack_ActivePowerL3", 551.600000,
"2023-09-27 00:00:00.088", "SystemTrack_AirPressure", 5.426357,
"2023-09-27 00:00:00.094", "SystemTrack_CurrentL1", 3.577860,
"2023-09-27 00:00:00.102", "SystemTrack_CurrentL2", 3.161120,
"2023-09-27 00:00:00.102", "RobotSignals_RobotIR03L3POSpringLoadingComIn_di_JobNoMirrored", 10.000000,
"2023-09-27 00:00:00.108", "SystemTrack_CurrentL3", 3.389500)
dat$Datetime <- as.POSIXlt(dat$Datetime, format="%Y-%m-%d %H:%M:%OS")
round(dat$Datetime[2:10]-dat$Datetime[1:9], 3)
#> Time differences in secs
#> [1] 0.001 0.035 0.007 0.007 0.006 0.006 0.008 0.000 0.006
Created on 2024-03-15 with reprex v2.0.2
There does seem to be a bit of floating point weirdness going on, but if you round to their original precision (as above) the results are right.