I have a table with internal_id, states, start time and end time. I am trying to overlay the recent state to the previous states and return the value.

I have a simple working example here which works fine for the initial case. It overlay recent data, i.e id=5 over id=4 .

But when I add a new row -

(5, 1, 'Simba', '2022-01-01 00:00:00', '2022-01-04 00:00:00');

I am expecting this new row to overlay over all records and only show this row as result. Recent (higher ID) should have the precedence over lower id's. This is shown in example 1. I can also have other cases where 'Simba' can be of shorter duration and overlay should be something like example 2.

Example 1

=A==    ====G====  

Example 2

=A==    ====G====  

I feel I am doing something wrong and might be using recursion but not sure. Can someone please point what I might be missing?

  • Please consider following these suggestions.
    – mustaccio
    Jan 29, 2022 at 23:52
  • Hi, and welcome to dba.se! I think I understand where you're coming from. However, what's not clear is how come Simba doesn't overlay everything since it starts at the beginning and goes on till the end? Perhaps if you put in a diagram for each stage (bottom is desired result?)? Unrelated: 9.6 is EoL - can you upgrade - multirange types (pg 14) may be of help here? Also, you may want to use [) instead of []?
    – Vérace
    Jan 30, 2022 at 7:23
  • Hi Vérace, thank you for responding. We can use Postgres 14 too, it's not a dependency for now. My expected result was Simba should overlay everything, but I think I have some mistakes in my query Jan 30, 2022 at 19:18
  • I don't understand how your final result (bottom line)? Is your result only for 1 day? But simba covers all timestamps between [2022-01-01... 2022-01-04]?
    – Vérace
    Jan 30, 2022 at 22:39
  • I have updated my diagram now, sorry for the confusion. Yes, SImba should cover the whole duration in example1. In example2, since the timestamp is of shorter duration, it only covers few of the overlays. Thanks you again for commenting. Jan 30, 2022 at 23:46


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.