0

I have some table in a database that looks like that:

phase_type phase_start phase_end Obsolete 01/01/2021 02/02/2022 Obsolete 01/03/2021 02/07/2022 Obsolete 05/01/2021 09/02/2022 Available 05/07/2021 09/02/2027 Available 05/07/2023 09/02/2025 Available 05/07/2024 09/02/2029

If I want to select on this table and return only the rows that the date of today is lying between the range of 30 days in the past of phase_end, I could do like this:

from datetime import date,timedelta fromo sqlalchemy import select past = my_table.phase_end - timedelta(30) future = my_table.phase_end query = select(my_table).where(date.today() >= past,date.today() <= future) session.exec(query).fetchall()

However I would like to use phase_start when calculating past and future for the case when phase_type is Obsolete, for all the other cases I would like to use phase_end as above. Thus the range should be calculated based on the value that phase_end takes. How can I do this and return all rows that pass the conditions ?

Improve this question
2
  • 1
    What is the type of the start and end columns in the database? Commented Dec 15, 2022 at 12:38
  • Is of date type Commented Dec 15, 2022 at 14:03

1 Answer 1

Reset to default
1

I am not sure that I understand your problem correctly, but if you want to return rows that meet the specified conditions, you can use a case statement in the where clause of your select query.

from datetime import date,timedelta from sqlalchemy import case, select past = case([(my_table.phase_type == 'Obsolete', my_table.phase_start)], else_=my_table.phase_end) - timedelta(30) future = case([(my_table.phase_type == 'Obsolete', my_table.phase_start)], else_=my_table.phase_end) query = select(my_table).where(date.today() >= past,date.today() <= future) session.exec(query).fetchall()

This query will return all rows from my_table where date.today() falls within the range of 30 days in the past of either phase_start (if phase_type is Obsolete) or phase_end (for all other cases).

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

1 Comment

Why do you use a list of tuples wrapped around case? I saw an example of case in sql alchemy docs with just a tuple

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.