Abs in TimescaleDB

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

Introduction

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

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 ABS?

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:

Image from Gyazo

Here is another perspective in an SQL statement of how the function works:

1
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:

WeekGrowthEst GrowthGrowth minus EstABS(Growth – Est)
1110130-2020
212712433
3111112-11
411511322
Total:-1626

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:

1
2
3
SELECT ID, intWeek, intGrowthActual, intGrowthEstimated, ABS(intGrowthActual - intGrowthEstimated)
FROM tblGrowthPlantsWeekly
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?

CarPerf RatingEst Perf Rating(Perf – Est) InaccuracyABS(Perf – Est) Inaccuracy
Tesla 3908555
Taycan8090-1010
Prius707000
Leaf6065-55
Total Inaccuracy:-1020

The above table of data can be pulled out of TimescaleDB using an SQL statement like:

1
2
3
SELECT ID, txtCarName, intPerformance, intPerfEst, abs(intPerfActual - intPerfEst)
FROM tblCars
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.

NameSalesReportedInaccurate Count (Act-Reported)Accurate Lies: ABS(Act-Reported)
Bob91000790001200012000
Tina970009700000
Fred9000091000-10001000
Tina8200092000-1000010000
Tot Lying100023000

The above represented data (absent the total) would be pulled out of TimescaleDB using an SQL statement like the you see here:

1
2
3
SELECT ID, txtName, curSales, curReported, curSales-curReported AS curInaccurate, ABS(curSalesActual - curSalesReported)
FROM tblEmployees
WHERE IDdept = 5

Use of Abs()

ABS Syntax

1
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:

InputReturnedType
55Integer
-55Integer
5.55.5Numeric
-5.55.5Numeric

TimescaleDB ABS Shortcut

The “@” operator functions the same as abs(). See in this next example:

1
SELECT @ -22

Returns 22

Conclusion

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

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.