Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Common Functions in PostgreSQL

Introduction
Dealing with dates
Working with time
Date and time data types in PostgreSQL
Extracting dates and times
29. The DATE_TRUNC() function – continued
Timezone conversion
Format date and time
Current date and time data
Summary

Instruction

Great! The DATE_TRUNC() function comes in handy when calculating yearly, monthly, weekly, and similar aggregates. Look at the query below:

SELECT
  DATE_TRUNC('month', produced_date) AS trunc_produced_date,
  COUNT(id) AS aircraft
FROM aircraft
GROUP BY trunc_produced_date;

This query displays produced_date truncated to year and month, and the number of aircraft produced during this time. For example, in the output there are two aircraft produced in March 2010.

Exercise

Let's find out how many aircraft were withdrawn in each week. In the first column show withdrawn_timestamp truncated to week (name the column week), and in the second show the count as withdrawn_count. Show this summary only for withdrawn aircraft.

Stuck? Here's a hint!

Use this expression:

DATE_TRUNC('week', withdrawn_timestamp) AS week