SQL for Data Analysis

Cover

Authors

Topics

Status

Book series

Average Rating

My Rating

My Review

Number of Pages

Private Notes

Publisher

Read Count

Series #

Year Published

Finish Date

Parent item

Progress

NaN

Read pages

Sub-item

Total pages

CHAPTER 3: Time Series Analysis

‣

PostgreSQL Date and Timestamp Functions - Study Guide

PostgreSQL Date, Timestamp, and Time Functions – Study Note

Working with dates and times is common in SQL for analytics, reporting, and scheduling. PostgreSQL provides rich functions to handle them, including formatting, extracting parts, doing arithmetic, and converting between formats.

This note covers:

Date and timestamp functions
Truncating and extracting parts of dates
Formatting dates
Date math (differences, age, intervals)
Time math (addition, subtraction, intervals)

1. Getting Current Date and Time

PostgreSQL provides multiple functions to fetch the current date and time.

SELECT current_date;      -- Returns current date only
SELECT localtimestamp;    -- Returns timestamp (without time zone)
SELECT current_timestamp; -- Returns timestamp with time zone
SELECT now();             -- Alias of current_timestamp
SELECT current_time;      -- Current time with time zone
SELECT localtime;         -- Current time without time zone

current_date → Only the date (YYYY-MM-DD)
current_timestamp and now() → Both date and time with time zone
localtimestamp and localtime → Same but without time zone info

2. Truncating Dates (`date_trunc`)

date_trunc rounds or truncates a timestamp to a specified unit (year, month, day, hour, etc.).

Syntax:

date_trunc('field', source)

Examples:

SELECT date_trunc('year', '2025-08-22 22:19:58'::timestamp);
-- 2025-01-01 00:00:00

SELECT date_trunc('day', '2025-08-22 22:19:58'::timestamp);
-- 2025-08-22 00:00:00

SELECT date_trunc('hour', '2025-08-22 22:19:58'::timestamp);
-- 2025-08-22 22:00:00

SELECT date_trunc('week', '2025-08-22 22:19:58'::timestamp);
-- 2025-08-18 00:00:00 (Monday start of week)

3. Extracting Parts of Dates

Two key functions:

a) `date_part()`

Returns a numeric value for the specified field.

Examples:

SELECT date_part('day', current_timestamp);   -- 2
SELECT date_part('month', current_timestamp); -- 9
SELECT date_part('year', current_timestamp);  -- 2025

b) `extract()`

Does the same, with different syntax.

Examples:

SELECT extract(day FROM current_timestamp);  -- 2
SELECT extract(hour FROM current_timestamp); -- 22

4. Formatting Dates (`to_char`)

to_char converts a date or timestamp into a custom string format.

Syntax:

to_char(source, 'format')

Examples:

SELECT to_char(current_timestamp, 'day');   -- 'tuesday'
SELECT to_char(current_timestamp, 'month'); -- 'september'

Useful for reporting and display.

5. Creating Dates

You can create dates explicitly.

Examples:

SELECT make_date(1977, 10, 26); -- 1977-10-26
SELECT to_date(concat(2020,'-',09,'-',01), 'YYYY-MM-DD');
-- 2020-09-01

make_date(year, month, day) → Builds a date
to_date(text, format) → Converts string into a date

6. Date Math

a) Subtracting Dates

The difference returns an integer (days).

SELECT DATE('1977-10-26') - DATE('2025-08-26') AS days; -- -17437
SELECT DATE('2025-08-01') - DATE('2025-08-26') AS days; -- -25
SELECT DATE('2025-08-26') - DATE('2025-08-01') AS days; -- 25

Order matters → negative or positive results.

b) Using `age()`

The age() function returns the interval between two dates.

SELECT age(DATE('2020-05-31'), DATE('2020-06-30'));
-- -1 mon

SELECT age(DATE('2025-10-25'), DATE('1977-10-26'));
-- 48 years

c) Extracting Components of `age`

We can extract years, months, days from an interval:

-- Years only
SELECT EXTRACT(year FROM AGE(DATE('2025-10-25'), DATE('1977-10-26'))) AS years;

-- Full breakdown
SELECT
    EXTRACT(years FROM AGE(DATE('2025-10-25'), DATE('1977-10-26'))) AS years,
    EXTRACT(months FROM AGE(DATE('2025-10-25'), DATE('1977-10-26'))) AS months,
    EXTRACT(days FROM AGE(DATE('2025-10-25'), DATE('1977-10-26'))) AS days;

d) Adding Intervals

SELECT DATE('1977-10-26') + INTERVAL '48 years' AS new_age;
-- 2025-10-26

7. Time Math

a) Adding and Subtracting Time Intervals

SELECT TIME '05:00' + INTERVAL '3 hours' AS new_time; -- 08:00:00
SELECT TIME '05:00' - INTERVAL '3 hours' AS new_time; -- 02:00:00

b) Multiplying Time (Not Supported)

SELECT TIME '05:00' * 2;
-- ERROR (PostgreSQL does not support multiplying time directly)

c) Multiplying Intervals

SELECT INTERVAL '1 second' * 2000 AS interval_multiplied;
-- 00:33:20 (2000 seconds = 33 minutes 20 seconds)

Key Takeaways

Use current_timestamp or now() for both date & time.
Use date_trunc for rounding timestamps to larger units.
Use date_part or extract for numeric parts of dates.
Use to_char for formatting into strings.
Use age for interval differences (years, months, days).
Use interval arithmetic (+, , ) for both dates and times.

SQL for Data Analysis

CHAPTER 3: Time Series Analysis

PostgreSQL Date and Timestamp Functions - Study Guide

PostgreSQL Date, Timestamp, and Time Functions – Study Note

1. Getting Current Date and Time

2. Truncating Dates (date_trunc)

3. Extracting Parts of Dates

a) date_part()

b) extract()

4. Formatting Dates (to_char)

5. Creating Dates

6. Date Math

a) Subtracting Dates

b) Using age()

c) Extracting Components of age

d) Adding Intervals

7. Time Math

a) Adding and Subtracting Time Intervals

b) Multiplying Time (Not Supported)

c) Multiplying Intervals

Key Takeaways

2. Truncating Dates (`date_trunc`)

a) `date_part()`

b) `extract()`

4. Formatting Dates (`to_char`)

b) Using `age()`

c) Extracting Components of `age`