Introduction
Dates
Time
Timestamps
Extract functions
Timezone conversion
Intervals
30. INTERVAL DAY TO SECOND
Current date and time
Revision

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;

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 few hours longer than usual, before they can officially take-off.

For plane with id 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).

Stuck? Here's a hint!

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

Console

Code editor

Result

TableConsole