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.
Show the launched timestamp of the aircraft and the same date truncated to day (as the launched_day column).
Use this expression:
DATE_TRUNC('day', launched_timestamp) AS launched_day
