How can I compare just the dates of datetime in SQLAlchemy?

Question

I have a query in SQLAlchemy:

weekly_schedule = WeeklyHour.query.filter(
    and_(
        WeeklyHour.start_time.in_(week_dates),
        WeeklyHour.end_time.in_(week_dates),
        WeeklyHour.employee == employee
        )
    ).all()

I am getting the dates of the current week like so:

today = datetime.datetime.today()
week_dates = [today + datetime.timedelta(days=i) for i in xrange(0 - today.weekday(), 7 - today.weekday())]

This returns a list of the current week in datetimes. Like so:

[datetime.date(2014, 4, 7), datetime.date(2014, 4, 8), datetime.date(2014, 4, 9), datetime.date(2014, 4, 10), datetime.date(2014, 4, 11), datetime.date(2014, 4, 12), datetime.date(2014, 4, 13)]

The problem is that the WeeklyHour is a datetime when the employee started and ended that day. And week_dates are a specific time set to now(). I will never get results because the time of the datetime won't match. All I really want to compare is the date portion of the datetime. My current solution (which works but is dumb) is to store separate dates of the datetime.

what database do you use?

van
– van

2014-04-13 14:45:42 +00:00
Commented Apr 13, 2014 at 14:45 — van
– van, Commented Apr 13, 2014 at 14:45

rockingskier · Accepted Answer · 2014-04-13 21:25:37Z

3

Rather than querying "in" all days of the week use the days as limits.

today = date.today()
start_of_week = today - timedelta(days=today.weekday())
start_of_following_week = start_of_week + timedelta(days=7)

weekly_schedule = WeeklyHour.query.filter(
    and_(
        WeeklyHour.start_time >= start_of_week,  # On or after Monday
        WeeklyHour.end_time < start_of_following_week, # Before next Monday
        WeeklyHour.employee == employee
        )
    ).all()

answered Apr 13, 2014 at 21:25

rockingskier

9,3763 gold badges42 silver badges49 bronze badges

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

1 Comment

DX-MON Over a year ago

As it happens, the and_() call surrounding all those conditions is not required as it's the default mode for .query.filter().

Collectives™ on Stack Overflow

How can I compare just the dates of datetime in 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