Abs in TimescaleDB
In this document, we’ll investigate the use of abs in TimescaleDB from multiple angles, including:
- What? What’s abs and what does abs() do? This includes a high level view of the ABS function.
- Why? Why and when do we use this function?
- How? How do we make use of abs() in our TimescaleDB database usage? This includes syntax as well as examples of “business reality” uses.
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.
Next, it is important to see how to connect to the TimescaleDB instance you created. Here’s we’ll use Python.
Connect to TimescaleDB
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()
TimescaleDB’s abs() returns the absolute value for a number. The returned value is how distant from zero our number is, no matter what the positive/negative sign is. Another way to put it: ABS does nothing to a positive number and removes the negative sign from a negative number.
Here is an image of using the formula Y = ABS(X) to convert both negative x values and positive x values into positives for Y, which, draws both lines on the top side (positive) of our 2D graph:
Here is another perspective in an SQL statement of how the function works:
SELECT ABS(i_Yaxis) FROM myDataTable
The above SQL command will return the following results: | i_Yaxis | Returned | |——–:|———:| | abs(12) | 12 | | abs(6) | 6 | | abs(0) | 0 | | abs(-6) | 6 | |abs(-12) | 12 |
Now that we have a high level (bird’s eye) understanding of the abs function, let’s go a bit deeper and talk about some use cases that might appear in a real business.
Why Use the ABS Function?
ABS Use Case
A potential use is when we’re comparing numbers of estimated plant growth vs. actual plant growth over time where we don’t necessarily understand if subtracting estimated growth from actual plant growth will give us a positive or negative number during any given week. With the comparison below, we need to understand how inaccurate or mismatched the estimates were, so we can give huge quantities of raw kale to our favorite botanist. Here is an example:
|Week||Growth||Est Growth||Growth minus Est||ABS(Growth – Est)|
Note how the column not using the ABS function, “Growth minus Est”, has an error in its total of -16. 26 is the correct total disparity between actual and estimated growth.
That data above (minus total) can be pulled from TimescaleDB with an SQL statement like:
SELECT ID, intWeek, intGrowthActual, intGrowthEstimated, ABS(intGrowthActual - intGrowthEstimated)
ORDER BY intWeek
Use Case: Calculate Accuracy
What if we need to calculate the total accuracy or inaccuracy of the performance estimates of a car industry journalist so we can decide whether to give him a raise or fire him?
|Car||Perf Rating||Est Perf Rating||(Perf – Est) Inaccuracy||ABS(Perf – Est) Inaccuracy|
The above table of data can be pulled out of TimescaleDB using an SQL statement like:
SELECT ID, txtCarName, intPerformance, intPerfEst, abs(intPerfActual - intPerfEst)
WHERE t_car_type = 'EV'
Of course, the above table would have a column called something like “txtCarType” to distinguish electric (EV) vehicles from internal combustion engines (ICE).
Use Case: Count False
At SellingSnowToCanadians.net, the CEO wants to have a report on all sales personnel, ranked by who stretches the truth most regarding their sales figures. In addition, he wants to see a total of just how much capitol is being misrepresented in their department. A byproduct he may gain from this type of report is that he will accomplish valuable clues about who may be stealing from the company.
|Name||Sales||Reported||Inaccurate Count (Act-Reported)||Accurate Lies: ABS(Act-Reported)|
The above represented data (absent the total) would be pulled out of TimescaleDB using an SQL statement like the you see here:
SELECT ID, txtName, curSales, curReported, curSales-curReported AS curInaccurate, ABS(curSalesActual - curSalesReported)
WHERE IDdept = 5
Use of Abs()
new_number = ABS(a_number)
The “a_number” can be a field or value that TimescaleDB evaluates as a number. Oh and in terms of data type, the data type you enter determines the data type returned from TimescaleDB. For example:
TimescaleDB ABS Shortcut
The “@” operator functions the same as abs(). See in this next example:
SELECT @ -22
In this document your knowledge grew about how to use ABS in TimescaleDB. We also investigated some use cases of why and when you may wish to use TimescaleDB’s ABS() function. Hopefully, you fully understood the pattern in the above use cases of removing negative results from the comparisons so that we could calculate accurate readings of totals. Do you see how – especially in statistical analysis scenarios with TimescaleDB – ABS can be quite useful.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started