获取两个日期列之间的月，日差 [英] Get month,days difference between two date columns

问题描述

我正在尝试解决数据库中的某些问题，并且我想根据其他2个日期列重新计算数据库中的列。这个col是浮动的，我想以月为单位，以小数点表示的月份中两个日期之间的差额。

例如，如果我有2个日期'2016-01-15'，'2015-02-01'差异应该是12个月差异中最好的12.5，其余15天最好为0.5

这是我到目前为止根据搜索尝试的结果，但我认为我有丢失，因为它告诉我日期列存在错误，因为它不存在

 选择EXTRACT（year FROM vehicle_delivery（date，vehicle_received_date））* 12 + EXTRACT（从vehicle_delivery起的月份（date，vehicle_received_date））; 
  

其中 vehicle_delivery 是我的表名& 日期是我的结束日期， vehicle_received_date 是我的开始日期

使用此sql可以使事情变得幸福：从vehicle_delivery）+提取（'days from vehicle_delivery）/ 30
from（select age（date :: timestamp，vehicle_received_date :: timestamp））a;

解决方案

SQL应该看起来像这样：

 选择提取物（比较中的年份）* 12 +提取物（比较中的月份）+提取物（比较中的日期）/ 30 
 （选择age（date :: timestamp，vehicle_received_date :: timestamp）作为vehicle_delivery 
的diff 
）vd; 
  

我不知道 / 30 是，但是您似乎想要它。

注意：

• FROM 子句引用该表。


• extract（）是关键字，而不是字符串。


• 您要引用<$ c $中的 age（）值c> extract（）。


• extract（）返回一个间隔，因此它对于取出零件（仅在您希望将它们放在单独的列中时才需要）。

I'm trying to fix some problems in my database and i want to re-calculate column in my db based on other 2 date columns. This col is float and i want to get the difference between 2 dates in months with decimal point for days.

For example if i have 2 dates '2016-01-15', '2015-02-01' the difference should be 12.5 best of 12 months differences and 0.5 for the remaining 15 days

Here is what i tried so far based on my searches but i think there is something i'm missing as it tells me there is an error with my date col as it doesn't exist

Select EXTRACT(year FROM vehicle_delivery(date, vehicle_received_date))*12 + EXTRACT(month FROM vehicle_delivery(date, vehicle_received_date));

Where vehicle_delivery is my table name & date is my end date and vehicle_received_date is my start date

same thing happes with this sql :

select extract('years' from vehicle_delivery) * 12 + extract('months' from vehicle_delivery) + extract('days' from vehicle_delivery) / 30
from (select age(date::timestamp, vehicle_received_date::timestamp)) a;

解决方案

The SQL should look like this:

select extract(year from diff) * 12 + extract(month from diff) + extract(day from diff) / 30
from (select age(date::timestamp, vehicle_received_date::timestamp) as diff
      from vehicle_delivery
     ) vd;

I don't know what the purpose of the / 30 is, but you appear to want it.

Notes:

• The FROM clause references the table.
• The first argument in extract() is a keyword, not a string.
• You want to reference the age() value in the extract().
• extract() returns an interval, so it is rather redundant to take out the parts (only needed if you want them in separate columns).

这篇关于获取两个日期列之间的月，日差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！