Sample Header Ad - 728x90

How to insert one record after every log record in PostgreSQL

0 votes
1 answer
266 views
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?
Asked by Lacie (101 rep)
Nov 2, 2022, 10:08 PM
Last activity: May 20, 2025, 06:06 AM