如何用一条记录减去日期oracle

发布于 2024-10-19 00:24:30 字数 244 浏览 3 评论 0原文

我有一个表格，其中有一列采用日期格式。我想计算两条记录之间的时间差，你能帮我吗？我需要 SQL 或 PL/SQL 查询。

ORDER_ID;ORDER_STATUS_ID;ORDER_DATE
8296;16;22-2-2011 13:56:31
8295;22;22-2-2011 13:07:15

查询结果应为 00:49:45 或接近此值。

你能帮忙吗？谢谢！

原文

I have a table with one column in the date format. I want to calculate the time difference between two records, can you help me? I need a query in SQL or PL/SQL.

ORDER_ID;ORDER_STATUS_ID;ORDER_DATE
8296;16;22-2-2011 13:56:31
8295;22;22-2-2011 13:07:15

The query should result in 00:49:45 or close to this.

Can you help? Thx!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

那一片橙海， 2024-10-26 00:24:30

问题不在于你想要如何计算它，而在于你想要如何显示它。日期算术很简单：

select date2 - date1 from
    (select to_Date('22-2-2011 13:07:15','dd-mm-yyyy hh24:mi:ss') as date1,
            to_Date('22-2-2011 13:56:31','dd-mm-yyyy hh24:mi:ss') as date2
     from dual);

给出答案，但它是一个小数。那么如何将其转化为视觉显示呢？

select to_char(trunc(sysdate) + mod(date2 - date1,1),'hh24:mi:ss') from
    (select to_Date('22-2-2011 13:07:15','dd-mm-yyyy hh24:mi:ss') as date1,
            to_Date('22-2-2011 13:56:31','dd-mm-yyyy hh24:mi:ss') as date2
     from dual);

好的，现在格式已经很好了。

哦，除了如果你真的想存储间隔怎么办？或者，如果间隔超过一天，那么您需要以不同的方式格式化它。

那么 - 您是否需要实际间隔用于其他目的？或者您只想以给定的格式显示间隔？根据您的需要将指导您的答案。

但间隔本身很容易。那只是减法。在你的情况下

select yt2.order_Date - yt1.order_date
from yourtable yt1, yourtable yt2
where yt1.order_id = 8295
and   yt2.order_id = 8296;

The question isn't really how you want to calculate it - it is how you want it shown. date arithmetic is simple:

select date2 - date1 from
    (select to_Date('22-2-2011 13:07:15','dd-mm-yyyy hh24:mi:ss') as date1,
            to_Date('22-2-2011 13:56:31','dd-mm-yyyy hh24:mi:ss') as date2
     from dual);

gives you the answer, but it is a decimal. So how to translate that into your visual display?

select to_char(trunc(sysdate) + mod(date2 - date1,1),'hh24:mi:ss') from
    (select to_Date('22-2-2011 13:07:15','dd-mm-yyyy hh24:mi:ss') as date1,
            to_Date('22-2-2011 13:56:31','dd-mm-yyyy hh24:mi:ss') as date2
     from dual);

OK, now this is nicely formatted.

Oh, except what if you actually want to store the interval? Or if the interval is more than one day then you need to format it differently.

So - is it that you need the actual interval for other purposes? Or that you just want to display the interval in a given format? Depending on what you need will guide your answer.

But the interval itself is easy. That is just subtraction. In your case

select yt2.order_Date - yt1.order_date
from yourtable yt1, yourtable yt2
where yt1.order_id = 8295
and   yt2.order_id = 8296;

回复收藏 0 原文

糖果控 2024-10-26 00:24:30

您可能会发现分析函数很有帮助。

SELECT order_id,
       order_date,
       order_date - LAG(order_date) OVER (ORDER BY order_id) time_since_last_order
  FROM order_table
  WHERE order_id IN (8295,8296) -- or whatever set of orders you're really interested in
  ORDER BY order_id;

这避免了您需要连接两行的自连接。

除此之外，迈克尔的回答很好地涵盖了这一点。

You might find an analytic function helpful.

SELECT order_id,
       order_date,
       order_date - LAG(order_date) OVER (ORDER BY order_id) time_since_last_order
  FROM order_table
  WHERE order_id IN (8295,8296) -- or whatever set of orders you're really interested in
  ORDER BY order_id;

This avoids the self-join you would otherwise need to connect the two rows.

Aside from that, Michael's answer covers it well.

回复收藏 0 原文