如何只从时间戳列中检索年份? [英] How to retrieve only the year from timestamp column?

问题描述

我有以下查询可以在Postgres 9.3上正确运行:

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

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

目的是仅在创建的 date_created 列上返回不同的年份:

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()

我需要将其转换为SQLAlchemy查询，但我写的代码在创建的date_而不是Year上进行选择，并返回整行，而不仅仅是返回唯一值:

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?

推荐答案

以下是两个变体:

使用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表达式:

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

这篇关于如何只从时间戳列中检索年份?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！