sqltimestampvertica

Convert a date to timestamp in a join with interpolation


I have a table a in Vertica containing the item_id attached to a code at time from_date

create table a (
    code      varchar(16),
    item_id   int,
    from_date timestamptz
);

and a table b containing a code for a given date t

create table b (
    code    varchar(16),
    t       date
);

I need to retrieve the a.item_id from the b.code at the date b.t. The data looks like that :

insert into a values ('abc', 1, '2013-01-01 10:00:00 EST'::timestamptz);
insert into a values ('abc', 2, '2013-01-05 11:00:00 EST'::timestamptz);
insert into a values ('abc', 3, '2013-01-09 9:00:00 EST'::timestamptz);

insert into b values ('abc', '2013-01-06'::date);

I tried to use Vertica's INTERPOLATE in a LEFT JOIN in order to retrieve the previous (code, item_id) pair at time t with

select
    b.code, b.t, a.item_id
from  b
left join a on a.code = b.code and b.t INTERPOLATE PREVIOUS VALUE a.t;

This is giving me a type error Interpolated predicates can accept arguments of the same type only.

So I tried to convert b.t to a timestamptz

select
    b.code, b.t, a.item_id
from b
left join a on a.code = b.code
    and (b.t::timestamptz) INTERPOLATE PREVIOUS VALUE a.t;

This is giving me a syntax error Syntax error at or near "INTERPOLATE".

Without INTERPOLATE, the SQL expression works but doesn't join on the previous row.

Is it possible to convert my date to a timestamptz in a JOIN with INTERPOLATE?

If not, is there any other approach that would allow me to retrieve the current item_id at time t ?


Solution

  • I found a hacky workaround by tricking the interpreter and do the date to timestamp conversion in a SELECT subquery:

    select
        b.code, b.t, a.item_id
    from b
    left join (select a.t::date, a.code, a.item from a) a on a.code = b.code
        and b.t INTERPOLATE PREVIOUS VALUE a.t;