Summer Deals - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Dates
Time
Timestamps
Extract functions
Timezone conversion
Intervals
Current date and time
Review

Instruction

Good. The second type of interval specified by the SQL standard is INTERVAL 'd hh:mm:ss' DAY TO SECOND, where d is the number of days, hh is the number of hours, mm is the number of minutes and ss is the number of seconds.

This interval can also be applied with a date/timestamp column to add/subtract a certain period of time:

SELECT
  id,
  launched + INTERVAL '1 2:05:20' DAY TO SECOND
FROM aircraft;

The above query will add 1 day, 2 hours, 5 minutes and 20 seconds to the timestamp in the column launched and return it as a result. So for example the timestamp 2010-04-01 21:58:00+02 would be turned into 2010-04-03 00:03:20+02.

Again, MySQL does not support the syntax for INTERVAL DAY TO SECOND.

Exercise

Before the official launch, every plane has a 14-day test period. However, some planes, due to complications with paperwork may be held for a few hours longer than usual, before they can officially take off.

For plane with ID of 4, show the original launch timestamp and the timestamp of when its test period began (it lasted exactly 14 days, 8 hours, 41 minutes and 16 seconds). Name the second column test_date.

Stuck? Here's a hint!

This time you need to subtract the interval from original column, instead of adding it.