Vertica: How to create a new column by subtracting two other columns?

I have a Vertica table named Start_End like:

|   name |    Started |      Ended |
------------------------------------
| Albert | 1970-01-16 | 1970-06-01 |
|  Barry | 1992-05-01 | 1992-07-14 |
|  Carol | 2001-03-16 | 2001-06-03 |

It has about 100,000 rows.

How can I use months_between to subtract Started from Ended?

The resulting table would look like:

|   name |    Started |      Ended | Month_diff |
-------------------------------------------------
| Albert | 1970-01-16 | 1970-06-01 |           4|
|  Barry | 1992-05-01 | 1992-07-14 |           2|
|  Carol | 2001-03-16 | 2001-06-03 |           2|

The following will add a blank column:

ALTER TABLE Start_End
ADD COLUMN Month_diff INTEGER

How can I use months_between?

Answer

Check here: https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Date-Time/MONTHS_BETWEEN.htm?zoom_highlight=MONTHS%20BETWEEN

to find out if the behaviour of MONTHS_BETWEEN() (when to return INTEGER, when to return FLOAT, last-day-of-month behaviour, etc) is the one you need. Otherwise, you might want to DATE_TRUNC() the two operands first, for example, as @Gordon Linoff suggests. Or use TIMESTAMPDIFF(month, …) instead: https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Date-Time/TIMESTAMPDIFF.htm?zoom_highlight=timestampdiff

That said, try this for size:

ALTER TABLE start_end
ADD month_diff INTEGER DEFAULT MONTHS_BETWEEN(ended,started)

Good luck …