Python func.date_trunc方法代码示例

# 需要导入模块: from sqlalchemy import func [as 别名]
# 或者: from sqlalchemy.func import date_trunc [as 别名]
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,
        FactNotificationStatus.bst_date < end_date,
        FactNotificationStatus.key_type != KEY_TYPE_TEST
    ).group_by(
        func.date_trunc('month', FactNotificationStatus.bst_date).label('month'),
        FactNotificationStatus.notification_type,
        FactNotificationStatus.notification_status
    ).all() 

# 需要导入模块: from sqlalchemy import func [as 别名]
# 或者: from sqlalchemy.func import date_trunc [as 别名]
def api_private_runs_by_month():
    # The query takes ~6s on local SSD @ AMS on 2018-04-04.
    # It was taking ~20s when it was fetching all the table from DB and doing grouping locally.
    # TODO: use-count-table
    # FIXME: support fastpath
    now = datetime.now()
    end_date = datetime(now.year, now.month, 1)
    start_date = end_date - relativedelta(months=24)
    rawsql = """SELECT
        date_trunc('month', report.test_start_time) AS test_start_month,
        count(*) AS count_1
        FROM report
        WHERE report.test_start_time >= :start_date
        AND report.test_start_time < :end_date
        GROUP BY test_start_month
    """
    params = dict(start_date=start_date, end_date=end_date)
    q = current_app.db_session.execute(rawsql, params)
    delta = relativedelta(months=+1, days=-1)
    result = [
        {"date": (bkt + delta).strftime("%Y-%m-%d"), "value": value}
        for bkt, value in sorted(q.fetchall())
    ]
    return jsonify(result) 

# 需要导入模块: from sqlalchemy import func [as 别名]
# 或者: from sqlalchemy.func import date_trunc [as 别名]
def get_london_month_from_utc_column(column):
    """
     Where queries need to count notifications by month it needs to be
     the month in BST (British Summer Time).
     The database stores all timestamps as UTC without the timezone.
      - First set the timezone on created_at to UTC
      - then convert the timezone to BST (or Europe/London)
      - lastly truncate the datetime to month with which we can group
        queries
    """
    return func.date_trunc(
        "month",
        func.timezone("Europe/London", func.timezone("UTC", column))
    ) 

# 需要导入模块: from sqlalchemy import func [as 别名]
# 或者: from sqlalchemy.func import date_trunc [as 别名]
def _get_printing_day(created_at):
    return func.date_trunc(
        'day',
        func.timezone('Europe/London', func.timezone('UTC', created_at)) + text(
            # We add 6 hours 30 minutes to the local created_at time so that
            # any letters created after 5:30pm get shifted into the next day
            "interval '6 hours 30 minutes'"
        )
    ) 

# 需要导入模块: from sqlalchemy import func [as 别名]
# 或者: from sqlalchemy.func import date_trunc [as 别名]
def api_private_stats_by_month(orm_stat):
    # data for https://api.ooni.io/stats
    # Report.test_start_time protection against time travellers may be
    # implemented in a better way, but that sanity check is probably enough.
    # ooni_epoch = datetime(2012, 12, 1)

    now = datetime.now()
    end_date = datetime(now.year, now.month, 1)
    start_date = end_date - relativedelta(months=24)

    s = (
        select(
            [
                func.date_trunc("month", sql.text("bucket_date")).label("bucket_month"),
                sql.text(orm_stat),
            ]
        )
        .where(
            and_(
                sql.text("bucket_date > :start_date"),
                sql.text("bucket_date < :end_date"),
            )
        )
        .group_by("bucket_month")
        .select_from(sql.table("ooexpl_bucket_msm_count"))
    )

    r = current_app.db_session.execute(
        s, {"start_date": start_date, "end_date": end_date}
    )
    result = [
        {
            "date": (bkt + relativedelta(months=+1, days=-1)).strftime("%Y-%m-%d"),
            "value": value,
        }
        for bkt, value in sorted(r)
    ]
    return result 

# 需要导入模块: from sqlalchemy import func [as 别名]
# 或者: from sqlalchemy.func import date_trunc [as 别名]
def api_private_reports_per_day():
    # TODO: use-count-table
    # FIXME: support fastpath
    rawsql = """SELECT
        count(date_trunc('day', report.test_start_time)) AS count_1,
        date_trunc('day', report.test_start_time) AS date_trunc_2
        FROM report
        GROUP BY date_trunc('day', report.test_start_time)
        ORDER BY date_trunc('day', report.test_start_time)
    """
    q = current_app.db_session.execute(rawsql)
    result = [{"count": count, "date": date.strftime("%Y-%m-%d")} for count, date in q]
    return jsonify(result) 

# 需要导入模块: from sqlalchemy import func [as 别名]
# 或者: from sqlalchemy.func import date_trunc [as 别名]
def get_where(current_sel):
        return (current_sel
                .where(SalesOrder.order_date > func.date_trunc('week', func.current_date()) - text("INTERVAL '7 DAYS'"))
                .where(SalesOrder.order_date < func.date_trunc('week', func.current_date()))) 

# 需要导入模块: from sqlalchemy import func [as 别名]
# 或者: from sqlalchemy.func import date_trunc [as 别名]
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_number = max([vote[2] for vote in total_votes])
    # 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_number, 100))

    image = image_from_figure(fig)
    image.name = "vote_statistics.png"
    return image 

# 需要导入模块: from sqlalchemy import func [as 别名]
# 或者: from sqlalchemy.func import date_trunc [as 别名]
def get_bad_replicas_summary(rse_expression=None, from_date=None, to_date=None, session=None):
    """
    List the bad file replicas summary. Method used by the rucio-ui.
    :param rse_expression: The RSE expression.
    :param from_date: The start date.
    :param to_date: The end date.
    :param session: The database session in use.
    """
    result = []
    incidents = {}
    rse_clause = []
    if rse_expression:
        for rse in parse_expression(expression=rse_expression, session=session):
            rse_clause.append(models.BadReplicas.rse_id == rse['id'])

    if session.bind.dialect.name == 'oracle':
        to_days = func.trunc(models.BadReplicas.created_at, str('DD'))
    elif session.bind.dialect.name == 'mysql':
        to_days = func.date(models.BadReplicas.created_at)
    elif session.bind.dialect.name == 'postgresql':
        to_days = func.date_trunc('day', models.BadReplicas.created_at)
    else:
        to_days = func.strftime(models.BadReplicas.created_at, '%Y-%m-%d')
    query = session.query(func.count(), to_days, models.BadReplicas.rse_id, models.BadReplicas.state, models.BadReplicas.reason)
    # To be added : HINTS
    if rse_clause != []:
        query = query.filter(or_(*rse_clause))
    if from_date:
        query = query.filter(models.BadReplicas.created_at > from_date)
    if to_date:
        query = query.filter(models.BadReplicas.created_at < to_date)
    summary = query.group_by(to_days, models.BadReplicas.rse_id, models.BadReplicas.reason, models.BadReplicas.state).all()
    for row in summary:
        if (row[2], row[1], row[4]) not in incidents:
            incidents[(row[2], row[1], row[4])] = {}
        incidents[(row[2], row[1], row[4])][str(row[3])] = row[0]

    for incident in incidents:
        res = incidents[incident]
        res['rse_id'] = incident[0]
        res['rse'] = get_rse_name(rse_id=incident[0], session=session)
        res['created_at'] = incident[1]
        res['reason'] = incident[2]
        result.append(res)
    return result 

# 需要导入模块: from sqlalchemy import func [as 别名]
# 或者: from sqlalchemy.func import date_trunc [as 别名]
def fetch_monthly_billing_for_year(service_id, year):
    year_start_datetime, year_end_datetime = get_financial_year(year)

    year_start_date = convert_utc_to_bst(year_start_datetime).date()
    year_end_date = convert_utc_to_bst(year_end_datetime).date()

    today = convert_utc_to_bst(datetime.utcnow()).date()
    # 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:
        data = fetch_billing_data_for_day(process_day=today, service_id=service_id, check_permissions=True)
        for d in data:
            update_fact_billing(data=d, process_day=today)

    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,
        FactBilling.bst_date <= year_end_date,
        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,
        FactBilling.bst_date <= year_end_date,
        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 

# 需要导入模块: from sqlalchemy import func [as 别名]
# 或者: from sqlalchemy.func import date_trunc [as 别名]
def api_private_website_stats():
    url = request.args.get("input")

    probe_cc = request.args.get("probe_cc")
    if probe_cc is None or len(probe_cc) != 2:
        raise BadRequest("missing probe_cc")

    probe_asn = request.args.get("probe_asn")
    if probe_asn is None:
        raise BadRequest("missing probe_asn")

    s = sql.text(
        """SELECT
d.test_day,
COALESCE(anomaly_count, 0) as anomaly_count,
COALESCE(confirmed_count, 0) as confirmed_count,
COALESCE(failure_count, 0) as failure_count,
COALESCE(total_count, 0) as total_count
FROM (
(
    SELECT
    date_trunc('day', (current_date - offs)) AS test_day
    FROM generate_series(0, 31, 1) AS offs
) d
LEFT OUTER JOIN
(
    SELECT
    test_day,
    SUM(anomaly_count) as anomaly_count,
    SUM(confirmed_count) as confirmed_count,
    SUM(failure_count) as failure_count,
    SUM(total_count) as total_count
    FROM ooexpl_wc_input_counts
    WHERE test_day >= current_date - interval '31 day'
    AND probe_cc =  :probe_cc
    AND probe_asn = :probe_asn
    AND input = :input
    GROUP BY test_day
) m
ON d.test_day = m.test_day
)
ORDER BY test_day;"""
    )

    results = []
    q = current_app.db_session.execute(
        s, {"probe_cc": probe_cc, "probe_asn": probe_asn, "input": url}
    )
    for test_day, anomaly_count, confirmed_count, failure_count, total_count in q:
        results.append(
            {
                "test_day": test_day,
                "anomaly_count": int(anomaly_count),
                "confirmed_count": int(confirmed_count),
                "failure_count": int(failure_count),
                "total_count": int(total_count),
            }
        )
    return jsonify({"results": results}) 

# 需要导入模块: from sqlalchemy import func [as 别名]
# 或者: from sqlalchemy.func import date_trunc [as 别名]
def api_private_im_stats():
    test_name = request.args.get("test_name")
    if not test_name or test_name not in TEST_GROUPS["im"]:
        raise BadRequest("invalid test_name")

    probe_cc = request.args.get("probe_cc")
    if probe_cc is None or len(probe_cc) != 2:
        raise BadRequest("missing probe_cc")

    probe_asn = request.args.get("probe_asn")
    if probe_asn is None:
        raise BadRequest("missing probe_asn")
    probe_asn = int(probe_asn.replace("AS", ""))

    s = sql.text(
        """SELECT COALESCE(count, 0), d.test_day
FROM (
(
	SELECT
	date_trunc('day', (current_date - offs)) AS test_day
	FROM generate_series(0, 31, 1) AS offs
) d
LEFT OUTER JOIN
(
	SELECT
	SUM(count) as count,
	test_day
	FROM ooexpl_daily_msm_count
	WHERE
    probe_cc = :probe_cc
    AND test_name = :test_name
    AND probe_asn = :probe_asn
	AND test_day >= current_date - interval '31 day'
	AND test_day < current_date
	GROUP BY test_day
) m
ON d.test_day = m.test_day
)
ORDER BY test_day;"""
    )

    query_params = {
        "probe_cc": probe_cc,
        "probe_asn": probe_asn,
        "test_name": test_name,
    }

    results = []
    q = current_app.db_session.execute(s, query_params)
    for count, test_day in q:
        results.append(
            {"test_day": test_day, "total_count": count, "anomaly_count": None}
        )

    return jsonify({"results": results})