CODEWITHSUNDEEP
rdatedatetimelubridatejanitor
Tyler Lane
Tyler Lane

Reputation: 89

Converting datetime to date in R

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?

Upvotes: 0

Views: 170

Answers (3)

Sri Sreshtan
Sri Sreshtan

Reputation: 545

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

Upvotes: 4

Wimpel
Wimpel

Reputation: 27732

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"

Upvotes: 3

vn1gam
vn1gam

Reputation: 148

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")

Upvotes: 1

Related Questions