0

I have a PostgreSQL table that store logs data with each row is one log and fields are logname, start, end. I want to visualize the logs, and due to a limit of Grafana the visualize tool I choose, I feel the need to insert one new record with stimestamp one second after each log, let's say with logname 'Idle'. For better explanation:

Start Logname End
10:00:00 log-in 10:00:01
10:30:00 call 11:00:00
10:55:00 some action (still in call) 11:05:00

Then I want to manually add logname 'Idle' one second after each available logs

Start Logname End
10:00:00 log-in 10:00:01
10:30:02 (1 second after the end time of the previous log) idle 10:30:03
10:30:00 call 11:00:00
10:55:00 some action 11:05:00
11:05:01 (1 second after) idle 11:05:01

I think of manually calculating each row and then inserting them, but how should I do it in terms of a PostgreSQL query?

Update Thanks to Akina & dwhitemv, I see it's illogical to insert a new record after every available record. Instead, the question should be inserting a new record into time ranges only if there are no available records or overlapping records. Aside from inserting, a new problem is how to check if there is a record(s) in the time range. I updated the tables above. I feel this makes it complicated since using NOT EXISTS & ON CONFLICT can't really check if records exist between 11:00 and 11:05?

3
  • So you want to find all the time ranges where no record exists?
    – dwhitemv
    Commented Nov 3, 2022 at 3:34
  • Oh, you can put it that way. At first, I intended to insert a new Idle record after every record, which will double the size of the table, regardless of whether there is already a log record in the next 1 second or even a time overlap of 2 log records. Now look at Akina's and your responses, it doesn't make sense to insert an Idle to the time ranges where records already exist. Post updated to clarify.
    – Lacie
    Commented Nov 3, 2022 at 13:50
  • I read you right then. Your question falls into the class of problems known as gaps and islands. I have another solution in my head I will try to complete & submit. Another thought I had was to use a view to generate the idle ranges on demand.
    – dwhitemv
    Commented Nov 4, 2022 at 19:40

1 Answer 1

0

Something like

INSERT INTO logs (Start, Logname, End)
SELECT End + INTERVAL '1 second',       -- previous End + 1 second
       'Idle',                          -- static literal value
       End + INTERVAL '2 second'        -- current Start + 1 second
FROM logs
ON CONFLICT (Start) DO NOTHING;         -- skip insertion if the row exists already

The query does not check the overlapping which may occur if 2 source rows are adjacent (End_1 = Start_2). If you'd check this then use according WHERE NOT EXISTS instread of ON CONFLICT.

3
  • can you explain more about how to use WHERE NOT EXISTS? Usually, it's WHERE NOT EXISTS (SELECT ....) & I'm not sure how to achieve it in my case
    – Lacie
    Commented Nov 3, 2022 at 20:58
  • @Elly INSERT INTO logs SELECT .. FROM logs t1 WHERE NOT EXISTS (SELECT NULL FROM logs t2 WHERE {t1&t2 overlapping detection} ).
    – Akina
    Commented Nov 4, 2022 at 13:36
  • Thank you! I combined your solution and another solution I found, and although I haven't done a complete sanity check yet, seems like it works.
    – Lacie
    Commented Nov 8, 2022 at 14:15

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.