Autumn Offers - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Dealing with dates
Working with time
Date and time data types in PostgreSQL
Extracting dates and times
28. The DATE_TRUNC() function
Timezone conversion
Format date and time
Current date and time data
Summary

Instruction

Fantastic! Let's learn how to display the date truncated to the given part of the date and time. We'll need a new function:

DATE_TRUNC(field, source)

The source argument is timestamp or interval, and the field string argument indicates precision to truncate the input date or time. The field can contain these values: millisecond, second, minute, hour, day, week, month, quarter, year, etc.

SELECT
  produced_date,
  DATE_TRUNC('year', produced_date) AS truncated_produced_date
FROM aircraft
WHERE id = 1;

Result:

produced_date truncated_produced_date
2014-04-05 2014-01-01 00:00:00.0

Notice that produced_date is truncated to the first day of the year. Only the year comes from the date, the remaining parts are changed to ones or zeros, as appropriate.

In a similar way you can display the first day of the week for the given date. You need only use the week field in the DATE_TRUNC() function: it returns the Monday for the week in question. For example:

SELECT DATE_TRUNC('week','2019-01-01');

The query above returns '2018-12-31', because it is Monday, while '2019-01-01' is Tuesday.

Exercise

Show the launched timestamp of the aircraft and the same date truncated to day (as the launched_day column).

Stuck? Here's a hint!

Use this expression:

DATE_TRUNC('day', launched_timestamp) AS launched_day