0

I am trying to write an SQL query using CASE where I want to return the values from the campaign table of the selected row when on two cases:

  1. The current time is between the start date and end date, which is displayed in the query. That part is working as expected and is included in the provided example.

  2. WHERE publish_end_at is null. This is what I am trying to implement.

My query:

CASE
    WHEN publish_queue.message_id IS NOT NULL
    THEN
        (
         SELECT ARRAY_AGG(id) FROM campaign WHERE react_to_publish_queue_id = publish_queue.id
         AND (CURRENT_TIMESTAMP BETWEEN publish_start_at AND publish_end_at)
        )
     ELSE '{}'
 END AS running,
 CASE
     WHEN publish_queue.message_id IS NOT NULL
     THEN
         (
           SELECT ARRAY_AGG(id) FROM campaign WHERE react_to_publish_queue_id = publish_queue.id 
           AND publish_start_at < CURRENT_TIMESTAMP)
     ELSE '{}'
 END AS published,

Basically, I mean that if publish_end_at is null and publish_start_at is less than a minute older than the current timestamp, it should appear in the running. If it’s more than a minute, it should appear in the published.

I do not know how to define this part of the query in the already defined one which I provided an example. Should I use OR operator inside the aggregate function ARRAY_AGG() or I can do it differently?

There are two tables just for the sake of the representation of data:

Here is publish_queue table:

id team_member_id message_id campaign_id
1859001 8566 1171 51543

campaign table:

id team_member_id publish_start_at publish_end_at react_to_publish_queue_id
2 8566 2022-05-30 09:19:00 null 1859001

The output would be:

"running_status": [
    2,
    ...
]

Can someone please help, I am pretty new to writing complex cases. Thanks

Improve this question
6
  • I think you lost identifier naming consistency in one of the edits before publishing the question - there's no status_end_at outside your first paragraph. "publish_start_at is less than a minute older than the current timestamp" this would translate to publish_start_at between current_timestamp-'1 minute' and current_timestamp which I don't see either Commented Nov 29, 2022 at 10:57
  • Thanks for replay. I edited the post, it was a typo, I thought of publish_end_at. I would define it like: The examples I already have are the ones where publish_end_at are set and I need to import one more WHERE publish_end_at will be null. @Zegarek Commented Nov 29, 2022 at 11:08
  • I meant that we do not cover both cases. One is where it is BETWEEN start and end time (that one is where publish_end_at is not null) and the other where publish_end_at is null. @Zegarek Commented Nov 29, 2022 at 11:10
  • Another attempt, to see if I understand it right: 1. running: publish_end_at is not known but publish_start_at was less than a minute ago OR we're currently between publish_start_at and a known publish_end_at. 2. published: publish_end_at unknown but publish_start_at was more than a minute ago OR we're currently past a known publish_end_at timestamp. Commented Nov 29, 2022 at 11:26
  • Yes, that's the case! Thanks, you helped a lot. :) @Zegarek Commented Nov 29, 2022 at 11:29

1 Answer 1

Reset to default
1

Assuming these conditions:

  1. Status running:

    • publish_end_at is not known but publish_start_at was less than a minute ago

    OR

    • we're currently between publish_start_at and a known publish_end_at.

  2. Status published:

    • publish_end_at unknown but publish_start_at was more than a minute ago

    OR

    • we're currently past a known publish_end_at timestamp.
CASE
    WHEN publish_queue.message_id IS NOT NULL
    THEN
        (SELECT ARRAY_AGG(id) 
         FROM campaign 
         WHERE react_to_publish_queue_id = publish_queue.id
         AND (   (    publish_end_at IS null
                  AND publish_start_at BETWEEN now()-'1 minute'::interval AND now() 
                 )
              OR (    publish_end_at IS NOT null
                  AND now() BETWEEN publish_start_at AND publish_end_at 
                 ) 
             )
        )
     ELSE '{}'
 END AS running,
 CASE
     WHEN publish_queue.message_id IS NOT NULL
     THEN
        (SELECT ARRAY_AGG(id) 
         FROM campaign 
         WHERE react_to_publish_queue_id = publish_queue.id
         AND (   (    publish_end_at IS null
                  AND publish_start_at < now()-'1 minute'::interval 
                 )
              OR (    publish_end_at IS NOT null
                  AND now() > publish_end_at  
                 )
             )
        )
     ELSE '{}'
 END AS published,

Demo

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

I tested the code and what needs to be done is to add an INTERVAL like NOW() - INTERVAL '1 minute' so it does not throw a syntax error. However, when I input the current timestamp into databases publish_start_at it is always published.. I am not sure why is that..? @Zegarek
Good catch, I've added the type casts. now()+'1 minute' works without the ::interval because of how + operator is defined: PostgreSQL can deduce that if the first argument is a timestamptz, the second can only be an interval. For -, the second can be either an interval or timestamp(tz), so a cast is required to select the variant. I overlooked that.
As to why everything fell into published category, we'd have to take a look at some samples and a fully working query. You can set up a small demo on dbfiddle, with minimal amount of anonymised/obfuscated/non-production data (e.g. real timestamps, fake ids).
I've added it. Can you please check: dbfiddle.uk/VxIaRLH3 @Zegarek
After some corrections to make the demo work, it seems to be fine. I added some test cases Updated demo
|

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.