18. Filtering records by time

So far we’ve been filtering records with equals = operator. If a value in the column matches the value in our filter we’d see this record in the result:

SELECT COUNT(*)
FROM users
WHERE
  country = 'us'

This query will tell us how many signups do we have from the US in total. But what if we want to know how many signups did we have in the last 30 days, last year or June 2018? As the title of this lesson suggests – we’ll filter records by time.

Comparing dates in SQL

In SQL, as in many other programming languages dates behind the scenes are just numbers. When we need to compare them we can use:

• greater than operator >
• less than operator <
• greater than or equal operator >=
• less than or equal operator <=

Let’s examine the query that counts users who signed up after 1, Jan 2018:

SELECT COUNT(*)
FROM users
WHERE
  signup_date > '2018-01-01'

Doesn’t look scary, right? We’re using a date string '2018-01-01' and the greater than comparison operator to filter records by signup_date column. Notice that the syntax is very similar to the previous query: we’re just using a different comparison operator (> instead of =).

You may be wondering, how does SQL figure out how to compare a string value '2018-01-01' and the actual dates inside the signup_date column? Remember that each column has a specific type? (we define column types when creating a table). In our case, the signup_date column has date type, which helps SQL perform an accurate comparison.

Comparison operators

Today’s SQL milestone: you’ve learned equals =, greater than > and less than < operators. Now you can filter records by numbers (“All users older 18” or “All customers who paid more than 20$”) and by dates (“Signups after 2019” or “Books published after January 1, 2018”). Congratz!