Q: Does the boundary date itself make the cut?

The <= operator includes the boundary date itself. Shipment 1 (May 1) and shipment 2 (May 10) both satisfy shipped_date <= '2024-05-10'. Shipments 3 and 4 are after May 10 and are excluded. This is the same chronological comparison you would apply to numbers - dates earlier in time are 'less than' dates later in time.

Q: Rolling 7-day window in PostgreSQL

CURRENT_DATE - INTERVAL '7 days' computes the date 7 days before today. The >= operator then keeps all rows on or after that date. DATEADD and DATEDIFF are SQL Server / MySQL functions - not valid PostgreSQL. This pattern (CURRENT_DATE - INTERVAL) is the standard PostgreSQL idiom for rolling date filters.

Q: Picking rows from one specific month

EXTRACT(YEAR FROM ...) = 2024 AND EXTRACT(MONTH FROM ...) = 1 filters to January 2024. Order 1 is in 2023 (wrong year). Order 4 is in February (wrong month). Orders 2 and 3 are both in January 2024. An alternative that uses an index better: WHERE order_date >= '2024-01-01' AND order_date < '2024-02-01' - EXTRACT on a column usually prevents index use.

Q: Which DOW number maps to Sunday?

In PostgreSQL, EXTRACT(DOW FROM date) returns the day of week as an integer from 0 (Sunday) through 6 (Saturday). So Sunday is 0, not 7. EXTRACT(DAY FROM ...) gives the day of the month (1-31), not the day of the week. MySQL uses DAYOFWEEK() where 1=Sunday, making the numbering off-by-one compared to PostgreSQL - always check the documentation for the database you are using.

Q: CURRENT_DATE vs NOW() for date filtering

CURRENT_DATE gives you just the calendar date - no hours, minutes, or seconds. NOW() (or CURRENT_TIMESTAMP) gives the full timestamp including time zone. When you write WHERE created_at >= CURRENT_DATE, the date is implicitly cast to '2024-04-23 00:00:00', which is what you usually want for 'since the start of today'. Using NOW() - INTERVAL '7 days' works well too, but it compares to the current exact time 7 days ago rather than the start of that day - a subtle difference worth knowing.

Question 1

What does date equality actually match?

Accepted Answer

Comparing a date column to a date string with = works when the column is a DATE type. Both Alpha and Gamma have event_date matching '2024-01-15'. If event_date were a TIMESTAMP column, this equality would still work in PostgreSQL because '2024-01-15' is cast to '2024-01-15 00:00:00'. Events at any other time on Jan 15 would NOT match - use a range instead for timestamps.

Question 2

Which rows does a strict date cutoff keep?

Accepted Answer

Date comparison operators (<, >, <=, >=) work on DATE and TIMESTAMP columns. Alpha's due date (Jan 10) is before Jan 15, so it fails the > condition. Beta (Jan 20) and Gamma (Jan 25) are both after Jan 15 and pass.

Question 3

Date equality when no rows fit

Accepted Answer

No row has event_date equal to '2024-03-10', so the result set is empty. Date equality filters are exact - the column value must match the literal precisely. This is a common gotcha when querying TIMESTAMP columns: if stored as '2024-03-10 09:00:00', the value does not equal the bare date '2024-03-10' cast to '2024-03-10 00:00:00'. When you're unsure whether any rows will match, a range query is safer than equality.

Question 4

BETWEEN inclusivity on date boundaries

Accepted Answer

BETWEEN in SQL is always inclusive on both the lower and upper bounds. A row with order_date = '2024-06-01' passes, and so does a row with '2024-06-30'. This differs from the half-open interval convention you see in many programming languages. For TIMESTAMP columns, BETWEEN '2024-06-30' AND '2024-06-30' would only match the exact midnight instant, not the rest of the day.

Question 5

Does the boundary date itself make the cut?

Accepted Answer

The <= operator includes the boundary date itself. Shipment 1 (May 1) and shipment 2 (May 10) both satisfy shipped_date <= '2024-05-10'. Shipments 3 and 4 are after May 10 and are excluded. This is the same chronological comparison you would apply to numbers - dates earlier in time are 'less than' dates later in time.

Question 6

Rolling 7-day window in PostgreSQL

Accepted Answer

CURRENT_DATE - INTERVAL '7 days' computes the date 7 days before today. The >= operator then keeps all rows on or after that date. DATEADD and DATEDIFF are SQL Server / MySQL functions - not valid PostgreSQL. This pattern (CURRENT_DATE - INTERVAL) is the standard PostgreSQL idiom for rolling date filters.

Question 7

Capturing every timestamp in a single day

Accepted Answer

For a TIMESTAMP column, = '2024-01-15' only matches midnight (00:00:00) exactly - rows at any other time are excluded. BETWEEN '2024-01-15 00:00:00' AND '2024-01-15 23:59:59' misses timestamps between 23:59:59 and midnight (e.g. 23:59:59.5). The half-open interval >= start AND < next_day captures all timestamps for the entire day without any gaps. DATE(created_at) = '2024-01-15' works but prevents index use on created_at in most databases.

Question 8

Picking rows from one specific month

Accepted Answer

EXTRACT(YEAR FROM ...) = 2024 AND EXTRACT(MONTH FROM ...) = 1 filters to January 2024. Order 1 is in 2023 (wrong year). Order 4 is in February (wrong month). Orders 2 and 3 are both in January 2024. An alternative that uses an index better: WHERE order_date >= '2024-01-01' AND order_date < '2024-02-01' - EXTRACT on a column usually prevents index use.

Question 9

Which DOW number maps to Sunday?

Accepted Answer

In PostgreSQL, EXTRACT(DOW FROM date) returns the day of week as an integer from 0 (Sunday) through 6 (Saturday). So Sunday is 0, not 7. EXTRACT(DAY FROM ...) gives the day of the month (1-31), not the day of the week. MySQL uses DAYOFWEEK() where 1=Sunday, making the numbering off-by-one compared to PostgreSQL - always check the documentation for the database you are using.

Question 10

CURRENT_DATE vs NOW() for date filtering

Accepted Answer

CURRENT_DATE gives you just the calendar date - no hours, minutes, or seconds. NOW() (or CURRENT_TIMESTAMP) gives the full timestamp including time zone. When you write WHERE created_at >= CURRENT_DATE, the date is implicitly cast to '2024-04-23 00:00:00', which is what you usually want for 'since the start of today'. Using NOW() - INTERVAL '7 days' works well too, but it compares to the current exact time 7 days ago rather than the start of that day - a subtle difference worth knowing.

Question 11

Which date filter can use the index?

Accepted Answer

An index on created_at stores raw timestamp values. A range filter (>= / <) lets the database seek directly to the start of the range and scan forward - it uses the index. DATE_TRUNC('month', created_at) = '2024-01-01' applies a function to every row's value before comparing. The database must evaluate DATE_TRUNC for every row, making the index unusable (a full table scan). This is the general rule: applying a function to an indexed column in WHERE defeats the index. Keep the column bare on one side.

Question 12

Spotting rows with no date set

Accepted Answer

IS NULL finds rows where the date column has no value. This is the standard way to find incomplete or open tasks - those without a completion date. WHERE completed_at = NULL would return 0 rows because = NULL is UNKNOWN, not TRUE. IS NOT NULL finds the opposite: tasks that have been completed.

Question 13

Rows that actually have a deadline

Accepted Answer

IS NOT NULL returns rows where the column has a real value - not missing. Alpha and Gamma both have a deadline set, so they pass. Beta and Delta have NULL deadlines and are excluded. You cannot use != NULL or <> NULL for this check; those expressions evaluate to UNKNOWN and match nothing. IS NOT NULL is the correct and only reliable way to filter for rows that have a date.

Date Filtering