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 find registration counts for the current year, month, quarter, etc. Unfortunately, the standard way to do this in SQL is quite cumbersome. Take a look:
EXTRACT(year FROM CURRENT_TIMESTAMP)
|| EXTRACT(month FROM CURRENT_TIMESTAMP)
) 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 don't usually need to use this expression in practice. Most databases come with built-in functions to compute the beginning of a given period. We'll look at those functions in the next exercise.