Re: Inner Join using datetime columns, Performance Issues

[Peter Brawley]

SQL is terrible with time, so to facilitate time comparison & duration computaiton it's usually a good idea to move interval computation from between-row SELECT computaitons to within-row INSERT and UPDATE computation. In your case, that'd require something like this table structure ...

id int primary key
... optional other identifying ids, eg of the machine or process neing measured
critical_hard_onset timestamp
critical_soft_onset timestamp
ok_hard_onset timestamp

Now for any such sequence, the values you need are simple within-row subtractions.

It might be further optimised by storing intervals directly ...

id int primary key
... optional other identifying ids, eg of the machine or process neing measured
critical_hard_onset timestamp
interval_to_critical_soft timestamp default null
interval_to_ ok_hard timestamp default null