LeetCode SQL - Datetime

Table of contents

Date and Time Functions

Current Date/Time

SELECT
    CURRENT_DATE AS today,                              -- 2025-10-03
    CURRENT_TIME AS now_time,                           -- 14:30:25
    CURRENT_TIMESTAMP AS now,                           -- 2025-10-03 14:30:25
    NOW() AS now_alt,                                   -- 2025-10-03 14:30:25 (PostgreSQL)
    CLOCK_TIMESTAMP() AS exact_now;                     -- Changes during query execution

Extraction

SELECT
    EXTRACT(YEAR FROM CURRENT_DATE) AS year,            -- 2025
    EXTRACT(MONTH FROM CURRENT_DATE) AS month,          -- 10
    EXTRACT(DAY FROM CURRENT_DATE) AS day,              -- 3
    EXTRACT(HOUR FROM CURRENT_TIMESTAMP) AS hour,       -- 14
    EXTRACT(DOW FROM CURRENT_DATE) AS day_of_week,      -- 0-6 (0=Sunday)
    EXTRACT(EPOCH FROM CURRENT_TIMESTAMP) AS unix_time; -- Seconds since 1970

Date Parts (PostgreSQL)

SELECT
    DATE_PART('year', CURRENT_DATE) AS year,
    DATE_PART('quarter', CURRENT_DATE) AS quarter,
    DATE_PART('week', CURRENT_DATE) AS week_number;

Date Arithmetic

SELECT
    CURRENT_DATE + INTERVAL '7 days' AS next_week,
    CURRENT_DATE - INTERVAL '1 month' AS last_month,
    CURRENT_DATE + INTERVAL '2 years' AS two_years_later,
    CURRENT_TIMESTAMP + INTERVAL '3 hours' AS three_hours_later;

Date Difference

SELECT
    AGE('2025-10-03', '2020-01-01') AS age,                    -- 5 years 9 mons 2 days
    '2025-10-03'::DATE - '2025-10-01'::DATE AS days_diff,      -- 2
    EXTRACT(DAY FROM '2025-10-03'::DATE - '2025-10-01'::DATE) AS days;

Formatting

SELECT
    TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD') AS iso_date,          -- '2025-10-03'
    TO_CHAR(CURRENT_DATE, 'Month DD, YYYY') AS formatted,     -- 'October 03, 2025'
    TO_CHAR(CURRENT_DATE, 'Day') AS day_name,                 -- 'Friday'
    TO_CHAR(CURRENT_TIMESTAMP, 'HH24:MI:SS') AS time_24hr;    -- '14:30:25'

Date Truncation

SELECT
    DATE_TRUNC('year', CURRENT_TIMESTAMP) AS year_start,      -- 2025-01-01 00:00:00
    DATE_TRUNC('month', CURRENT_TIMESTAMP) AS month_start,    -- 2025-10-01 00:00:00
    DATE_TRUNC('week', CURRENT_TIMESTAMP) AS week_start;      -- 2025-09-29 00:00:00

Real Example:

-- Find orders from last 30 days
SELECT
    order_id,
    order_date,
    CURRENT_DATE - order_date AS days_ago,
    TO_CHAR(order_date, 'Day, Month DD, YYYY') AS formatted_date
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days';

Date

1384. Total Sales Amount by Year

WITH year_splits AS (
    SELECT
        s.product_id,
        p.product_name,
        gs.year::TEXT as report_year,
        s.period_start,
        s.period_end,
        s.average_daily_sales,
        -- Calculate start date for this year
        GREATEST(s.period_start, DATE(gs.year || '-01-01')) as year_start,
        -- Calculate end date for this year
        LEAST(s.period_end, DATE(gs.year || '-12-31')) as year_end
    FROM Sales s
    JOIN Product p ON s.product_id = p.product_id
    -- Generate consecutive years
    CROSS JOIN LATERAL 
        generate_series(
            EXTRACT(YEAR FROM s.period_start)::INTEGER,
            EXTRACT(YEAR FROM s.period_end)::INTEGER
        ) as gs(year)
)
SELECT
    product_id,
    product_name,
    report_year,
    (year_end - year_start + 1) * average_daily_sales as total_amount
FROM year_splits
ORDER BY product_id, report_year;

1107. New Users Daily Count

WITH cte AS (
    SELECT
        user_id,
        MIN(activity_date) AS first_login
    FROM Traffic
    WHERE activity = 'login'
    GROUP BY user_id
)
SELECT
    first_login AS login_date,
    COUNT(*) AS user_count
FROM cte
WHERE '2019-06-30'::DATE - first_login <= 90
GROUP BY login_date
ORDER BY login_date

1098. Unpopular Books

SELECT 
    b.book_id,
    b.name
FROM
	Books b
	LEFT JOIN Orders o ON b.book_id = o.book_id 
		AND o.dispatch_date BETWEEN '2018-06-23'::DATE AND '2019-06-23'::DATE
WHERE b.available_from < '2019-06-23'::DATE - INTERVAL '1 month'
GROUP BY
	b.book_id,
	b.name
HAVING COALESCE(SUM(o.quantity), 0) < 10
ORDER BY b.book_id

Duration

1435. Create a Session Bar Chart

WITH bins (bin, bin_order) AS (
    SELECT '[0-5>', 1
    UNION
    SELECT '[5-10>', 2
    UNION
    SELECT '[10-15>', 3
    UNION
    SELECT '15 or more', 4
), stats AS (
    SELECT
        CASE
            WHEN duration / 60 < 5 THEN '[0-5>'
            WHEN duration / 60 < 10 THEN '[5-10>'
            WHEN duration / 60 < 15 THEN '[10-15>'
            ELSE '15 or more'
        END AS bin
    FROM Sessions
)
SELECT
    b.bin,
    COUNT(s.bin) AS total
FROM
    bins b
    LEFT JOIN stats s ON b.bin = s.bin
GROUP BY
    b.bin,
    b.bin_order
ORDER BY b.bin_order

Time between

1747. Leetflex Banned Accounts

SELECT DISTINCT l1.account_id
FROM
    LogInfo l1
    JOIN LogInfo l2 ON l1.account_id = l2.account_id
    AND l1.ip_address != l2.ip_address
WHERE
    l1.logout >= l2.login
    AND l2.logout >= l1.login