How to change a timestamp with timezone to date (YYYY)

I'm trying to change an entire columns format into a smaller date format. I'm currently using PostgreSQL and need to change it from timestamp with timezone to an easier format, preferably one that just displays the year? How can I get this done?

Ive tried using to_char functions as well as date_parse to no avail. Im still very new to this so its stumping me at this point, any help is much appreciated

Wed Dec 31 2008 19:00:00 GMT -0500 (Eastern Time Zone) is the current format.

I just want to be able to turn that into the year, so 2008

Solution

Seeing that you're dealing with text, it's best to convert it to an actual timestamptz first, using to_timestamp(). Then you can alter the column, casting it in place: online demo

create table your_table(invoicedate text);
insert into your_table values 
('Wed Dec 31 2008 19:00:00 GMT -0500 (Eastern Time Zone)');

alter table your_table
   alter column invoicedate
   type timestamptz using 
   (to_timestamp(invoicedate,'Dy Mon DD YYYY HH24:MI:SS AAA TZHTZM'));

alter table your_table
   add column invoiceyear smallint;
update your_table set invoiceyear=extract(year from invoicedate);

Already mentioned extract() can get you the year out of a timestamptz.

Keep in mind that PostgreSQL will consume the timestamps and keep them internally as UTC. This means that if you want to process invoices differently based on their timezone of origin, you'll have to save that origin information in a separate column. Right now, extracting a year from that timestamp will give you 2009 instead of 2008, because that time in EST corresponds to 2009 in UTC. To get it as 2008, you'll have to read it as invoicedate at time zone 'EST'