This article is part of a series.
- Part 1 - HMIS, R, and SQL -- Introduction
- Part 2 - HMIS, R, and SQL -- Basics
- Part 3 - Preamble to Mixing R and SQL
- Part 4 - HMIS, R, SQL -- Work Challenge One
- Part 5 - Read and Write CSVs in R
- Part 6 - Working with Dates in SQLdf
- Part 7 - Filter to Most Recent HUD Assessment
- Part 8 - HMIS, R, SQL -- Work Challenge Two
- Part 9 - This Article
- Part 10 - Working with R Excel Libraries
- Part 11 - HMIS, R, SQL -- Work Challenge Three
- Part 12 - HMIS, R, SQL -- Work Challenge Four
- Part 13 - SQL CASE and R Paste
Comparing two or more values is an extremely important concept when talking to computers. In writing a report script, it is is essential. Comparisons allow us to filter to values within a range, allowing us to provide a report of relevant information.
Take the following data:
If you run the above in R you should get a dataframe called
peopleDf which looks like this:
It’s a simple table. But let’s say we wanted to get a list of everyone born before 2000-01-01. Of course, we can easily see Timmy is the only person born after 2000. But if our table was thousands of records it wouldn’t be possible to quickly assess.
Luckily, this is pretty straight forward in SQL-R. We will use a
less than operator (<). You probably remember this sign from high-school while solving inequalities. However, we will be using it as what’s known as a relational operator.
In short, it states,
Is x less than y
x is less than y the computer is going to say the statement is true (or 1 in binary). If it is not, then the computer will say it’s false (or 0 in binary). Believe it or not, this simple operation is why you have a device in your pocket which could calculate the internal mass of the sun.
For us, things are a little simpler. We just want to know who was born before 2000. Let’s re-write the statement above with our problem:
Is Sarah’s DOB less than 2000-01-01
Well, what is Sarah’s DOB? 1992-04-01. Let’s rewrite and assess (gah, this feels like high-school algebra again).
Is 1992-04-01 less than 2000-01-01
Hmm. This can get confusing for humans, but more importantly, confusing to computers.
In English, we’d probably state this as,
Did 1992-04-01 come before 2001-01-01?
Essentially, that’s what we are doing. Just know, the computer will translate all dates into a number. This number is how many seconds transpired since 1970-01-01.
Why? On Thursday, January 1st 1970 the Universal Coordinated Time (UTC) was established. Think of it is when the world came together to standardize time. Computer people figured, “Well, if we have to convert dates into a raw number for computers to understand it, it might as well be the number of seconds since UTC was established.”
Ok, enough history lesson. How is this relevant?
- Computers convert dates into seconds since 1970-01-01.
- Comparing dates is actually comparing numbers.
Taking our statement again, let’s re-write it with the number of seconds since
Is number of seconds between 1970-01-01 and 1992-04-01 less than number of seconds between 1970-01-01 and 2000-01-01
Is 702,086,400 less than 46,684,800 seconds
Aha, now this makes sense. And the result is
true. We can now say, in computer speak: Sarah was born before
Why? Really, dude.
In my world there is a saying: RFTM.
It’s hard to follow now days. Everything moves quick and we don’t have time to dig into the “Why.” But, like most things, if you want to be good, you must take the time to do so.
The reason we review how computers understand dates is it directly impacts how we write reports. Do you remember the date conversion trick to get dates to work in SQL from R? This is because R holds dates as the number of seconds since 1970 and passes it as a string to SQL. But, then SQL tries to convert the date from a date into seconds again, screwing everything up.
It pays to RFTM.
Filtering Dataframes by Date
Back to the problem. How do we write a script which provides a dataframe of people born before 2000-01-01?
The code is actually pretty simple,
This should provide a
nonMillennialsDf dataframe, which contains:
And there we go, for all my nerdsplaining the code’s pretty simple, right?
Well, there are a few gotchas. Notice the date we’ve written. It has the following format
YYYY-MM-DD and is surrounded by single quotes. Any time you use dates in SQL they must be written in this format.
Another tricky part is trying to find if a date falls between two dates. Let’s take the
peopleDf and write a query which provides everyone who was born between
Here’s the query.
This should result in a table with only Fela:
It is important to understand, the first comparison removed Sarah, as
1992-04-01 is less than
1998-01-01. Then, the second comparison got rid of Timmy as
2010-01-01 is greater than
There is one more critical command in writing robust date comparisons. The
NOW() function. This function is different in R and SQL, but pretty much every programming language has a version of the function.
NOW() asks the computer what today’s date is when the script runs.
In SQL-R it looks like this:
This should provide:
And it doesn’t matter when this script is run, it will always insert today’s date in the
TodaysDate column. Nifty, right? Trust me, if you don’t see the possibilities yet, give it time. It’ll grow into one of your favorite functions.
Well, we can’t talk about the
NOW() function without discussing the
DATE() function I slipped in there. What does it do?
As we discussed earlier, the computer looks at dates as the number of seconds since
1970-01-01. When you use the
NOW() function by itself then it will return the number of seconds–um, not something humans like to read. The
DATE() function says, “Take whatever is inside the parentheses and try to convert it into a human readable date.” Voila! A human readable date.
Let’s get fancy. We can use the
NOW() function and our
peopleDf to calculate everyone’s age.
This should provide:
Cool, right? Now, it does not matter when this above code of line is run, it will calculate everyone’s age correctly.
One important note, if the date and time are wrong on your computer this calculation will be incorrect.
The nerd-judo which can be done with dates in SQL-R is endless. But this covers a lot of the basics.
If you’ve missed the code bits throughout this article, here it is all at once: