dim_date

73413 rows


Description

Table represents a dimension of a day. This is not locked to a timezone and is updated daily

Columns

Column Type Size Nulls Auto Default Children Parents Comments
dim_date_id serial 10 √ nextval('dim_date_seq'::regclass)
account_transaction_summary.dim_date_id account_transaction_summary_dim_date_id_fkey R
admin_transfer.dim_date_id admin_transfer_dim_date_id_fkey R
customer.dob_dim_date_id customer_dob_dim_date_id_fkey R
customer.signup_dim_date_id customer_signup_dim_date_id_fkey R
customer_account_transaction_summary.dim_date_id customer_account_transaction_summary_dim_date_id_fkey R
customer_action.dim_date_id customer_action_dim_date_id_fkey R
deposit.dim_date_id deposit_dim_date_id_fkey R
prize.dim_date_id Implied Constraint R
syndicate_dividend.dim_date_id syndicate_dividend_dim_date_id_fkey R
ticket_dividend.dim_date_id ticket_dividend_dim_date_id_fkey R

Primary Key; an identifier for this record
date_value date 13 now()

Date-only typed value of the date
date_iso bpchar 10 √ null

String representation in YYYY-MM-DD format, e.g. 2014-08-24
year int2 5 √ null

Just the year, YYYY, e.g. 2014
quarter int2 5 √ null

Quarter number that the month falls into, January - March = 1, April - June =2, July - September = 3, October - December = 4
quarter_name bpchar 2 √ null

Q(n) quarter name that the month falls into, January - March = Q1, April - June = Q2, July - September = Q3, October - December = Q4
month int2 5 √ null

Numeric representation of the month, January = 1, February = 2, … December = 12
month_name varchar 10 √ null

English titled month, January, February, … December
month_abbreviation varchar 10 √ null

Abbreviated english titled month, Jan, Feb, … Dec
week int2 5 √ null

Week number of the year the day fell on, first week of January = 1, last week of December = 52
day_of_month int2 5 √ null

Day of the month this date fell on, e.g. 24 if 2014-08-24
day_of_year int2 5 √ null

Day of the year this date fell on. Count starts on 1st Jan (1) & finishes on 31st Dec (365/6), ie 2014-08-24 = 236
day_of_week int2 5 √ null

Day of the week this date fell on, Monday = 1 … Sunday = 7
day_name varchar 10 √ null

English titled day, Monday … Sunday
day_abbreviation varchar 10 √ null

Abbreviated English titled day, Mon … Sun
is_weekend bool 1 √ false
is_weekday bool 1 √ false
is_today bool 1 √ false
is_yesterday bool 1 √ false
is_this_week bool 1 √ false
is_last_week bool 1 √ false
is_3_week bool 1 √ false
is_4_week bool 1 √ false
is_5_week bool 1 √ false
is_6_week bool 1 √ false
is_this_month bool 1 √ false
is_last_month bool 1 √ false
is_2_month bool 1 √ false
is_3_month bool 1 √ false
is_4_month bool 1 √ false
is_5_month bool 1 √ false
is_6_month bool 1 √ false
is_9_month bool 1 √ false
is_18_month bool 1 √ false
is_30_month bool 1 √ false
is_42_month bool 1 √ false
is_this_year bool 1 √ false
is_last_year bool 1 √ false
is_3_year bool 1 √ false
is_4_year bool 1 √ false
is_5_year bool 1 √ false
is_over_5_year bool 1 √ false
age_range text 2147483647 √ null

Age category the date falls into (this is updated daily) so age_range changes daily. The categories are: <18, 18-25, 26-30, 31-35 … 76+
days_since int4 10 √ 0

Indexes

Constraint Name Type Sort Column(s)
dim_date_pkey Primary key Asc dim_date_id
dim_date_date_iso_key Must be unique Asc date_iso
dim_date_date_value_key Must be unique Asc date_value

Relationships

Close relationships within degrees of separation