时间戳

首页 > 解决方案 > 如何及时获取特定日期的记录？

sql - 如何及时获取特定日期的记录？

问题描述

编辑：2020-07-08 15:09，我解决了它，虽然它不是最佳解决方案，也不是最简洁纤薄的解决方案，但它也可以满足我的需要。所以感谢那些花时间评论和回答的人！

所以我有一个查询，我想输入一个日期，然后获取在该日期“活动”的记录。我目前只使用当前日期，所以它非常静态。但是如何让它更具动态性，以便我可以像我所说的那样及时选择任何日期，并获取该数据？你有什么建议？

我在 pgadmin 4 中使用 postgresql 9.6

如果这有任何帮助，请不要现在，但这是我的查询.....

SELECT due.contract_id,due.component,due.due_not_paid, due.current_arrears_days,ob.outstandingamount, due.record_valid_to
FROM
    (SELECT a.record_valid_to, a.contract_id,a.component, sum(a.invoice_non_paid_waivedamount) as due_not_paid,max(no_latedays) as Current_arrears_days
    FROM
        (SELECT 
        sub.contract_id,sub.record_valid_to,sub.invoice_generation_date,sub.invoice_duedate,sub.invoice_paiddate,
        sub.invoice_dueamount,sub.invoice_paid_waivedamount,sub.invoice_non_paid_waivedamount,sub.component,
        CASE WHEN sub.invoice_dueamount = 0 THEN 0 ELSE sub.latedays END as no_latedays,
        CASE WHEN sub.invoice_non_paid_waivedamount > 0 AND invoice_duedate < current_date THEN 'DUE' ELSE 'PAID or non-due' END AS Paid_status
        FROM
            (select contract_id,record_valid_from,record_valid_to,invoice_generation_date,invoice_duedate,invoice_paiddate, invoice_dueamount,invoice_paid_waivedamount,
            CASE WHEN invoice_paid_waivedamount>0  AND (invoice_dueamount=invoice_paid_waivedamount) THEN (invoice_paiddate-invoice_duedate) ELSE (current_date-invoice_duedate) END as latedays,
            (invoice_dueamount-invoice_paid_waivedamount) as invoice_non_paid_waivedamount,component
            from dwd_tb_contracts_invoices where record_valid_to='9999-12-31' 
            ORDER BY invoice_duedate) 
         sub) a
    WHERE a.Paid_status = 'DUE'
    GROUP BY a.record_valid_to, a.contract_id, a.component) due
LEFT OUTER JOIN
    (select * from exposure_split('2020-07-06')) ob 
    ON due.contract_id = ob.contract_id
    where due.component = 'INTEREST_INTERMEDIATE'
or due.component = 'PRINCIPAL'
or due.component = 'INTEREST'
or due.component = 'INTEREST_DIFFERENCE'
    order by contract_id

标签： sqlpostgresqlpostgresql-9.6

解决方案

如果您说的是插入时间，那么您可以创建一个函数过程，将当前日期和时间自动设置为该表的单元格，并且不允许直接访问它，然后直接查询它。如果涉及访问行或表，也可以这样做。

我不是很喜欢 SQL，我也没有代表。发表评论，但如果可以编写相同的代码，请告诉我......这样我就可以安全地删除答案。

推荐阅读