ORACLE EST GMT日期问题

Question

我需要获取按日期分组的库存计数。我使用了以下查询。我的问题是，查找数据的应用程序是EST，而数据库运行在GMT时区。因此，当我在应用程序中选择一个特定的日期时，它需要获取相应的GMT日期和计数。目前，im使用的查询是

SELECT tble.dte, COUNT(DISTINCT INVENTORY_ID)As cmt 
  FROM ATL_GROUNDING_INFO groundinginfo 
       right join (SELECT 
                   TO_DATE('03/11/2015 04:00:00','MM/DD/YYYY HH24:MI:SS') - 1 + rownum dte 
                     FROM DUAL 
                  CONNECT BY ROWNUM < 366 
                      AND 
         TO_DATE('03/11/2015 04:00:00','MM/DD/YYYY HH24:MI:SS') - 1 + rownum <= 
                         TO_DATE('04/02/2015 03:59:59','MM/DD/YYYY HH24:MI:SS')
                  ) tble 
              ON tble.dte = (groundinginfo.DATE_TURNED_IN) 
 group by tble.dte
 order by tble.dte desc`

但在这种情况下，计数是不正确的，因为美国东部夏令时11号上午04:00:00到格林威治时间12 3月03:59:59。因此，当我搜索3月11日时，我需要从数据库中获取上述日期范围内的数据。请协助

Answer 1

假设EST是你的SESSIONTIMEZONE，你可以这样做：

WITH t AS
    (SELECT TIMESTAMP '2015-04-02 04:00:00' + ROWNUM * INTERVAL '1' DAY AS DT
    FROM dual
    CONNECT BY TIMESTAMP '2015-04-02 04:00:00' + ROWNUM * INTERVAL '1' DAY <= TIMESTAMP '2015-11-03 04:00:00')
SELECT tble.dte, COUNT(DISTINCT INVENTORY_ID)As cmt 
FROM ATL_GROUNDING_INFO groundinginfo 
   right join t ON FROM_TZ(CAST(dte AS TIMESTAMP), 'UTC') = DT

或者更明确地说：

WITH t AS
    (SELECT TIMESTAMP '2015-04-02 04:00:00 EST' + ROWNUM * INTERVAL '1' DAY AS DT
    FROM dual
    CONNECT BY TIMESTAMP '2015-04-02 04:00:00 EST' + ROWNUM * INTERVAL '1' DAY <= TIMESTAMP '2015-11-03 04:00:00 EST')
SELECT tble.dte, COUNT(DISTINCT INVENTORY_ID)As cmt 
FROM ATL_GROUNDING_INFO groundinginfo 
   right join t ON FROM_TZ(CAST(dte AS TIMESTAMP), 'UTC') = DT