Learn Working with Dates and Times | Date and Time Functions

When managing data in SQL, you often need to work with dates and times. Understanding date and time data types is essential because they allow you to track when orders are placed, record delivery times, and measure durations. These data types include DATE, which stores just the calendar date; TIME, which stores only the time of day; and DATETIME, which combines both date and time into a single value. Using the correct data type helps ensure accuracy in your records and enables you to perform calculations, comparisons, and reporting based on time-related data.


              1234
            
-- Get the current date, time, and date-time in SQL
SELECT CURRENT_DATE AS today;
SELECT CURRENT_TIME AS current_time;
SELECT NOW() AS current_timestamp;

You can extract specific parts of a date in PostgreSQL using the EXTRACT function or simple type casts. This allows you to retrieve the year, month, day, or even more detailed components such as hours or minutes.


              1234567891011
            
-- Calculate how many days it took for each order to be shipped
SELECT
  order_id,
  (shipped_at::date - order_date::date) AS days_to_ship
FROM orders;

-- Add 3 days to the order_date to estimate a delivery date
SELECT
  order_id,
  (order_date::date + INTERVAL '3 day')::date AS estimated_delivery
FROM orders;

1. Which function returns the current date in SQL?

2. What does DATEDIFF calculate?

3. Fill in the blanks to extract the year from a date:

Everything was clear?

Thanks for your feedback!

Section 2. Chapter 1

Ask AI

Ask anything or try one of the suggested questions to begin our chat

Suggested prompts:

Can you explain how the INTERVAL keyword works in SQL?

What other date and time calculations can I perform in SQL?

How do I extract specific parts like year or month from a date in SQL?

Swipe to show menu