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)
