Here are the examples of the python api sqlalchemy.func.date_trunc taken from open source projects. By voting up you can indicate which examples are most useful and appropriate.
9 Examples
0
Source : fact_billing_dao.py
with MIT License
from cds-snc
with MIT License
from cds-snc
def fetch_monthly_billing_for_year(service_id, year):
year_start_date, year_end_date = get_financial_year(year)
utcnow = datetime.utcnow()
today = convert_utc_to_local_timezone(utcnow)
# if year end date is less than today, we are calculating for data in the past and have no need for deltas.
if year_end_date >= today:
yesterday = today - timedelta(days=1)
for day in [yesterday, today]:
data = fetch_billing_data_for_day(process_day=day, service_id=service_id)
for d in data:
update_fact_billing(data=d, process_day=day)
email_and_letters = (
db.session.query(
func.date_trunc("month", FactBilling.bst_date).cast(Date).label("month"),
func.sum(FactBilling.notifications_sent).label("notifications_sent"),
func.sum(FactBilling.notifications_sent).label("billable_units"),
FactBilling.rate.label("rate"),
FactBilling.notification_type.label("notification_type"),
FactBilling.postage,
)
.filter(
FactBilling.service_id == service_id,
FactBilling.bst_date >= year_start_date.strftime("%Y-%m-%d"),
FactBilling.bst_date < = year_end_date.strftime("%Y-%m-%d"),
FactBilling.notification_type.in_([EMAIL_TYPE, LETTER_TYPE]),
)
.group_by(
"month",
FactBilling.rate,
FactBilling.notification_type,
FactBilling.postage,
)
)
sms = (
db.session.query(
func.date_trunc("month", FactBilling.bst_date).cast(Date).label("month"),
func.sum(FactBilling.notifications_sent).label("notifications_sent"),
func.sum(FactBilling.billable_units * FactBilling.rate_multiplier).label("billable_units"),
FactBilling.rate,
FactBilling.notification_type,
FactBilling.postage,
)
.filter(
FactBilling.service_id == service_id,
FactBilling.bst_date >= year_start_date.strftime("%Y-%m-%d"),
FactBilling.bst_date < = year_end_date.strftime("%Y-%m-%d"),
FactBilling.notification_type == SMS_TYPE,
)
.group_by(
"month",
FactBilling.rate,
FactBilling.notification_type,
FactBilling.postage,
)
)
yearly_data = email_and_letters.union_all(sms).order_by("month", "notification_type", "rate").all()
return yearly_data
def delete_billing_data_for_service_for_day(process_day, service_id):
0
Source : fact_notification_status_dao.py
with MIT License
from cds-snc
with MIT License
from cds-snc
def fetch_notification_status_for_service_by_month(start_date, end_date, service_id):
return (
db.session.query(
func.date_trunc("month", FactNotificationStatus.bst_date).label("month"),
FactNotificationStatus.notification_type,
FactNotificationStatus.notification_status,
func.sum(FactNotificationStatus.notification_count).label("count"),
)
.filter(
FactNotificationStatus.service_id == service_id,
FactNotificationStatus.bst_date >= start_date.strftime("%Y-%m-%d"),
# This works only for timezones to the west of GMT
FactNotificationStatus.bst_date < end_date.strftime("%Y-%m-%d"),
FactNotificationStatus.key_type != KEY_TYPE_TEST,
)
.group_by(
func.date_trunc("month", FactNotificationStatus.bst_date).label("month"),
FactNotificationStatus.notification_type,
FactNotificationStatus.notification_status,
)
.all()
)
def fetch_delivered_notification_stats_by_month():
0
Source : fact_notification_status_dao.py
with MIT License
from cds-snc
with MIT License
from cds-snc
def fetch_delivered_notification_stats_by_month():
return (
db.session.query(
func.date_trunc("month", FactNotificationStatus.bst_date).cast(db.Text).label("month"),
FactNotificationStatus.notification_type,
func.sum(FactNotificationStatus.notification_count).label("count"),
)
.filter(
FactNotificationStatus.key_type != KEY_TYPE_TEST,
FactNotificationStatus.notification_status.in_([NOTIFICATION_DELIVERED, NOTIFICATION_SENT]),
FactNotificationStatus.bst_date >= "2019-11-01", # GC Notify start date
)
.group_by(
func.date_trunc("month", FactNotificationStatus.bst_date),
FactNotificationStatus.notification_type,
)
.order_by(
func.date_trunc("month", FactNotificationStatus.bst_date).desc(),
FactNotificationStatus.notification_type,
)
.all()
)
def fetch_notification_stats_for_trial_services():
0
Source : fact_notification_status_dao.py
with MIT License
from cds-snc
with MIT License
from cds-snc
def fetch_notification_stats_for_trial_services():
ServiceHistory = Service.get_history_model()
return (
db.session.query(
Service.id.label("service_id"),
Service.name.label("service_name"),
func.date_trunc("day", Service.created_at).cast(db.Text).label("creation_date"),
User.name.label("user_name"),
User.email_address.label("user_email"),
FactNotificationStatus.notification_type.label("notification_type"),
func.sum(FactNotificationStatus.notification_count).label("notification_sum"),
)
.join(
Service,
FactNotificationStatus.service_id == Service.id,
)
.join(
ServiceHistory,
Service.id == ServiceHistory.id,
)
.join(
User,
User.id == ServiceHistory.created_by_id,
)
.filter(
ServiceHistory.version == 1,
Service.restricted,
FactNotificationStatus.notification_status.in_([NOTIFICATION_DELIVERED, NOTIFICATION_SENT]),
)
.group_by(
Service.id,
Service.name,
Service.created_at,
User.name,
User.email_address,
FactNotificationStatus.notification_type,
)
.order_by(
Service.created_at,
)
.all()
)
def fetch_notification_status_for_service_for_day(bst_day, service_id):
0
Source : fact_notification_status_dao.py
with MIT License
from cds-snc
with MIT License
from cds-snc
def fetch_monthly_notification_statuses_per_service(start_date, end_date):
return (
db.session.query(
func.date_trunc("month", FactNotificationStatus.bst_date).cast(Date).label("date_created"),
Service.id.label("service_id"),
Service.name.label("service_name"),
FactNotificationStatus.notification_type,
func.sum(
case(
[
(
FactNotificationStatus.notification_status == NOTIFICATION_SENDING,
FactNotificationStatus.notification_count,
)
],
else_=0,
)
).label("count_sending"),
func.sum(
case(
[
(
FactNotificationStatus.notification_status == NOTIFICATION_DELIVERED,
FactNotificationStatus.notification_count,
)
],
else_=0,
)
).label("count_delivered"),
func.sum(
case(
[
(
FactNotificationStatus.notification_status.in_([NOTIFICATION_TECHNICAL_FAILURE, NOTIFICATION_FAILED]),
FactNotificationStatus.notification_count,
)
],
else_=0,
)
).label("count_technical_failure"),
func.sum(
case(
[
(
FactNotificationStatus.notification_status == NOTIFICATION_TEMPORARY_FAILURE,
FactNotificationStatus.notification_count,
)
],
else_=0,
)
).label("count_temporary_failure"),
func.sum(
case(
[
(
FactNotificationStatus.notification_status == NOTIFICATION_PERMANENT_FAILURE,
FactNotificationStatus.notification_count,
)
],
else_=0,
)
).label("count_permanent_failure"),
func.sum(
case(
[
(
FactNotificationStatus.notification_status == NOTIFICATION_SENT,
FactNotificationStatus.notification_count,
)
],
else_=0,
)
).label("count_sent"),
)
.join(Service, FactNotificationStatus.service_id == Service.id)
.filter(
FactNotificationStatus.notification_status != NOTIFICATION_CREATED,
Service.active.is_(True),
FactNotificationStatus.key_type != KEY_TYPE_TEST,
Service.research_mode.is_(False),
Service.restricted.is_(False),
FactNotificationStatus.bst_date >= start_date,
FactNotificationStatus.bst_date < = end_date,
)
.group_by(
Service.id,
Service.name,
func.date_trunc("month", FactNotificationStatus.bst_date).cast(Date),
FactNotificationStatus.notification_type,
)
.order_by(
func.date_trunc("month", FactNotificationStatus.bst_date).cast(Date),
Service.id,
FactNotificationStatus.notification_type,
)
.all()
)
0
Source : model_group.py
with MIT License
from jet-admin
with MIT License
from jet-admin
def get_query_lookup_func_by_name(session, lookup_type, lookup_param, column):
try:
if lookup_type == 'date':
date_group = lookup_param or 'day'
if get_session_engine(session) == 'postgresql':
if date_group in date_trunc_options:
return func.date_trunc(date_trunc_options[date_group], column)
elif get_session_engine(session) == 'mysql':
if date_group in strftime_options:
return func.date_format(column, strftime_options[date_group])
else:
if date_group in strftime_options:
return func.strftime(strftime_options[date_group], column)
except IndexError:
pass
if lookup_type:
print('Unsupported lookup: {}'.format(lookup_type))
return column
class ModelGroupFilter(CharFilter):
0
Source : plot.py
with MIT License
from Nukesor
with MIT License
from Nukesor
def get_vote_activity(session):
"""Create a plot showing the inline usage statistics."""
creation_date = func.date_trunc("day", Vote.created_at).label("creation_date")
votes = (
session.query(creation_date, func.count(Vote.id).label("count"))
.group_by(creation_date)
.order_by(creation_date)
.all()
)
total_votes = [("Total votes", q[0], q[1]) for q in votes]
# Grid style
plt.style.use("seaborn-whitegrid")
# Combine the results in a single dataframe and name the columns
dataframe = pandas.DataFrame(total_votes, columns=["type", "date", "votes"])
months = mdates.MonthLocator() # every month
months_fmt = mdates.DateFormatter("%Y-%m")
max_value = max([vote[2] for vote in total_votes])
magnitude = get_magnitude(max_value)
# Plot each result set
fig, ax = plt.subplots(figsize=(30, 15), dpi=120)
for key, group in dataframe.groupby(["type"]):
ax = group.plot(ax=ax, kind="bar", x="date", y="votes", label=key)
ax.xaxis.set_major_locator(months)
ax.xaxis.set_major_formatter(months_fmt)
ax.yaxis.set_ticks(np.arange(0, max_value, math.pow(10, magnitude - 1)))
image = image_from_figure(fig)
image.name = "vote_statistics.png"
return image
def get_new_activity(session):
0
Source : plot.py
with MIT License
from Nukesor
with MIT License
from Nukesor
def get_new_activity(session):
"""Create a plot showing new users and polls per day."""
creation_date = func.date_trunc("week", DailyStatistic.date).label("creation_date")
stats = (
session.query(
creation_date,
func.avg(DailyStatistic.created_polls).label("new_polls"),
func.avg(DailyStatistic.new_users).label("new_users"),
)
.group_by(creation_date)
.order_by(creation_date)
.all()
)
new_polls = [("New polls", stat[0], float(stat[1])) for stat in stats]
new_users = [("New users", stat[0], float(stat[2])) for stat in stats]
# Grid style
plt.style.use("seaborn-whitegrid")
combined = new_polls + new_users
# Combine the results in a single dataframe and name the columns
dataframe = pandas.DataFrame(combined, columns=["type", "date", "count"])
months = mdates.MonthLocator() # every month
months_fmt = mdates.DateFormatter("%Y-%m")
max_value = max([vote[2] for vote in combined])
magnitude = get_magnitude(max_value)
# Plot each result set
fig, ax = plt.subplots(figsize=(30, 15), dpi=120)
for key, group in dataframe.groupby(["type"]):
ax = group.plot(ax=ax, kind="line", x="date", y="count", label=key)
ax.xaxis.set_major_locator(months)
ax.xaxis.set_major_formatter(months_fmt)
ax.yaxis.set_ticks(np.arange(0, max_value, math.pow(10, magnitude - 1)))
image = image_from_figure(fig)
image.name = "new_statistics.png"
return image
0
Source : period.py
with MIT License
from OneGov
with MIT License
from OneGov
def conflicting_activities(self):
if not isinstance(self.model, Period):
return None
session = self.request.session
mindate = self.execution_start.data
maxdate = self.execution_end.data
if not (mindate and maxdate):
return None
# turn naive utc to aware utc to local timezone
start = OccasionDate.start.op('AT TIME ZONE')(literal('UTC'))
start = start.op('AT TIME ZONE')(OccasionDate.timezone)
end = OccasionDate.end.op('AT TIME ZONE')(literal('UTC'))
end = end.op('AT TIME ZONE')(OccasionDate.timezone)
qd = session.query(OccasionDate)
qd = qd.with_entities(OccasionDate.occasion_id)
qd = qd.filter(or_(
func.date_trunc('day', start) < mindate,
func.date_trunc('day', start) > maxdate,
func.date_trunc('day', end) < mindate,
func.date_trunc('day', end) > maxdate
))
q = session.query(OccasionDate).join(Occasion)
q = q.with_entities(distinct(Occasion.activity_id))
q = q.filter(Occasion.period == self.model)
q = q.filter(Occasion.id.in_(qd.subquery()))
return tuple(
session.query(Activity).filter(Activity.id.in_(q.subquery()))
)
def ensure_dependant_fields_empty(self):