sqlalchemy.func.date_trunc

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 7

0 Source : fact_billing_dao.py
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

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

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

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

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

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

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

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

    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):