Joining a POSIXct column to Date column using data.table in R

Question

In the following example, can someone explain to me why the date 2015-03-31 changed to 1034-04-03?

dt1 = data.table(id = c(1,2), date = as.POSIXct("2015-03-31 BST"), key = "id")
dt1
#    id       date
# 1:  1 2015-03-31
# 2:  2 2015-03-31
dt2 = data.table(id = c(1,2), date = as.Date(NA), key = "id")
dt2
#    id date
# 1:  1 <NA>
# 2:  2 <NA>
dt2[dt1, date := i.date]
dt2
#    id       date
# 1:  1 1034-04-03
# 2:  2 1034-04-03

My aim is to get date from dt1 to dt2 in Date format instead of POSIXct by id (different id would have different date). How would I do that?

Following is what I want:

dt2
#    id       date
# 1:  1 2015-03-31
# 2:  2 2015-03-31

---

EDIT:

I've tried the following:

dt2[dt1, date := as.Date(i.date)]
dt2
#    id       date
# 1:  1 2015-03-30
# 2:  2 2015-03-30

Unfortunately, it is giving me 2015-03-30 instead of 2015-03-31...??? How to get 2015-03-31 instead?

Mangonel

Can you dput the sample data? – Goatsucker

Your code probably should give at least a warning. Please file a bug report. – Frequently

Chengcj, @Roland, filed #1200. – Coen

regarding your second question, you need to supply a timezone to as.Date(). – Yakutsk

Answer 1

You can use

dt2[dt1, date:=as.Date(i.date)]

You might want to also see the question Convert column classes in data.table about changing data types in data.tables.

Barhorst

Unfortunately, if I do what you have proposed, I get 2015-03-30 instead of 2015-03-31.... How come? – Mangonel

@Mangonel That's very likely a timezone issue. Pass your timezone to as.Date. – Frequently