Date and Time Functions is a practical SQL concept used to design, query, secure, operate, or analyze relational data correctly.
SELECT UPPER(name), ROUND(price, 2), CURRENT_DATE FROM products;Use the smallest correct statement, test it with representative data, and verify constraints and performance before production use.
A sales manager needs a monthly report showing revenue, order counts, rankings, and changes over time without exporting raw data to a spreadsheet.
BEGIN;\n-- Preview the exact target set first.\nSELECT id FROM orders WHERE status = 'pending';\n-- Apply the Date and Time Functions operation, verify affected rows, then commit.\nCOMMIT;The query returns only the intended rows and columns, with deterministic ordering where order matters.
This lesson reuses picostore. Relevant tables: customers, products, orders, order_items. Keep the starter rows from the Introduction lesson so results remain comparable.
SELECT category,\n COUNT(*) AS products,\n ROUND(AVG(price), 2) AS average_price,\n SUM(stock) AS units_available\nFROM products\nGROUP BY category\nHAVING SUM(stock) > 0;Run the verification query, compare the returned rows with the starter data, and explain why every included or excluded row is correct.
This catalog covers the practical public SQL surface. Check the exact server-version documentation before using a vendor-specific item.
CURRENT_DATE — current dateCURRENT_TIME — current timeCURRENT_TIMESTAMP — current timestampLOCALTIME — local timeLOCALTIMESTAMP — local timestampEXTRACT — extract year, month, day, and other fieldsNOW / SYSDATE — current date and timeCURDATE / CURTIME — current date or timeDATE — date portionTIME — time portionYEAR / MONTH / DAY — individual date fieldsDATE_ADD / DATE_SUB — date arithmeticDATEDIFF — days between datesTIMESTAMPDIFF — difference in selected unitDATE_FORMAT — format date as textSTR_TO_DATE — parse text as dateLAST_DAY — last date of monthDAYOFWEEK / WEEKDAY / WEEK — calendar helpersUNIX_TIMESTAMP / FROM_UNIXTIME — Unix time conversionCONVERT_TZ — timezone conversionNOW / TRANSACTION_TIMESTAMP — transaction start timestampSTATEMENT_TIMESTAMP — statement start timestampCLOCK_TIMESTAMP — actual changing clock timeDATE_TRUNC — truncate to a time unitDATE_PART — extract a fieldAGE — symbolic intervalMAKE_DATE / MAKE_TIME / MAKE_TIMESTAMP — construct valuesTO_DATE / TO_TIMESTAMP — parse formatted textTO_CHAR — format date or numberAT TIME ZONE — timezone conversionJUSTIFY_DAYS / JUSTIFY_HOURS — normalize intervalsGENERATE_SERIES — generate date/time rowsStart with a small customer table and retrieve active customers in a predictable order.
SELECT customer_id, name, email\nFROM customers\nWHERE status = 'active'\nORDER BY name;Use a CTE and a window function to rank customer revenue while keeping the query readable and testable.
WITH customer_revenue AS (\n SELECT customer_id, SUM(total_amount) AS revenue\n FROM orders\n WHERE order_status = 'completed'\n GROUP BY customer_id\n)\nSELECT customer_id, revenue,\n DENSE_RANK() OVER (ORDER BY revenue DESC) AS revenue_rank\nFROM customer_revenue\nORDER BY revenue_rank, customer_id;Date and Time Functions is a practical SQL concept used to design, query, secure, operate, or analyze relational data correctly.
SELECT UPPER(name), ROUND(price, 2), CURRENT_DATE FROM products;Use the smallest correct statement, test it with representative data, and verify constraints and performance before production use.
A sales manager needs a monthly report showing revenue, order counts, rankings, and changes over time without exporting raw data to a spreadsheet.
BEGIN;\n-- Preview the exact target set first.\nSELECT id FROM orders WHERE status = 'pending';\n-- Apply the Date and Time Functions operation, verify affected rows, then commit.\nCOMMIT;The query returns only the intended rows and columns, with deterministic ordering where order matters.
This lesson reuses picostore. Relevant tables: customers, products, orders, order_items. Keep the starter rows from the Introduction lesson so results remain comparable.
SELECT category,\n COUNT(*) AS products,\n ROUND(AVG(price), 2) AS average_price,\n SUM(stock) AS units_available\nFROM products\nGROUP BY category\nHAVING SUM(stock) > 0;Run the verification query, compare the returned rows with the starter data, and explain why every included or excluded row is correct.
This catalog covers the practical public SQL surface. Check the exact server-version documentation before using a vendor-specific item.
CURRENT_DATE — current dateCURRENT_TIME — current timeCURRENT_TIMESTAMP — current timestampLOCALTIME — local timeLOCALTIMESTAMP — local timestampEXTRACT — extract year, month, day, and other fieldsNOW / SYSDATE — current date and timeCURDATE / CURTIME — current date or timeDATE — date portionTIME — time portionYEAR / MONTH / DAY — individual date fieldsDATE_ADD / DATE_SUB — date arithmeticDATEDIFF — days between datesTIMESTAMPDIFF — difference in selected unitDATE_FORMAT — format date as textSTR_TO_DATE — parse text as dateLAST_DAY — last date of monthDAYOFWEEK / WEEKDAY / WEEK — calendar helpersUNIX_TIMESTAMP / FROM_UNIXTIME — Unix time conversionCONVERT_TZ — timezone conversionNOW / TRANSACTION_TIMESTAMP — transaction start timestampSTATEMENT_TIMESTAMP — statement start timestampCLOCK_TIMESTAMP — actual changing clock timeDATE_TRUNC — truncate to a time unitDATE_PART — extract a fieldAGE — symbolic intervalMAKE_DATE / MAKE_TIME / MAKE_TIMESTAMP — construct valuesTO_DATE / TO_TIMESTAMP — parse formatted textTO_CHAR — format date or numberAT TIME ZONE — timezone conversionJUSTIFY_DAYS / JUSTIFY_HOURS — normalize intervalsGENERATE_SERIES — generate date/time rowsStart with a small customer table and retrieve active customers in a predictable order.
SELECT customer_id, name, email\nFROM customers\nWHERE status = 'active'\nORDER BY name;Use a CTE and a window function to rank customer revenue while keeping the query readable and testable.
WITH customer_revenue AS (\n SELECT customer_id, SUM(total_amount) AS revenue\n FROM orders\n WHERE order_status = 'completed'\n GROUP BY customer_id\n)\nSELECT customer_id, revenue,\n DENSE_RANK() OVER (ORDER BY revenue DESC) AS revenue_rank\nFROM customer_revenue\nORDER BY revenue_rank, customer_id;