타임스탬프 열에서 연도만 검색하는 방법은 무엇입니까? (How to retrieve only the year from timestamp column?)

문제 설명

타임스탬프 열에서 연도만 검색하는 방법은 무엇입니까? (How to retrieve only the year from timestamp column?)

Postgres 9.3에서 올바르게 실행되는 다음 쿼리가 있습니다.

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

다음과 같이 생성된 date_created 열에서 고유한 연도만 반환하려는 의도입니다.

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

이를 SQLAlchemy 쿼리로 변환해야 하지만 내가 작성한 것은 연도가 아닌 date_created에 대해 고유한 선택을 수행하고 고유한 값뿐만 아니라 전체 행을 반환합니다.

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

테이블 주제에서 고유한 연도만 가져오려면 어떻게 해야 합니까?

참조 솔루션

방법 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])

방법 2:

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;

방법 3:

Use extract function:

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

this is a concept code, not tested.

(by ruipacheco、Roberto Damian Alfonso、Erwin Brandstetter、Tomasz Jakub Rup)

참조 문서

1. How to retrieve only the year from timestamp column? (CC BY‑SA 2.5/3.0/4.0)