INTERVAL of time

Intervals of time.

schema:gisq

We can add (or subtract) a number of days (or years, months, hours, minutes or seconds) from date.

In this example we want to find the totp events that went out in the week of my 14th birthday.

SELECT * FROM totp
WHERE '1976-05-20'
 BETWEEN wk - 7 DAY
     AND wk

SELECT * FROM totp
WHERE #1976-05-20#
 BETWEEN wk - 7 
     AND wk

SELECT * FROM totp
WHERE '1976-05-20'
 BETWEEN DATEADD(DAY,-7,wk)
     AND wk

SELECT * FROM totp
WHERE '1976-05-20'
 BETWEEN wk - 7
     AND wk

SELECT * FROM totp
WHERE '1976-05-20'
 BETWEEN DATE(wk, '-7 days')
     AND wk

SELECT * FROM totp
WHERE DATE '1976-05-20'
 BETWEEN wk - INTERVAL '7' DAY
     AND wk

Just use numbers.

Drop the DATE and the INTERVAL

Make it clear we are using a date - then rely on the fact that dates are stored as number of days.

DATE and TIME Operations

Date and time types

Format a date and time

Specify a date

Match a range of dates

Subtract dates

INTERVAL of time

Components of date

Date functions

Date arithmetics

Select the oldest person

Select a record with the latest date

YYYYMMDD date format

Getting the latest record