Find average date in sqlalchemy

Question

I have two tables. Foo and Bar.

class Foo(db.Model):
  id = db.Column(db.Integer, primary_key=True)
  bars = db.relationship('Bar', backref='foo', lazy='dynamic')

class Bar(db.Model):
  id = db.Column(db.Integer, primary_key=True)
  date = db.Column(db.DateTime, default=datetime.utcnow)
  foo_id= db.Column(db.Integer, db.ForeignKey('foo.id'))

How can I have a hybrid property in Foo that can find the average date from all the bars. I have tried the following, which doesn't produce any errors but doesnt give the desired outcome

@hybrid_property
  def baz(self):
    return db.select([db.func.avg(Bar.date)]) \
      .where(Bar.foo_id == self.id) \
      .label('baz')

What am I doing wrong?

SivolcC · Answer 1 · 2018-09-21T12:38:53.967

1

That's a tricky one.

I am not sure applying avg()on a DateTime object works very well. If I was you, I would calculate the average of this field in seconds. For that, you can have an hybrid_property along with an expression in Bar that converts the date field to seconds:

class Bar(db.Model):
  id = db.Column(db.Integer, primary_key=True)
  date = db.Column(db.DateTime)
  foo_id= db.Column(db.Integer, db.ForeignKey('foo.id'))

  @hybrid_property
  def date_seconds(self):
    return time.mktime(self.date.timetuple())

  @date_seconds.expression
  def date_seconds(cls):
    return time.mktime(cls.date.timetuple())

And then use this date_seconds to calculate the average of seconds, and transform it back to a readable time format.

edited Sep 21 '18 at 12:38

answered Sep 21 '18 at 02:35

SivolcC

3,258
2
14
32

1

This seems to be the right path for an answer to my question but I get an error `TypeError: 'NoneType' object is not callable` on this part `cls.date.timetuple()` – Kazuren Sep 21 '18 at 11:40
1

Indeed I get the same error after trying multiple solutions based on this method, maybe someone else can help with that one? Sorry for the non-solution, but am sure we are on the right path. – SivolcC Sep 21 '18 at 11:50
2

The problem is that you cannot treat the instrumented attribute `cls.date` as a Python date object in `date_seconds.expression`. It should be used to form a suitable SQL expression. – Ilja Everilä Sep 21 '18 at 16:30

Kazuren · Accepted Answer · 2018-09-22T13:17:27.367

I managed to solve the problem thanks to this question / answer

I made a custom type that stores datetime as seconds since Jan 01 1970. (UTC)

class IntegerDate(types.TypeDecorator):
  impl = types.Integer

  def process_bind_param(self, value, dialect):
    return value.replace(tzinfo=timezone.utc).timestamp() * 1000

  def process_result_value(self, value, dialect):
    return datetime.utcfromtimestamp(value / 1000)

and changing this date = db.Column(db.DateTime) to this date = db.Column(IntegerDate)

and then I can get the average of that

@hybrid_property
  def baz(self):
    return db.select([db.func.avg(Bar.date)]) \
      .where(Bar.foo_id == self.id) \
      .label('baz')

Find average date in sqlalchemy

2 Answers2