Error Creating R Data.Table with Date-Time Posixlt

Error creating R data.table with date-time POSIXlt

Formatting response from Blue Magister's comment (thanks so much), data.table does not support POSIXlt data types for performance reason -- see cast string to IDateTime as suggested as possible duplicate.

So the way to go is to cast time as ITime (type provided by data.table) or date-time (or date only) as POSIXct, depending upon whether date info is important or not:

> mdt <- data.table(id=1:3, d=as.ITime(strptime(c("06:02:36", "06:02:48", "07:03:12"), "%H:%M:%S")))
> print(mdt)
   id        d
1:  1 06:02:36
2:  2 06:02:48
3:  3 07:03:12
> mdt <- data.table(id=1:3, d=as.POSIXct(strptime(c("06:02:36", "06:02:48", "07:03:12"), "%H:%M:%S")))
> print(mdt)
   id                   d
1:  1 2014-01-31 06:02:36
2:  2 2014-01-31 06:02:48
3:  3 2014-01-31 07:03:12

As an extra note in case someone can benefit from it, I wanted to create date & time from my input data with date & time in separate fields.
I found it useful to learn (see ?ITime) that one can add time ITime to date-time POSIXct and get a date-time POSIXct as follows:

> mdt <- as.POSIXct("2014-01-31") + as.ITime("06:02:36")
> print(mdt)
[1] "2014-01-31 06:02:36 EST"
> class(mdt)
[1] "POSIXct" "POSIXt" 

assign POSIXlt date to data.table column

Just use POSIXct which you, inter alia, get for free from the anytime package.

R> dt <- data.table(strdate = c("20140101", "20140103"))
R> dt
    strdate
1: 20140101
2: 20140103
R> library(anytime)
R> dt[, date := anytime(strdate)]   # creates POSIXct, use anydate() for Date
R> dt
    strdate       date
1: 20140101 2014-01-01
2: 20140103 2014-01-03
R> 

The column now contains proper date(time) representation which you can still compute on:

R> dt[, newdate := as.IDate(date) + 7]
R> dt
    strdate       date    newdate
1: 20140101 2014-01-01 2014-01-08
2: 20140103 2014-01-03 2014-01-10
R> 

Or, if you want dates, just use the Date type, eg via anydate():

R> dt <- data.table(strdate = c("20140101", "20140103"))
R> dt[, date := anydate(strdate)]
R> dt[, newdate := date + 7]
R> dt
    strdate       date    newdate
1: 20140101 2014-01-01 2014-01-08
2: 20140103 2014-01-03 2014-01-10
R> 

Issues with creating a new column in datatable using strptime

You can't use POSIXlt in data.table, use POSIXct instead:

library(data.table)
## setting up the data
solarX <- fread('MEASDATE      rrp exp_kwh  
"1/05/2015 0:00" 33.99299 0
"1/05/2015 0:30" 31.53335 0
"1/05/2015 1:00" 29.37092 0
"1/05/2015 1:30" 28.03197 0
"1/05/2015 2:00" 26.82800 0
"1/05/2015 2:30" 25.22149 0')

solarX[, date := as.POSIXct(MEASDATE, format = "%d/%m/%Y %H:%M")]

#           MEASDATE      rrp exp_kwh                date
# 1: 1/05/2015 0:00 33.99299       0 2015-05-01 00:00:00
# 2: 1/05/2015 0:30 31.53335       0 2015-05-01 00:30:00
# 3: 1/05/2015 1:00 29.37092       0 2015-05-01 01:00:00
# 4: 1/05/2015 1:30 28.03197       0 2015-05-01 01:30:00
# 5: 1/05/2015 2:00 26.82800       0 2015-05-01 02:00:00
# 6: 1/05/2015 2:30 25.22149       0 2015-05-01 02:30:00

str(solarX)
Classes ‘data.table’ and 'data.frame':  6 obs. of  4 variables:
 $ MEASDATE: chr  "1/05/2015 0:00" "1/05/2015 0:30" "1/05/2015 1:00" "1/05/2015 1:30" ...
 $ rrp    : num  34 31.5 29.4 28 26.8 ...
 $ exp_kwh: int  0 0 0 0 0 0
 $ date   : POSIXct, format: "2015-05-01 00:00:00" "2015-05-01 00:30:00" "2015-05-01 01:00:00" "2015-05-01 01:30:00" ...
 - attr(*, ".internal.selfref")=<externalptr> 

R data table recommended way to deal with date time

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.

R round PosixCT variable in a data table

From ?round.POSIXt:

Value

An object of class "POSIXlt" or "Date".

Which means the result of round on a POSIX object is a POSIXlt object.

Therefore you need to wrap your round function inside as.POSIXct() to get it back to POSIXct

dt[, timestamp2 := as.POSIXct(round(timeStamp, "mins"))]

R data table with trunc on POSIXct

Try this:

dt[, day:=as.POSIXct(trunc(ts, 'days')) ]

From ?data.table

   POSIXlt is not supported as a column type because it uses 40 bytes to
store a single datetime. Unexpected errors may occur if you manage to create
a column of type POSIXlt. Please see NEWS for 1.6.3, and IDateTime instead.

---

Related Topics