Converting datetime to date in R

Question

I'm having difficult converting datetime data to a date cell in R. Data are from this data collection on http://data-ral.opendata.arcgis.com/datasets/raleigh-police-incidents-srs/data.

The website lists the first "incident date" as "29/04/2005, 3:36 am". However, when I download the data and import it to R, it comes out as "1.114710e+12"; I've reproduced the first six cells below.

   INC_DATETIME
          <dbl>
1 1114709760000
2 1104581100000
3 1105283580000
4 1105748940000
5 1107976740000
6 1113182880000

This is impervious the reliable function from the janitor package, excel_numeric_to_date(). Any clue as to what I need to do to convert these to date?

Would you mind to share data in an appropriate way, e.g. using `dput`. Read: https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example/5963610#5963610 — jay.sf, Jun 15 '20 at 08:45
I've given it a go and updated the post. Thanks for the link. — Tyler Lane, Jun 15 '20 at 09:24

score 4 · Answer 1 · answered Jun 15 '20 at 09:03

library(tidyverse)
library(lubridate)

data_tbl <- read.csv("Raleigh_Police_Incidents_(SRS).csv")
data_tbl$INC_DATETIME1 <- as.POSIXct(data_tbl$INC_DATETIME/1000, origin = "1970-01-01")
data_tbl$date_format <- as.Date(data_tbl$INC_DATETIME1, format = "%Y-%m-%d")
data_tbl <- data_tbl %>%  
    mutate(only_date = ymd(date_format), date = day(only_date))
head(data_tbl$date)

# [1] 28 1 9 15 9 11

score 3 · Accepted Answer · edited Jun 15 '20 at 08:59

3

Looks like it is a numeric in milliseconds from the unix-epoch (1970-01-01)... so first divide by 1000, en then make is a posix. Make sure to set the correct timezone!

For me (Netherlands), the data is presented in timezone "Europe/Amsterdam"... but this might differ for you...

as.POSIXct( 1114709760000/1000, origin = "1970-01-01", tz = "Europe/Amsterdam" )

[1] "2005-04-28 19:36:00 CEST"

edited Jun 15 '20 at 08:59

s_baldur

29,441
4
36
69

answered Jun 15 '20 at 08:45

Wimpel

26,031
1
20
37

score 1 · Answer 3 · answered Jun 15 '20 at 08:50

1

Try this:

df <- read.csv("./Data/Raleigh_Police_Incidents_(SRS).csv", stringsAsFactors = FALSE)
df$INC_DATETIME1 <- as.POSIXct(df$INC_DATETIME/1000, origin = "1970-01-01")

answered Jun 15 '20 at 08:50

vn1gam

128
1
8

Converting datetime to date in R

3 Answers3