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:
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.
EXTRACT(time unit FROM date) function takes two parameters: a time unit (such as
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.