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
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_timestampandnow()→ Both date and time with time zonelocaltimestampandlocaltime→ 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 dateto_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_timestampornow()for both date & time. - Use
date_truncfor rounding timestamps to larger units. - Use
date_partorextractfor numeric parts of dates. - Use
to_charfor formatting into strings. - Use
agefor interval differences (years, months, days). - Use interval arithmetic (
+, , ) for both dates and times.