Skip to content

Instantly share code, notes, and snippets.

@Jan-Zeiseweis
Last active August 8, 2020 14:40
Show Gist options
  • Save Jan-Zeiseweis/a9417ac5a40c1e077115 to your computer and use it in GitHub Desktop.
Save Jan-Zeiseweis/a9417ac5a40c1e077115 to your computer and use it in GitHub Desktop.
User defined python functions for date dimensions in redshift
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment