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
Intervals
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