How to perform date_trunc query in Postgres using SQLAlchemy

Question

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.

Hi, could you post some examples of the data and the desired output? — pazitos10
– pazitos10, Commented Oct 26, 2018 at 2:18

Espoir Murhabazi · Accepted Answer · 2018-11-07 19:38:53Z

22

+50

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

edited Nov 7, 2018 at 19:38

Espoir Murhabazi

6,4415 gold badges49 silver badges78 bronze badges

answered Oct 26, 2018 at 11:20

r-m-n

15.3k6 gold badges74 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

TVH7 Over a year ago

Thanks. Does indeed work. For other people reading this, only add .all() to the query to make it complete.

Collectives™ on Stack Overflow

How to perform date_trunc query in Postgres using SQLAlchemy

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related