End of Summer - hours only!Up to 80% off on all courses and bundles.-Close
Customer cohorts


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 DATE_TRUNC():
    SELECT DATE_TRUNC('month', CURRENT_TIMESTAMP) AS current_month_start;
    The DATE_TRUNC() function has two arguments:
    • the text defining precision, e.g., 'minute', 'hour', 'day', 'month', 'quarter', or 'year'.
    • the date that is to be rounded down with a precision defined in the first argument.
    The 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 2018-00-00 00:00:00.0.
  • 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 date.
  • 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.


In a column named current_year_start, show the beginning of the current year using the function DATE_TRUNC().

Stuck? Here's a hint!

Use the code from the explanation. Change 'month' to 'year'.