Between in TimescaleDB

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

In this lesson document, we’ll explain the use of an operator called Between in TimescaleDB using SQL, including but not limited to:

  • What? What does the Between operator attain?
  • Why? In what circumstances will we benefit from using the “between” operator?
  • How? How do we use the “BETWEEN” operator in the SQL we write for TimescaleDB?

Prerequisites

  • Novice grasping of how to run the most basic of SQL statements against your TimescaleDB database, using Dbeaver or some other database administration tool, or by using code like Python or .Net in the database application that supplies a way to connect to your TimescaleDB data and query that data with SQL.
  • Novice level knowing of how the “WHERE” and “AND” clauses work in SQL statements.

The first thing to do is set up a database instance. We prefer using Objectrocket because we like their value and efficiency.

Create a TimescaleDB Database Instance on ObjectRocket

  • (1) Go to kb.objectrocket.com and “Log In”.
  • (2) Use left menu to “Create Instance”.
  • (3) In the “Name” field, add a descriptive name for your instance.
  • (4) Under “Select your service” choose the system you want to use from “CockroachDB, Elasticsearch, PostgreSQL, Redis, TimescaleDB, and MongoDB”.
  • (5) Choose the “Cloud Provider” and “Type” you want.
  • (6) Select “Version” and “Region”.
  • (7) Click the “GO TO STEP 2” button.
  • (8) Make a choice in the “Pick Your Flavor” section.
  • (9) Choose the “Capacity (Storage/Memory)”. Notice how this choice influences your total fee at the bottom right.
  • (10) Click the “GO TO STEP 3” button.
  • (11) Under “Add a Whitelist IP” pick “ALOW ANY IP” or “USE MY IP” or “Add my IPs later”.
  • (12) Choose between “Master” and “Replica” and click the “ADD” button.
  • (13) Now click the “CREATE INSTANCE” button at the bottom.

Prior to creating queries, let’s look at how to use Flask’s psycopg2 to connect to a TimescaleDB instance at ObjectRocket.

Connect to TimescaleDB

1
2
3
4
5
6
7
8
9
t_dbname = "myTSdb"
t_name_user = "tsdbadmin"
t_password = "secret"
t_sslmode = "require"
t_host = "ingress.hkybrhnz.launchpad.objectrocket.cloud"
t_port = "4129"
connection_string = "dbname=" & t_dbname & " user=" & t_name_user & " password=" & t_password & " host=" & t_host & " port=" & t_port & " sslmode=" & sslmode
db_conn = psycopg2.connect(connection_string)
db_cursor = db_conn.cursor()

What’s “BETWEEN”?

In TimescaleDB, BETWEEN is often used as piece of a WHERE clause in SQL; returning a true or false, depending on whether the values you use with the “BETWEEN” operator. We use this operator in order to limit or filter a rowset to return records that meet values found between x and y, as seen here:

Between Syntax

Now let’s back up and take a examine syntax:


GeSHi Error: GeSHi could not find the language code (using path /nas/content/live/orkbprod/wp-content/plugins/codecolorer/lib/geshi/) (code 2)

Between SQL in TimescaleDB

1
2
3
SELECT txtNameProduct
FROM tblProducts
WHERE curPrice BETWEEN x_value AND y_value

Curious about inclusion of values before and after the “between” operator? Keep reading.

Use Cases for “BETWEEN”

For this exercise, our task is to select a car that fits our budretrieve (and other) constraints from a table called “Products”. First, let us study all the rows in our “Products” table by executing the following SQL on our TimescaleDB database:

1
2
SELECT txtNameProduct, curPrice
FROM tblProducts

Which returns the following rows:

txtNameProductcurPrice
1962 VW Bug with infinite mi$400
2007 Ford Mustang with 120,832 mi$3381
Tesla 3 – RWD – Mid Range$42100
Tesla 3 – AWD – Long Range$53100
Tesla 3 – AWD – Performance$64100
Tesla S – P75 AWD$81100
Tesla S – P90 AWD$102100
Tesla S – P100 AWD$130000

Now we add a “WHERE” clause using the “BETWEEN” operator to modify our SELECT statement. We need to filter the records returned to show only the automobiles within our budretrieve range. The maximum price we need to pay is $4,000 but we don’t need to buy a part of junk. Can you guess what the new line of SQL will look like?

1
2
3
SELECT txtNameProduct, curPrice
FROM tblProducts
WHERE curPrice BETWEEN 1500 AND 3000

Execution of the above SQL gives us the following:

txtNameProductcurPrice
2007 Ford Mustang with 120,832 mi$3381

Do you know why the results dropped from eight automobiles returned down to one automobile returned?

At this stage, if you’re focusing on your TimescaleDB database and saying to yourself, “When I don’t need to measure purely by price because miles are important.” So let us put more data in our table and add a field (column) called “numMiles” to keep track of how many miles each car has on it.

Our new table with all rows looks like:

1
2
SELECT txtNameProduct, numMiles, curPrice
FROM tblProducts

Returns the following rowset:

txtNameProductnumMilescurPrice
1962 VW Bug400000$400
2006 Ford Mustang120832$3381
Tesla 3 – RWD – Mid Range0$42100
Tesla 3 – AWD – Long Range0$53100
Tesla 3 – AWD – Performance0$64100
Tesla S – P75 AWD0$81100
Tesla S – P90 AWD0$102100
Tesla S – P100 AWD0$130000

Now we can filter the “tblProducts” table by the “numMiles” AND “curPrice” columns to pull only the autos within our budget range AND that have the number of miles we need to stay under.

1
2
3
4
SELECT txtNameProduct, numMiles, curPrice
FROM tblProducts
WHERE curPrice =< 3000
AND numMiles BETWEEN 0 AND 150000

Some items of interest regarding the SQL command above:

  • We changed the filtering of “curPrice” to use “=<” operator instead of “BETWEEN” because we don’t have a lower limit on the price we’re looking for when shopping.
  • We have used “BETWEEN” to filter our rowset to show us records where miles are between 0 and 150,000 but this is not really necessary. We could have also used “=<” here, too.

So. Let us fix this by exposing more of the database structure:

1
2
SELECT txtNameProduct, numSeats, numMiles, curPrice
FROM tblProducts
txtNameProductnumSeatsnumMilescurPrice
1962 VW Bug3.5420050$400
2006 Ford Mustang4120651$3381
2009 Mazda Miata2119030$8000
2010 Mazda 3579000$9700
2009 Mazda 3 wagon684000$9840
2009 Ford Mustang4109000$9888
Tesla 3 – RWD – Mid Range50$42100
Tesla 3 – AWD – Long Range50$53100
Tesla 3 – AWD – Performance50$64100
Tesla S – P75 AWD70$81100
Tesla S – P90 AWD70$102100
Tesla S – P100 AWD70$130000

Now let’s assume we changed our purchase constraints to the following:

  • A budget of $11,000.
  • We want an automobile with less than 199,000 miles.
  • It is important that the auto is a specific size in terms of the seating we want. We’re searching for a back seat, which disqualifies 2-seat automobiles like the Mazda Miata. We don’t want a stationwagon, bus, cargo helicopter, or semi rig, so 5 is our seat upper limitation. Given those requirements, the Tesla S, though not being a wagon, does allow for two extra smaller seats in the furthest rear area so it is disqualified from this search. That said, Teslas are far above our price range. We are making the assumption that these cars are limited to no more than two people in the front seats and three in the back seats.

We will re-write the SQL statement to reflect the above-stated requires:

1
2
3
4
5
SELECT txtNameProduct, n_mpg, numMiles, curPrice
FROM tblProducts
WHERE curPrice =< 11000
AND numMiles =< 199000
AND numSeats BETWEEN 3 AND 5

This brings up an important question: Is the “BETWEEN” operator inclusive? In other words, does “BETWEEN 4 and 7” (we’ll think in terms here of only integers, to simplify) give us only 5 and 6 OR does it give us 4, 5, 6, and 7? Yes. It is inclusive in that way where “BETWEEN 4 and 5” returns 4 and 5. Let us see what our new SQL execution returns:

txtNameProductnumSeatsnumMilescurPrice
2006 Ford Mustang4120651$3381
2009 Mazda Miata2119030$8000
2010 Mazda 3579000$9700
2009 Ford Mustang4109000$9888

Another Use Case for “BETWEEN”

The situation: We work in a kindergarten class and need to retrieve a list of students who are equal to or older than 5 while being younger than or equal to 6. So yeah, 5 and 6 year olds. To start out, here’s a full table of students of all ages:

1
2
SELECT txtNameStudent, numAge
FROM tblUsers
txtNameStudentnumAge
Georgio4.5
Veronika5
Jimmie5.5
Tonica6
Jameson6.5
Kimmie7
Toby8

When we add our “BETWEEN” clause, we get:

1
2
3
SELECT txtNameStudent, numAge
FROM tblUsers
WHERE numAge BETWEEN 5 AND 6
txtNameStudentnumAge
Veronika5
Jimmie5.5
Tonica6

Notice how Georgio did not appear in our new returned rowset because he is 4.5 years of age, which is below our lower limit of age 5. Also, records for Jameson and Kimmie were not returned because their ages are above our upper limit of 6.

Conclusion

Here in this document we gained a better understanding of how and when to use the “BETWEEN” operator in TimescaleDB SQL. We also investigated two use cases where a person may need to utilize the “between” SQL operator. What are your thoughts on using “BETWEEN” to filter specific rows in your TimescaleDB database queries?

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.