Introduction
Dates
Time
Timestamps
Extract functions
Timezone conversion
Intervals
31. INTERVAL 'x' FIELD
Current date and time
Revision

Instruction

Okay!There is one more syntax for intervals. It is not in SQL Standard, but most databases support it. The syntax is as follows:

  • INTERVAL '2' HOUR
  • INTERVAL '3' DAY
  • INTERVAL '5' MONTH
  • INTERVAL '1' YEAR
etc.

In MySQL you use the syntax without the apostrophes: INTERVAL 2 HOUR, INTERVAL 3 DAY, etc.

As usual, you can add such intervals to a timestamp/date (or subtract it):

SELECT id,
  launched + INTERVAL '3' MONTH
FROM aircraft;

The above query will add 3 months to the timestamp stored in the column launched.

Exercise

PerfectAir has changed its timetable. All flights which depart after 1 PM have been moved forward by 1 hour. Show the code of each route together with the new departure and arrival times.

Stuck? Here's a hint!

Use + INTERVAL '1' HOUR.

Console

Code editor

Result

TableConsole