How can i make this date input in a PostgreSQL query to work?

Question

I'm working with a query that has a series of joins and brings info from them like this:

SELECT
fleet."name"                                                               AS "Fleet",
wp."tail"                                                                  AS "AC",
wp."iata"                                                                  AS "LOC",
CONCAT(usrr."firstName", ' ', usrr."lastName")                             AS "Supervisor",
CONCAT(usr."firstName", ' ', usr."lastName")                               AS "Mechanic",
to_char(wp."req" AT TIME ZONE 'utc' AT TIME ZONE airbase."timeZone", :format)   AS "ETA",
to_char(wp."etr" AT TIME ZONE 'utc' AT TIME ZONE airbase."timeZone", :format)   AS "ETD",
round(cast(date_part('hour', age(wp."etr", wp."req")) / 24 AS NUMERIC), 2) AS TAT,
NULL                                                                       AS "Position",
wp."barcode"                                                               AS "WP",
breq."barcode"                                                             AS "REQ/BLOQ",
breq."name"                                                                AS "REQ Name",
jic."barcode"                                                              AS "Task BC",
jic."description"                                                          AS "Task Name",
jic."due_date"                                                             AS "Due date",
labor."name"                                                               AS "Skills",
CASE WHEN date_part('hour', age(jic."estimated_end_date", jic."estimated_start_date")) > 0
          AND date_part('minute', age(jic."estimated_end_date", jic."estimated_start_date")) > 0
  THEN date_part('hour', age(jic."estimated_end_date", jic."estimated_start_date")) +
       date_part('minute', age(jic."estimated_end_date", jic."estimated_start_date")) * 0.01
WHEN date_part('hour', age(jic."estimated_end_date", jic."estimated_start_date")) > 0
     AND date_part('minute', age(jic."estimated_end_date", jic."estimated_start_date")) = 0
  THEN date_part('hour', age(jic."estimated_end_date", jic."estimated_start_date"))
WHEN date_part('hour', age(jic."estimated_end_date", jic."estimated_start_date")) = 0
     AND date_part('minute', age(jic."estimated_end_date", jic."estimated_start_date")) > 0
  THEN date_part('minute', age(jic."estimated_end_date", jic."estimated_start_date")) * 0.01
WHEN date_part('hour', age(jic."estimated_end_date", jic."estimated_start_date")) = 0
     AND date_part('minute', age(jic."estimated_end_date", jic."estimated_start_date")) = 0
  THEN date_part('hour', age(jic."estimated_end_date", jic."estimated_start_date"))
END                                                                        AS "Duration",
jic."resources_quantity"                                                   AS "nTec",
CASE WHEN date_part('hour', age(jic."estimated_end_date", jic."estimated_start_date")) > 0
          AND date_part('minute', age(jic."estimated_end_date", jic."estimated_start_date")) > 0
  THEN date_part('hour', age(jic."estimated_end_date", jic."estimated_start_date")) +
       date_part('minute', age(jic."estimated_end_date", jic."estimated_start_date")) * 0.01 *
       jic."resources_quantity"
WHEN date_part('hour', age(jic."estimated_end_date", jic."estimated_start_date")) > 0
     AND date_part('minute', age(jic."estimated_end_date", jic."estimated_start_date")) = 0
  THEN date_part('hour', age(jic."estimated_end_date", jic."estimated_start_date")) * jic."resources_quantity"
WHEN date_part('hour', age(jic."estimated_end_date", jic."estimated_start_date")) = 0
     AND date_part('minute', age(jic."estimated_end_date", jic."estimated_start_date")) > 0
  THEN date_part('minute', age(jic."estimated_end_date", jic."estimated_start_date")) * 0.01 *
       jic."resources_quantity"
WHEN date_part('hour', age(jic."estimated_end_date", jic."estimated_start_date")) = 0
     AND date_part('minute', age(jic."estimated_end_date", jic."estimated_start_date")) = 0
  THEN date_part('hour', age(jic."estimated_end_date", jic."estimated_start_date")) * jic."resources_quantity"
END                                                                        AS "MH",
to_char(jic.estimated_start_date AT TIME ZONE 'utc' AT TIME ZONE airbase."timeZone", :format) AS "Start",
to_char(jic.estimated_end_date AT TIME ZONE 'utc' AT TIME ZONE airbase."timeZone", :format) AS "End",
usr.bp                                                                     AS "BP",
CASE WHEN breq.is_productive = 1 AND breq.task_type_id != 3
  THEN 'PROD'
WHEN breq.is_productive = 0 or breq.task_type_id = 3
  THEN 'NPROD' END                                                         AS "Tipo",
work_location."name"                                                       AS "Zone",
breq."review_status"                                                        AS "Acceptance",
CASE WHEN wp."published" = TRUE
  THEN 'PUBLISHED'
WHEN wp."published" = FALSE
  THEN 'UNPUBLISHED' END                                                   AS "Published"
FROM public."work_package" AS wp
INNER JOIN public."task" AS breq ON wp."id" = breq."work_package_id"
INNER JOIN public."labor" AS jic ON breq."id" = jic."task_id"
LEFT JOIN public."Skill" AS labor ON jic."skill_id" = labor."id"
LEFT JOIN public."user_labor" AS usr_l ON jic."id" = usr_l."labor_id"
LEFT JOIN public."User" AS usr ON usr_l."user_id" = usr."id"
LEFT JOIN public."Aircraft" AS aircraft ON wp."aircraft_id" = aircraft."id"
LEFT JOIN public."Fleet" AS fleet ON aircraft."fleetId" = fleet."id"
left JOIN public."Airbase" as airbase ON  wp.base_id = airbase.id
LEFT JOIN public."BSE_WORK_LOCATIONS" AS work_location ON wp."work_location_id" = work_location."id"
LEFT JOIN public."User" AS usrr ON wp."supervisor_user_id" = usrr."id"
WHERE wp."start" BETWEEN to_timestamp(:start) AND to_timestamp(:finish) OR wp."end"
BETWEEN to_timestamp(:start) AND to_timestamp(:finish);

The script is composed of calls to different fields of different tables, then using case to some ones, calling for an input where the format is inserted to structure part of the output, and at last, a WHERE statement that i'm testing that closes the call to something more bounded. The conflictive bit is the last part, with the WHERE statement. I've used many different approaches, like: imitating the same structure that the table has on that field, using and non using quotes, using utc timezone, using milliseconds, and nothing has worked until now. The original field has a timestamp type, that's why i used the parse there. This is the input viewed on DataGrip:

By now, after i dropped the quotes, it will finish the process, but it will return nothing, even where i know there is data there.

Is there something visible that i'm doing wrong? Please help. Thanks in advance!

Answer 1

Because the field is a timestamp, PostgreSQL deserializes one from a string with the following format: 'YYYY-MM-DD HH:MM:SS.M' (note the single quotes). Then it works just fine.