Extract time with milliseconds from character vector-CodePudding

I have a data base with time column in the format of dd/mm/yy hh:mm:ss:milliseconds. example: 09/08/22 09:51:59:195

How do I extract the time from this into a new vector?

CodePudding user response：

Since to go-to way for R to recognize milliseconds is with a dot, you can replace the last : by a dot, and then use %OS, which is the appropriate format for milliseconds in R. Convert your string to a POSIXct object, and use format to format it as you need.

time = "09/08/22 09:51:59:195"
time = sub(":([^:]*)$", "\\.\\1", time)
time <- as.POSIXct(time, format = "%d/%m/%y %H:%M:%OS")

format(time, "%H:%M:%OS")
#[1] "09:51:59.194"

A regex way, totally ignoring the fact that the string is a date, but which might save some lines, is:

sub("^\\S \\s ", '', time)
#[1] "09:51:59:195"

CodePudding user response：

If you are in mysql, try that's in mysql:

SELECT fields
FROM table
WHERE timefield BETWEEN DATE_SUB('$date', INTERVAL $tohour HOUR) AND '$date'

Also i found a similar question here so maybe you will found an awnser?