Question 1

Date arithmetic with intervals

Accepted Answer

Adding an INTERVAL to a DATE shifts the date by that amount. January 15 + 7 days = January 22. 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 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

EXTRACT a date part

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. PostgreSQL also supports DATE_PART('year', date) which works identically. Common fields: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, DOW (day of week, 0=Sunday), DOY (day of year).

Question 4

Difference between two dates

Accepted Answer

Subtracting one DATE from another in PostgreSQL returns an integer - the number of days between them. January 20 - January 15 = 5 days. 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 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

EXTRACT month and day

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.