r - converting POSIXct to milliseconds

Question

From ?POSIXct we know that

Class "POSIXct" represents the (signed) number of seconds since the beginning of 1970 (in the UTC time zone) as a numeric vector.

Therefore, I've assumed that to get a POSIXct value in milliseconds we need to multiply by 1000

---

Consider the days in December 2015

## generate sequence of days in December 2015
d <- seq(as.POSIXct("2015-12-01"), as.POSIXct("2015-12-31"), by = 60*60*24)
#  [1] "2015-12-01 AEDT" "2015-12-02 AEDT" 
#  ...
# [29] "2015-12-29 AEDT" "2015-12-30 AEDT" "2015-12-31 AEDT"

Converting them to integer

d <- as.integer(d)

We see that each integer is 10 digits

nchar(d)
# [1] 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10

When multiplying by 1000 to convert to milliseconds we get

nchar(d * 1000)
# [1] 13 13 13 13 12 13 13 13 13 12 13 13 13 13 12 13 13 13 13 11 13 13 13 13 12 13 13 13 13 12 13

some values are only 11 or 12 digits (whereas I would have thought multiplying a 10-digit number by 1000 would add 3 digits)

Is there an explanation for this that I'm not seeing?

Answer 1

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.