Agenda


  • how does R treat date/time?
  • create and store date/time variables
  • extract date and time components
  • compute time differences
  • compute time interval and duration

Libraries


Origin


## [1] "1970-01-01 UTC"

Current Date/Time


## [1] "2019-03-20"
## [1] "2019-03-20 12:53:45 IST"
## [1] "Asia/Calcutta"

Current Date/Time


## [1] "2019-03-20 12:53:45 IST"
## [1] "2019-03-20"

Current Date/Time


## [1] FALSE
## [1] TRUE

How does R handle date/time?


## [1] 1995
## [1] "numeric"

How does R handle date/time?


## [1] "2018-08-15"
## [1] "character"

How does R handle date/time?


## [1] "2018-08-15"
## [1] "Date"

Date from Character


## [1] "2018-08-15"
## [1] "Date"

Date & Timezone


## [1] "2018-08-15"
## [1] "Date"

Date/Time & Timezone


## [1] "2018-08-15 22:30:42 IST"
## [1] "POSIXct" "POSIXt"

Date from Days


## [1] "2011-07-17"

Date/Time from Days


## [1] "2000-01-01 01:10:15 UTC"

ISO 8601 Format




Data


## # A tibble: 2,466 x 3
##    Invoice    Due        Payment   
##    <date>     <date>     <date>    
##  1 2013-01-02 2013-02-01 2013-01-15
##  2 2013-01-26 2013-02-25 2013-03-03
##  3 2013-07-03 2013-08-02 2013-07-08
##  4 2013-02-10 2013-03-12 2013-03-17
##  5 2012-10-25 2012-11-24 2012-11-28
##  6 2012-01-27 2012-02-26 2012-02-22
##  7 2013-08-13 2013-09-12 2013-09-09
##  8 2012-12-16 2013-01-15 2013-01-12
##  9 2012-05-14 2012-06-13 2012-07-01
## 10 2013-07-01 2013-07-31 2013-07-26
## # ... with 2,456 more rows

Data Dictionary


The data set has 3 columns. All the dates are in the format (yyyy-mm-dd).

  • Invoice: invoice date
  • Due: due date
  • Payment: payment date

Case Study


  • extract date, month and year from Due
  • compute the number of days to settle invoice
  • compute days over due
  • check if due year is a leap year
  • check when due day in february is 29, whether it is a leap year
  • how many invoices were settled within due date
  • how many invoices are due in each quarter
  • what is the average duration between invoice date and payment date

Day, Month & Year




Day, Month & Year


## [1] 23
## [1] 3
## [1] 2017

Extract Day, Month & Year from Due Date


## # A tibble: 2,466 x 6
##    Invoice    Due        Payment    due_day due_month due_year
##    <date>     <date>     <date>       <int>     <dbl>    <dbl>
##  1 2013-01-02 2013-02-01 2013-01-15       1         2     2013
##  2 2013-01-26 2013-02-25 2013-03-03      25         2     2013
##  3 2013-07-03 2013-08-02 2013-07-08       2         8     2013
##  4 2013-02-10 2013-03-12 2013-03-17      12         3     2013
##  5 2012-10-25 2012-11-24 2012-11-28      24        11     2012
##  6 2012-01-27 2012-02-26 2012-02-22      26         2     2012
##  7 2013-08-13 2013-09-12 2013-09-09      12         9     2013
##  8 2012-12-16 2013-01-15 2013-01-12      15         1     2013
##  9 2012-05-14 2012-06-13 2012-07-01      13         6     2012
## 10 2013-07-01 2013-07-31 2013-07-26      31         7     2013
## # ... with 2,456 more rows

Course Duration




Course Duration


## Time difference of 9 days

Days to settle invoice


## # A tibble: 2,466 x 4
##    Invoice    Due        Payment    days_to_pay
##    <date>     <date>     <date>     <time>     
##  1 2013-01-02 2013-02-01 2013-01-15 13 days    
##  2 2013-01-26 2013-02-25 2013-03-03 36 days    
##  3 2013-07-03 2013-08-02 2013-07-08  5 days    
##  4 2013-02-10 2013-03-12 2013-03-17 35 days    
##  5 2012-10-25 2012-11-24 2012-11-28 34 days    
##  6 2012-01-27 2012-02-26 2012-02-22 26 days    
##  7 2013-08-13 2013-09-12 2013-09-09 27 days    
##  8 2012-12-16 2013-01-15 2013-01-12 27 days    
##  9 2012-05-14 2012-06-13 2012-07-01 48 days    
## 10 2013-07-01 2013-07-31 2013-07-26 25 days    
## # ... with 2,456 more rows

Days over due


## # A tibble: 2,466 x 4
##    Invoice    Due        Payment    delay   
##    <date>     <date>     <date>     <time>  
##  1 2013-01-02 2013-02-01 2013-01-15 -17 days
##  2 2013-01-26 2013-02-25 2013-03-03   6 days
##  3 2013-07-03 2013-08-02 2013-07-08 -25 days
##  4 2013-02-10 2013-03-12 2013-03-17   5 days
##  5 2012-10-25 2012-11-24 2012-11-28   4 days
##  6 2012-01-27 2012-02-26 2012-02-22  -4 days
##  7 2013-08-13 2013-09-12 2013-09-09  -3 days
##  8 2012-12-16 2013-01-15 2013-01-12  -3 days
##  9 2012-05-14 2012-06-13 2012-07-01  18 days
## 10 2013-07-01 2013-07-31 2013-07-26  -5 days
## # ... with 2,456 more rows

Is due year a leap year?


## # A tibble: 2,466 x 5
##    Invoice    Due        Payment    due_year is_leap
##    <date>     <date>     <date>        <dbl> <lgl>  
##  1 2013-01-02 2013-02-01 2013-01-15     2013 FALSE  
##  2 2013-01-26 2013-02-25 2013-03-03     2013 FALSE  
##  3 2013-07-03 2013-08-02 2013-07-08     2013 FALSE  
##  4 2013-02-10 2013-03-12 2013-03-17     2013 FALSE  
##  5 2012-10-25 2012-11-24 2012-11-28     2012 TRUE   
##  6 2012-01-27 2012-02-26 2012-02-22     2012 TRUE   
##  7 2013-08-13 2013-09-12 2013-09-09     2013 FALSE  
##  8 2012-12-16 2013-01-15 2013-01-12     2013 FALSE  
##  9 2012-05-14 2012-06-13 2012-07-01     2012 TRUE   
## 10 2013-07-01 2013-07-31 2013-07-26     2013 FALSE  
## # ... with 2,456 more rows

Case Study


If the due day happens to be February 29, let us ensure that the due year is a leap year. Below are the steps to check if the due year is a leap year:

  • we will extract the following from the due date:
    • day
    • month
    • year
  • we will then create a new column is_leap which will have be set to TRUE if the year is a leap year else it will be set to FALSE
  • filter all the payments due on 29th Feb
  • select the following columns:
    • Due
    • is_leap

Case Study


## # A tibble: 4 x 2
##   Due        is_leap
##   <date>     <lgl>  
## 1 2012-02-29 TRUE   
## 2 2012-02-29 TRUE   
## 3 2012-02-29 TRUE   
## 4 2012-02-29 TRUE

Shift Date




Shift Date


## [1] "2017-04-14"
## [1] "2017-04-19"
## [1] "2018-04-12"

Interval




Interval


## [1] 2017-04-12 UTC--2017-04-21 UTC

Shift Interval




Shift Interval


## [1] 2017-04-13 UTC--2017-04-22 UTC
## [1] 2017-05-03 UTC--2017-05-12 UTC
## [1] 2018-04-12 UTC--2018-04-21 UTC

Interval Overlap