CODEWITHSUNDEEP
postgresql
ueg1990
ueg1990

Reputation: 1033

PostgreSql - Adding YEar and Month to Table

I am creating a Customer table and i want one of the attributes to be Expiry Date of credit card.I want the format to be 'Month Year'. What data type should i use? i want to use date but the format is year/month/day. Is there any other way to restrict format to only Month and year?

Upvotes: 1

Views: 2767

Answers (3)

Chris Travers
Chris Travers

Reputation: 26454

As another idea you could essentially create some brief utilities to do this for you using int[]:

CREATE OR REPLACE FUNCTION exp_valid(int[]) returns bool LANGUAGE SQL IMMUTABLE as
$$ 
SELECT $1[1] <= 12 AND (select count(*) = 2 FROM unnest($1));
$$;

CREATE OR REPLACE FUNCTION first_invalid_day(int[]) RETURNS date LANGUAGE SQL IMMUTABLE AS 
$$ 
SELECT (to_date($1[2]::text || $1[1]::text, CASE WHEN $1[2] < 100 THEN 'YYMM' ELSE 'YYYYMM' END) + '1 month'::interval)::date;
$$;

These work:

postgres=# select exp_valid('{04,13}');
 exp_valid 
-----------
 t
(1 row)

postgres=# select exp_valid('{13,04}');
 exp_valid 
-----------
 f
(1 row)

postgres=# select exp_valid('{04,13,12}');
 exp_valid 
-----------
 f
(1 row)

Then we can convert these into a date:

postgres=# select first_invalid_day('{04,13}');
 first_invalid_day 
-------------------
 2013-05-01
(1 row)

This use of arrays does not violate any normalization rules because the array as a whole represents a single value in its domain. We are storing two integers representing a single date. '{12,2}' is December of 2002, while '{2,12}' is Feb of 2012. Each represents a single value of the domain and is therefore perfectly atomic.

Upvotes: 0

Mike Sherrill &#39;Cat Recall&#39;
Mike Sherrill &#39;Cat Recall&#39;

Reputation: 95532

Use either

  • char(5) for two-digit years, or
  • char(7) for four-digit years.

Code below assumes two-digit years, which is the form that matches all my credit cards. First, let's create a table of valid expiration dates.

create table valid_expiration_dates (
  exp_date char(5) primary key
);

Now let's populate it. This code is just for 2013. You can easily adjust the range by changing the starting date (currently '2013-01-01'), and the "number" of months (currently 11, which lets you get all of 2013 by adding from 0 to 11 months to the starting date).

with all_months as (
  select '2013-01-01'::date + (n || ' months')::interval months
  from generate_series(0, 11) n
)
insert into valid_expiration_dates
select to_char(months, 'MM') || '/' || to_char(months, 'YY') exp_date
from all_months;

Now, in your data table, create a char(5) column, and set a foreign key reference from it to valid_expiration_dates.exp_date.

While you're busy with this, think hard about whether "exp_month" might be a better name for that column than "exp_date". (I think it would.)

Upvotes: 0

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125214

You can constrain the date to the first day of the month:

create table customer (
    cc_expire date check (cc_expire = date_trunc('month', cc_expire))
);

Now this fails:

insert into customer (cc_expire) values ('2014-12-02');
ERROR:  new row for relation "customer" violates check constraint "customer_cc_expire_check"
DETAIL:  Failing row contains (2014-12-02).

And this works:

insert into customer (cc_expire) values ('2014-12-01');
INSERT 0 1

But it does not matter what day is entered. You will only check the month:

select
    date_trunc('month', cc_expire) > current_date as valid
from customer;
 valid 
-------
 t

Extract year and month separately:

select extract(year from cc_expire) "year", extract(month from cc_expire) "month"
from customer
;
 year | month 
------+-------
 2014 |    12

Or concatenated:

select to_char(cc_expire, 'YYYYMM') "month"
from customer
;
 month  
--------
 201412

Upvotes: 4

Related Questions