Defined with examples
Contemplating the large quantity of structured knowledge saved in relational databases, knowledge scientist and analysts are more likely to work together with a relational database virtually each day. SQL is what we use to make these interactions, which make it one of the crucial in-demand expertise in knowledge science.
There are numerous completely different relational database administration programs (RDBMS) that use SQL to work together with and handle knowledge in relational databases. One of many in style RDBMSs is PostgreSQL, which is open-source and has a robust popularity for reliability, function robustness, and efficiency.
On this article, we are going to study 2 PostgreSQL features, which simplify complicated operations and turn out to be useful in lots of circumstances. I name them as life-saver features.
This operate can be utilized for creating ordered tables with numbers and dates. A typical use case with dates is when you’ve got a beginning and ending dates for an occasion and must develop it as a calendar.
Let’s go over an instance. We’ve a reserving desk that accommodates the bookings at a resort. It consists of the reserving id, check-in and check-out dates. We need to develop it in order that will probably be simpler to calculate the occupancy.
SELECT
id,
generate_series(
checkIn,
checkOut,
INTERVAL '1 day'
) AS booked
FROM reserving
The question above will embody the try date which is definitely not thought to be booked. Thus, with a view to mark booked days precisely, we have to subtract someday from the try date earlier than producing the sequence.
Right here is how this may be achieved:
SELECT
id,
generate_series(
checkIn,
(checkOut - INTERVAL '1 day'),
INTERVAL '1 day'
) AS booked
FROM reserving
What this code does is illustrated beneath:
We will be part of the desk on the left to a calendar desk and that’s it! We’ve a calendar with booked days marked.
The to_char operate can be utilized for changing timestamps, intervals, or a numeric values to formatted strings. I discover it probably the most helpful when extracting a chunk of data from timestamps or dates.
Its syntax is as follows:
TO_CHAR(expression, format)
We will use it to extract month identify and month abbreviation utilizing the ‘MONTH’ and ‘MON’ codecs, respectively. Think about now we have a reserving desk which has a date column referred to as booked. We will use the to_char operate to extract these items as follows:
choose
booked,
to_char(booked, 'MONTH') AS month,
to_char(booked, 'MON') AS mon,
from reserving
We will additionally get the 12 months month data as a string:
choose
booked,
to_char(booked, 'YYYY-MM') AS yearmonth
from reserving
If the date is 2022–10–08, then 12 months month worth will probably be 2022–10.
There are additionally helpful codecs associated to days. As an illustration, we will extract the day identify and quantity.
choose
booked,
to_char(booked, 'DY') AS day_name,
to_char(booked, 'DD') AS day,
from reserving
There are different codecs that may be helpful for various duties. You may test the official documentation for different codecs supported by the to_char operate.
We’ve coated two necessary features of PostgreSQL. They’re primarily utilized in knowledge preprocessing, or making the uncooked knowledge higher match the duties you’ll do afterwards. These features really present that SQL is not only a question language however a extremely environment friendly knowledge evaluation and manipulation instrument as nicely.
You may turn into a Medium member to unlock full entry to my writing, plus the remainder of Medium. When you already are, don’t neglect to subscribe when you’d wish to get an electronic mail at any time when I publish a brand new article.
Thanks for studying. Please let me know in case you have any suggestions.
