sql uses what to extract records from a database

Go out/TIME Functions in SQL

Teacher on how to use CAST, EXTRACT, and DATE_TRUNC

What is time-serial data?

Working with dates and times is a common practice when working in SQL. Using dates, we seat calculate changes over time, trends in the data, perform interval arithmetic. To better see the impacts of the underlying business problem.

"time-serial data" As a chronological sequence of information points, measure the same matter over time, stored in time order.

Few common uses of time-serial publication data

Stock damage movement
Sensor tracking (e.g., weather tracking)
Rental services (e.g., bike operating room scooter rentals)

Let's Begin

For this teacher, I wish assume you have second-year SQL skills. We will be cover three functions and working with PostgreSQL syntax.

CAST
Educe
DATE_TRUNC

Each of these functions can be functional when breaking down datasets with a lot of data. We will construe the benefits of each one as we undergo the code with some examples.

CAST Function

A CAST work converts the selected data type into another. Pretty straight assuming. Information technology changes nonpareil type into your preferred type. The syntax is below.

            CAST(expression AS              datatype)

Below is an example of how it can be practical to date and time.

            SELECT
              NOW(),
              CAST(NOW() AS TIMESTAMP),
              CAST(NOW() Every bit Engagement),
              CAST(NOW() A TIME),
              CURRENT_DATE,
              CURRENT_TIME

Therein query, we expect to feature 6 outputs. If you are unacquainted Immediately(), CURRENT_DATE, CURRENT_TIME they are SQL functions that retrieve the current prison term or engagement. Downstairs are all the outputs from the query in order. (note — you will get different numbers pool since the functions call the exact prison term or date stamp)

2020–03–28 23:18:20.261879+00:00
2020–03–28 23:18:20.261879
2020–03–28
23:18:20.261879
2020–03–28
23:18:20.261879+00:00

Look the first result from the NOW() used on its own, we get a full timestamp value, including the timezone. Now as we go down to the second yield, we used CAST to only recall the TIMESTAMP, which does non include the timezone from the NOW(). Now we can see how cast kit and caboodle. We pass in a value we want to represent converted then Department of State the eccentric we want.

Next, we use CAST() on At present() simply pass in DATE atomic number 3 our desired character. We now cause a timestamp bare to solely the year/month/day format. Likewise, appear at the Contrive() function with NOW() and only TIME, and we get just the note value with no date.

We can see how the CAST function plant with time, and the hold up two outputs victimisation CURRENT_DATE and CURRENT_TIME are just there for you to see the comparing of results.

More Examples — Non with timestamps

SQL also allows CAST() functions to be used with types that are not timestamped.

            SELECT              
              CAST(1.34 AS INT),
              Tramp(1 AS BOOLEAN),
              CAST(2.65 AS DEC(3,0))

The results from this query are,

1 → Since an integer can't have decimals, it will round to the nearest integer value
true → 1 as a boolean is true, and 0 is false
3 → using the DEC() we can also do the reverse of our first integer CAST.

Intervals

In SQL, you crapper also usance INTERVAL to sum up more clock time to any timestamp you have. For the examples below, you don't demand to employ the Plaster cast() function, but I've chosen to do so only to have the date.

            SELECT
              CAST(Forthwith() AS See) Atomic number 3 TODAY_DATE,
              Regorge((INTERVAL              '3 DAYS' + NOW()) American Samoa DATE) Eastern Samoa three_days,
              CAST((INTERVAL              '3 WEEKS' + NOW()) Atomic number 3 Engagement) AS three_weeks,
              CAST((Separation              '3 MONTHS' + NOW()) AS DATE) AS three_months,
              Shake off((INTERVAL              '3 YEARS' + Right away()) AS DATE) AS three_years

We can date that exploitation the INTERVAL to boot to the length of the interval in days, weeks, months or years adds more clock to whatsoever date you experience — in this example the current date is obtained from NOW().

Distil()

Next, we can look at pulling a specific format out of a timestamp. The goal is to extract a portion out of a timestamp. For example, if we want just the month from the date 12/10/2018, we would set about December (12).

Rent's take a view EXTRACT sentence structure

            Distill(role FROM date)

We state the type of origin we want as part then the root to cost extracted date. EXTRACTis an import tool in clock serial data analysis. It helps you keep apart groups in your timestamps to aggregate data based on precise timings. For deterrent example, if a self-drive store wanted to find the busiest rental60 minutes along MONDAYS every English hawthorn, you can ut that with Draw out. You can drill down the inside information and take care more valuable insights.

Let's bear we run NOW() and our timestamp is 2020–03–29 00:27:51.677318+00:00, we can use Evoketo get the undermentioned.

            SELECT
              EXTRACT(MINUTE      FROM NOW()) American Samoa MINUTE,
              EXTRACT(HOUR        FROM NOW()) Atomic number 3 HOUR,
              Infusion(DAY         FROM NOW()) Atomic number 3 Twenty-four hour period,
              EXTRACT(WEEK        FROM Forthwith()) AS WEEK,
              Elicit(MONTH       FROM NOW()) AS MONTH,
              EXTRACT(Twelvemonth        FROM In real time()) AS YEAR,
              EXTRACT(DOW         FROM NOW()) AS DAY_OF_WEEK,
              EXTRACT(DOY         FROM NOW()) AS DAY_OF_YEAR,
              EXTRACT(QUARTER     FROM NOW()) AS QUARTER,
              EXTRACT(TIMEZONE    FROM NOW()) AS TIMEZONE

Output

We examine that we can go into great detail in how we want to evoke info out of our timestamps. Note — DOW — The day of the calendar week is Sunday(0) to Saturday(6).

We can physical body on the renting car example to begin with and see how it would forg.

            Pick out              
              EXTRACT(HOUR FROM RENTAL_DATE) American Samoa HOUR,              
              COUNT(*) as RENTALS              
FROM RENTAL              
WHERE
              Draw out(DOW   FROM RENTAL_DATE) = 1 AND
              EXTRACT(MONTH FROM RENTAL_DATE) = 5
Grouping BY 1
ORDER BY RENTALS DESC

Remember we want the busiest rentalMinute happening MONDAYS every Crataegus oxycantha . First, we use EXTRCT in the SELECT to state, we want only the 60 minutes and the total COUNT . We then pass in two EXTRACT functions for in the WHERE clause to percolate only MONDAYS and MAY. Looking at the table below 11 am is the most popular rental time every Monday in May with a total of 11 rentals.

DATE_TRUNC

Short — to shorten by or as if by thinning bump off

The purpose of truncating a date in SQL is to return an interval at an absolute precision. The precision values are a subset of the field identifiers that can be used with the EXTRACT. DATE_TRUNC will return an interval or timestamp rather than a number.

The syntax for DATE_TRUNC, time_column is the database column that contains the timestamp you'd like to round, and '[interval]' dictates your sought after preciseness level.

            DATE_TRUNC('[interval]', time_column)

Let's assume our NOW() returns the same 2020–03–29 00:27:51.677318+00:00, we posterior use date_part to get the following.

            Take
              CAST(DATE_TRUNC('Mean solar day', NOW()) AS DATE) AS DAY,
              CAST(DATE_TRUNC('WEEK', Immediately()) AS DATE) AS WEEK,
              CAST(DATE_TRUNC('MONTH', Nowadays()) AS DATE) AS Calendar month,
              CAST(DATE_TRUNC('YEAR', NOW()) AS Appointment) Equally YEAR

Think of using DATE_TRUNC as getting the interval of where information technology is currently, and each level of an time interval is how the see gets trimmed. Note — we did not need to use CAST in that example. I misused it to ensure the data formatting was clean for analysis.

We dismiss use DATE_TRUNC on the rental car scenario from earlier, and judge to find which day in the year regardless of time is Sir Thomas More common for rentals.

            SELECT
              CAST(DATE_TRUNC('DAY', RENTAL_DATE) Atomic number 3 DATE) AS RENTAL_DAY,
              Weigh(*) AS RENTALS
FROM RENTAL
GROUP BY
              RENTAL_DAY
ORDER Aside RENTALS DESC

Putting it All Together

At once that we have seen how to use CAST, EXTRACT, and DATE_TRUNC we potty bring together some of the techniques we knowledgeable into one practical example.

For this example, we volition add a new time function called AGE — which takes in 2 dates as an argument and outputs the "AGE" or the time in years and months between the dates.

Let's get through this teacher by extracting a list of customers who had the longest rental continuance ( AGE ) from 2019–05–01 + 30 years.

            SELECT
              CUSTOMER_ID,
              Pull out(DOW FROM RENTAL_DATE) AS DAY_OF_WEEK,
              AGE(RETURN_DATE, RENTAL_DATE) AS RENTAL_DAYS
FROM
              Material possession
WHERE              
              RENTAL_DATE              
              BETWEEN              Frame('2019-05-01' AS TIMESTAMP) AND
              CAST('2019-05-01' AS TIMESTAMP) +              INTERVAL              '30 DAY'
ORDER Aside 3 DESC

Final Outcomes

I hope you feel homely working with timestamps in SQL. You deliver learned to use CAST , EXTRACT, INTERVAL,DATE_TRUNC , and Get on. You should atomic number 4 comfortably equipped to undertake slew related analysis, and dive deeper into the data to bump insights you may not have been able to find out before!

If you are interested in more SQL tutorials, balk outgoing my early posts.

How to exercise CTEs in SQL
Introduction to Window Functions
Creating Tables in SQL

Connect with me on Linkedin or Github

sql uses what to extract records from a database

Source: https://towardsdatascience.com/date-time-functions-in-sql-1885e2cbdc1a

Erna Aldo