使用EPOCH时间为时间戳获取1分钟内的记录

Question

从test1中选择(Sold_date-to_date('1970-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss'))*86400作为epoch_sold_date；

Answer 1

什么是sold_date？对于例如sysdate（返回datedatatype的函数），代码工作正常。

SQL> select (sysdate
  2          - to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')
  3         ) * 86400 as epoch_sold_date
  4  from dual;

EPOCH_SOLD_DATE
---------------
     1600807918

SQL>

由于sold_date是一个时间戳，但是--您似乎对一秒钟的分数不感兴趣或不感兴趣，因此将其强制转换为date:

select (cast (systimestamp as date)                                --> this
        - to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')
       ) * 86400 as epoch_sold_date 
from dual;

如果您对所有行都得到相同的结果：嗯，我没有，如果sold_date不同，您也不应该这样做。

SQL> with test (sold_date) as
  2    (select timestamp '2020-09-22 00:00:00.000000' from dual union all
  3     select timestamp '2015-03-18 00:00:00.000000' from dual
  4    )
  5  select sold_date,
  6         (cast (sold_date as date)
  7          - to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')
  8         ) * 86400 as epoch_sold_date
  9  from test;

SOLD_DATE                      EPOCH_SOLD_DATE
------------------------------ ---------------
22.09.20 00:00:00,000000000         1600732800
18.03.15 00:00:00,000000000         1426636800

SQL>

SQL> with test (sold_date) as
  2    (select timestamp '2020-09-22 10:15:00.000000' from dual union all
  3     select timestamp '2015-03-18 08:05:00.000000' from dual
  4    )
  5  select sold_date,
  6         lead(sold_date) over (order by sold_date) next_sold_date,
  7         --
  8         lead(sold_date) over (order by sold_date) - sold_date diff,
  9         --
 10         extract(day from lead(sold_date) over (order by sold_date) - sold_date) diff_mins
 11  from test
 12  order by sold_date;

SOLD_DATE                      NEXT_SOLD_DATE                 DIFF                            DIFF_MINS
------------------------------ ------------------------------ ------------------------------ ----------
18.03.15 08:05:00,000000000    22.09.20 10:15:00,000000000    +000002015 02:10:00.000000000        2015
22.09.20 10:15:00,000000000

SQL>

null

SQL> with test (date_1, date_2) as
  2    (select timestamp '2020-09-22 10:15:00.000000',
  3            timestamp '2020-09-22 08:05:00.000000' from dual
  4    )
  5  select (cast(date_1 as date) - cast(date_2 as date)) * 24 * 60 diff_minutes
  6  from test;

DIFF_MINUTES
------------
         130

SQL>