Impala 日期减去时间戳并得到等效天数的结果，无论小时或年份或天或秒的差异
我想减去黑斑羚中的两个日期。我知道impala中有一个datediff函数，但是如果有两个时间戳值如何处理它，比如考虑这种情况：
select to_date('2022-01-01 15-05-53','yyyy-mm-dd HH24-mi-ss')-to_date('2022-01-01 15-04-53','yyyy-mm-dd HH24-mi-ss') from dual;
有1分钟的差异，oracle会将结果作为0.000694444天。
我的要求是，impala 中是否有任何此类功能，我可以以“yyyy-mm-dd HH24-mi-ss”的方式减去两个时间戳值，并在等效天内获得结果，无论如果天、年、小时、分钟或秒存在差异。任何差异都应反映在同等天数中。
我也愿意以任何其他方式实现同样的目标。
先感谢您。
I want to subtract two date in impala. I know there is a datediff funciton in impala but if there is two timestamp value how to deal with it, like consider this situation:
select to_date('2022-01-01 15-05-53','yyyy-mm-dd HH24-mi-ss')-to_date('2022-01-01 15-04-53','yyyy-mm-dd HH24-mi-ss') from dual;
There is 1 minute difference and oracle would put the result as 0.000694444 days.
My requirement is if there is any such functionality in impala where I can subtract two timestamp value in the manner 'yyyy-mm-dd HH24-mi-ss', and get the result in equivalent days irrespective of if there is difference in days , year, hours, minute or seconds. Any difference should reflect in equivalent number of days.
Any other way where I can achieve the same thing, I am open to that as well.
Thank you in advance.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您可以使用
unix_timestamp(timestamp)将这两个字段转换为 unixtime (int) 格式。这实际上是 1970-01-01 以来的秒数，非常适合计算以秒为单位的日期时间差异。一旦您拥有 1970-01-01 的秒数，您就可以轻松地减去它们以了解差异。你的sql应该是这样的 -
一旦你有秒数的差异，你可以轻松地将它们转换为分钟/小时/天 - 无论你想要什么格式。
You can use
unix_timestamp(timestamp)to convert both fields to unixtime (int) format. This is actually seconds from 1970-01-01 and very suitable to calculate date time differences in seconds. Once you have seconds from 1970-01-01, you can easily minus them both to know the differences.Your sql should be like this -
Once youhave difference in seconds, you can easily convert them to minutes/hours/days - whatever format you want it.