Can my DB?

-- Add an interval to a timestamp
SELECT NOW() + INTERVAL '30 days';

-- Subtract intervals
SELECT NOW() - INTERVAL '2 hours 30 minutes';

-- Compound interval in a column
CREATE TABLE subscriptions (
  id INT PRIMARY KEY,
  duration INTERVAL NOT NULL DEFAULT INTERVAL '1 year'
);

-- Difference between timestamps returns an interval
SELECT end_date - start_date AS duration FROM projects;

• Has a native INTERVAL data type that can be used as a column type.
• Supports compound intervals: '1 year 2 months 3 days 4 hours'.
• Timestamp subtraction returns an INTERVAL value.
• Intervals can be multiplied and divided by numbers.

-- Add interval to a date
SELECT NOW() + INTERVAL 30 DAY;

-- Using DATE_ADD function
SELECT DATE_ADD('2024-01-01', INTERVAL 3 MONTH);

-- Subtract interval
SELECT NOW() - INTERVAL 2 HOUR;

• INTERVAL is used in expressions but is not a storable data type.
• Supports single-unit intervals: DAY, MONTH, YEAR, HOUR, MINUTE, SECOND, etc.
• Compound intervals use special units like DAY_HOUR, YEAR_MONTH.
• DATE_ADD() and DATE_SUB() functions provide equivalent functionality.
• Timestamp subtraction does not return an interval; use TIMESTAMPDIFF() instead.

SELECT NOW() + INTERVAL 1 DAY;

SELECT DATE_ADD(NOW(), INTERVAL 1 MONTH);

SELECT TIMESTAMPDIFF(DAY, '2024-01-01', '2024-06-15');

• INTERVAL is used in expressions but is not a storable column type.
• Supports DATE_ADD(d, INTERVAL 1 MONTH), DATE_SUB, TIMESTAMPDIFF(UNIT, t1, t2).
• DATEDIFF(d1, d2) for day difference.
• Fractional intervals not natively supported.

-- Add interval to a timestamp
SELECT SYSTIMESTAMP + INTERVAL '30' DAY FROM dual;

-- Year-to-month interval
SELECT SYSTIMESTAMP + INTERVAL '1-6' YEAR TO MONTH FROM dual;

-- Day-to-second interval
SELECT SYSTIMESTAMP + INTERVAL '2 12:30:00' DAY TO SECOND FROM dual;

• Supports two interval column types: INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND.
• Cannot mix year-month and day-second intervals in a single value.
• Subtracting timestamps returns an INTERVAL DAY TO SECOND; subtracting dates returns a NUMBER of days.
• NUMTODSINTERVAL() and NUMTOYMINTERVAL() convert numbers to intervals.

-- Add 30 days using DATEADD
SELECT DATEADD(DAY, 30, GETDATE());

-- Subtract 2 hours
SELECT DATEADD(HOUR, -2, GETDATE());

-- Difference between dates
SELECT DATEDIFF(DAY, '2024-01-01', '2024-06-15');

• No INTERVAL keyword or data type.
• All date arithmetic uses DATEADD() and DATEDIFF() functions.
• DATEDIFF returns an integer count of the specified unit, not a compound duration.
• DATEDIFF_BIG() is available for large differences that exceed INT range.

-- Add days using datetime() modifier
SELECT datetime('now', '+30 days');

-- Subtract hours
SELECT datetime('now', '-2 hours');

-- Combine multiple modifiers
SELECT datetime('now', '+1 year', '-3 months', '+10 days');

• No INTERVAL keyword or data type.
• Date arithmetic is done via modifier strings in datetime(), date(), and time() functions.
• Modifiers support: +/- N years, months, days, hours, minutes, seconds.
• No direct way to compute the difference between two dates as an interval.

SELECT GETDATE() + INTERVAL '1 month';

SELECT DATEADD(month, 1, GETDATE());

SELECT DATEDIFF(day, '2024-01-01', '2024-06-15');

• Supports INTERVAL literals and arithmetic.
• DATEADD(unit, n, date), DATEDIFF(unit, start, end) functions available.
• Supports INTERVAL with year, month, day, hour, minute, second.
• Redshift INTERVAL follows PostgreSQL conventions.

SELECT CURRENT DATE + 1 YEAR FROM sysibm.sysdummy1;

SELECT CURRENT DATE + 2 MONTHS FROM sysibm.sysdummy1;

SELECT TIMESTAMPDIFF(32, CHAR(CURRENT TIMESTAMP - TIMESTAMP('2024-01-01-00.00.00'))) FROM sysibm.sysdummy1;

• DB2 uses labeled duration arithmetic rather than an INTERVAL type: date + 1 YEAR, date + 2 MONTHS, timestamp + 30 DAYS.
• No INTERVAL type or INTERVAL literals.
• Arithmetic uses labeled duration keywords: YEARS, MONTHS, DAYS, HOURS, MINUTES, SECONDS.
• TIMESTAMPDIFF uses numeric unit codes (16=months, 32=days, etc.).

SELECT DATEADD('month', 1, CURRENT_DATE());

SELECT DATEDIFF('day', '2024-01-01', '2024-06-15');

SELECT CURRENT_DATE() + INTERVAL '1 month';

• Supports INTERVAL literals: INTERVAL '1 month'.
• DATEADD(unit, n, date_expr), DATEDIFF(unit, start, end), TIMESTAMPADD / TIMESTAMPDIFF available.
• Supports a wide range of interval units: year, quarter, month, week, day, hour, minute, second, millisecond, microsecond, nanosecond.

SELECT DATE_ADD(CURRENT_DATE(), INTERVAL 1 MONTH);

SELECT DATE_DIFF(DATE '2024-06-15', DATE '2024-01-01', DAY);

SELECT TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 2 HOUR);

• Full INTERVAL type and functions.
• DATE_ADD(date, INTERVAL n unit), DATE_DIFF(d1, d2, unit), TIMESTAMP_ADD, TIMESTAMP_DIFF.
• Full INTERVAL type as a literal: INTERVAL 1 MONTH, INTERVAL '3-6' YEAR TO MONTH.
• Supports arithmetic with DATE, DATETIME, TIMESTAMP types.