CODEWITHSUNDEEP
pythonsqlpostgresqlsqlalchemy
TVH7
TVH7

Reputation: 465

How to perform date_trunc query in Postgres using SQLAlchemy

I can't seem to be able to translate the following query into SQLAlchemy.

I would like to translate the following query: 

SELECT date_trunc('day', time), "PositionReport".callsign FROM tvh_aircraft."PositionReport" WHERE "PositionReport".reg = 'PH-BVA'
GROUP BY 1, "PositionReport".callsign

I've tried the following, but with no luck. 

flight_days = session\
        .query(PositionReport)\
        .filter(PositionReport.reg == reg) \
        .group_by(func.date_trunc('day', PositionReport.time))\
        .group_by('1')\
        .all()

    trunc_date = func.date_trunc('day', PositionReport.time)
    flight_days = session.query(trunc_date, PositionReport.callsign) \
        .filter(PositionReport.reg == reg) \
        .group_by("date_trunc_1")

Thanks in advance for your help.

Upvotes: 22

Views: 9900

Answers (1)

r-m-n
r-m-n

Reputation: 15120

session.query(func.date_trunc('day', PositionReport.time), 
              PositionReport.callsign) \
    .filter(PositionReport.reg=='PH-BVA') \
    .group_by(func.date_trunc('day', PositionReport.time),
              PositionReport.callsign).all()

or if you need exactly GROUP BY 1

from sqlalchemy import text

session.query(func.date_trunc('day', PositionReport.time), 
              PositionReport.callsign) \
    .filter(PositionReport.reg=='PH-BVA') \
    .group_by(text('1'),
              PositionReport.callsign).all()

Upvotes: 22

Related Questions