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
Last activity: May 20, 2025, 06:06 AM