Milliseconds in Posixct Class

as.POSIXct is mishandling timestamp with leading zero in the milliseconds portion

You are using incorrect format. Try -

time_043_millis <- "20210909-20:05:10.043"    
time_143_millis <- "20210909-20:05:10.143" 

as.POSIXct(time_043_millis, format = "%Y%m%d-%H:%M:%OS", tz = 'UTC')
#[1] "2021-09-09 20:05:10 UTC"

as.POSIXct(time_143_millis, format = "%Y%m%d-%H:%M:%OS", tz = 'UTC')
#[1] "2021-09-09 20:05:10 UTC"

Milliseconds in POSIXtc

You are missing the options(digits.secs=6) setting.

Demo

R> data <- read.csv(text="dt,val
+ 2010-03-01 13:02:00.0010070, 0.030
+ 2010-03-01 13:02:00.0020141, 0.031
+ 2010-03-01 13:02:00.0030059, 0.035
+ 2010-03-01 13:02:00.0040130, 0.041
+ 2010-03-01 13:02:00.0050048, 0.049
+ 2010-03-01 13:02:00.0060119, 0.060")
R>
R> data[,1] <- anytime::anytime(data[, 1])  ## or as.POSIXct or ...
R>
R> options(digits.secs=6)
R> data
                           dt   val
1 2010-03-01 13:02:00.0010070 0.030
2 2010-03-01 13:02:00.0020141 0.031
3 2010-03-01 13:02:00.0030059 0.035
4 2010-03-01 13:02:00.0040130 0.041
5 2010-03-01 13:02:00.0050048 0.049
6 2010-03-01 13:02:00.0060119 0.060
R> 
R> class(data[,1])
[1] "POSIXct" "POSIXt" 
R> 

The zoo package has a few fine vignettes showing to slice, dice and aggregate data based on timestamps.

as.POSIXct/as.POSIXlt doesn't like .61 milliseconds

If you change the format for the time part to be %H%M%OS instead of %H%M%S.%OS, it seems to parse correctly. You may have to adjust your options so see this:

as.POSIXlt(vec, tz = "EST", format = "%Y%m%d.%H%M%OS")
#[1] "2015-01-01 01:01:01 EST" "2015-01-01 01:01:01 EST" 
#[3] "2015-01-01 01:01:01 EST"

options(digits.secs = 2)
as.POSIXlt(vec, tz = "EST", format = "%Y%m%d.%H%M%OS")
# [1] "2015-01-01 01:01:01.60 EST" "2015-01-01 01:01:01.61 EST"
# [3] "2015-01-01 01:01:01.62 EST"

Converting unix seconds in milliseconds to POSIXct/POSIXlt

The help page actually hints at a difference:

Value:

     ‘as.POSIXct’ and ‘as.POSIXlt’ return an object of the appropriate
     class.  If ‘tz’ was specified, ‘as.POSIXlt’ will give an
     appropriate ‘"tzone"’ attribute.

This stuff is finicky -- I think there is an implicit TZ conversion happening for as.POSIXct. Consider that

R> print(as.numeric(as.POSIXct(as.POSIXlt(1268736919, 
                               origin="1970-01-01"))), digits=10)
[1] 1268736919
R> print(as.numeric(as.POSIXct(1268736919, origin="1970-01-01")), digits=10)
[1] 1268758519

the second one (using as.POSIXct) does not return the original input. Unfortunately, Brian D. Ripley seems to be the only human having all the details here.

Lastly, you can't do it without the origin. But you could define wrappers that use the epoch as origin (as here) or use 2000-01-01 or ... Just keep it consistent.

r - converting POSIXct to milliseconds

Summary answer

The short answer to this is to do with how numbers are printed in scientific format

To see this we can set options(scipen=1000) and we get the result as expected.

options(scipen=1000); 
nchar(d*1000)
# [1] 13 13 13 13 13 13 13 13 13 13 13 13 13 13 13 13 13 13 13 13 13 13 13 13 13 13 13 13 13 13 13

---

longer answer

The background to this question came from trying to query a mongodb database using a date range in library(mongolite)

For example, this question and this issue show that to query a date it needs to be converted to numberLong for the query to work correctly.

To illistrate this, and the issue I was having, consider this data and subsequent queries

library(mongolite)

df <- data.frame(date = as.POSIXct(c("2015-12-19","2015-12-20","2015-12-21")),
                 val = c(1,2,3))

mongo <- mongo(collection = "test", db = "test", url = "mongodb://localhost")

## insert data into test database
mongo$insert(df)

## querying the 19th December 2015 works as expected, because d is formatted with 13 digits
d <- as.integer(as.POSIXct("2015-12-19")) * 1000
q <- paste0('{"date" : {"$date" : { "$numberLong" : "', d,'" } } }')
mongo$find(query = q)
# 
# Imported 1 records. Simplifying into dataframe...
# date val
# 1 2015-12-19   1

## the 20th December 2015 errors, as d is formatted with < 13 digits
d <- as.integer(as.POSIXct(("2015-12-20"))) * 1000
q <- paste0('{"date" : {"$date" : { "$numberLong" : "', d,'" } } }')
mongo$find(query = q)
#
# Error: Invalid input string 1.45053e+12, looking for 11

## the 21st December 2015 works as expected because d is formatted with 13 digits.
d <- as.integer(as.POSIXct("2015-12-21")) * 1000
q <- paste0('{"date" : {"$date" : { "$numberLong" : "', d,'" } } }')
mongo$find(query = q)
#
# Imported 1 records. Simplifying into dataframe...
# date val
# 1 2015-12-21   3

## cleanup 
rm(mongo); gc()

So to resolve this I either need to set options(scipen=1000), or right-pad my d with zeroes when it goes into the query.

---

Related Topics