User defined python functions for date dimensions in redshift

date_user_defined_functions_redshift.sql

create or replace function f_sk_date (ts timestamp )
  returns integer
  stable as $$
    if not ts:
      return None
    return int(str(ts)[0:10].replace('-',''))
  $$ language plpythonu;

create or replace function f_date (ts timestamp)
  returns date
  stable as $$
    if not ts:
      return None
    return ts
  $$ language plpythonu;

create or replace function f_de_date (ts timestamp)
  returns varchar
  stable as $$
    if not ts:
      return None
    return ts.date().strftime('%d.%m.%Y')
  $$ language plpythonu;

create or replace function f_us_date (ts timestamp)
  returns varchar
  stable as $$
    if not ts:
      return None
    return ts.date().strftime('%m/%d/%Y')
  $$ language plpythonu;

create or replace function f_uk_date (ts timestamp)
  returns varchar
  stable as $$
    if not ts:
      return None
    return ts.date().strftime('%d/%m/%Y')
  $$ language plpythonu;

create or replace function f_day_of_month (ts timestamp)
  returns integer
  stable as $$
    if not ts:
      return None
    return ts.day
  $$ language plpythonu;

create or replace function f_day_suffix (ts timestamp)
  returns char(2)
  stable as $$
    if not ts:
      return None
    s = {
      1: 'st',
      2: 'nd',
      3: 'rd',
      21: 'st',
      22: 'nd',
      23: 'rd',
      31: 'st'
    }
    day = ts.day
    return s.get(day, 'th')
  $$ language plpythonu;

create or replace function f_day_name (ts timestamp)
  returns varchar
  stable as $$
    if not ts:
      return None
    return ts.strftime('%A')
  $$ language plpythonu;

create or replace function f_day_name_short (ts timestamp)
  returns varchar
  stable as $$
    if not ts:
      return None
    return ts.strftime('%a')
  $$ language plpythonu;

create or replace function f_day_of_week (ts timestamp)
  returns integer
  stable as $$
    if not ts:
      return None
    return ts.isoweekday()
  $$ language plpythonu;

create or replace function f_day_of_week_in_month (ts timestamp)
  returns varchar
  stable as $$
    if not ts:
      return None
    s = {
      1: 'st',
      2: 'nd',
      3: 'rd'
    }
    pre = ((ts.day - 1)//7) + 1
    prefix = str(pre) + s.get(pre, 'th')
    return prefix + ts.strftime(' %A')
  $$ language plpythonu;

create or replace function f_day_of_week_in_year (ts timestamp)
  returns varchar
  stable as $$
    if not ts:
      return None
    d = int(ts.strftime('%-j'))
    pre = ((d - 1)//7) + 1
    if 4 <= pre <= 20 or 24 <= pre <= 30 or (pre % 10 -1) > 2:
      suffix = "th"
    else:
      suffix = ["st", "nd", "rd"][pre % 10 - 1]
    prefix = str(pre) + suffix
    return prefix + ts.strftime(' %A')
  $$ language plpythonu;

create or replace function f_day_of_quarter (ts timestamp)
  returns integer
  stable as $$
    if not ts:
      return None
    from datetime import date
    q = (ts.month-1)//3 + 1
    qs = date(ts.year, (q-1)*3+1, 1)
    return (date(ts.year, ts.month, ts.day) - qs).days + 1
  $$ language plpythonu;

create or replace function f_day_of_year (ts timestamp)
  returns integer
  stable as $$
    if not ts:
      return None
    return int(ts.strftime('%-j'))
  $$ language plpythonu;

create or replace function f_week_of_year (ts timestamp)
  returns integer
  stable as $$
    if not ts:
      return None
    return ts.date().isocalendar()[1]
  $$ language plpythonu;

create or replace function f_month (ts timestamp)
  returns integer
  stable as $$
    if not ts:
      return None
    return ts.month
  $$ language plpythonu;

create or replace function f_month_name (ts timestamp)
  returns varchar
  stable as $$
    if not ts:
      return None
    return ts.strftime('%B')
  $$ language plpythonu;

create or replace function f_month_name_short (ts timestamp)
  returns varchar
  stable as $$
    if not ts:
      return None
    return ts.strftime('%b')
  $$ language plpythonu;

create or replace function f_month_of_quarter (ts timestamp)
  returns integer
  stable as $$
    if not ts:
      return None
    m = ts.month
    return int((m-1) % 3 + 1)
  $$ language plpythonu;

create or replace function f_quarter (ts timestamp)
  returns integer
  stable as $$
    if not ts:
      return None
    m = ts.month
    return int((m-1)//3 + 1)
  $$ language plpythonu;

create or replace function f_quarter_name (ts timestamp)
  returns varchar
  stable as $$
    if not ts:
      return None
    m = ts.month
    return 'Q' + str((m-1)//3 + 1)
  $$ language plpythonu;

create or replace function f_year (ts timestamp)
  returns integer
  stable as $$
    if not ts:
      return None
    return ts.year
  $$ language plpythonu;

create or replace function f_month_year (ts timestamp)
  returns varchar
  stable as $$
    if not ts:
      return None
    return ts.strftime('%b-%Y')
  $$ language plpythonu;

create or replace function f_MMYYYY (ts timestamp)
  returns varchar
  stable as $$
    if not ts:
      return None
    return ts.strftime('%m-%Y')
  $$ language plpythonu;

create or replace function f_first_day_of_month (ts timestamp)
  returns date
  stable as $$
    from datetime import datetime
    if not ts:
      return None
    return datetime(ts.year, ts.month, 1)
  $$ language plpythonu;

create or replace function f_last_day_of_month (ts timestamp)
  returns date
  stable as $$
    from datetime import timedelta, datetime
    if not ts:
      return None
    y = ts.year
    m = ts.month + 1
    if m > 12:
        m -= 12
        y += 1
    return datetime(y, m, 1) - timedelta(days=1)
  $$ language plpythonu;

create or replace function f_first_day_of_quarter (ts timestamp)
  returns date
  stable as $$
    from datetime import datetime
    if not ts:
      return None
    q = (ts.month-1)//3 + 1
    return datetime(ts.year, (q-1)*3+1, 1)
  $$ language plpythonu;

create or replace function f_last_day_of_quarter (ts timestamp)
  returns date
  stable as $$
    from datetime import datetime, timedelta
    if not ts:
      return None
    q = (ts.month-1)//3 + 2
    m = (q-1)*3+1
    y = ts.year
    if m > 12:
        m -= 12
        y += 1
    return datetime(ts.year, m, 1) - timedelta(days=1)
  $$ language plpythonu;

create or replace function f_first_day_of_year (ts timestamp)
  returns date
  stable as $$
    from datetime import datetime
    if not ts:
      return None
    return datetime(ts.year, 1, 1)
  $$ language plpythonu;

create or replace function f_last_day_of_year (ts timestamp)
  returns date
  stable as $$
    from datetime import datetime
    if not ts:
      return None
    return datetime(ts.year, 12, 31)
  $$ language plpythonu;

create or replace function f_is_weekday (ts timestamp)
  returns bool
  stable as $$
    if not ts:
      return None
    return ts.isoweekday() < 6
  $$ language plpythonu;