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

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 a 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))
  • 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 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.

Exercise

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'.