Fundamentals of Data Analysis Filtering records by time

16. 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:

FROM users
  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:

FROM users
  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! 🍾

Anatoli Makarevich, author of SQL Habit About SQL Habit

Hi, it’s Anatoli, the author of SQL Habit. 👋

SQL Habit is a course (or, as some of the students say, “business simulator”). It’s based on a story of a fictional startup called Bindle. You’ll play a role of their Data Analyst 📊 and solve real-life challenges from Business, Marketing, and Product Management.

SQL Habit course is made of bite-sized lessons (you’re looking at one atm) and exercises. They always have a real-life setting and detailed explanations. You can immediately apply everything you’ve learned at work. 🚀

“well worth the money”

Fluent in SQL in a month

Master Data Analysis with SQL with real life examples from Product Management, Marketing, Finance and more.
-- Type your query here, for example this one -- lists all records from users table: SELECT * FROM users
Loading chart... ⏳