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
  • 1
    Don't store timestamps as unix epoch. Use timestamptz
    – user1822
    Jan 14, 2021 at 6:32

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.