SQL Date and Time Functions Practice Questions

Truncate the timestamp to the month, then group by the result.

• DATE_TRUNC('month', ts) returns the first day of that month.
• Group and order by the same expression (GROUP BY 1 works in PostgreSQL).
• EXTRACT(MONTH ...) gives only the month number, which mixes different years together.

Subtract dates for a day count, and compare against CURRENT_DATE minus an interval.

• In PostgreSQL, date minus date gives a whole number of days.
• timestamp minus timestamp gives an interval.
• Compare the column to a computed bound; avoid wrapping the column in a function so indexes still work.

Timestamps are stored in UTC and shown in your session time zone, so day and month boundaries can shift.

• Convert first with AT TIME ZONE 'Asia/Kolkata', then truncate.
• Use >= start and < next day or month; BETWEEN includes both ends and can catch midnight of the next day.
• Check the session time zone when results differ between tools.