R - Converting Posixct to Milliseconds

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.

Accurately converting from character-POSIXct-character with sub millisecond datetimes

Two things:

1) @statquant is right (and the otherwise known experts @Joshua Ulrich and @Dirk Eddelbuettel are wrong), and @Aaron in his comment, but that will not be important for the main question here:

POSIXlt by design is definitely more accurate in storing times than POSIXct: As its seconds are always in [0, 60), it has a granularity of about 6e-15, i.e., 6 femtoseconds which would be dozens of million times less granular than POSIXct.

However, this is not very relevant here (and for current R): Almost all operations, notably numeric ones, use the Ops group method (yes, not known to beginners, but well documented), just look at Ops.POSIXt which indeed trashes the extra precision by first coercing to POSIXct. In addition, the format()/print() ing uses 6 decimals after the "." at most, and hence also does not distinguish between the internally higher precision of POSIXlt and the "only" 100 nanosecond granularity of POSIXct.

(For the above reason, both Dirk and Joshua were lead to their wrong assertion: For all simple practical uses, the precision of *lt and *ct is made the same).

2) I do tend to agree that we (R Core) should improve the format()ing and hence print()ing of such fractions of seconds POSIXt objects (still after the bug fix mentioned by @Aaron above).

But then I may be wrong, and "we" have got it right, by some definition of "right" ;-)

Convert timestamp to milliseconds

First you have to convert to POSIXct as in this thread: Dealing with timestamps in R. So the code should be:

as.numeric(as.POSIXct(Time))

Solution using your own example

a <- 1383293400000
b <- as.POSIXct((a)/1000, origin = "1970-01-01")
b <- strftime(b, format="%Y-%m-%d %H:%M") #"2013-11-01 09:10"

outputs

> as.numeric(as.POSIXct(b))*1000 == a
[1] TRUE

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"

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"

Adding milliseconds to a timestamp in R, even though the original character does not have milliseconds?

The milliseconds will be dropped if there are no times with effective milliseconds.

options(digits.secs=4)

x1 <- as.POSIXct("2017-07-19 16:30:25")
as.POSIXct(paste0(x1, ".000"), format="%Y-%m-%d %H:%M:%OS")
# [1] "2017-07-19 16:30:25 UTC"

However, they will be added automatically if there are.

x2 <- as.POSIXct("2017-07-19 16:30:25.002")
c(x1, x2)
# [1] "2017-07-19 18:30:25.000 CEST" "2017-07-19 18:30:25.002 CEST"

Convert numeric time in milliseconds to datetime with seconds with decimal value

With lubridate, using as_datetime

library(lubridate)
as_datetime(time/1000)
[1] "2021-10-08 16:01:17 UTC"

Note that the milliseconds are not printed in the console. If we need to print, then format with strftime or format (but it will not be a datetime object anymore)

strftime(as_datetime(time/1000), '%Y-%m-%d %H:%M:%OS3') 
#[1] "2021-10-08 11:01:17.772"

---

Or without using any package, just specify it in as.POSIXct

as.POSIXct(time/1000, origin = '1970-01-01')
[1] "2021-10-08 11:01:17 CDT"

---

Related Topics