def test_extract_join(self): q = Q.from_(self.t).join(self.t2).on(self.t.id == self.t2.t_id).select( fn.Extract(DatePart.year, self.t.foo)) self.assertEqual( 'SELECT EXTRACT(YEAR FROM "abc"."foo") FROM "abc" ' 'JOIN "efg" ON "abc"."id"="efg"."t_id"', str(q))
def __init__(self, timeframe, table, name): self.sql = None field = Field(name, table=table) if timeframe == "date*": alias = "{}_date".format(field.get_sql().replace("_", "")) self.sql = fn.Date(field, alias=alias) elif timeframe == "month*": alias = "{}_month".format(field.get_sql().replace("_", "")) self.sql = fn.ToChar(DateTrunc("month", field), "YYYY-MM", alias=alias) elif timeframe == "week*": alias = "{}_week".format(field.get_sql().replace("_", "")) self.sql = fn.ToChar(DateTrunc("week", field), "YYYY-MM-DD", alias=alias) elif timeframe == "year*": alias = "{}_year".format(field.get_sql().replace("_", "")) self.sql = fn.Extract("YEAR", field, alias=alias)
def _test_extract_datepart(self, date_part): q = Q.from_(self.t).select(fn.Extract(date_part, self.t.foo)) self.assertEqual( "SELECT EXTRACT(%s FROM \"foo\") FROM \"abc\"" % date_part.value, str(q))
def sql(self): return fn.Cast( fn.Extract(self.period["label"], self.field.sql), "INTEGER", alias=self.alias, )
def _test_extract_datepart(self, date_part): q = Q.from_(self.t).select(fn.Extract(date_part, self.t.foo)) value = getattr(date_part, 'value', date_part) self.assertEqual('SELECT EXTRACT(%s FROM "foo") FROM "abc"' % value, str(q))
def sql(self): return fn.Extract(self.period["label"], self.field.sql, alias=self.alias)