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

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()`. ```sql 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. ```sql 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. ```sql 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. ```sql 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**. ```sql 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.