Time Functions

Time and Timestamp Functions

Where date functions deal with calendar dates, time functions deal with time-of-day and combined date+time values.

Recap: DATE vs TIME vs TIMESTAMP/DATETIME (Cross-Reference to Chapter 8)

As introduced in Chapter 8 (Data Types), these three types store fundamentally different things:

Choosing the wrong one is a common design mistake — e.g. storing an order's placement moment as DATE loses the time-of-day entirely, making it impossible to answer "which orders were placed in the morning?"

CURRENT_TIME — Time of Day Right Now

sql
SELECT CURRENT_TIME;
-- e.g. 09:42:17   (just the time, no date)

CURRENT_TIMESTAMP / NOW() — Date and Time Right Now

sql
SELECT CURRENT_TIMESTAMP;
-- e.g. 2026-09-14 09:42:17

SELECT NOW();
-- e.g. 2026-09-14 09:42:17   (MySQL / PostgreSQL convenience alias)

Dialect variance: CURRENT_TIMESTAMP is standard SQL and works broadly. NOW() is a widely supported convenience function (MySQL, PostgreSQL) that is generally equivalent to CURRENT_TIMESTAMP. SQL Server uses GETDATE() for the same purpose.

Adding a Placed-At Column and Querying by Time

sql
ALTER TABLE orders ADD COLUMN placed_at TIMESTAMP;
UPDATE orders SET placed_at = '2024-01-05 14:30:00' WHERE id = 1;

SELECT * FROM orders WHERE EXTRACT(HOUR FROM placed_at) < 12;
-- orders placed before noon

Time Arithmetic

Time arithmetic follows the same interval patterns as date arithmetic:

sql
-- PostgreSQL
SELECT placed_at + INTERVAL '2 hours' AS two_hours_later FROM orders WHERE id = 1;
-- 2024-01-05 16:30:00

-- MySQL
SELECT DATE_ADD(placed_at, INTERVAL 2 HOUR) AS two_hours_later FROM orders WHERE id = 1;
-- 2024-01-05 16:30:00

Subtracting two TIMESTAMP values typically yields an interval (PostgreSQL) or requires an explicit function like TIMESTAMPDIFF() (MySQL) to get a numeric difference in a chosen unit:

sql
-- MySQL: difference in minutes between two timestamps
SELECT TIMESTAMPDIFF(MINUTE, '2024-01-05 14:30:00', '2024-01-05 16:30:00') AS minutes_apart;
-- 120

Edge Cases

• Mixing DATE and TIMESTAMP values in a comparison usually works because the DATE is implicitly treated as midnight (00:00:00) of that day — but this can cause subtle off-by-one-day bugs in range filters (e.g. WHERE placed_at <= '2024-01-05' silently excludes any time later than midnight on the 5th).
• Time zone handling differs sharply by type name: plain TIMESTAMP (or DATETIME) usually stores a "naive" value with no time zone, while TIMESTAMPTZ / TIMESTAMP WITH TIME ZONE (PostgreSQL) stores and normalizes a time zone-aware instant — mixing the two carelessly is a common source of bugs in distributed applications.
• CURRENT_TIME and CURRENT_TIMESTAMP are typically evaluated once per statement (not once per row), so all rows in the same query see the identical "now" value.

Key Takeaways / Interview Q&A

Q: What's the difference between DATE, TIME, and TIMESTAMP? A: DATE stores only a calendar date, TIME stores only a time-of-day, and TIMESTAMP/DATETIME stores both together.

Q: What does NOW() return, and where is it commonly available? A: The current date and time together; it's a common convenience alias for CURRENT_TIMESTAMP in MySQL and PostgreSQL. SQL Server instead uses GETDATE().

Q: Why might comparing a TIMESTAMP column to a bare date string with <= silently exclude some rows on that day? A: A bare date is implicitly midnight (00:00:00) of that day, so any TIMESTAMP later than midnight on the target date fails a <= comparison meant to include the whole day.

Q: What's the practical difference between plain TIMESTAMP and TIMESTAMPTZ? A: Plain TIMESTAMP has no time zone awareness ("naive"); TIMESTAMPTZ (PostgreSQL) stores/normalizes a time-zone-aware instant, which matters for distributed or multi-region applications.