How to retrieve only the year from timestamp column?

Question

I have the following query that runs correctly on Postgres 9.3:

select distinct date_part('year', date_created) 
from "Topic";

The intention is to return only the distinct years on the column date_created which is created thus:

date_created  | timestamp with time zone | not null default now()

I need to turn it into a SQLAlchemy query but what I wrote does a select distinct on the date_created, not on the year, and returns the whole row, not just the distinct value:

topics = Topic.query.distinct(func.date_part('YEAR', Topic.date_created)).all()

How can I get only the distinct years from the table Topic?

Answer 1

Here are two variants:

Using ORM:

from sqlalchemy import func, distinct

result = session.query(distinct(func.date_part('YEAR', Topic.date_created)))
for row in result:
    print(row[0])

SQL Expression:

from sqlalchemy import func, select, distinct

query = select([distinct(func.date_part('YEAR', Topic.date_created))])
for row in session.execute(query):
        print(row[0])

Answer 2

Use extract function:

session.query(func.extract(Topic.date_created, 'year'))

this is a concept code, not tested.

Answer 3

SQL Alchemy syntax aside, you have a potential problem in your query.

Your data type is timestamptz (timestamp with time zone), which is a good choice. However, you cannot tell the year reliably form a timestamptz alone, you need to specify the time zone additionally. If you don't, the current time zone setting of the session is applied silently, which may or may not work for you.

Think of New Year's Eve: timestamptz '2016-01-01 04:00:00+00' - what year is it?

It's 2016 in Europe, but still 2015 in the USA.
You should make that explicit with the AT TIME ZONE construct to avoid sneaky mistakes:

SELECT extract(year FROM timestamptz '2016-01-01 04:00:00+00'
                         AT TIME ZONE 'America/New_York') AS year;

Detailed explanation:

• Ignoring timezones altogether in Rails and PostgreSQL

date_part() and extract() do the same in Postgres, extract() is the SQL standard, so rather use that.

SQL Fiddle.

BTW, you could also just:

SELECT extract(year FROM date_created) AS year
FROM   "Topic"
GROUP  BY 1;