CODEWITHSUNDEEP
postgresql
sergzach
sergzach

Reputation: 6754

Update time only if not a minute passed

I have created the next table.

-- TABLE user_time
user_id integer PRIMARY KEY,
prev_time TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW(),
total_time INTERVAL DEFAULT interval '0 second'

I have to add an interval value to the total_time, e.g.

total_time = total_time + NOW() - prev_time

only if not a minute passed since prev_time (so, less than 1 minute passed) in a single query.

The next construction is about what I want but it's wrong:

UPDATE user_time SET total_time = total_time +
(
    SELECT NOW() - prev_time incinterval,
    CASE
        WHEN incinterval < interval '1 minute' THEN incinterval
        ELSE interval '0 second'
        END
    FROM user_time WHERE user_id=6
)

Firstly the SELECT is wrong, PostgreSQL does not recognize incinterval in the CASE construction. Secondly there is the first extra column in SELECT which creates a pseudo name.

Do you have an idea how to correct the query OR Is it the common practice to increment total time with the condition and store it to a database with a single query?

Upvotes: 0

Views: 81

Answers (2)

partic
partic

Reputation: 1

The deadlock issue can be resolved by doing the analysis post hoc. Just log the queries and "roll up" clusters that meet your interval spec. This allows for variable-length intervals, which can be very useful for sites that provide dense content.

The "post hoc" process can/should run periodically in overlapping ranges to catch longer intervals.

There is an relevant example of a generalizable temporal clustering query using analytic functions from about 8 years ago on asktom.oracle.com.

Upvotes: 0

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17147

You can not expect Postgres to respect your alias like this inside a SELECT. Here's the way to go:

UPDATE user_time SET total_time = total_time +
(
    SELECT 
      CASE
        WHEN NOW() - prev_time < interval '1 minute' 
        THEN NOW() - prev_time
        ELSE interval '0 second'
        END
    FROM user_time WHERE user_id=6
)

Or to use your alias:

UPDATE user_time SET total_time = total_time +
(
    SELECT
        CASE
            WHEN incinterval < interval '1 minute' 
            THEN incinterval
            ELSE interval '0 second'
            END
    FROM(
        SELECT 
            NOW() - prev_time incinterval
        FROM user_time WHERE user_id=6 
        ) foo
)

Edit after comment: Simply add , prev_time = NOW() after the last parenthesis in any option you choose from those above.

Upvotes: 1

Related Questions