How to extract data from DWH on a certain date? Is there the special pattern for this case in Postgres SQL?

Question

Usually we use EXTRACT (FROM YEAR date_column) = 2000 (let it be 2000 year). Also we can add EXTRACT (MONTH FROM date_column) = 1 (let it be January). Also we can extract a day - EXTRACT (DAY FROM date_column) = 5 (let it 5). But is it possible to use the pattern for this data? How does it look like in Postgres SQL

Say we have the table Shipment, the columns - date_payment, quantity, sum. I'd like to get the table that content all shipments for 01.01.2020 How to query this table with data format 'YYYY-MM-DD', not using EXTRACT-function?

Answer 1

If the date column is a date type then:

SELECT * FROM some_table WHERE date_col = '2020-01-01';

If the column is timestamp or timestamptz then:

SELECT * FROM some_table WHERE date_trunc('day', date_col) = '2020-01-01'

Beware that with timestamptz time zones come into play when doing the date_trunc. From here date_trunc:

When the input value is of type timestamp with time zone, the truncation is performed with respect to a particular time zone; for example, truncation to day produces a value that is midnight in that zone. By default, truncation is done with respect to the current TimeZone setting, but the optional time_zone argument can be provided to specify a different time zone. The time zone name can be specified in any of the ways described in Section 8.5.3.

For a timestamp value per the above link:

A time zone cannot be specified when processing timestamp without time zone or interval inputs. These are always taken at face value.