CHAPTER 3: Time Series Analysis

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:

1. Date and timestamp functions
2. Truncating and extracting parts of dates
3. Formatting dates
4. Date math (differences, age, intervals)
5. 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:

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:

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.