[PostgreSQL] ERROR: invalid input syntax for type date

[Sr Juan]

Mijn vraag
Een collega en mijzelf hebben een Query geschreven voor een automatisch raport/tabel dat wij versturen vanaf de server naar Email via ons framework.

Waar de Functie FRIDAY is om het aantal dagen met een marketing status het aantal dagen aangeeft.
Hoe lang heeft de persoon deze status?

code:

SELECT 
    b.marketingname AS "FE Name",
    c.employee_number AS "Paycor ID",
    techmap.exp AS "Technology",
    d.firstname || ' ' || d.lastname AS "FEM",
    e.firstname || ' ' || e.lastname AS "PS",
    f.firstname || ' ' || f.lastname AS "SME Trainer",
    (select count(*) from submissions subs2
        WHERE subs2.submissiondate between
            (date('{{FRIDAY}}') - interval '6 days') and '{{FRIDAY}}'
            AND subs2.marketingprofile_id = b.pk_marketingprofile
            AND subs2.status = 'ACTIVE'
        ) AS "Submissions"
FROM marketingprofiles b
INNER JOIN (SELECT h.marketingprofile_id, h.changed
    FROM history h
    JOIN (
        SELECT marketingprofile_id, max(change_date) AS maxCD,
            max(pk_history_id) AS maxHistoryID
        FROM history h2
        WHERE h2.change_date  < (date('{{FRIDAY}}') + interval '1 days')
                    AND h2.fieldname = 'marketingstatus'
        GROUP BY marketingprofile_id)
    h2 ON h2.marketingprofile_id = h.marketingprofile_id
    WHERE h.change_date = h2.maxCD AND h.pk_history_id = maxHistoryID
    GROUP BY h.marketingprofile_id, h.changed)
        h ON h.marketingprofile_id = b.pk_marketingprofile
JOIN employees c ON b.fe_id = c.pk_employee_id
    AND NOT
        (c.terminationdate is not null and lifecycle_status = 'TERMINATED' and
        c.terminationdate <= '{{FRIDAY}}')
JOIN employees d ON c.manager_id = d.pk_employee_id
JOIN employees e ON b.ps_id = e.pk_employee_id
LEFT OUTER JOIN employees f ON c.trainer_id = f.pk_employee_id
JOIN (values ('DS','Data Science'), ('BD','Big Data'),
    ('AN','Android'), ('IOS','iPhone iOS'), ('CS','Cyber Security'),
    ('AWS','Amazon Web Services'), ('AZ','Azure'),
    ('EX','O365/Exchange'), ('O365','O365/Exchange'),
    ('SP','Sharepoint')
    )
    AS techmap(abbr,exp) ON b.technology = techmap.abbr
WHERE
    h.changed = 'MARKETING'
        -- OR NOT (c.lifecycle_status='ON-PROJECT' -- ARGHH
            -- and b.marketingstatus='INACTIVE'))
    AND (select count(*) from submissions subs
        WHERE subs.submissiondate between
            (date('{{FRIDAY}}') - interval '6 days') and '{{FRIDAY}}'
            AND subs.marketingprofile_id = b.pk_marketingprofile
            -- AND subs.status = 'ACTIVE'
            ) < 6
    -- AND b.pk_marketingprofile=41724
    -- AND b.marketingstatus = 'MARKETING'
GROUP BY "FE Name","Paycor ID","Technology","FEM","PS","SME Trainer",
    b.pk_marketingprofile
HAVING count(*) < 6
ORDER BY "FE Name"

Relevante software en hardware die ik gebruik
PG admin 4

Wat ik al gevonden of geprobeerd heb:

Ik krijg:
ERROR: invalid input syntax for type date: '{{FRIDAY}}'
LINE 21: WHERE h2.change_date < (date('{{FRIDAY}}') + interval '1 ...

SQL state: 22007 ( An invalid datetime format was detected; that is, an invalid string representation or value was specified.)
Google leert mij (https://stackoverflow.com...3/invalid-datetime-format) en (https://www.postgresqltut.../postgresql-to_timestamp/) hoe ik dit zou kunnen oplossen, maar zou 1 van de twee redenenen waar kunnen zijn?

Mis ik gewoon ergens wat of een character ergens te veel.
OF
Kan het zijn dat ik een DATE en een String probeer te filteren?

[Voor 2% gewijzigd door Sr Juan op 01-02-2023 18:32. Reden: extra informatie]

[RobIII]

Begin eens gewoon met je query (heul erg veul) te versimpelen. Breng die 57 regels eens terug naar een testcase van een paar regels ofzo. Een select * from <table> where <blah> en werk daar die {{FRIDAY}} in. En bouw van daar uit terug naar de query die je nu hebt. Dus zoiets om te beginnen:

SQL:

SELECT *
FROM history h2
WHERE h2.change_date  < date('{{FRIDAY}}')
LIMIT 10

En kijk of dat werkt.

We hebben verder geen kijk op je schema dus we kunnen niet ruiken of een veld een string is of een datetime en zonder de context te kennen zegt zo'n query met, voor ons, 95% irrelevante zaken ons natuurlijk ook weinig.

Sr Juan schreef op woensdag 1 februari 2023 @ 18:09:
Waar de Functie FRIDAY is om het aantal dagen met een marketing status het aantal dagen aangeeft.
Hoe lang heeft de persoon deze status?

Ook hier kan ik weinig hout van snijden...

Tot slot: Ik kan me vergissen maar "{{FRIDAY}}" is volgens mij geen PGSQL syntax; is dit iets waar in een applicatielaag een string-replace op wordt gedaan ofzo? Want in dat geval: Gebruik parametrized queries. Want Bobby Tables enzo.

[Voor 75% gewijzigd door RobIII op 01-02-2023 19:30]

[Sr Juan]

RobIII schreef op woensdag 1 februari 2023 @ 19:20:
Begin eens gewoon met je query (heul erg veul) te versimpelen. Breng die 57 regels eens terug naar een testcase van een paar regels ofzo. Een select * from <table> where <blah> en werk daar die {{FRIDAY}} in. En bouw van daar uit terug naar de query die je nu hebt. Dus zoiets om te beginnen:

SQL:

SELECT *
FROM history h2
WHERE h2.change_date  < date('{{FRIDAY}}')
LIMIT 10

En kijk of dat werkt.

We hebben verder geen kijk op je schema dus we kunnen niet ruiken of een veld een string is of een datetime en zonder de context te kennen zegt zo'n query met, voor ons, 95% irrelevante zaken ons natuurlijk ook weinig.


[...]

Ook hier kan ik weinig hout van snijden...

Tot slot: Ik kan me vergissen maar "{{FRIDAY}}" is volgens mij geen PGSQL syntax; is dit iets waar in een applicatielaag een string-replace op wordt gedaan ofzo? Want in dat geval: Gebruik parametrized queries. Want Bobby Tables enzo.

Je hebt helemaal gelijk, Ik liep te hard van stapel.

De FRIDAY functie is inderdaad van een Java injection, maar dat was mijn collega vergeten mij te vertellen.

Bijdeze opgelost en kan dicht.

Toch bedankt voor de tijd gespendeerd

[RobIII]

Een slotje is niet nodig op een topic als je je oplossing hebt. Zie daarvoor ook onze faq betreffende topiceinde.