CODEWITHSUNDEEP
postgresql
user3800040
user3800040

Reputation: 71

How can I get year from the date field

How can I get year from the date field, If I have a date field product_date, it has 1000 entries per year I want to take a YEAR wise record. product_date = 2013/01/01 like i want search 2013.

Upvotes: 1

Views: 277

Answers (2)

Vivek S.
Vivek S.

Reputation: 21895

SELECT EXTRACT(YEAR FROM product_date ) FROM tbl_product;

sort your table according to YEAR

 with cte as(
 SELECT EXTRACT(YEAR FROM product_date) as myYear FROM tbl_product order by myYear
 )
 select product_date,* from tbl_product where  extract(year from product_date) in (select myYear    
 from cte)

Upvotes: 4

user330315
user330315

Reputation:

I'm not sure what "year wise record" should mean because you failed to supply some sample data, but to get the year from a date column, you can use the extract() function:

select *
from some_table
where extract(year from product_date) = 2013;

For details about the extract function, please see the manual:
http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT

The above however will not make use of an index on the product_date column. So if performance is really important you have two options:

  • create an index on extract(year from product_date)

  • use a between clause:

    select *
from some_table
where product_date between date '2013-01-01' and date '2013-12-31';

Upvotes: 1

Related Questions