a nice blogpost on how to use the INTERVAL data types in Snowflake:
https://medium.com/snowflake/snowflake-supports-native-interval-data-type-public-preview-0a7a03dbf089
Some examples from the blog post:
We start by defining a subscriptions table with an INTERVAL YEAR TO MONTH column:
CREATE OR REPLACE TABLE subscriptions (
customer_id INT,
start_date DATE,
plan_duration INTERVAL YEAR TO MONTH,
promo_extension INTERVAL MONTH(2),
payment_terms INTERVAL DAY(3)
);
This lets us model "6-month plan" as INTERVAL '0-6' YEAR TO MONTH, and "1-year plan" as INTERVAL '1-0' YEAR TO MONTH.
Inserting Subscription Plans (DML)
INSERT INTO subscriptions VALUES
(101, '2024-01-15', '0-6', '1', '30'),
(102, '2023-07-01', '1-0', '6', '45'),
(103, '2022-12-01', '1-6', '0', '30');
To calculate each customer's renewal date:
-- Renewal date with promotion
-- Invoice due date after renewal
SELECT
customer_id,
start_date,
plan_duration::VARCHAR AS plan_len,
promo_extension::VARCHAR AS promo_ext,
payment_terms::VARCHAR AS net_terms,
-- Base renewal
start_date + plan_duration AS renewal_date,
-- Renewal with promo (adds months cleanly)
start_date + plan_duration + promo_extension AS renewal_with_promo,
-- Assume the renewal invoice is issued on the renewal_with_promo date;
-- payment is due after the stored NET terms (in days).
start_date + plan_duration + promo_extension
+ payment_terms AS invoice_due
FROM subscriptions
ORDER BY customer_id;
Because plan_duration is a real INTERVAL, Snowflake will correctly compute:
6 months after Jan 15, 2024 -> July 15, 2024
1 year after July 1, 2023 -> July 1, 2024
18 months after Dec 1, 2022 -> June 1, 2024