Introduction
Dates
Time
Timestamps
Extract functions
Timezone conversion
Intervals
29. INTERVAL YEAR TO MONTH
Current date and time
Revision

Instruction

The results we got are intervals: 1307 days 23:16:00 tells us that the difference between the two timestamps is 1307 days, 23 hours and 16 minutes. This is the result we got in our database (PostgreSQL), but be aware that other databases may return something else (like the number of milliseconds between these two dates, for instance).

The standard of SQL provides two types of intervals. One of them is INTERVAL 'x-y' YEAR TO MONTH, where x is the number of years and y is the number of months. You can add such interval to a date/timestamp:

SELECT id,
  launched + INTERVAL '1-2' YEAR TO MONTH
FROM aircraft;

The above query will add 1 year and 2 months to each launched timestamp in the table aircraft.

Between popular databases, only MySQL does not support the syntax for INTERVAL YEAR TO MONTH.

Exercise

PerfectAir decided to use the withdrawn aircraft (id = 5) once again. Show its id, its original withdrawn date and the withdrawn date postponed by 1 year and 6 months.

Stuck? Here's a hint!

Use INTERVAL '1-6' YEAR TO MONTH.

Console

Code editor

Result

TableConsole