Amazon Spectrum incremental load directly from string

Question

I have take a field as 'filename Pro_180913_171842' from spectrum. Tried the function in sql like

       `select
       fields
      from spectrum.ex
      where cast(SPLIT_PART('filename Pro_180913_171842','Pro_',2)as 
      timestamp)>cast('2018-09-12 15:13:54.0' as timestamp)`

but it returned empty rows only!

It is timestamp like '2018-09-13 17:18:42.0'.Able to take as field but incremental load is not working. — Ganesh Pitchai, Nov 08 '18 at 17:01
You are trying to compare a time, with no date component, against a timestamp, which has a date component. What is your logic here? — Tim Biegeleisen, Nov 08 '18 at 17:05

score 0 · Answer 1 · answered Nov 08 '18 at 17:09

Your field has no date component, so unless we add date information, it makes no sense to compare to a full timestamp. If you intend to compare only times, then try this:

SELECT fields
FROM spectrum.ex
WHERE SPLIT_PART('filename Pro_180913_171842', '_', 2) > '151354';

Amazon Spectrum incremental load directly from string

1 Answers1