Search code examples
rlubridate

Extract date and time from datetime field in R

I have a dateset that looks like this, the readingdate is in POSIXct format. I want to extract date in one field and time in another field in R. I'm trying to avoid using base R as much as possible so if you can do this that'ld be great (lubridate ). I want newly extracted fields to be in the right format because my ultimate goal is to plot the time(x) against total items sold (y) in order to determine what time of the day the highest sale is made. Thanks for your help.

mydata

Solution

  • If I understood well, R can read correctly your dates and times as you import your data (because they are in POSIXct format), but you can not extract the date and the time in the right format from your date-time column.

    Considering that you have a data.frame in R, like this:

                date_time Sold
1 2020-01-01 03:16:01    2
2 2020-01-02 02:15:12    2
3 2020-01-03 08:26:11    3
4 2020-01-04 09:29:14    2
5 2020-01-05 12:06:06    1
6 2020-01-06 08:08:11    3

    Lubridate does not offer a function to extract the time component, so you have to extract piece by piece with the minute(), hour() and second() functions. Then you can just concatenate these components with paste() function. Now, with the dates, you can use the date() function to extract then, after that, you use the format() function to format these dates in the way you want.

    library(lubridate)
library(dplyr)
library(magrittr)

tab <- tab %>% 
  mutate(
    date = as.Date(date_time),
    hour = hour(date_time),
    minute = minute(date_time),
    second = second(date_time)
  ) %>% 
  mutate(
    format_date = format(date, "%m/%d/%Y"),
    format_hour = paste(hour, minute, second, sep = ":")
  )

    Resulting this:

    tab %>% select(format_date, format_hour) %>% head()

  format_date format_hour
1  01/01/2020     12:4:23
2  01/02/2020     3:19:13
3  01/03/2020      8:6:24
4  01/04/2020      6:28:2
5  01/05/2020     2:16:20
6  01/06/2020     12:8:28