Case Statement - want to display proper "duration" time with BMC PostgreSQL

Grafana PostgreSQL
1

hi, I am getting a failed to parse error.

What I am trying to do is display the duration of a major incident record based on the following conditions:

  1. if HPD.Closed Date = NULL, then give duration from current date.
  2. if HPD.Closed Date has a date in it, then the incident has been closed and I want to see the duration from the reported date to the closed date.

My original “(DATEDIFF (‘ss’, HPD.Reported Date, currentDate())) As Duration,” gives the rolling duration, however it keeps adding time even when an incident is closed.

My SQL:
USING: BMC PostgresSQL

SELECT
  HPD.`Incident Number`,
  HPD.`Major Incident`,
  HPD.`Reported Source`,
  HPD.`Major Incident Accepted Date`,
  HPD.`Closed Date`,
  --(DATEDIFF ('ss', HPD.`Reported Date`, currentDate())) As Duration,
  CASE WHEN HPD.`Closed Date` = NULL THEN
    (DATEDIFF ('ss', HPD.`Reported Date`, currentDate())) 
  ELSE
     (DATEDIFF ('ss', HPD.`Reported Date`, HPD.`Closed Date`)) As Duration,
  HPD.`Last Resolved Date`
FROM
  `HPD:Help Desk`  HPD

Any help appreciated.

thanks,
Azard

2

Comment some lines in your SQL until you will be able to identify which line(s) is causing a problem. Than check PostgreSQL doc how correct syntax should looks like for that line.

I would say that at leat “= NULL” is not correct. I bet “IS NULL” is correct (but I’m not saying that’s the only one issue in your SQL - there can be much more - you have really terrible naming convention :sob:, so it is hard to read ).

3

Might be something like this @jangaraj:

SELECT
    HPD."Incident Number",
    HPD."Major Incident",
    HPD."Reported Source",
    HPD."Major Incident Accepted Date",
    HPD."Closed Date",
    CASE
        WHEN HPD."Closed Date" IS NULL THEN
            (DATEDIFF('ss', HPD."Reported Date", CURRENT_DATE()))
        ELSE
            (DATEDIFF('ss', HPD."Reported Date", HPD."Closed Date"))
    END AS Duration, HPD."Last Resolved Date"
FROM
    "HPD:Help Desk" HPD;
1 Like
4

I don’t know. Standard approach is to improving/developing SQL until it is not failing and it it is returning desired result. You have data, so only YOU can try it.

5

Is your question about grafana adding time or your psql query adding time?

6

Wait, @jangaraj, I’m not the one who asked the question! I’m just trying to correct the query of @azo69.

2 Likes
7

Sorry, my bad.

1 Like
8

hi, thanks for the response.

  1. naming convention, I didn’t create the database

  2. I don’t have access to full grafana postrgesql as this is BMC’s version of grafana

  3. if you comment out the case and just use
    (DATEDIFF (‘ss’, HPD.Reported Date, currentDate())) As Duration,
    [ NOTE: Reported Date surrounded by single quotes, is the correct format for BMC grafana postgresql ]
    everything works, so the SQL issue has to do with my case statement.
    However this is not giving the the result I want. it will always give me duration based on current date even when the incident is closed.

  4. tried IS NULL, still got parse error

Any other thoughts?
thanks,

9

what data type is Closed Date

10

both HPD.Reported Date and HPD.Closed Date are Date Time fields.

11

what do you see when you run this same query in a querying tool for your db

Do you see the same symptoms?

12

Tried the querying tool and I get the following
"… “messageType”: “ERROR”,
“messageText”: "Failed to parse the SQL ",
“messageAppendedText”: “[line 11:59] mismatched input ‘As’ expecting END. SQL …”

thanks, for the suggestion. I fixed it by changing
ELSE
(DATEDIFF (‘ss’, HPD.Reported Date, HPD.Closed Date)) As Duration,

to
ELSE
(DATEDIFF (‘ss’, HPD.Reported Date, HPD.Closed Date))
END As Duration,

and that worked.
@yosiasz . Thanks for pointing me in the right direction to solve this.
much appreciated.

cheers

1 Like
13

So always check in source tool to make sure it is not a source tool issue vs grafana issue :wink:

This is where the use of views or stored procedures comes into play also instead of raw sql query within grafana.

Also I think you could simplify that section further with an COALESCE check one liner sort of tertiary in js

COALESCE(HPD.`Closed Date`, 
(DATEDIFF ('ss', HPD.`Reported Date`, currentDate())),
(DATEDIFF ('ss', HPD.`Reported Date`, HPD.`Closed Date`)) As Duration

kind of thing

14

those would be nice, however with BMC Helix, those are not an option available to me.
:frowning:

1 Like