Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2009
    Posts
    4

    Unanswered: Time Delayed Function

    Just wondering if it is possible to change records to x amount of time and then change back to original values?

    So if have a column, and I want to change the values to a static value for 2 hours and then revert back to the original value (or to another static value if easier)

    Thanks in advance!

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    If you need to change data, then restore the data later, the simplest approach is to create a table to store the changing data. First, run a query to populate the new table with the original data, plus the associated primary key of the original table. Then, you can change the original data.

    Then, at a later date, copy the original data from the new table to the original table.

    However, you can't automate the process only with SQL. (you might be able to do so with pl/pgSql, but, it would undoubtedly be very inefficient, as you would have to loop until the current time is > than the target time for the data restore. This would probably impact the server performance for the entire time the loop is running.)

    You could also set up a job using pgAgent, I suppose.

    Why would you need to do this sort of thing?
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Mar 2009
    Posts
    4
    Quote Originally Posted by loquin
    If you need to change data, then restore the data later, the simplest approach is to create a table to store the changing data. First, run a query to populate the new table with the original data, plus the associated primary key of the original table. Then, you can change the original data.

    Then, at a later date, copy the original data from the new table to the original table.

    However, you can't automate the process only with SQL. (you might be able to do so with pl/pgSql, but, it would undoubtedly be very inefficient, as you would have to loop until the current time is > than the target time for the data restore. This would probably impact the server performance for the entire time the loop is running.)

    You could also set up a job using pgAgent, I suppose.

    Why would you need to do this sort of thing?
    Thanks for the reply. Main reason I want to do this is for a GIS project involving routing and road works. I wanted to be able to put weightings on certain roads for x amount of time, so that you would be rerouted around it depending on the weightings.

    Then once the time has passed, lift the weighting and allow for routing on the road again. I wanted to have a function to be automated, so a user could use a simple form to enter this kind of data and let the database do the rest. Never heard of pgAgent, but doubt it could automate the process without me manually entering data?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •