Good job! The standard way to compute the beginning of the current period is quite cumbersome. Fortunately, most databases come with a built-in function to compute it.
- In PostgreSQL (this is the database we're using in this course), there is a function named
SELECT DATE_TRUNC('month', CURRENT_TIMESTAMP) AS current_month_start; The
DATE_TRUNC() function has two arguments:
- the text defining precision, e.g.,
- the date that is to be rounded down with a precision defined in the first argument.
DATE_TRUNC() function returns a timestamp with unwanted details removed, e.g., the result of:
SELECT DATE_TRUNC('day', CAST('2019-09-09 12:31:08.5' AS timestamp)) is
2019-09-09 00:00:00.0 and the result of:
SELECT DATE_TRUNC('year', CAST('2018-07-10' AS timestamp)) is
- Oracle has the function
TRUNC() which works similar to the PostgreSQL version.
- In MySQL you can use the
DATE_FORMAT() function like this:
DATE_FORMAT(date, "%Y-%m-01"). This code returns the beginning of the month of the
- Check out the documentation of your database to find out what functions it offers.
Since this course uses PostgreSQL, we'll use the PostgreSQL syntax in this section. Just remember that you can replace PostgreSQL's
DATE_TRUNC() with whatever expression your database documentation suggests.