Date Functions

Karen Hovhannisyan

2026-04-02

Dates as Stories

Almost every real business question is, at its core, a story over time.

What happened before?
What changed after?
How fast did something grow?
When did behavior shift?
How often did something was happenng?

Dates Create Analytical Structure

A single timestamp can represent many analytical dimensions at once.

day
week
month
quarter
year
weekday vs weekend

Without explicitly extracting and structuring these dimensions:

trends disappear
seasonality remains hidden
comparisons become invalid

Dates turn events into patterns but only if we ask the right questions.

The Story We Will Follow

Throughout this session, we will use the sales_analysis table created earlier.

Each row represents:

one transaction
occurring at a specific point in time

Our goal is to answer progressively deeper analytical questions:

Each new date function will add structure to this story.

Sales Analysis Context

Remember to create or recreate the table using the following reference:

https://hovhannisyan91.github.io/aca/materials/sql/session4.html#creating-sales_analysis-table

Date vs Timestamp | The First Fork in the Road

Before using any date function, we must answer a fundamental question:

What kind of time do we have?

Date:
- Represents a calendar day only.
- no time of day
- no ordering within the day
- example: 2024-05-12
TIMESTAMP: Represents date + time.
- includes hours, minutes, seconds
- preserves event sequence
- example: 2024-05-12 14:37:22

Why This Distinction Matters

grouping behaves differently
comparisons behave differently
truncation behaves differently

Most analytical mistakes with dates begin right here.

In Our Dataset

In sales_analysis:

order_date_date → DATE
order_date → TIMESTAMP

We will intentionally use both to understand their analytical implications.

`EXTRACT()`

Dates contain multiple dimensions inside a single value \(\rightarrow\) Pulling Meaning from Dates

year
quarter
months
date
weekday

EXTRACT() allows us to isolate these components.

EXTRACT(field FROM date)

Example

SELECT
  order_date_date,
  EXTRACT(YEAR FROM order_date_date)  AS year,
  EXTRACT(MONTH FROM order_date_date) AS month,
  EXTRACT(DAY FROM order_date_date)   AS day, 
  EXTRACT(DOW FROM some_date) as weekday
FROM sales_analysis
LIMIT 5

\[\downarrow\]

order_date_date	year	month	day	weekday
2021-02-11	2021	2	11	4
2022-12-10	2022	12	10	6
2021-02-22	2021	2	22	1
2022-07-12	2022	7	12	2
2021-04-19	2021	4	19	1

`DATE_PART()`

DATE_PART() is functionally equivalent to EXTRACT().

DATE_PART('month', order_date_date)

Both return numeric components of a date.

When to Prefer Which

EXTRACT() → ANSI-standard, portable SQL
DATE_PART() → PostgreSQL-native, readable

In this course, we prefer EXTRACT() for consistency.

`DATE_TRUNC()`

In analytics, grain matters.

The same data can tell very different stories depending on how time is grouped.

DATE_TRUNC('unit', timestamp)

Example 1

SELECT
  DATE_TRUNC('month', order_date_date) AS month,
  SUM(total_sales) AS total_revenue
FROM sales_analysis
GROUP BY DATE_TRUNC('month', order_date_date)
ORDER BY month;

\[\downarrow\]

month	total_revenue
2020-01-01 00:00:00+00	38621.59
2020-02-01 00:00:00+00	30460.16
2020-04-01 00:00:00+00	18096.39
2020-05-01 00:00:00+00	23722.28
2020-06-01 00:00:00+00	42599.07

Example 2

SELECT
  DATE_TRUNC('month', order_date_date) AS month,
  SUM(total_sales) AS total_revenue
FROM sales_analysis
GROUP BY DATE_TRUNC('month', order_date_date)
ORDER BY SUM(total_sales) DESC;

\[\downarrow\]

month	total_revenue
2022-12-01 00:00:00+00	52510.67
2022-07-01 00:00:00+00	49011.10
2023-12-01 00:00:00+00	46700.47
2021-09-01 00:00:00+00	46433.23
2020-06-01 00:00:00+00	42599.07

Example 3

SELECT
  DATE_TRUNC('quarter', order_date_date) AS quarter,
  SUM(total_sales) AS total_revenue
FROM sales_analysis
GROUP BY DATE_TRUNC('quarter', order_date_date)
ORDER BY quarter;

\[\downarrow\]

quarter	total_revenue
2020-01-01 00:00:00+00	69081.75
2020-04-01 00:00:00+00	84417.74
2020-07-01 00:00:00+00	62003.53
2020-10-01 00:00:00+00	69127.16
2021-01-01 00:00:00+00	74184.68
2021-04-01 00:00:00+00	68431.42
2021-07-01 00:00:00+00	84194.98
2021-10-01 00:00:00+00	63543.31

Example 4

SELECT
  DATE_TRUNC('year', order_date_date) AS year,
  SUM(total_sales) AS total_revenue
FROM sales_analysis
GROUP BY DATE_TRUNC('year', order_date_date)
ORDER BY year;

\[\downarrow\]

year	total_revenue
2020-01-01 00:00:00+00	284630.18
2021-01-01 00:00:00+00	290354.39
2022-01-01 00:00:00+00	345714.03
2023-01-01 00:00:00+00	350319.25

Analytical Best Practices

always justify your chosen time grain
never mix different grains in the same comparison
re-aggregate data before drawing conclusions
validate insights by switching grains

DATE_TRUNC() does not change the data.
It changes how you see the data.

Working With the Present

`CURRENT_DATE`, `NOW()`

In analytics, we often need a reference point called now.

This allows us to answer questions such as:

how recent is this transaction?
how many days ago did something happen?
is this record outdated?

`NOW()`

If you try:

SELECT NOW();

type: TIMESTAMP WITH TIME ZONE
includes hours, minutes, seconds
ideal for sequence-sensitive logic

`CURRENT_DATE`

If you try:

SELECT NOW();

type: DATE
changes once per day
ideal for day-level comparisons

Measuring Time Gaps | Date Arithmetic

Dates become analytical only when we compare them.

PostgreSQL allows direct subtraction between dates.

Example | Days Since Order

SELECT
  order_date_date,
  CURRENT_DATE - order_date_date AS days_since_order
FROM sales_analysis
LIMIT 5;

\[\downarrow\]

order_date_date	days_since_order
2021-02-11	1797
2022-12-10	1130
2021-02-22	1786
2022-07-12	1281
2021-04-19	1730

Tip

subtraction of two DATEs returns an integer
the unit is days
ideal for:
- recency analysis
- churn logic
- aging reports

Months Between Dates | Naive Approach

SELECT
  order_date_date,
  (CURRENT_DATE - order_date_date) / 30 AS months_estimate
FROM sales_analysis
LIMIT 5;

\[\downarrow\]

order_date_date	months_estimate
2022-08-03	43
2020-10-10	65
2023-11-17	27
2023-05-25	33
2023-05-25	33

Warning

This introduces systematic bias.

Assumes every month has 30 days.

Months Between Dates | Approximation

SELECT
  (CURRENT_DATE - order_date_date) / 30.4375 AS approx_months
FROM sales_analysis
LIMIT 5;

\[ 30.4375 = \frac{365.25}{12} \]

\[\downarrow\]

approx_months
42.8090349075975359
64.5585215605749487
27.3347022587268994
33.1170431211498973
33.1170431211498973

Months Between Dates | Calendar-Safe

SELECT
  order_date_date,
    (DATE_PART('year', CURRENT_DATE) - DATE_PART('year', order_date_date)) * 12
  + (DATE_PART('month', CURRENT_DATE) - DATE_PART('month', order_date_date))
  - CASE
      WHEN DATE_PART('day', CURRENT_DATE)
        < DATE_PART('day', order_date_date)
      THEN 1 ELSE 0
  END AS full_months
FROM sales_analysis
ORDER BY order_date_date DESC
LIMIT 10;

\[\downarrow\]

order_date_date	full_months
2023-12-24	26
2023-12-24	26
2023-12-24	26
2023-12-24	26
2023-12-24	26
2023-12-24	26
2023-12-24	26
2023-12-24	26
2023-12-24	26
2023-12-24	26

INTERVAL | Expressing Duration

An INTERVAL represents a span of time, not a point.

Example | Orders in Last 30 Days

SELECT
  COUNT(*) AS recent_orders
FROM sales_analysis
WHERE order_date_date >= CURRENT_DATE - INTERVAL '1000 days';

\[\downarrow\]

recent_orders
636

Common INTERVAL Units

INTERVAL '7 days'
INTERVAL '1 month'
INTERVAL '1 year'

Date + INTERVAL | Shifting Time

some_date + INTERVAL '2 days'

some_date - INTERVAL '3 months'

some_date + INTERVAL '5 years'

Example | Simulated Future Date

SELECT
  order_date_date,
  order_date_date + INTERVAL '14 days' AS follow_up_date
FROM sales_analysis
LIMIT 5;

\[\downarrow\]

order_date_date	follow_up_date
2022-08-03	2022-08-17 00:00:00
2020-10-10	2020-10-24 00:00:00
2023-11-17	2023-12-01 00:00:00
2023-05-25	2023-06-08 00:00:00
2023-05-25	2023-06-08 00:00:00

AGE() | Calendar-Aware Difference

AGE() returns an INTERVAL representing true calendar distance.

Example | AGE Output

SELECT
  order_date_date,
  AGE(CURRENT_DATE, order_date_date) AS order_age
FROM sales_analysis
LIMIT 5;

\[\downarrow\]

order_date_date	order_age
2021-02-11	4 years 11 mons 2 days
2022-12-10	3 years 1 mon 3 days
2021-02-22	4 years 10 mons 19 days

AGE() | Extracting Components

SELECT
  order_date_date,
  EXTRACT(YEAR FROM AGE(CURRENT_DATE, order_date_date))  AS years,
  EXTRACT(MONTH FROM AGE(CURRENT_DATE, order_date_date)) AS months,
  EXTRACT(DAY FROM AGE(CURRENT_DATE, order_date_date))   AS days
FROM sales_analysis
LIMIT 5;

\[\downarrow\]

order_date_date	years	months	days
2022-08-03	3	6	23
2020-10-10	5	4	16
2023-11-17	2	3	9
2023-05-25	2	9	1
2023-05-25	2	9	1

AGE() Interpretation

extracted components are not independent
months reset after 12
days reset after month boundaries

Use AGE() for human interpretation,
numeric arithmetic for features and modeling.

Date Functions

Dates as Stories

Dates Create Analytical Structure

The Story We Will Follow

Date vs Timestamp | The First Fork in the Road

Why This Distinction Matters

EXTRACT()

EXTRACT()

Example

DATE_PART()

When to Prefer Which

DATE_TRUNC()

DATE_TRUNC()

Example 1

Example 2

Example 3

Example 4

Analytical Best Practices

Working With the Present

CURRENT_DATE, NOW()

NOW()

CURRENT_DATE

Measuring Time Gaps | Date Arithmetic

Example | Days Since Order

Months Between Dates | Naive Approach

Months Between Dates | Approximation

Months Between Dates | Calendar-Safe

INTERVAL | Expressing Duration

Example | Orders in Last 30 Days

Common INTERVAL Units

Date + INTERVAL | Shifting Time

Example | Simulated Future Date

AGE() | Calendar-Aware Difference

Example | AGE Output

AGE() | Extracting Components

`EXTRACT()`

`EXTRACT()`

`DATE_PART()`

`DATE_TRUNC()`

`DATE_TRUNC()`

`CURRENT_DATE`, `NOW()`

`NOW()`

`CURRENT_DATE`