Good day,

there is a SQL query

SELECT movements.shipment_id, movements.location_id, string_agg(movements.status_code, ',') AS statuses, max(movements.moved_at) AS monitoring_start_time, (max(movements.moved_at) + '3 day') AS monitoring_deadline, NOW() AT TIME ZONE 'UTC' AS monitoring_now_time FROM movements INNER JOIN shipments ON shipments.id = movements.shipment_id INNER JOIN airports ON airports.id = movements.location_id WHERE shipments.controlled = true AND airports.movements_update_method = 'web' AND movements.location_id IS NOT NULL AND monitoring_now_time BETWEEN monitoring_start_time AND monitoring_deadline GROUP BY movements.shipment_id, movements.location_id

in AND monitoring_now_time BETWEEN monitoring_start_time AND monitoring_deadline I want to check if the current date and time is in the time interval between the start_time and deadline dates

but for all these three aliases I get errors:

 column "monitoring_now_time" does not exist column "monitoring_start_time" does not exist column "monitoring_deadline" does not exist ERROR: column "monitoring_now_time" does not exist LINE 25: monitoring_now_time BETWEEN monitoring_start_time AND moni... ^ ********** Ошибка ********** ERROR: column "monitoring_now_time" does not exist SQL-состояние: 42703 Символ: 550

so the result of the query without AND monitoring_now_time BETWEEN monitoring_start_time AND monitoring_deadline enter image description here

In general, please tell me how to do it so that aliases for the fields are also taken into account?

  • AND NOW() AT TIME ZONE 'UTC' BETWEEN max(movements.moved_at) AND (max(movements.moved_at) + '3 day') Does this work? - ilyaplot
  • So did already, does not work - Mikhail Lutsko
  • What error comes in this case? As far as I remember, the data that we get in select cannot be used in where. Those. on select 1 as one where one = 1; get error ERROR: column "one" does not exist - ilyaplot
  • I get aggregate functions are not allowed in WHERE - Mikhail Lutsko

1 answer 1

Aliases from the select list or group functions cannot be used in the where clause, since it works up to grouping and forming a list of samples. These conditions can be checked in the having or it is necessary to wrap the request in the external one and already check in it

 SELECT movements.shipment_id, movements.location_id, string_agg(movements.status_code, ',') AS statuses, max(movements.moved_at) AS monitoring_start_time, (max(movements.moved_at) + '3 day') AS monitoring_deadline, NOW() AT TIME ZONE 'UTC' AS monitoring_now_time FROM movements INNER JOIN shipments ON shipments.id = movements.shipment_id INNER JOIN airports ON airports.id = movements.location_id WHERE shipments.controlled = true AND airports.movements_update_method = 'web' AND movements.location_id IS NOT NULL GROUP BY movements.shipment_id, movements.location_id HAVING NOW() AT TIME ZONE 'UTC' BETWEEN max(movements.moved_at) AND (max(movements.moved_at) + '3 day')

or:

 SELECT * FROM ( SELECT ..., max(movements.moved_at) AS monitoring_start_time, (max(movements.moved_at) + '3 day') AS monitoring_deadline, NOW() AT TIME ZONE 'UTC' AS monitoring_now_time FROM ... GROUP BY ... ) X WHERE monitoring_now_time BETWEEN monitoring_start_time AND monitoring_deadline