R data table recommended way to deal with date time

Question

I have a csv file with one column of timestamps "2000-01-01 12:00:00.123456". What's the recommended way to dealing with it in data table? I need to deal with grouping, matching/rolling join with IDate column from another table, time series plotting, etc.

IDateTime("2000-01-01 12:00:00.123456")

Error in if (any(neg)) res[neg] = paste("-", res[neg], sep = "") :
missing value where TRUE/FALSE needed

I see this answer in the possible duplicate question in which Matthew suggested manually casting dates into integers. But that's 3 years old and I wonder if there now exists a better way?

Answer 1

IDateTime requires a POSIXct class object in order to work properly (it seems to work properly with a factor conversion too, not sure why). I agree it isn't documented very well and maybe worth opening an FR/PR on GH regarding documentation- there is an open queue regarding an IDateTime vignette though. And there is already an FR regarding allowing it to work with a character class.

IDateTime(as.POSIXct("2000-01-01 12:00:00.123456"))
#         idate    itime
# 1: 2000-01-01 12:00:00
## IDateTime(factor("2000-01-01 12:00:00.123456")) ## will also work

Pay attention to the tz parameter in as.POSIXct if you want to avoid unexpected behaviour

---

Regardless, it seems like the error is actually caused by the print method of ITime which calls format.ITime, see here and here e.g., if you will run res <- IDateTime("2015-09-29 08:22:00") this will not produce an error, though res will be NA due to wrong conversion (I believe) in here (the format is only "%H:%M:%OS"). It seems like a bug to me and I still uncertain why factor class works correctly when there is no factor method in methods(as.ITime). Maybe due to its integer internal storage mode which calls another related method.

Answer 2

Depending on the precision required for your time fields you may need to use POSIXct instead of IDateTime.
The timestamp format stored in your source file can be reproduced in R by format(Sys.time(), "%Y-%m-%d %H:%M:%OS6").
When using IDateTime you will lose the subseconds, you can play with ITime and see if it fits your need.
If you will stick to POSIXctthen you should be aware of ?setNumericRounding function which may be sometimes important as it affects ordering and joining on POSIXct's underlying numeric data type.