Q: What does DATE_TRUNC do to the time?

DATE_TRUNC(field, timestamp) rounds the timestamp down to the start of the specified unit. Truncating to 'month' sets the day to 1 and time to 00:00:00, giving 2024-07-01 00:00:00. Truncating to 'year' would give 2024-01-01 00:00:00; to 'day' would give 2024-07-15 00:00:00. This is the standard way to group data by month or year in PostgreSQL.

Q: Monthly rollup - spot the key column

DATE_TRUNC('month', created_at) maps both January timestamps to 2024-01-01 00:00:00. GROUP BY then collapses them into one group. January total = 100+200 = 300. February total = 150. This DATE_TRUNC + GROUP BY pattern is the standard approach for time-series aggregation in PostgreSQL.

Q: Full years from a birthdate - which expression works?

AGE(timestamp) in PostgreSQL returns the interval between the given timestamp and now (e.g. '30 years 4 months 12 days'). EXTRACT(YEAR FROM AGE(birth_date)) then pulls the year component - the number of complete years elapsed. CURRENT_DATE - birth_date gives days, not years. DATEDIFF is a MySQL/SQL Server function, not standard PostgreSQL.

Question 1

Which field does EXTRACT pull?

Accepted Answer

EXTRACT(field FROM date) pulls out a single component of a date or timestamp. YEAR returns 2024, MONTH would return 7, DAY would return 15. QUARTER would return 3 (July is in Q3) - a common confusion when the field name is easy to mix up. PostgreSQL also supports DATE_PART('year', date) which works identically. Common fields: YEAR, MONTH, DAY, QUARTER, HOUR, MINUTE, SECOND, DOW (day of week, 0=Sunday), DOY (day of year).

Question 2

Getting the current date and time

Accepted Answer

CURRENT_DATE returns today's date as a date type (e.g. 2025-01-15) with no time component. NOW() and CURRENT_TIMESTAMP return a timestamp with date and time. GETDATE() is SQL Server syntax, not standard SQL. Use CURRENT_DATE when you only need the date - it avoids time-of-day edge cases in range comparisons.

Question 3

Off-by-one trap: subtracting dates

Accepted Answer

Subtracting one DATE from another in PostgreSQL returns an integer - the number of days between them. January 20 - January 15 = 5. A common off-by-one mistake: counting intermediate days (16, 17, 18, 19, 20) and getting 4, but SQL date subtraction counts from the start date itself, not the first day after it. In MySQL, use DATEDIFF(end_date, start_date) for the same result. To subtract timestamps and get an interval (with hours/minutes), subtract two TIMESTAMP values instead.

Question 4

7 days forward - where do you land?

Accepted Answer

Adding an INTERVAL to a DATE shifts the date by that amount. January 15 + 7 days = January 22. Confusing the unit gives a different answer: '7 months' would produce August 15, '1 month' would produce February 15. Intervals can express any unit: '1 month', '2 years', '3 hours', '30 minutes', or combinations like '1 year 2 months'. Subtraction also works: DATE '2024-01-15' - INTERVAL '7 days' = 2024-01-08.

Question 5

NOW() vs CURRENT_TIMESTAMP

Accepted Answer

In PostgreSQL, NOW() is an alias for CURRENT_TIMESTAMP - they return the same value and both reflect the start of the current transaction, not the moment of each row evaluation. Both return a TIMESTAMPTZ (timestamp with time zone). If you want the true wall-clock time at the moment of evaluation (not transaction start), use clock_timestamp() instead.

Question 6

Zero-padded or raw number?

Accepted Answer

EXTRACT(MONTH ...) returns the numeric month (1-12), and EXTRACT(DAY ...) returns the day of the month (1-31). For July 15, the results are 7 and 15. Note that EXTRACT returns a numeric value, not a zero-padded string - you get 7, not '07'. This is handy when filtering by month across multiple years: WHERE EXTRACT(MONTH FROM created_at) = 7.

Question 7

Which rows survive the date window?

Accepted Answer

The range >= '2024-01-10' AND < '2024-02-01' includes January 10 through January 31 (using an exclusive upper bound). Alpha (Jan 5) is before the start. Beta (Jan 15) and Gamma (Jan 31) are both within the range. Misreading '2024-02-01' as '2024-01-20' (transposing month and day) would cut off Gamma and return only Alpha and Beta - a classic date-string parsing mistake. Using an exclusive upper bound (< '2024-02-01') rather than <= '2024-01-31' is safer with timestamps - it avoids cutting off events at 2024-01-31 23:59:59.

Question 8

What does DATE_TRUNC do to the time?

Accepted Answer

DATE_TRUNC(field, timestamp) rounds the timestamp down to the start of the specified unit. Truncating to 'month' sets the day to 1 and time to 00:00:00, giving 2024-07-01 00:00:00. Truncating to 'year' would give 2024-01-01 00:00:00; to 'day' would give 2024-07-15 00:00:00. This is the standard way to group data by month or year in PostgreSQL.

Question 9

Monthly rollup - spot the key column

Accepted Answer

DATE_TRUNC('month', created_at) maps both January timestamps to 2024-01-01 00:00:00. GROUP BY then collapses them into one group. January total = 100+200 = 300. February total = 150. This DATE_TRUNC + GROUP BY pattern is the standard approach for time-series aggregation in PostgreSQL.

Question 10

Full years from a birthdate - which expression works?

Accepted Answer

AGE(timestamp) in PostgreSQL returns the interval between the given timestamp and now (e.g. '30 years 4 months 12 days'). EXTRACT(YEAR FROM AGE(birth_date)) then pulls the year component - the number of complete years elapsed. CURRENT_DATE - birth_date gives days, not years. DATEDIFF is a MySQL/SQL Server function, not standard PostgreSQL.

Question 11

TIMESTAMP vs TIMESTAMPTZ

Accepted Answer

TIMESTAMP WITHOUT TIME ZONE stores the literal date and time you give it, with no timezone context. If you store '2024-01-15 14:00:00', that's what you get back regardless of the session timezone. TIMESTAMPTZ (TIMESTAMP WITH TIME ZONE) normalizes the value to UTC on storage and converts back to the session timezone on retrieval. For most applications that serve users across timezones, TIMESTAMPTZ is the right choice - it prevents the silent data corruption that comes from mixing timezone-unaware timestamps.

Question 12

Which January filter won't miss late-night rows?

Accepted Answer

When order_date is a TIMESTAMP, '<= 2024-01-31' stops at midnight on Jan 31 - any order placed later on that day (e.g. 2024-01-31 14:00:00) is excluded. Using an exclusive upper bound of '2024-02-01' captures all of January 31. BETWEEN has the same problem as <= on timestamps. The EXTRACT approach works but cannot use an index on order_date effectively.

Date Functions