Jessica Aki

Posted on Jan 21

SQL Dates Finally Made Sense: NOW, AGE, EXTRACT, and Why Time Is Weird

#sql #postgres #beginners #database

Up until now, SQL felt very… solid.

Numbers are numbers. Strings are strings. GROUP BY either works or it doesn’t.

Dates?
Dates humbled me.

Today I spent time learning how SQL handles time and I finally understand why people say dates are one of the hardest parts of databases.

This post is me documenting what clicked, what confused me, and what I’ll definitely need to revisit.

The First Surprise: Dates Are Not Just Numbers or strings

At first, I thought dates were just formatted numbers .

Turns out they’re not.
Dates come with:

time zones
hidden time components
built-in functions that behave differently depending on context

Once I accepted that, things started to make more sense.

NOW() — The Current Moment

The simplest place to start was NOW().

SELECT NOW();

This returns the current timestamp**, that is, it's not just the date, but the exact moment the query runs.

What clicked for me:

NOW() includes date and time, including the time zone
It’s commonly used for logs, audits, and tracking events

Seeing a real timestamp come back made SQL feel a lot more like a backend system, not just a learning exercise.

AGE() — Time Between Two Dates

This function confused me at first, mostly because of the example usage i saw.

SELECT AGE(date_of_birth);

This example was where a users date of birth was passed into the function and the age was produced, but what I didn't understand at the time was that it's more flexible than just getting the difference between now and that day.

SELECT AGE(DATE '2025-10-10', '2021-01-10'); 
-- The amount of time I spent in Univeristy

What AGE() actually does:

Returns the time difference between two timestamps
The result is an interval, not a number

This is important.

Instead of giving me days or seconds, it returns something like:

4 years 9 mons

This made sense for things like:

How old an account is
How long ago something happened
Time since last activity

EXTRACT() — Pulling Out Pieces of Time

This is where things started clicking properly.

Sometimes you don’t want the full date.
You just want part of it.

SELECT EXTRACT(YEAR FROM  AGE(DATE '2025-10-10', '2021-01-10')); --4
SELECT EXTRACT(MONTH FROM  AGE(DATE '2025-10-10', '2021-01-10')); --9 
SELECT EXTRACT(DAY FROM  AGE(DATE '2025-10-10', '2021-01-10')); --0
SELECT EXTRACT(DOW FROM  '2025-10-10'); --5 (Friday)
-- DOW is Day of the week, it returns a value between 0 and 6 to represent the day of the week with Sunday being 0

What finally made sense:

EXTRACT doesn’t change the date
It reads a specific part of it

This is useful for:

grouping by year or month
filtering specific periods
reporting trends over time

Once I saw it as “reading metadata from time”, not transforming it, it felt less magical.

Intervals — Time as a Data Type

This part surprised me.

Time differences aren’t just numbers, rather SQL treats them as intervals.

SELECT NOW() - INTERVAL '7 days';
SELECT NOW() + INTERVAL '1 month';

That means SQL understands:

days
months
years
hours, minutes, seconds

And it handles calendar logic for you.

No manual math. No guessing.

What Still Feels Weird

I’m being honest, some things still feel slippery:

Time zones (I know this will bite me later)
Comparing dates vs timestamps
Understanding when time gets truncated or rounded

But compared to yesterday, I’m no longer scared of date functions.

That’s progress.

Conclusion

Dates are usually taught quickly, like:

“Here’s NOW(). Here’s EXTRACT(). Moving on.”

But as a beginner, this isn’t about syntax.
It’s about understanding how databases think about time.

If dates confused you too, you’re not behind.
They confuse everyone.

I’m learning SQL slowly, properly, and in public and documenting both the clarity and the confusion.

If you’re on the same path, you’re not alone. You can join me on this series and learn with me.

— Jessica Aki
Data and Database Engineering Enthusiast

I’m documenting my journey into data engineering learning SQL, databases, and the systems behind modern data platforms.

DEV Community