EDITED: auto update timestamp every second in postgres

Question

I have a column local_date_time (timestamp withhout time zone) in postgres DB and that needs to be auto updated every second based on speed like 1x, 2x(Increment by 2 Seconds for each 1 sec in real time) and so on. Suggest ways to do it within pgadmin or with python script. the code should be running continuously on server machine. I have sched running like this,

            def autoupdate_userclock(userid, speed):
                """Auto update clock time based on speed."""
                try:
                    connection = psycopg2.connect(
                  # DB connection parameters )
        # Speed check - Increment based on speed
                    if speed == "1x":
                        x = timedelta(seconds=1)
                    elif speed == "2x":
                        x = timedelta(seconds=2)
                    elif speed == "3x":
                        x = timedelta(seconds=3) and so on
          
            # DB query to update timestamp based on speed
                    update_query = """ UPDATE users
                    set local_date_time = (local_date_time + %s)::timestamp where (local_date_time+%s)::timestamp<=now()::timestamp 
    and userid =%s"""
            
          with connection.cursor() as cursor:
             cursor.execute(update_query, (x, x, userid))
          connection.commit()
                except Exception as e:
                    print(e)
            
            # Task scheduling
            connection = psycopg2.connect(#DB connection string) 
with connection.cursor() as cursor:
                cursor.execute("select userid, speed from users")
                t = cursor.fetchall()
            connection.close()
            for row in t:schedule.every(1).seconds.do(
autoupdate_userclock,row[0].strip(), row[1].strip())
# Keep running the pending tasks      
while True:
  schedule.run_pending()

Any efficient solutions?

Can you provide a snippet with what you've tried so far? – Jerven Clark Jul 12 '21 at 10:06 — Jerven Clark, Jul 12 '21 at 10:06

score 0 · Answer 1 · answered Jul 12 '21 at 10:55

You cannot do that, and it would make no sense.

Rather, define the table like this:

CREATE TABLE sample (
   id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   ...,
   base_timestamp timestamp with time zone NOT NULL,
   speed double precision NOT NULL
);

and query it like

SELECT id, ...,
       base_timestamp + (current_timestamp - base_timestamp) * speed
FROM sample;

Then you calculate the timestamp right when you need it, in the query.

EDITED: auto update timestamp every second in postgres

1 Answers1