Right, let’s talk about the tools you’ll actually use to wrangle time in SQL. Forget abstract theory; we’re going straight to the functions that will save your skin and ruin your day if you get them wrong. I’m talking about getting the right now, slicing timestamps into parts, and figuring out how much time has passed without losing your mind.

First, a crucial public service announcement: now() and current_timestamp are the same function. current_timestamp is the standard SQL verbiage; now() is PostgreSQL’s more friendly, colloquial alias. They don’t return a static value; they return the moment the current transaction began. This is a lifesaver for consistency. If you use it ten times in a single transaction, every call returns the identical timestamp. It freezes time for the scope of your transaction, so you’re not dealing with a slightly different “now” on each line, which would be a nightmare for data integrity.

BEGIN;
SELECT now(); -- Returns 2023-10-25 14:30:00.000000
-- ... some other queries that take 5 seconds ...
SELECT now(); -- Still returns 2023-10-25 14:30:00.000000
COMMIT;

If you need the actual advancing clock time within a transaction, you’d use clock_timestamp(), but that’s a recipe for confusion for most use cases. Stick with now().

Getting Precise with extract() and date_trunc()

These two are your scalpel and your cleaver. extract() is for when you need a single field from a timestamp, like the year, the hour, or the dreadfully misnamed “epoch” (which is just the number of seconds since January 1, 1970, UTC).

SELECT
    extract(YEAR FROM now()) AS year,
    extract(DOW FROM now()) AS day_of_week, -- 0=Sunday, 6=Saturday. Because of course.
    extract(EPOCH FROM now()) AS seconds_since_1970;

date_trunc(), on the other hand, is brilliant for grouping or rounding down to a specific precision. It truncates the timestamp, setting all smaller units to zero (or one, for month/day). Want to group all events by the hour they occurred in? This is your function.

SELECT date_trunc('hour', now()); -- returns something like 2023-10-25 14:00:00
SELECT date_trunc('month', timestamp '2023-10-25 14:42:19'); -- returns 2023-10-01 00:00:00

It’s the ultimate “I don’t care about the details below this level” tool.

The Deceptive Simplicity of age()

age() seems straightforward: it gives you the difference between two timestamps. Its output is an interval, which is a human-readable format like “3 years 2 mons 5 days”. The classic use is calculating a person’s age.

SELECT age(timestamp '2000-01-01', timestamp '2023-10-25');
-- Result: 23 years 9 mons 24 days

But here’s the first pitfall: age(timestamp) with one argument is a shortcut for age(current_timestamp, timestamp), which is almost always what you want for an age-in-years calculation.

The second, more insidious pitfall is that an interval can be ambiguous. It represents a human concept of time, not a fixed number of seconds. A day isn’t always 86,400 seconds (thanks, Daylight Saving Time). A month isn’t a fixed number of days. This is why you can’t just convert an interval to a number of days and expect precision for date arithmetic. For that, you need to operate on the timestamp itself.

The Interval Arithmetic Gotcha

This is where people’s brains short-circuit. You cannot add one “month” interval to a date and get a predictably identical day of the month. It’s a calendar operation, not a math operation.

SELECT (timestamp '2023-01-31' + interval '1 month') AS result;
-- You might hope for Feb 31st. The database gives you 2023-02-28. It does its best.
SELECT (timestamp '2023-03-31' + interval '1 month') AS result;
-- This gives you 2023-04-30. Not wrong, just... calendar-aware.

This is the correct behavior, but it’s a common source of bugs if you expected pure mathematical addition. Always be aware you’re working with a calendar, not a number line.

The golden rule? Use now() and current_timestamp for transaction-time consistency. Use extract() to grab parts and date_trunc() to group. Use age() for human-readable durations but be hyper-aware of its limitations for precise arithmetic. And never, ever assume a month is 30 days. The database is smarter than that, and it will happily prove you wrong.