12 — Stored Procedures and Date/Time Functions

Two topics nested together on the roadmap looked unrelated until their connection surfaced. Writing them down revealed it: stored procedures encapsulate reusable multi-statement logic server-side, and the date/time functions are the specialized toolkit for the one data type whose arithmetic is genuinely its own thing. [1][2]

The framing that clicked on the procedure side is where the logic lives. A query in application code has to travel over the wire, and complex multi-step logic means several round trips. A stored procedure parks that logic inside the database, callable by name, so a single call does the work of many statements. The framing on the date side is that dates aren't numbers — "add one month" crosses month boundaries and leap years, so SQL ships dedicated functions to do that arithmetic correctly.

Stored procedures vs functions

Stored procedures and functions are both precompiled database objects that encapsulate SQL logic, but they serve different roles [1]:

• Stored procedures encapsulate a sequence of operations — often data manipulation — and are invoked with CALL. They can run multiple statements, manage transactions, and don't necessarily return a value.
• Functions compute and return a value, and can be used inside a SELECT (e.g., SELECT compute_tax(total) FROM orders). They're more restricted — typically no side effects — so they compose into queries where procedures don't.

The shared payoff is encapsulation: the logic lives in the database, is callable by name, and every application that calls it gets the same behavior without re-implementing it. That also centralizes optimization — tune the procedure once, every caller benefits.

Why put logic server-side

The practical reasons to reach for a procedure [1]:

• Reduce network round trips. A procedure that runs five statements does so in one call instead of five, cutting latency on chatty workflows.
• Code reuse. Logic shared across services is written once in the database rather than copied into each.
• Security. Applications can be granted execute permission on a procedure without direct table access, limiting what they can do to exactly what the procedure does.

The trade-off is that logic in the database is harder to version, test, and instrument than logic in application code. I reach for procedures when the logic is tightly bound to the data (multi-step writes that must be atomic) and stay in application code when the logic is bound to the product (workflow rules, API shaping).

DATE, TIME, TIMESTAMP — the temporal types

Date and time have their own data types because they're not plain numbers [2][3][4]:

• DATE — calendar date (2024-03-15), no time. Used for birthdates, event days [2].
• TIME — time of day (14:30:00), no date.
• TIMESTAMP — a specific point in time, date plus time, often with sub-second precision. The standard created_at / updated_at columns [3].

TIMESTAMP is the workhorse for "when did this happen," and many engines auto-update a timestamp column when its row changes — the canonical updated_at behavior.

DATEPART — pull a piece out

DATEPART extracts a single component — year, month, day, hour, etc. — from a date or timestamp [5]:

SELECT DATEPART(year, order_placed_at) AS yr,
       DATEPART(month, order_placed_at) AS mon
FROM orders;

This is how I bucket time-series data: group by DATEPART(year, ...) for annual summaries, by the month for monthly, by the weekday for "which day of the week gets the most orders." Most engines also offer a EXTRACT(field FROM col) spelling that does the same thing.

DATEADD — shift a date in time

DATEADD adds (or subtracts, with a negative count) an interval to a date — days, months, years [6]:

-- SQL Server spelling
SELECT DATEADD(day, 30, order_placed_at) AS ships_by FROM orders;

-- MySQL spelling
SELECT DATE_ADD(order_placed_at, INTERVAL 30 DAY) AS ships_by FROM orders;

The reason this needs a dedicated function rather than + 30 is correct boundary handling: "add one month" to 2024-01-31 should yield 2024-02-29 (a leap year), not 2024-03-02. The function knows the calendar; arithmetic on a number doesn't.

How I use this

Two habits, one per side. For procedures: I put logic in the database when it's about the data's integrity (a multi-step write that must be atomic, a calculation every caller needs identically) and keep it in the application when it's about the product (workflow, presentation). For dates: I never store dates as strings or epoch numbers — I use the native DATE/TIMESTAMP types and do all arithmetic through DATEADD/DATEPART, because every "why are my dates off by one" bug I've met traces back to hand-rolled date math that ignored a leap year or a timezone.

References

[1] Shiksha, "Stored Procedure vs Function — What are the differences?," shiksha.com, 2024. [Online]. Available: https://www.shiksha.com/online-courses/articles/stored-procedure-vs-function-what-are-the-differences/

[2] YouTube, "Working with Dates," 2023. [Online]. Available: https://www.youtube.com/watch?v=XyZ9HwXoR7o

[3] SQLShack, "Different SQL TimeStamp functions in SQL Server," sqlshack.com, 2024. [Online]. Available: https://www.sqlshack.com/different-sql-timestamp-functions-in-sql-server/

[4] PostgreSQL Tutorial, "PostgreSQL Data Types," postgresqltutorial.com, 2024. [Online]. Available: https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-data-types/

[5] Hightouch, "SQL DATEPART," hightouch.com, 2024. [Online]. Available: https://hightouch.com/sql-dictionary/sql-datepart

[6] MSSQLTips, "SQL DATEADD function," mssqltips.com, 2024. [Online]. Available: https://www.mssqltips.com/sqlservertutorial/9380/sql-dateadd-function/