def test_extract(self):
t = sql.table("t", sql.column("col1"))
for field in "year", "month", "day":
self.assert_compile(
select([extract(field, t.c.col1)]),
"SELECT EXTRACT(%s FROM t.col1) AS anon_1 FROM t" % field,
)
# millsecondS to millisecond
self.assert_compile(
select([extract("milliseconds", t.c.col1)]),
"SELECT EXTRACT(millisecond FROM t.col1) AS anon_1 FROM t",
)
def _test(self, expr, field="all", overrides=None):
t = self.tables.t
if field == "all":
fields = {
"year": 2012,
"month": 5,
"day": 10,
"epoch": 1336652125.0,
"hour": 12,
"minute": 15,
}
elif field == "time":
fields = {"hour": 12, "minute": 15, "second": 25}
elif field == "date":
fields = {"year": 2012, "month": 5, "day": 10}
elif field == "all+tz":
fields = {
"year": 2012,
"month": 5,
"day": 10,
"epoch": 1336637725.0,
"hour": 8,
"timezone": 0,
}
else:
fields = field
if overrides:
fields.update(overrides)
for field in fields:
result = self.bind.scalar(
select([extract(field, expr)]).select_from(t))
eq_(result, fields[field])
def test_extract(self):
t = table("t", column("col1"))
for field in "day", "month", "year":
self.assert_compile(
select([extract(field, t.c.col1)]),
"SELECT DATEPART(%s, t.col1) AS anon_1 FROM t" % field,
)
def test_extract_expression(self):
meta = MetaData(testing.db)
table = Table("test", meta, Column("dt", DateTime), Column("d", Date))
meta.create_all()
try:
table.insert().execute(
{
"dt": datetime.datetime(2010, 5, 1, 12, 11, 10),
"d": datetime.date(2010, 5, 1),
}
)
rs = select(
[extract("year", table.c.dt), extract("month", table.c.d)]
).execute()
row = rs.first()
assert row[0] == 2010
assert row[1] == 5
rs.close()
finally:
meta.drop_all()
def test_extract(self):
from sqlalchemy_1_3 import extract
fivedaysago = datetime.datetime.now() - datetime.timedelta(days=5)
for field, exp in (
("year", fivedaysago.year),
("month", fivedaysago.month),
("day", fivedaysago.day),
):
r = testing.db.execute(select([extract(field,
fivedaysago)])).scalar()
eq_(r, exp)
def test_extract(self):
fivedaysago = testing.db.scalar(
select([func.now()])
) - datetime.timedelta(days=5)
for field, exp in (
("year", fivedaysago.year),
("month", fivedaysago.month),
("day", fivedaysago.day),
):
r = testing.db.execute(
select(
[extract(field, func.now() + datetime.timedelta(days=-5))]
)
).scalar()
eq_(r, exp)
def test_extract(self):
t = sql.table("t", sql.column("col1"))
mapping = {
"day": "day",
"doy": "dayofyear",
"dow": "weekday",
"milliseconds": "millisecond",
"millisecond": "millisecond",
"year": "year",
}
for field, subst in list(mapping.items()):
self.assert_compile(
select([extract(field, t.c.col1)]),
'SELECT DATEPART("%s", t.col1) AS anon_1 FROM t' % subst,
)
def execute(field):
return testing.db.execute(select([extract(field, date)])).scalar()