Dynamic Dates in Snowflake

dynamic_dates.sql

-- Step 1: Create the table without generated columns
create or replace table dates (
  timestamp_column TIMESTAMP_NTZ PRIMARY KEY
);


-- Create a join table
create or replace table memorable_dates (
  id integer autoincrement,
  date timestamp_ntz,
  note string
);


-- Insert some memorable dates
insert into memorable_dates (date, note) values (to_timestamp_ntz('1976-11-19'), 'Someone was born');
insert into memorable_dates (date, note) values (to_timestamp_ntz('1992-11-19'), 'Someone was legally allowed to drive');
insert into memorable_dates (date, note) values (to_timestamp_ntz('1997-11-19'), 'Someone was legally allowed to drink');


-- Step 2: Create a view that simulates the generated columns
create or replace view vw_dates as
select 
  timestamp_column,
  day(timestamp_column) as day,
  month(timestamp_column) as month,
  year(timestamp_column) as year,
  weekofyear(timestamp_column) as week_of_year,
  quarter(timestamp_column) as quarter,
  case
    when month(timestamp_column) between 1 and 4 then 1
    when month(timestamp_column) between 5 and 8 then 2
    else 3
  end as trimester,
  hour(timestamp_column) as hour,
  minute(timestamp_column) as minute,
  second(timestamp_column) as second,  
  lpad(hour(timestamp_column) % 12, 2, '0') as hour_12,  -- 12-hour format
  case 
    when hour(timestamp_column) < 12 then 'AM'
    else 'PM'
  end as am_pm,  -- AM/PM
  dayname(timestamp_column) as day_of_week,  -- Correct day of week names
  to_char(timestamp_column, 'MMMM') as month_of_year,  -- Correct month names
    -- JSON object for ms, µs, and ns in a variant column
  object_construct(
    'ms', date_part('epoch_millisecond', timestamp_column),
    'µs', date_part('epoch_microsecond', timestamp_column),
    'ns', date_part('epoch_nanosecond', timestamp_column)
  ) as subsecond  -- Stores milliseconds, microseconds, nanoseconds in a JSON object

from dates;

-- Step 2a: Optionally build the view from a table that already has a date column
create or replace view vw_memorable_dates as
select
  note,
  date,
  day(date) as day,
  month(date) as month,
  year(date) as year,
  weekofyear(date) as week_of_year,
  quarter(date) as quarter,
  case
    when month(date) between 1 and 4 then 1
    when month(date) between 5 and 8 then 2
    else 3
  end as trimester,
  hour(date) as hour,
  minute(date) as minute,
  second(date) as second,  
  lpad(hour(date) % 12, 2, '0') as hour_12,  -- 12-hour format
  case 
    when hour(date) < 12 then 'AM'
    else 'PM'
  end as am_pm,  -- AM/PM
  dayname(date) as day_of_week,  -- Correct day of week names
  to_char(date, 'MMMM') as month_of_year,  -- Correct month names
    -- JSON object for ms, µs, and ns in a variant column
  object_construct(
    'ms', date_part('epoch_millisecond', date),
    'µs', date_part('epoch_microsecond', date),
    'ns', date_part('epoch_nanosecond', date)
  ) as subsecond  -- Stores milliseconds, microseconds, nanoseconds in a JSON object

from memorable_dates;


-- Insert some sample values
insert into dates values (current_timestamp());


-- Insert memorable dates into the timestamp_data table
insert into dates select date from memorable_dates;


-- Join the memorable dates table with the dates view
select 
    m.note, 
    t.*
from 
    vw_dates t,
    memorable_dates m
where 
    m.date = t.timestamp_column;

    
-- Where we aren't joining
select * from vw_memorable_dates;

-- One more thing....
create or replace function date_info_udtf(input_date timestamp_ntz)
returns table (
  day int,
  month int,
  year int,
  week_of_year int,
  quarter int,
  trimester int,
  hour int,
  minute int,
  second int,
  hour_12 string,
  am_pm string,
  day_of_week string,
  month_of_year string,
  subsecond object
)
language sql
as
$$
  select 
    day(input_date) as day,
    month(input_date) as month,
    year(input_date) as year,
    weekofyear(input_date) as week_of_year,
    quarter(input_date) as quarter,
    case
      when month(input_date) between 1 and 4 then 1
      when month(input_date) between 5 and 8 then 2
      else 3
    end as trimester,
    hour(input_date) as hour,
    minute(input_date) as minute,
    second(input_date) as second,
    lpad(hour(input_date) % 12, 2, '0') as hour_12,  -- 12-hour format
    case 
      when hour(input_date) < 12 then 'AM'
      else 'PM'
    end as am_pm,  -- AM/PM
    dayname(input_date) as day_of_week,  -- Day of week
    to_char(input_date, 'MMMM') as month_of_year,  -- Full month name
    object_construct(
      'ms', date_part('epoch_millisecond', input_date),
      'µs', date_part('epoch_microsecond', input_date),
      'ns', date_part('epoch_nanosecond', input_date)
    ) as subsecond  -- Milliseconds, microseconds, nanoseconds
$$;

select * 
from table(date_info_udtf('2024-09-10 15:45:00'::timestamp_ntz));