1

I'm looking for a solution in postgresql to insert or update a row, based on the timestamp of that row.

What I'm looking for is the following:

I want to insert the use of a specific hashtag, and count this use, based on the last hour.

+--------------------------------------------------+
| hashtag     | count | timestamp                  |
+--------------------------------------------------+
| hello       | 1     | 2020-01-01 12:00:00+00.00  |
| world       | 3     | 2020-01-01 12:00:00+00.00  |
| hello       | 2     | 2020-01-01 11:00:00+00.00  |
| ...                                              |
+--------------------------------------------------+

What I want to achieve:

I want to insert a new row if there is no row for the specific hashtag in the last hour created (based on the timestamp). If a row exists with timestamp > NOW() - INTERVAL '1 HOUR', I just want to update the count of that row.

How can I achieve this? Can I do this with a CHECK-constraint?

3
  • Don't do that. Round the timestamp to the hour, store that column, define a unique constraint and use INSERT ... ON CONFLICT. Jan 14, 2021 at 6:09
  • thats my current solution: taking unix timestamp of start of hour (eg. 2020-01-01 12:00:00+00.00) with a unique constraint. I was guessing this wasn't the best solution, therefor my question. thank you.
    – Sam Leurs
    Jan 14, 2021 at 6:12
  • Don't store timestamps as unix epoch. Use timestamptz Jan 14, 2021 at 6:32

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.