Dates and Times

Packages for this section

library(tidyverse)
# library(lubridate)

lubridate is the package that handles dates and times, but is now part of the tidyverse, so no need to load separately.

Dates

  • Dates represented on computers as “days since an origin”, typically Jan 1, 1970, with a negative date being before the origin:
mydates <- c("1970-01-01", "2007-09-04", "1931-08-05")
(somedates <- tibble(text = mydates) %>%
  mutate(
    d = as.Date(text),
    numbers = as.numeric(d)
  ))

Doing arithmetic with dates

  • Dates are “actually” numbers, so can add and subtract (difference is 2007 date in d minus others):
somedates %>% mutate(plus30 = d + 30, diffs = d[2] - d)

Reading in dates from a file

  • read_csv and the others can guess that you have dates, if you format them as year-month-day, like column 1 of this .csv:
date,status,dunno
2011-08-03,hello,August 3 2011
2011-11-15,still here,November 15 2011
2012-02-01,goodbye,February 1 2012
  • Then read them in:
my_url <- "http://ritsokiguess.site/datafiles/mydates.csv"
ddd <- read_csv(my_url)
  • read_csv guessed that the 1st column is dates, but not 3rd.

The data as read in

ddd

Dates in other formats

  • Preceding shows that dates should be stored as text in format yyyy-mm-dd (ISO standard).
  • To deal with dates in other formats, use package lubridate and convert. For example, dates in US format with month first:
tibble(usdates = c("05/27/2012", "01/03/2016", "12/31/2015")) %>%
  mutate(iso = mdy(usdates))

Trying to read these as UK dates

tibble(usdates = c("05/27/2012", "01/03/2016", "12/31/2015")) %>%
  mutate(uk = dmy(usdates))
  • For UK-format dates with month second, one of these dates is legit, but the other two make no sense.

Our data frame’s last column:

  • Back to this:
ddd
# A tibble: 3 × 3
  date       status     dunno           
  <date>     <chr>      <chr>           
1 2011-08-03 hello      August 3 2011   
2 2011-11-15 still here November 15 2011
3 2012-02-01 goodbye    February 1 2012
  • Month, day, year in that order.

so interpret as such

(ddd %>% mutate(date2 = mdy(dunno)) -> d4)

Are they really the same?

  • Column date2 was correctly converted from column dunno:
d4 %>% mutate(equal = identical(date, date2))
# A tibble: 3 × 5
  date       status     dunno            date2      equal
  <date>     <chr>      <chr>            <date>     <lgl>
1 2011-08-03 hello      August 3 2011    2011-08-03 TRUE 
2 2011-11-15 still here November 15 2011 2011-11-15 TRUE 
3 2012-02-01 goodbye    February 1 2012  2012-02-01 TRUE
  • The two columns of dates are all the same.

Making dates from pieces

Starting from this file:

year month day
1970 1 1
2007 9 4
1940 4 15
my_url <- "http://ritsokiguess.site/datafiles/pieces.txt"
dates0 <- read_delim(my_url, " ")

Making some dates

dates0
dates0 %>%
  unite(dates, day, month, year) %>%
  mutate(d = dmy(dates)) -> newdates

The results

newdates
  • unite glues things together with an underscore between them (if you don’t specify anything else). Syntax: first thing is new column to be created, other columns are what to make it out of.
  • unite makes the original variable columns year, month, day disappear.
  • The column dates is text, while d is a real date.

Extracting information from dates

newdates %>%
  mutate(
    mon = month(d),
    day = day(d),
    weekday = wday(d, label = TRUE)
  )

Dates and times

  • Standard format for times is to put the time after the date, hours, minutes, seconds:
(dd <- tibble(text = c(
  "1970-01-01 07:50:01", "2007-09-04 15:30:00",
  "1940-04-15 06:45:10", "2016-02-10 12:26:40"
)))
# A tibble: 4 × 1
  text               
  <chr>              
1 1970-01-01 07:50:01
2 2007-09-04 15:30:00
3 1940-04-15 06:45:10
4 2016-02-10 12:26:40

Converting text to date-times:

  • Then get from this text using ymd_hms:
dd %>% mutate(dt = ymd_hms(text)) %>% pull(dt)
[1] "1970-01-01 07:50:01 UTC" "2007-09-04 15:30:00 UTC"
[3] "1940-04-15 06:45:10 UTC" "2016-02-10 12:26:40 UTC"

Timezones

  • Default timezone is “Universal Coordinated Time”. Change it via tz= and the name of a timezone:
OlsonNames()
  [1] "Africa/Abidjan"                 "Africa/Accra"                  
  [3] "Africa/Addis_Ababa"             "Africa/Algiers"                
  [5] "Africa/Asmara"                  "Africa/Bamako"                 
  [7] "Africa/Bangui"                  "Africa/Banjul"                 
  [9] "Africa/Bissau"                  "Africa/Blantyre"               
 [11] "Africa/Brazzaville"             "Africa/Bujumbura"              
 [13] "Africa/Cairo"                   "Africa/Casablanca"             
 [15] "Africa/Ceuta"                   "Africa/Conakry"                
 [17] "Africa/Dakar"                   "Africa/Dar_es_Salaam"          
 [19] "Africa/Djibouti"                "Africa/Douala"                 
 [21] "Africa/El_Aaiun"                "Africa/Freetown"               
 [23] "Africa/Gaborone"                "Africa/Harare"                 
 [25] "Africa/Johannesburg"            "Africa/Juba"                   
 [27] "Africa/Kampala"                 "Africa/Khartoum"               
 [29] "Africa/Kigali"                  "Africa/Kinshasa"               
 [31] "Africa/Lagos"                   "Africa/Libreville"             
 [33] "Africa/Lome"                    "Africa/Luanda"                 
 [35] "Africa/Lubumbashi"              "Africa/Lusaka"                 
 [37] "Africa/Malabo"                  "Africa/Maputo"                 
 [39] "Africa/Maseru"                  "Africa/Mbabane"                
 [41] "Africa/Mogadishu"               "Africa/Monrovia"               
 [43] "Africa/Nairobi"                 "Africa/Ndjamena"               
 [45] "Africa/Niamey"                  "Africa/Nouakchott"             
 [47] "Africa/Ouagadougou"             "Africa/Porto-Novo"             
 [49] "Africa/Sao_Tome"                "Africa/Timbuktu"               
 [51] "Africa/Tripoli"                 "Africa/Tunis"                  
 [53] "Africa/Windhoek"                "America/Adak"                  
 [55] "America/Anchorage"              "America/Anguilla"              
 [57] "America/Antigua"                "America/Araguaina"             
 [59] "America/Argentina/Buenos_Aires" "America/Argentina/Catamarca"   
 [61] "America/Argentina/Cordoba"      "America/Argentina/Jujuy"       
 [63] "America/Argentina/La_Rioja"     "America/Argentina/Mendoza"     
 [65] "America/Argentina/Rio_Gallegos" "America/Argentina/Salta"       
 [67] "America/Argentina/San_Juan"     "America/Argentina/San_Luis"    
 [69] "America/Argentina/Tucuman"      "America/Argentina/Ushuaia"     
 [71] "America/Aruba"                  "America/Asuncion"              
 [73] "America/Atikokan"               "America/Atka"                  
 [75] "America/Bahia"                  "America/Bahia_Banderas"        
 [77] "America/Barbados"               "America/Belem"                 
 [79] "America/Belize"                 "America/Blanc-Sablon"          
 [81] "America/Boa_Vista"              "America/Bogota"                
 [83] "America/Boise"                  "America/Cambridge_Bay"         
 [85] "America/Campo_Grande"           "America/Cancun"                
 [87] "America/Caracas"                "America/Cayenne"               
 [89] "America/Cayman"                 "America/Chicago"               
 [91] "America/Chihuahua"              "America/Ciudad_Juarez"         
 [93] "America/Coral_Harbour"          "America/Costa_Rica"            
 [95] "America/Creston"                "America/Cuiaba"                
 [97] "America/Curacao"                "America/Danmarkshavn"          
 [99] "America/Dawson"                 "America/Dawson_Creek"          
[101] "America/Denver"                 "America/Detroit"               
[103] "America/Dominica"               "America/Edmonton"              
[105] "America/Eirunepe"               "America/El_Salvador"           
[107] "America/Ensenada"               "America/Fort_Nelson"           
[109] "America/Fortaleza"              "America/Glace_Bay"             
[111] "America/Goose_Bay"              "America/Grand_Turk"            
[113] "America/Grenada"                "America/Guadeloupe"            
[115] "America/Guatemala"              "America/Guayaquil"             
[117] "America/Guyana"                 "America/Halifax"               
[119] "America/Havana"                 "America/Hermosillo"            
[121] "America/Indiana/Indianapolis"   "America/Indiana/Knox"          
[123] "America/Indiana/Marengo"        "America/Indiana/Petersburg"    
[125] "America/Indiana/Tell_City"      "America/Indiana/Vevay"         
[127] "America/Indiana/Vincennes"      "America/Indiana/Winamac"       
[129] "America/Inuvik"                 "America/Iqaluit"               
[131] "America/Jamaica"                "America/Juneau"                
[133] "America/Kentucky/Louisville"    "America/Kentucky/Monticello"   
[135] "America/Kralendijk"             "America/La_Paz"                
[137] "America/Lima"                   "America/Los_Angeles"           
[139] "America/Lower_Princes"          "America/Maceio"                
[141] "America/Managua"                "America/Manaus"                
[143] "America/Marigot"                "America/Martinique"            
[145] "America/Matamoros"              "America/Mazatlan"              
[147] "America/Menominee"              "America/Merida"                
[149] "America/Metlakatla"             "America/Mexico_City"           
[151] "America/Miquelon"               "America/Moncton"               
[153] "America/Monterrey"              "America/Montevideo"            
[155] "America/Montreal"               "America/Montserrat"            
[157] "America/Nassau"                 "America/New_York"              
[159] "America/Nipigon"                "America/Nome"                  
[161] "America/Noronha"                "America/North_Dakota/Beulah"   
[163] "America/North_Dakota/Center"    "America/North_Dakota/New_Salem"
[165] "America/Nuuk"                   "America/Ojinaga"               
[167] "America/Panama"                 "America/Pangnirtung"           
[169] "America/Paramaribo"             "America/Phoenix"               
[171] "America/Port_of_Spain"          "America/Port-au-Prince"        
[173] "America/Porto_Acre"             "America/Porto_Velho"           
[175] "America/Puerto_Rico"            "America/Punta_Arenas"          
[177] "America/Rainy_River"            "America/Rankin_Inlet"          
[179] "America/Recife"                 "America/Regina"                
[181] "America/Resolute"               "America/Rio_Branco"            
[183] "America/Santa_Isabel"           "America/Santarem"              
[185] "America/Santiago"               "America/Santo_Domingo"         
[187] "America/Sao_Paulo"              "America/Scoresbysund"          
[189] "America/Shiprock"               "America/Sitka"                 
[191] "America/St_Barthelemy"          "America/St_Johns"              
[193] "America/St_Kitts"               "America/St_Lucia"              
[195] "America/St_Thomas"              "America/St_Vincent"            
[197] "America/Swift_Current"          "America/Tegucigalpa"           
[199] "America/Thule"                  "America/Thunder_Bay"           
[201] "America/Tijuana"                "America/Toronto"               
[203] "America/Tortola"                "America/Vancouver"             
[205] "America/Virgin"                 "America/Whitehorse"            
[207] "America/Winnipeg"               "America/Yakutat"               
[209] "America/Yellowknife"            "Antarctica/Casey"              
[211] "Antarctica/Davis"               "Antarctica/DumontDUrville"     
[213] "Antarctica/Macquarie"           "Antarctica/Mawson"             
[215] "Antarctica/McMurdo"             "Antarctica/Palmer"             
[217] "Antarctica/Rothera"             "Antarctica/Syowa"              
[219] "Antarctica/Troll"               "Antarctica/Vostok"             
[221] "Arctic/Longyearbyen"            "Asia/Aden"                     
[223] "Asia/Almaty"                    "Asia/Amman"                    
[225] "Asia/Anadyr"                    "Asia/Aqtau"                    
[227] "Asia/Aqtobe"                    "Asia/Ashgabat"                 
[229] "Asia/Atyrau"                    "Asia/Baghdad"                  
[231] "Asia/Bahrain"                   "Asia/Baku"                     
[233] "Asia/Bangkok"                   "Asia/Barnaul"                  
[235] "Asia/Beirut"                    "Asia/Bishkek"                  
[237] "Asia/Brunei"                    "Asia/Chita"                    
[239] "Asia/Choibalsan"                "Asia/Chongqing"                
[241] "Asia/Colombo"                   "Asia/Damascus"                 
[243] "Asia/Dhaka"                     "Asia/Dili"                     
[245] "Asia/Dubai"                     "Asia/Dushanbe"                 
[247] "Asia/Famagusta"                 "Asia/Gaza"                     
[249] "Asia/Harbin"                    "Asia/Hebron"                   
[251] "Asia/Ho_Chi_Minh"               "Asia/Hong_Kong"                
[253] "Asia/Hovd"                      "Asia/Irkutsk"                  
[255] "Asia/Istanbul"                  "Asia/Jakarta"                  
[257] "Asia/Jayapura"                  "Asia/Jerusalem"                
[259] "Asia/Kabul"                     "Asia/Kamchatka"                
[261] "Asia/Karachi"                   "Asia/Kashgar"                  
[263] "Asia/Kathmandu"                 "Asia/Khandyga"                 
[265] "Asia/Kolkata"                   "Asia/Krasnoyarsk"              
[267] "Asia/Kuala_Lumpur"              "Asia/Kuching"                  
[269] "Asia/Kuwait"                    "Asia/Macau"                    
[271] "Asia/Magadan"                   "Asia/Makassar"                 
[273] "Asia/Manila"                    "Asia/Muscat"                   
[275] "Asia/Nicosia"                   "Asia/Novokuznetsk"             
[277] "Asia/Novosibirsk"               "Asia/Omsk"                     
[279] "Asia/Oral"                      "Asia/Phnom_Penh"               
[281] "Asia/Pontianak"                 "Asia/Pyongyang"                
[283] "Asia/Qatar"                     "Asia/Qostanay"                 
[285] "Asia/Qyzylorda"                 "Asia/Riyadh"                   
[287] "Asia/Sakhalin"                  "Asia/Samarkand"                
[289] "Asia/Seoul"                     "Asia/Shanghai"                 
[291] "Asia/Singapore"                 "Asia/Srednekolymsk"            
[293] "Asia/Taipei"                    "Asia/Tashkent"                 
[295] "Asia/Tbilisi"                   "Asia/Tehran"                   
[297] "Asia/Tel_Aviv"                  "Asia/Thimphu"                  
[299] "Asia/Tokyo"                     "Asia/Tomsk"                    
[301] "Asia/Ulaanbaatar"               "Asia/Urumqi"                   
[303] "Asia/Ust-Nera"                  "Asia/Vientiane"                
[305] "Asia/Vladivostok"               "Asia/Yakutsk"                  
[307] "Asia/Yangon"                    "Asia/Yekaterinburg"            
[309] "Asia/Yerevan"                   "Atlantic/Azores"               
[311] "Atlantic/Bermuda"               "Atlantic/Canary"               
[313] "Atlantic/Cape_Verde"            "Atlantic/Faroe"                
[315] "Atlantic/Jan_Mayen"             "Atlantic/Madeira"              
[317] "Atlantic/Reykjavik"             "Atlantic/South_Georgia"        
[319] "Atlantic/St_Helena"             "Atlantic/Stanley"              
[321] "Australia/Adelaide"             "Australia/Brisbane"            
[323] "Australia/Broken_Hill"          "Australia/Canberra"            
[325] "Australia/Currie"               "Australia/Darwin"              
[327] "Australia/Eucla"                "Australia/Hobart"              
[329] "Australia/Lindeman"             "Australia/Lord_Howe"           
[331] "Australia/Melbourne"            "Australia/Perth"               
[333] "Australia/Sydney"               "Australia/Yancowinna"          
[335] "CET"                            "CST6CDT"                       
[337] "EET"                            "EST"                           
[339] "EST5EDT"                        "Etc/GMT"                       
[341] "Etc/GMT-0"                      "Etc/GMT-1"                     
[343] "Etc/GMT-10"                     "Etc/GMT-11"                    
[345] "Etc/GMT-12"                     "Etc/GMT-13"                    
[347] "Etc/GMT-14"                     "Etc/GMT-2"                     
[349] "Etc/GMT-3"                      "Etc/GMT-4"                     
[351] "Etc/GMT-5"                      "Etc/GMT-6"                     
[353] "Etc/GMT-7"                      "Etc/GMT-8"                     
[355] "Etc/GMT-9"                      "Etc/GMT+0"                     
[357] "Etc/GMT+1"                      "Etc/GMT+10"                    
[359] "Etc/GMT+11"                     "Etc/GMT+12"                    
[361] "Etc/GMT+2"                      "Etc/GMT+3"                     
[363] "Etc/GMT+4"                      "Etc/GMT+5"                     
[365] "Etc/GMT+6"                      "Etc/GMT+7"                     
[367] "Etc/GMT+8"                      "Etc/GMT+9"                     
[369] "Etc/GMT0"                       "Etc/Greenwich"                 
[371] "Etc/UCT"                        "Etc/Universal"                 
[373] "Etc/UTC"                        "Etc/Zulu"                      
[375] "Europe/Amsterdam"               "Europe/Andorra"                
[377] "Europe/Astrakhan"               "Europe/Athens"                 
[379] "Europe/Belfast"                 "Europe/Belgrade"               
[381] "Europe/Berlin"                  "Europe/Bratislava"             
[383] "Europe/Brussels"                "Europe/Bucharest"              
[385] "Europe/Budapest"                "Europe/Busingen"               
[387] "Europe/Chisinau"                "Europe/Copenhagen"             
[389] "Europe/Dublin"                  "Europe/Gibraltar"              
[391] "Europe/Guernsey"                "Europe/Helsinki"               
[393] "Europe/Isle_of_Man"             "Europe/Istanbul"               
[395] "Europe/Jersey"                  "Europe/Kaliningrad"            
[397] "Europe/Kirov"                   "Europe/Kyiv"                   
[399] "Europe/Lisbon"                  "Europe/Ljubljana"              
[401] "Europe/London"                  "Europe/Luxembourg"             
[403] "Europe/Madrid"                  "Europe/Malta"                  
[405] "Europe/Mariehamn"               "Europe/Minsk"                  
[407] "Europe/Monaco"                  "Europe/Moscow"                 
[409] "Europe/Nicosia"                 "Europe/Oslo"                   
[411] "Europe/Paris"                   "Europe/Podgorica"              
[413] "Europe/Prague"                  "Europe/Riga"                   
[415] "Europe/Rome"                    "Europe/Samara"                 
[417] "Europe/San_Marino"              "Europe/Sarajevo"               
[419] "Europe/Saratov"                 "Europe/Simferopol"             
[421] "Europe/Skopje"                  "Europe/Sofia"                  
[423] "Europe/Stockholm"               "Europe/Tallinn"                
[425] "Europe/Tirane"                  "Europe/Tiraspol"               
[427] "Europe/Ulyanovsk"               "Europe/Vaduz"                  
[429] "Europe/Vatican"                 "Europe/Vienna"                 
[431] "Europe/Vilnius"                 "Europe/Volgograd"              
[433] "Europe/Warsaw"                  "Europe/Zagreb"                 
[435] "Europe/Zurich"                  "Factory"                       
[437] "GMT"                            "HST"                           
[439] "Indian/Antananarivo"            "Indian/Chagos"                 
[441] "Indian/Christmas"               "Indian/Cocos"                  
[443] "Indian/Comoro"                  "Indian/Kerguelen"              
[445] "Indian/Mahe"                    "Indian/Maldives"               
[447] "Indian/Mauritius"               "Indian/Mayotte"                
[449] "Indian/Reunion"                 "MET"                           
[451] "MST"                            "MST7MDT"                       
[453] "Pacific/Apia"                   "Pacific/Auckland"              
[455] "Pacific/Bougainville"           "Pacific/Chatham"               
[457] "Pacific/Chuuk"                  "Pacific/Easter"                
[459] "Pacific/Efate"                  "Pacific/Fakaofo"               
[461] "Pacific/Fiji"                   "Pacific/Funafuti"              
[463] "Pacific/Galapagos"              "Pacific/Gambier"               
[465] "Pacific/Guadalcanal"            "Pacific/Guam"                  
[467] "Pacific/Honolulu"               "Pacific/Johnston"              
[469] "Pacific/Kanton"                 "Pacific/Kiritimati"            
[471] "Pacific/Kosrae"                 "Pacific/Kwajalein"             
[473] "Pacific/Majuro"                 "Pacific/Marquesas"             
[475] "Pacific/Midway"                 "Pacific/Nauru"                 
[477] "Pacific/Niue"                   "Pacific/Norfolk"               
[479] "Pacific/Noumea"                 "Pacific/Pago_Pago"             
[481] "Pacific/Palau"                  "Pacific/Pitcairn"              
[483] "Pacific/Pohnpei"                "Pacific/Port_Moresby"          
[485] "Pacific/Rarotonga"              "Pacific/Saipan"                
[487] "Pacific/Samoa"                  "Pacific/Tahiti"                
[489] "Pacific/Tarawa"                 "Pacific/Tongatapu"             
[491] "Pacific/Wake"                   "Pacific/Wallis"                
[493] "Pacific/Yap"                    "PST8PDT"                       
[495] "UTC"                            "WET"                           
attr(,"Version")
[1] "2024a"
dd %>% 
  mutate(dt = ymd_hms(text, tz = "America/Toronto")) -> dd
dd %>% mutate(zone = tz(dt))

Extracting time parts

  • As you would expect:
dd %>%
  select(-text) %>%
  mutate(
    h = hour(dt),
    sec = second(dt),
    min = minute(dt),
    zone = tz(dt)
  )
# A tibble: 4 × 5
  dt                      h   sec   min zone           
  <dttm>              <int> <dbl> <int> <chr>          
1 1970-01-01 07:50:01     7     1    50 America/Toronto
2 2007-09-04 15:30:00    15     0    30 America/Toronto
3 1940-04-15 06:45:10     6    10    45 America/Toronto
4 2016-02-10 12:26:40    12    40    26 America/Toronto

Same times, but different time zone:

dd %>%
  select(dt) %>%
  mutate(oz = with_tz(dt, "Australia/Sydney"))

In more detail:

dd %>%
  mutate(oz = with_tz(dt, "Australia/Sydney")) %>%
  pull(oz)
[1] "1970-01-01 22:50:01 AEST" "2007-09-05 05:30:00 AEST"
[3] "1940-04-15 21:45:10 AEST" "2016-02-11 04:26:40 AEDT"

How long between date-times?

  • We may need to calculate the time between two events. For example, these are the dates and times that some patients were admitted to and discharged from a hospital:
admit,discharge
1981-12-10 22:00:00,1982-01-03 14:00:00
2014-03-07 14:00:00,2014-03-08 09:30:00
2016-08-31 21:00:00,2016-09-02 17:00:00

Do they get read in as date-times?

  • These ought to get read in and converted to date-times:
my_url <- "http://ritsokiguess.site/datafiles/hospital.csv"
stays <- read_csv(my_url)
stays
  • and so it proves.

Subtracting the date-times

  • In the obvious way, this gets us an answer:
stays %>% mutate(stay = discharge - admit)
  • Number of hours; hard to interpret.

Days

  • Fractional number of days would be better:
stays %>% 
  mutate(
    stay_days = as.period(admit %--% discharge) / days(1))

Completed days

  • Pull out with day() etc, as for a date-time:
stays %>% 
  mutate(
    stay = as.period(admit %--% discharge),
    stay_days = day(stay),
    stay_hours = hour(stay)
    ) %>%
  select(starts_with("stay"))

Comments

  • Date-times are stored internally as seconds-since-something, so that subtracting two of them will give, internally, a number of seconds.
  • Just subtracting the date-times is displayed as a time (in units that R chooses for us).
  • Convert to fractional times via a “period”, then divide by days(1), months(1) etc.
  • These ideas useful for calculating time from a start point until an event happens (in this case, a patient being discharged from hospital).