While temporality creates interesting study design challenges, it also comes with interesting technical nuances and challenges as well. In this video, we will cover some of the details about how dates and times are stored in Google BigQuery and R. First, let's look at SQL. There are four common types of date classes that you will see for dates and times in Google BigQuery. The first is date, which is just a date in the format of a four digit year, one or two digit month, and one or two digit day separated by dashes. The second is time, which is just the time within the day of the format of one or two digit hour, one or two digit minutes, and one or two digit seconds separated by colons. There's also optionally support for a microsecond precision separated by a period. The third are date times, which contain both a date and a time in the same format as the previous components, but now the date and time are separated either by a space or the letter T. The fourth type timestamp looks just like date time, but it also has a timezone formatted either as the offset from coordinated universal time or UTC or a timezone abbreviation. Although date time and timestamp look the same, they actually mean two very different things. Timestamp represents an absolute point in time, regardless of timezone or daylight savings time. Timestamp is actually stored in the system as the number of seconds before or after January 1 1970, and zero hours, zero minutes, and zero seconds UTC. This is called the Unix epoch, and is a common way of handling date times and electronic systems. In R there are a number of different data types for date times as well. The most similar to BigQuery's timestamp is the POSIXct format that also stores times the number of seconds since the epoch. Whereas the POSIXlt format is more similar to date time and just keeps the relative components of dates. R does have a date type that acts just like the BigQuery date. However, R does not have any option for a time without an associated date. So this is the point in the video where you should gain kind of a confused, frustrated, or annoyed look on your face thinking, but why are there so many options? Believe me, we are just scratching the surface of the programming pain that comes from working with dates and times programmatically. In R, there's a handy package called lubridate that tries to make working with dates and times a bit easier, but I'll be honest, it's still kind of a mess. First though, I want to introduce you to some of the vocabulary used by the lubridate package. The first word you should know is instant. Think of this like a timestamp or POSIXct format. This is an exact moment in time that you can identify regardless of your location's timezone or current daylight saving status. Duration then represents exact lengths of time in seconds. So the duration of a day function or D-day gives you the number of seconds in a specified number of days. A D-day of one would return 86,400 seconds. A week duration or D-week would return 604,800 seconds. And a D-year duration would return 31,536,000 seconds which corresponds to 365 days. Periods on the other hand, represent the change in clock time that occurs between two instance. These functions let you manipulate date time objects with functions like days, weeks, months, and years, which sounds a lot like durations. Why do we have two sets of functions for this? Let's look at an example. If I checked the date January 1 of 2020, and I wanted to know the date a year from then, I could store the date and add D years one. And R would return December 31 of 2020, what's up with that? Well, it turns out that 2020 is a leap year, and that has 366 days. Well, D-year is strictly the number of seconds in 365 days. If we were to instead take the same date and add the period function years one, we would get the expected January 1 2021. In other words, periods in lubridate provide predictions of clock times, taking into account things like leap years, leap seconds, and changes in daylight savings time. More often than not, when you think about date time manipulations, you probably are expecting period like calculations rather than duration based methods. This is just the briefest of taste of working with dates and times in BigQuery and R. There are a number of different functions that you can use for manipulating, processing, and comparing date time objects, each with the same level of nuance and frustration. In other words, this definitely belongs in the advanced clinical data science section.
