Thread: Year Only Date Column

I want to create a date column that contains only the year, such as
YYYY. Can this done in PostgreSQL? I didn't see this listed in the
handbook and right now, the only way I can see to do would be to create
a numeric data field with zero scale and a precision of four.


Thanks,

--
Jeff Flowers

On Nov 23, 2004, at 10:16 PM, Jeff Flowers wrote:

> I want to create a date column that contains only the year, such as
> YYYY. Can this done in PostgreSQL? I didn't see this listed in the
> handbook and right now, the only way I can see to do would be to create
> a numeric data field with zero scale and a precision of four.

You could do that, or create a domain that would restrict the date to
only be, say, CCYY-01-01, which would represent the year. Something
like:

test=# create domain date_year as date check (Date_trunc('year',value)
= value);
CREATE DOMAIN
test=# select '1990-01-01'::date_year;
  date_year
------------
  1990-01-01
(1 row)

test=# select '1990-02-01'::date_year;
ERROR:  value for domain date_year violates check constraint
"date_year_check"

It'll throw an error if you try to enter a date that isn't on January 1
of that year.

Just an idea.

Michael Glaesemann
grzm myrealbox com

On Tue, 23 Nov 2004 22:28:08 +0900, "Michael Glaesemann"
<grzm@myrealbox.com> said:
>
> On Nov 23, 2004, at 10:16 PM, Jeff Flowers wrote:
>
> > I want to create a date column that contains only the year, such as
> > YYYY. Can this done in PostgreSQL? I didn't see this listed in the
> > handbook and right now, the only way I can see to do would be to create
> > a numeric data field with zero scale and a precision of four.
>
> You could do that, or create a domain that would restrict the date to
> only be, say, CCYY-01-01, which would represent the year.

Since I will be using this column only for the sake of completeness, I
think that I will either use the numeric column (or perhaps a CHAR(4)
column).


Thanks,

--
Jeff Flowers