Date and Time Functions

Lesson content

Date and Time Functions Date and Time Functions is a practical SQL concept used to design, query, secure, operate, or analyze relational data correctly. Example SELECT UPPER(name), ROUND(price, 2), CURRENT_DATE FROM products; Key point Use the smallest correct statement, test it with representative data, and verify constraints and performance before production use. Real-life example A sales manager needs a monthly report showing revenue, order counts, rankings, and changes over time without exporting raw data to a spreadsheet. Advanced example BEGIN; -- Preview the exact target set first. SELECT id FROM orders WHERE status = 'pending'; -- Apply the Date and Time Functions operation, verify affected rows, then commit. COMMIT; Expected result The query returns only the intended rows and columns, with deterministic ordering where order matters. Production check Test with empty, duplicate, null, and boundary values. Use a transaction for related writes. Inspect the execution plan before adding an index. Use parameterized queries for application input. Continue with the PicoStore database This lesson reuses picostore . Relevant tables: customers, products, orders, order_items . Keep the starter rows from the Introduction lesson so results remain comparable. Another practical example SELECT category, COUNT(*) AS products, ROUND(AVG(price), 2) AS average_price, SUM(stock) AS units_available FROM products GROUP BY category HAVING SUM(stock) > 0; Check the result Run the verification query, compare the returned rows with the starter data, and explain why every included or excluded row is correct. SQL reference catalog: Date and Time Functions This catalog covers the practical public SQL surface. Check the exact server-version documentation before using a vendor-specific item. Portable/current values CURRENT_DATE — current date CURRENT_TIME — current time CURRENT_TIMESTAMP — current timestamp LOCALTIME — local time LOCALTIMESTAMP — local timestamp EXTRACT — extract year, month, day, and other fields MySQL date/time functions NOW / SYSDATE — current date and time CURDATE / CURTIME — current date or time DATE — date portion TIME — time portion YEAR / MONTH / DAY — individual date fields DATE_ADD / DATE_SUB — date arithmetic DATEDIFF — days between dates TIMESTAMPDIFF — difference in selected unit DATE_FORMAT — format date as text STR_TO_DATE — parse text as date LAST_DAY — last date of month DAYOFWEEK / WEEKDAY / WEEK — calendar helpers UNIX_TIMESTAMP / FROM_UNIXTIME — Unix time conversion CONVERT_TZ — timezone conversion PostgreSQL date/time functions NOW / TRANSACTION_TIMESTAMP — transaction start timestamp STATEMENT_TIMESTAMP — statement start timestamp CLOCK_TIMESTAMP — actual changing clock time DATE_TRUNC — truncate to a time unit DATE_PART — extract a field AGE — symbolic interval MAKE_DATE / MAKE_TIME / MAKE_TIMESTAMP — construct values TO_DATE / TO_TIMESTAMP — parse formatted text TO_CHAR — format date or number AT TIME ZONE — timezone conversion JUSTIFY_DAYS / JUSTIFY_HOURS — normalize intervals GENERATE_SERIES — generate date/time rows Easy example Start with a small customer table and retrieve active customers in a predictable order. SELECT customer_id, name, email FROM customers WHERE status = 'active' ORDER BY name; How to verify the easy example Run it with representative input. Confirm the expected output. Try one missing, invalid, or boundary value. Advanced example Use a CTE and a window function to rank customer revenue while keeping the query readable and testable. WITH customer_revenue AS ( SELECT customer_id, SUM(total_amount) AS revenue FROM orders WHERE order_status = 'completed' GROUP BY customer_id ) SELECT customer_id, revenue, DENSE_RANK() OVER (ORDER BY revenue DESC) AS revenue_rank FROM customer_revenue ORDER BY revenue_rank, customer_id; Advanced review Explain the tradeoffs and assumptions. Test failure, scale, security, and recovery behavior. Capture evidence from tests, execution plans, logs, or review output. Additional practical guidance Function names and interval syntax differ. PostgreSQL commonly uses date_trunc and INTERVAL; MySQL commonly uses DATE_FORMAT and DATE_ADD.