WHERE Conditions in PostgreSQL

Introduction

WHERE is used to narrow the output by adding the requirement to match some condition.

Examples

Output the full rows in which status equals new

SELECT * FROM sites WHERE (status = 'new')

Count the rows in which status equals new

SELECT COUNT(*) FROM sites WHERE (status = 'new')

Output rows with a specific time

SELECT * FROM events WHERE (event_time = '2020-12-29 14:27:20.275562+02')

You can set the time condition explicitly.

SELECT created, event_name, event_id FROM audit_events WHERE (created > '2022-12-01 12:16:09');

Perform an action on the timestamp

SELECT * FROM events WHERE (event_time::timestamp + interval '3 hours' = '2020-12-29 17:27:20.275562+02')

You can use comparison operators

SELECT * FROM events WHERE (event_time::timestamp + interval '3 hours' < '2020-12-29 17:27:20.275562+02')

To compare with the current time you can use the function now()

SELECT * FROM events WHERE (event_time::timestamp + interval '3 hours' < now())

Select from the table events database all events older than five days

SELECT * FROM events WHERE (event_time::timestamp + interval '5 days' < now())

Select from the table events database the fields id, status, protocol of events older than 15 minutes

SELECT id, status, protocol FROM events WHERE (event_time::timestamp + interval '15 minutes' < now())

Article author: Andrei Olegovich