Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Get to know the database
Basic revenue metrics
Summary

Instruction

Very well done! Now, we'll learn how to calculate the beginning of the current period (e.g. the current day, month, year, etc.). This will help us create revenue reports for the current year, month, quarter, etc. Unfortunately, the standard way to it in SQL is quite cumbersome. Take a look:

SELECT CAST(
  EXTRACT(year FROM CURRENT_TIMESTAMP) || '-'
  || EXTRACT(month FROM CURRENT_TIMESTAMP)
  || '-01' AS DATE) AS current_month_start;

There's quite a lot happening here:

  • The expression CURRENT_TIMESTAMP returns current date and time.
  • The EXTRACT(time_unit FROM date) function takes two parameters: a time unit (such as year, quarter, month, etc.) and a date or time column. As the name suggests, the function returns the specified part of a given date. In our example EXTRACT(year FROM CURRENT_TIMESTAMP) returns current year as a number.
  • We create the string representation of the first day of the month with the help of concatenation operator ||.
  • Finally, we cast the string to DATE data type.

Fortunately, you usually don't need to use this expression in practice. Most databases come with built-in function to compute the beginning of a given period. We'll look at those functions in the next exercise.

Exercise

In a column named current_year_start, show the beginning of the current year.

Stuck? Here's a hint!

Use the code form the example. Replace expression '-' || EXTRACT(month FROM current_timestamp) with '-01'.