---
title: "Temporal filtering"
author: "Callum Waite & Shandiya Balasubramaniam"
date: '2023-10-13'
output:
  rmarkdown::html_vignette
vignette: >
  %\VignetteIndexEntry{Temporal filtering}
  %\VignetteEngine{knitr::rmarkdown}
  %\VignetteEncoding{UTF-8}
---



Queries to the ALA will almost always require some form of temporal filtering. It is important to know how these types of data are stored in the ALA and how we can query them to obtain desired filters.

The ALA database possesses numerous date and time fields that relate to each observation. Here we provide descriptions of each of these fields and how they are best used to obtain specific queries. Ultimately, there are two ways users can filter temporal queries:

-   filter using pre-existing/defined parameters, such as specific years or months

-   filter within a bespoke date and/or time range

All temporal filtering is conducted using `galah_filter()`. All temporal fields described below can be queried for exact matches (`==`), greater/less than (`>`, `<`) or greater/less than or equal to (`<=`, `>=`). Queries for multiple fields or multiple queries of the same field can be combined in a single `galah_filter()` call to filter a time window.

### Year, Month and Day

The ALA contains in-built `year`, `month`, and `day` fields for every record. These are queried as numeric fields (i.e. July = `7`) and can be used for quick data exploration and filtering. These fields are most useful when the date limits of a query can be easily defined by year, month and/or day.

For instance, we can get monthly counts of amphibians from 2021 using the `year` and `month` fields.



``` r
library(galah)
library(tidyverse)
library(gt)
library(lubridate)
```

``` r
galah_config(email = "your_email_here", verbose = FALSE)
```

``` r
galah_call() |>
  filter(class == "Amphibia", year == 2021) |>
  group_by(month) |>
  count() |>
  collect()
```

```
## # A tibble: 12 × 2
##    month count
##    <chr> <int>
##  1 11    83810
##  2 10    38208
##  3 12    36578
##  4 9     27616
##  5 1     22352
##  6 8     18758
##  7 3     17964
##  8 2     16550
##  9 7      8945
## 10 4      7769
## 11 6      6961
## 12 5      5996
```

It is also important to observe that the outputted `month` column is of type 
`character` even though the values are numeric. This is the case for each of the 
`year`, `month` and `day` fields. However, they can be queried as either numeric 
or character values within `filter()`.

One limitation of using these fields for queries with `filter()` is their 
independence; they cannot be used to query within windows bounded by two dates 
because the day and month filters are applied universally. For instance, consider 
the native perennial Australian wildflower *Chamaescilla corymbosa*, 
whose known growth and flowering times are from August–October. We might be 
interested in the number of records for this species in the first week of spring 
(i.e. September) in each of the last 10 years. The following query does not 
provide all results between 1/9/2013 and 7/9/2023. Rather, it will only return 
results that fall within all 3 windows at once.


``` r
galah_call() |>
  filter(species == "Chamaescilla corymbosa",
         year >= 2013, 
         year <= 2023, 
         month == 9, 
         day >= 1, 
         day <= 7) |>
  group_by(year) |>
  count() |>
  collect() |>
  arrange(year)
```

```
## # A tibble: 11 × 2
##    year  count
##    <chr> <int>
##  1 2013      8
##  2 2014     13
##  3 2015      8
##  4 2016      7
##  5 2017      1
##  6 2018      9
##  7 2019      6
##  8 2020     20
##  9 2021     36
## 10 2022     26
## 11 2023     54
```

### Occurrence dates

For a more bespoke way to query exact dates of records, users can use the 
`eventDate` field. This field contains the exact date and time information of 
records and enables specific time windows to be queried easily. The only caveat 
is that the time/date must be provided in a specific format to `filter()` for 
the query to work. 

The required format of dates in `eventDate` is the 
[ISO 8601 International Date Standard](https://en.wikipedia.org/wiki/ISO_8601) 
format. This requires dates and times to be of the form "YYYY-MM-DDTHH:MM:SSZ". 
Note that the `T` in the middle should be the actual letter "T" to delimit the 
date and time components, while the "Z" officially denotes that the time should 
be queried as UTC (Greenwich Meridian) time. Timezones can be confusing at the 
best of times, however it is easiest to remember that all ALA records are 
recorded at the local time of their location, and all times are then treated as 
effectively being UTC times.

The upshot of this specific formatting is that, for instance, the time I am 
writing this paragraph, 4:26pm on the 2nd of August 2023, would be represented 
as `"2023-08-02T16:26:44Z"` in the ALA, even though officially my timezone is 
`"+0930"`.

Because `eventDate` specifies the time to seconds, it is recommended that 
greater or less than queries are used rather than exact matches. When used with 
`filter()`, we can easily identify how many records of the humpback whale 
(*Megaptera novaeangliae*) have occurred since the species was removed from the 
Australian threatened species list on 26/02/2022.


``` r
galah_call() |>
  filter(species == "Megaptera novaeangliae", 
         eventDate >= "2022-02-26T00:00:00Z") |>
  count() |>
  collect()
```

```
## # A tibble: 1 × 1
##   count
##   <int>
## 1  1240
```

It can be unintuitive to provide dates in this format. Luckily, it is very 
simple to convert standard R dates or {lubridate} dates into this format because 
they are already in the required "YYYY-MM-DD" form. If we took the above date 
(26/02/2022), it could be converted to this form using base R or lubridate as 
follows:


``` r
humpback_date <- "26/02/2022"
# Base R
paste0(as.Date(humpback_date, format = "%d/%m/%Y"), "T00:00:00Z")
```

```
## [1] "2022-02-26T00:00:00Z"
```

``` r
# lubridate
paste0(dmy(humpback_date), "T00:00:00Z")
```

```
## [1] "2022-02-26T00:00:00Z"
```
After sending a query, any outputted `eventDate` values returned by a `galah` 
query will be of date class `"POSIXct"`.

### Upload dates

The other important date field present in the ALA pertains to the date that the 
record was provided to the ALA. This field is called `firstLoadedDate` and is 
formatted in exactly the same manner as `eventDate`.

Different data providers provide batches of records to the ALA at different 
intervals. iNaturalist Australia provide weekly uploads of data, while eBird 
provides yearly uploads. `firstLoadedDate` can be especially useful for finding 
new records to the ALA that have been provided since you last checked. For 
instance, we can use it to see how many observations of Sulphur-Crested 
Cockatoos recorded in the first week of 2023 were actually loaded into the ALA 
by the following week:


``` r
# Total records of Cactua galerita in Jan 1-7
galah_call() |>
  filter(species == "Cacatua galerita",
         eventDate >= "2023-01-07T00:00:00Z", 
         eventDate < "2023-01-08T00:00:00Z") |>
  count() |>
  collect()
```

```
## # A tibble: 1 × 1
##   count
##   <int>
## 1   407
```


``` r
# Records of Cactua galerita uploaded in Jan 1-14
galah_call() |>
  filter(species == "Cacatua galerita",
         eventDate >= "2023-01-07T00:00:00Z", 
         eventDate < "2023-01-08T00:00:00Z",
         firstLoadedDate < "2023-01-15T00:00:00Z") |>
  count() |>
  collect()
```

```
## # A tibble: 1 × 1
##   count
##   <int>
## 1     5
```

Note that no lower bound is required for `firstLoadedDate` because `eventDate` 
imposes that by proxy (records can't be uploaded before they've occurred).