Introduction
Dates
Time
Timestamps
19. Timestamps with BETWEEN
Extract functions
Timezone conversion
Intervals
Current date and time
Revision

Instruction

Nice job. As you may expect, it is also possible to use BETWEEN with timestamps:

SELECT id
FROM aircraft
WHERE launched
BETWEEN '2015-11-01'
  AND '2015-12-01';

Again, we can skip the time – the database will fill in the missing fields with zeroes.

This automatic addition of zeroes is very convenient, but you need to watch out when retrieving rows for the whole month, year etc. If you want the aircraft launched in November 2015, you must use the condition we've just shown you:

BETWEEN '2015-11-01' AND '2015-12-01'

The second date is 1 December because the database will add the missing zeroes: 2015-12-01 00:00:00. This is the exact point in time when December starts (the beginning of its first day). If you wrote the second date as the last day of November (2015-11-30), you would skip all the aircraft which were launched on November 30, 2015. Keep that in mind.

Exercise

Find all the aircraft which were launched in 2015. Show the columns id and launched.

Stuck? Here's a hint!

Remember about the proper date format and use apostrophes: 'YYYY-MM-DD'. Use BETWEEN. The second date should be January 1, 2016.

Console

Code editor

Result

TableConsole