自动更新用户定义报表(SQL Developer)中的时间戳值

Question

我在Oracle SQL Developer中定义了一个用户定义报告，每月运行以下查询：

SELECT TO_CHAR(SUMMARY_TIMESTAMP,'YYYY-MM-DD') AS day, Count(ID) AS row_count
FROM S_SEARCH_SUMMARIES 
WHERE SUMMARY_TIMESTAMP > '01-Nov-13' AND SUMMARY_TIMESTAMP < '01-Dec-13' 
AND SOURCE_INSTITUTION = 'My Institution' 
GROUP BY TO_CHAR(SUMMARY_TIMESTAMP,'YYYY-MM-DD')
ORDER BY day DESC

有没有办法在当月的基础上自动更新SUMMARY_TIMESTAMP，而不是手动更改？

Answer 1

也许有一种更干净的方法可以做到这一点，但有一个想法是在查询中添加这样的东西：

    WITH dates
     AS (SELECT TO_DATE (
                   EXTRACT (YEAR FROM SYSDATE) || '-' || EXTRACT (MONTH FROM
                   SYSDATE) || '-01',
                   'YYYY-MM-DD')
                   DT_END,
                TO_DATE ( (EXTRACT (YEAR FROM ADD_MONTHS (SYSDATE, -1))) ||
                         '-' || EXTRACT (MONTH FROM ADD_MONTHS (SYSDATE, -1))
                         || '-01',
                         'YY-MM-DD')
                   DT_START
           FROM DUAL)
  SELECT TO_CHAR (SUMMARY_TIMESTAMP, 'YYYY-MM-DD') AS day,
         COUNT (ID) AS row_count
    FROM S_SEARCH_SUMMARIES, dates
   WHERE     SUMMARY_TIMESTAMP > dates.DT_START
         AND SUMMARY_TIMESTAMP < dates.DT_END
         AND SOURCE_INSTITUTION = 'My Institution'
GROUP BY TO_CHAR (SUMMARY_TIMESTAMP, 'YYYY-MM-DD')
ORDER BY day DESC